--- layout: default title: PostgreSQL --- # 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 ```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 ```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 ```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. ```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 ```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 ```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 ```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 ```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](https://wiki.postgresql.org/wiki/Main_Page) ### CLUSTER-ing [CLUSTER](https://www.postgresql.org/docs/current/sql-cluster.html) ```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](https://www.postgresql.org/docs/current/app-clusterdb.html) 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](https://wiki.postgresql.org/wiki/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](https://www.postgresql.org/docs/current/app-vacuumdb.html) CLI tool is recommended for manual runs. #### Finding number of dead rows ```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 ```sql SELECT relname, last_vacuum, last_autovacuum FROM pg_stat_user_tables; ``` #### Checking auto-vacuum is enabled ```sql SELECT name, setting FROM pg_settings WHERE name='autovacuum'; ``` #### View all auto-vacuum setting ```sql SELECT * from pg_settings where category like 'Autovacuum'; ``` ## Locks ### Looking at granted locks ```sql SELECT relation::regclass, * FROM pg_locks WHERE NOT granted; ``` ### Сombination of blocked and blocking activity ```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; ```