diff options
Diffstat (limited to 'kubernetes/docs/_queries/postgres.md')
| -rw-r--r-- | kubernetes/docs/_queries/postgres.md | 301 |
1 files changed, 301 insertions, 0 deletions
diff --git a/kubernetes/docs/_queries/postgres.md b/kubernetes/docs/_queries/postgres.md new file mode 100644 index 0000000..13728f6 --- /dev/null +++ b/kubernetes/docs/_queries/postgres.md @@ -0,0 +1,301 @@ +--- +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. + +### 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. + +#### 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; +``` |