f the checkpoints_req is more than the checkpoints_timed, PostgreSQL is doing checkpoints due to the high WAL generation. <\/strong><\/p>\nIf the checkpoints are happening frequently it will cause more IO load on the machine so increase the max_wal_size parameter.<\/p>\n
Use below query to find the frequency of the checkpoints<\/p>\n
WITH sub as (\n SELECT \n EXTRACT(\n EPOCH \n FROM \n (now() - stats_reset)\n ) AS seconds_since_start, \n (\n checkpoints_timed + checkpoints_req\n ) AS total_checkpoints \n FROM \n pg_stat_bgwriter\n) \nSELECT \n total_checkpoints, \n seconds_since_start \/ total_checkpoints \/ 60 AS minutes_between_checkpoints \nFROM \n sub;<\/pre>\nTo determine how much data is being written for each <\/span>checkpoint<\/span><\/strong>, set <\/span>log_checkpoints<\/span><\/strong>. If not, we may use this query to determine how many blocks were written during the checkpoint.<\/span><\/p>\nselect buffers_checkpoint\/(checkpoints_timed+checkpoints_req) from pg_stat_bgwriter<\/pre>\nGet top five tables with highest sequential scans?<\/strong><\/h2>\nSELECT \n schemaname, \n relname, \n seq_scan, \n seq_tup_read, \n seq_tup_read \/ seq_scan as avg_seq_tup_read \nFROM \n pg_stat_all_tables \nWHERE \n seq_scan > 0 \n and pg_total_relation_size(schemaname || '.' || relname) > 104857600 \n and schemaname not like 'pg_%' \nORDER BY \n 5 DESC \nLIMIT \n 5;<\/pre>\nThis query will display all tables with more consecutive scans and a size greater than 100MB.<\/p>\n
Find tables with low hot updates?<\/strong><\/h2>\nTo know the hot updates in PostgreSQL refer – https:\/\/www.postgresql.org\/docs\/current\/storage-hot.html.<\/p>\n
To identify the hot update ratio for tables use the below query.<\/p>\n
SELECT \n pg_stat_all_tables.schemaname || '.' || pg_stat_all_tables.relname AS TABLE_NAME, \n pg_size_pretty(\n pg_relation_size(relid)\n ) AS table_size, \n coalesce(\n t.spcname, \n (\n SELECT \n spcname \n FROM \n pg_tablespace \n WHERE \n oid = (\n SELECT \n dattablespace \n FROM \n pg_database \n WHERE \n datname = current_database()\n )\n )\n ) AS tblsp, \n seq_scan, \n idx_scan, \n n_tup_ins, \n n_tup_upd, \n n_tup_hot_upd, \n n_tup_del, \n coalesce(n_tup_ins, 0)+ 2 * coalesce(n_tup_upd, 0)- coalesce(n_tup_hot_upd, 0)+ coalesce(n_tup_del, 0) AS total, \n (\n coalesce(n_tup_hot_upd, 0):: float * 100 \/(\n CASE WHEN n_tup_upd > 0 THEN n_tup_upd ELSE 1 END\n ):: float\n ):: numeric(10, 2) AS hot_rate, \n (\n SELECT \n v[1] \n FROM \n regexp_matches(\n reloptions :: text, E 'fillfactor=(\\d+)'\n ) AS r(v) \n LIMIT \n 1\n ) AS fillfactor \nFROM \n pg_stat_all_tables \n JOIN pg_class c ON c.oid = relid \n LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace \nWHERE \n (\n coalesce(n_tup_ins, 0)+ coalesce(n_tup_upd, 0)+ coalesce(n_tup_del, 0)\n )> 0 \n AND pg_stat_all_tables.schemaname NOT IN ('pg_catalog', 'pg_global') \nORDER BY \n total DESC \nLIMIT \n 50;<\/pre>\n