Count Estimates
SELECT
reltuples AS ESTIMATE
FROM
pg_class
WHERE
relname = 'table_name';Check size of Database
SELECT pg_size_pretty(pg_database_size('db_name'));Check size of table
SELECT pg_size_pretty(pg_relation_size('table_name'));
-- Alternatively, if you are in psql
\dt+ table_name;Show Triggers
SELECT * FROM information_schema.triggers;Change sequence ownership
ALTER SEQUENCE table_name_id_seq OWNER BY new_table_name.id;Describe Table
-- in psql, one can use
\d table_name;-- if you are not in psql, use this.
SELECT
*
FROM
information_schema.columns
WHERE
table_name = 'table_name';See Constraints on a table
SELECT
*
FROM
information_schema.constraint_table_usage
WHERE
table_name = 'table_name'Also
SELECT
tc.table_schema,
tc.constraint_name,
tc.table_name,
kcu.column_name,
ccu.table_schema AS foreign_table_schema,
ccu.table_name AS foreign_table_name,
ccu.column_name AS foreign_column_name
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
ON tc.constraint_name = kcu.constraint_name
AND tc.table_schema = kcu.table_schema
JOIN information_schema.constraint_column_usage AS ccu
ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
AND tc.table_schema='public'
AND tc.table_name='table_name';Create a CSV file from a SELECT
-- inside psql
\copy (SELECT * FROM users WHERE id = 6) TO '/tmp/user_data.csv' DELIMITER ',' CSV HEADER;Analyze table
ANALYZE table_name;Vacuum table * good to use VACUUM VERBOSE ANALYZE
VACUUM VERBOSE ANALYZE table_name;VACUUM FULL table_name;Find Last Auto-Vacuum, Vacuum, Auto-Analyzed Analyzed Date
SELECT
RELNAME,
LAST_VACUUM,
LAST_AUTOVACUUM,
LAST_ANALYZE,
LAST_AUTOANALYZE
FROM
pg_stat_user_tables;Confirm that you can install pgstattuple
SELECT
*
FROM
pg_available_extensions
WHERE
name = 'pgstattuple';Install pgstattuple
CREATE EXTENSION pgstattuple;Obtain tuple level statistics
SELECT
*
FROM
pgstattuple('schema_name.table_name');Show indexes on a table
SELECT
indexname,
indexdef
FROM
pg_indexes
WHERE
tablename = 'table_name';Check size of index
Select pg_size_pretty(pg_relation_size('index_name'));Show information about a B-Tree index
SELECT
*
FROM
pgstatindex('schema_name.index_name');Show information about a gin index
SELECT * FROM pgstatginindex('my_gin_index');See the pending list limit on a gin index
SHOW gin_pending_list_limit;Clean up the pending list on a gin index
select gin_clean_pending_list('my_gin_index');See all indexes on your db / table
select
t.relname as table_name,
i.relname as index_name,
a.attname as column_name
from
pg_class t,
pg_class i,
pg_index ix,
pg_attribute a
where
t.oid = ix.indrelid
and i.oid = ix.indexrelid
and a.attrelid = t.oid
and a.attnum = ANY(ix.indkey)
and t.relkind = 'r'
--and t.relname like 'table_name'
order by
t.relname,
i.relname;See how much space an index occupies
-- inside psql
\di+See slow running queries
SELECT
pid,
user,
pg_stat_activity.query_start,
now() - pg_stat_activity.query_start AS query_time,
query,
state,
wait_event_type,
wait_event
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';Find blocked processes and blocking queries
SELECT
activity.pid,
activity.usename,
activity.query,
blocking.pid AS blocking_id,
blocking.query AS blocking_query
FROM pg_stat_activity AS activity
JOIN pg_stat_activity AS blocking ON blocking.pid = ANY(pg_blocking_pids(activity.pid));List and order tables by size
SELECT
table_name,
pg_size_pretty(pg_total_relation_size(quote_ident(table_name))),
pg_total_relation_size(quote_ident(table_name))
FROM
information_schema.tables
WHERE
table_schema = 'public'
ORDER BY 3 desc;
-- the order by could also be written as
-- ORDER BY pg_total_relation_size(quote_ident(table_name)) DESC;Show path to config file
show config_file;Check that replication is active on the master
select * from pg_stat_replication;Check that the slave node is receiving
select * from pg_stat_wal_receiver;