PostgreSQL Maintenance Commands

postgesql_cmds.md

General Commands

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';

DB Maintenance Commands

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

VACUUM 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;

Tuple Level Statistics

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');

Indexes

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+

Other Topics

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;

Config

Show path to config file

show config_file;