Postgres Database Performance Tuning

Following steps will be helpful to improve the performance of Postgres database :
First  you have to issue the top command then see the process which one process is taking more CPU utilization then note that PID, if that  is postgres process means use this script you can find out query and their status and timing else you can use following query to finding current running query

SELECT now() - query_start as "runtime",state,query from pg_stat_activity WHERE pid= 32696;
    runtime     | state  |                                                                       query
----------------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------
 00:00:01.93486 | active | select   timestatus::date from mhrornad_akl.edit_mut_new_audit WHERE ccode='270100010000760000' and report_status='Generated' and edit_mut_no=214

Postgresql query tuning Type:

Check the query whether DRL (select) type or modified(DML -insert,update,delete) type ,if modified (DML) query means check the table whether fragmented or not and don’t kill the DML and DDL query if you killed this type of query means you loss your transaction.

To checking​ long running queries and find out table which one query is running more than  else use script for long running query

postgres=# select datname,pid,query_start,state,query from pg_stat_activity ;
postgres=# select datname,pid,query_start,state,query from pg_stat_activity where query like '%copy%';                          

if that query is DRL (select) means check the query status if status is active wait for some time if that query is running more than threshold period means take the explain plan for that query ,from this explain plan you can check the index used or not
If sequential scans:
check table having any index or not If index is not suggest to create index for that table,you can use \dt table_nameor Following query to finding table having index or not

postgres=# select relname,indexrelname,indexrelid from pg_stat_all_indexes where indexrelid=24700;
 relname | indexrelname | indexrelid 
---------+--------------+------------
 k       | production   |      24700

If table having index but not using index means check the index status valid  nor not, you can use following query to finding the index status

postgres=# select indexrelid,indislive,indisvalid from pg_index where indexrelid=24700;
 indexrelid | indislive | indisvalid 
------------+-----------+------------
      24700 | t         | t
(1 row)

Here index status is true so index is valid  if query not using index means you have to use HINTS like

SET enable_nestloop = off;
select * from emp_table where id=15045;

Otherwise index is not valid and index status is false means you have  rebuilt the index using  rebuild command or schedule script for vacuum , analyze and reindexdb

REINDEX INDEX myindex

If index scans:
query is taking index scan but it is running more than threshold period means check table dead tubles as well as last analyze using  following query you can check the table dead tubles and last analyze of the table.

postgres=# select relname,last_vacuum,n_dead_tup,last_analyze from pg_stat_all_tables where relname='k';
 relname | last_vacuum | n_dead_tup | last_analyze 
---------+-------------+------------+--------------
 k       |             |       8192 |

Note: Here dead tuble is 8192  and you did not analyze the table from the table creation so you have to clear this dead tubles, for clearing dead tuble you can use vacuum

Analyze and vacuum the table:
it will helpful for update the current status to optimizer

postgres=# analyze  k;     
ANALYZE
postgres=# vacuum k ;
VACUUM

Killing Query: 
if that query is running more than threshold period and taking more CPU utilization as well as server is getting slow means  kill that query using following query or use script for killing long running query 

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid=32696;

Note: if query is taking more CPU utilization you can check the CPU utilization using top command Before killing query, Before killing  you have to get approval from your organization if query status is active, if query status is idle means you can directly kill without permission.
Before  killing long running query  you have to understand the query status 

Script For Postgres Server Tuning:
Then Implement the killing idle connection script if your server getting more idle connection Then implement the drop Cache script if you have root access its helpful for clearing cache memory(RAM Buffer)

Monitoring Script For Postgres Server:
you can check your postgresql server status using monitoring script this script will be helpful for monitoring disk and ram usage as well as how many table having more than 5000 dead tubles in a postgres database.

Basic Linux server monitoring commands