PostgreSQL queries ================== Disk usage ---------- Most of these queries vary based on the database you are connected to. General Table Size Information Grouped For Partitioned Tables ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ .. code:: sql WITH RECURSIVE pg_inherit(inhrelid, inhparent) AS (select inhrelid, inhparent FROM pg_inherits UNION SELECT child.inhrelid, parent.inhparent FROM pg_inherit child, pg_inherits parent WHERE child.inhparent = parent.inhrelid), pg_inherit_short AS (SELECT * FROM pg_inherit WHERE inhparent NOT IN (SELECT inhrelid FROM pg_inherit)) SELECT table_schema , TABLE_NAME , row_estimate , pg_size_pretty(total_bytes) AS total , pg_size_pretty(index_bytes) AS INDEX , pg_size_pretty(toast_bytes) AS toast , pg_size_pretty(table_bytes) AS TABLE FROM ( SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM ( SELECT c.oid , nspname AS table_schema , relname AS TABLE_NAME , SUM(c.reltuples) OVER (partition BY parent) AS row_estimate , SUM(pg_total_relation_size(c.oid)) OVER (partition BY parent) AS total_bytes , SUM(pg_indexes_size(c.oid)) OVER (partition BY parent) AS index_bytes , SUM(pg_total_relation_size(reltoastrelid)) OVER (partition BY parent) AS toast_bytes , parent FROM ( SELECT pg_class.oid , reltuples , relname , relnamespace , pg_class.reltoastrelid , COALESCE(inhparent, pg_class.oid) parent FROM pg_class LEFT JOIN pg_inherit_short ON inhrelid = oid WHERE relkind IN ('r', 'p') ) c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace ) a WHERE oid = parent ) a ORDER BY total_bytes DESC; General Table Size Information ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ .. code:: sql SELECT *, pg_size_pretty(total_bytes) AS total , pg_size_pretty(index_bytes) AS index , pg_size_pretty(toast_bytes) AS toast , pg_size_pretty(table_bytes) AS table FROM ( SELECT *, total_bytes-index_bytes-coalesce(toast_bytes,0) AS table_bytes FROM ( SELECT c.oid,nspname AS table_schema, relname AS table_name , c.reltuples AS row_estimate , pg_total_relation_size(c.oid) AS total_bytes , pg_indexes_size(c.oid) AS index_bytes , pg_total_relation_size(reltoastrelid) AS toast_bytes FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace WHERE relkind = 'r' ) a ) a; Finding the largest databases in your cluster ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ .. code:: sql SELECT d.datname as Name, pg_catalog.pg_get_userbyid(d.datdba) as Owner, CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT') THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname)) ELSE 'No Access' END as Size FROM pg_catalog.pg_database d order by CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT') THEN pg_catalog.pg_database_size(d.datname) ELSE NULL END desc -- nulls first LIMIT 20; Finding the size of your biggest relations ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Relations are objects in the database such as tables and indexes, and this query shows the size of all the individual parts. .. code:: sql SELECT nspname || '.' || relname AS "relation", pg_size_pretty(pg_relation_size(C.oid)) AS "size" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN ('pg_catalog', 'information_schema') ORDER BY pg_relation_size(C.oid) DESC LIMIT 20; Finding the total size of your biggest tables ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ .. code:: sql SELECT nspname || '.' || relname AS "relation", pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN ('pg_catalog', 'information_schema') AND C.relkind <> 'i' AND nspname !~ '^pg_toast' ORDER BY pg_total_relation_size(C.oid) DESC LIMIT 20; Indexes ------- Index summary ~~~~~~~~~~~~~ .. code:: sql SELECT pg_class.relname, pg_size_pretty(pg_class.reltuples::bigint) AS rows_in_bytes, pg_class.reltuples AS num_rows, count(indexname) AS number_of_indexes, CASE WHEN x.is_unique = 1 THEN 'Y' ELSE 'N' END AS UNIQUE, SUM(case WHEN number_of_columns = 1 THEN 1 ELSE 0 END) AS single_column, SUM(case WHEN number_of_columns IS NULL THEN 0 WHEN number_of_columns = 1 THEN 0 ELSE 1 END) AS multi_column FROM pg_namespace LEFT OUTER JOIN pg_class ON pg_namespace.oid = pg_class.relnamespace LEFT OUTER JOIN (SELECT indrelid, max(CAST(indisunique AS integer)) AS is_unique FROM pg_index GROUP BY indrelid) x ON pg_class.oid = x.indrelid LEFT OUTER JOIN ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns FROM pg_index x JOIN pg_class c ON c.oid = x.indrelid JOIN pg_class ipg ON ipg.oid = x.indexrelid ) AS foo ON pg_class.relname = foo.ctablename WHERE pg_namespace.nspname='public' AND pg_class.relkind = 'r' GROUP BY pg_class.relname, pg_class.reltuples, x.is_unique ORDER BY 2; Index size/usage statistics ~~~~~~~~~~~~~~~~~~~~~~~~~~~ .. code:: sql SELECT t.schemaname, t.tablename, indexname, c.reltuples AS num_rows, pg_size_pretty(pg_relation_size(quote_ident(t.schemaname)::text || '.' || quote_ident(t.tablename)::text)) AS table_size, pg_size_pretty(pg_relation_size(quote_ident(t.schemaname)::text || '.' || quote_ident(indexrelname)::text)) AS index_size, CASE WHEN indisunique THEN 'Y' ELSE 'N' END AS UNIQUE, number_of_scans, tuples_read, tuples_fetched FROM pg_tables t LEFT OUTER JOIN pg_class c ON t.tablename = c.relname LEFT OUTER JOIN ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan AS number_of_scans, idx_tup_read AS tuples_read, idx_tup_fetch AS tuples_fetched, indexrelname, indisunique, schemaname FROM pg_index x JOIN pg_class c ON c.oid = x.indrelid JOIN pg_class ipg ON ipg.oid = x.indexrelid JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid ) AS foo ON t.tablename = foo.ctablename AND t.schemaname = foo.schemaname WHERE t.schemaname NOT IN ('pg_catalog', 'information_schema') ORDER BY 1,2; Duplicate indexes ~~~~~~~~~~~~~~~~~ .. code:: sql SELECT pg_size_pretty(sum(pg_relation_size(idx))::bigint) as size, (array_agg(idx))[1] as idx1, (array_agg(idx))[2] as idx2, (array_agg(idx))[3] as idx3, (array_agg(idx))[4] as idx4 FROM ( SELECT indexrelid::regclass as idx, (indrelid::text ||E'\n'|| indclass::text ||E'\n'|| indkey::text ||E'\n'|| coalesce(indexprs::text,'')||E'\n' || coalesce(indpred::text,'')) as key FROM pg_index) sub GROUP BY key HAVING count(*)>1 ORDER BY sum(pg_relation_size(idx)) DESC; Maintenance ----------- `PostgreSQL wiki `__ CLUSTER-ing ~~~~~~~~~~~ `CLUSTER `__ .. code:: sql CLUSTER [VERBOSE] table_name [ USING index_name ] CLUSTER [VERBOSE] ``CLUSTER`` instructs PostgreSQL to cluster the table specified by ``table_name`` based on the index specified by ``index_name``. The index must already have been defined on ``table_name``. When a table is clustered, it is physically reordered based on the index information. The `clusterdb `__ CLI tool is recommended, and can also be used to cluster all tables at the same time. VACUUM-ing ~~~~~~~~~~ Proper vacuuming, particularly autovacuum configuration, is crucial to a fast and reliable database. `Introduction to VACUUM, ANALYZE, EXPLAIN, and COUNT `__ It is not advised to run ``VACUUM FULL``, instead look at clustering. VACUUM FULL is a much more intensive task and acquires an ACCESS EXCLUSIVE lock on the table, blocking reads and writes. Whilst ``CLUSTER`` also does acquire this lock it’s a less intensive and faster process. The `vacuumdb `__ CLI tool is recommended for manual runs. Finding number of dead rows ^^^^^^^^^^^^^^^^^^^^^^^^^^^ .. code:: sql SELECT relname, n_dead_tup FROM pg_stat_user_tables WHERE n_dead_tup <> 0 ORDER BY 2 DESC; Finding last vacuum/auto-vacuum date ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ .. code:: sql SELECT relname, last_vacuum, last_autovacuum FROM pg_stat_user_tables; Checking auto-vacuum is enabled ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ .. code:: sql SELECT name, setting FROM pg_settings WHERE name='autovacuum'; View all auto-vacuum setting ^^^^^^^^^^^^^^^^^^^^^^^^^^^^ .. code:: sql SELECT * from pg_settings where category like 'Autovacuum'; Locks ----- Looking at granted locks ~~~~~~~~~~~~~~~~~~~~~~~~ .. code:: sql SELECT relation::regclass, * FROM pg_locks WHERE NOT granted; Сombination of blocked and blocking activity ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ .. code:: sql SELECT blocked_locks.pid AS blocked_pid, blocked_activity.usename AS blocked_user, blocking_locks.pid AS blocking_pid, blocking_activity.usename AS blocking_user, blocked_activity.query AS blocked_statement, blocking_activity.query AS current_statement_in_blocking_process FROM pg_catalog.pg_locks blocked_locks JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid AND blocking_locks.pid != blocked_locks.pid JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid WHERE NOT blocked_locks.granted;