PostgreSQL queries
Disk usage
Most of these queries vary based on the database you are connected to.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
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 ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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
1
2
3
4
5
6
7
8
9
10
11
12
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.
1
2
3
4
5
6
7
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
1
2
3
4
5
6
7
8
9
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
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
1
2
3
4
5
6
7
8
9
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
1
2
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
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
1
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
1
SELECT relname , last_vacuum , last_autovacuum FROM pg_stat_user_tables ;
Checking auto-vacuum is enabled
1
SELECT name , setting FROM pg_settings WHERE name = 'autovacuum' ;
View all auto-vacuum setting
1
SELECT * from pg_settings where category like 'Autovacuum' ;
Locks
Looking at granted locks
1
SELECT relation :: regclass , * FROM pg_locks WHERE NOT granted ;
Сombination of blocked and blocking activity
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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 ;