List sizes of tables in current schema.
SELECT
table_name AS table_name,
pg_size_pretty(pg_total_relation_size(quote_ident(table_name))) AS total_size,
pg_size_pretty(pg_table_size(quote_ident(table_name))) AS table_size,
pg_size_pretty(pg_indexes_size(quote_ident(table_name))) AS indexes_size
FROM
information_schema.tables
WHERE
table_schema = current_schema()
ORDER BY
pg_total_relation_size(quote_ident(table_name)) DESC;
Uses:
current_schema
pg_indexes_size
pg_size_pretty
pg_table_size
pg_total_relation_size
quote_ident
tables
SELECT
n.nspname AS namespace_name,
t.relname AS table_name,
pg_size_pretty(pg_relation_size(t.oid)) AS table_size,
t.reltuples::bigint AS num_rows,
psai.indexrelname AS index_name,
pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
CASE WHEN i.indisunique THEN 'Y' ELSE 'N' END AS "unique",
psai.idx_scan AS number_of_scans,
psai.idx_tup_read AS tuples_read,
psai.idx_tup_fetch AS tuples_fetched
FROM
pg_index i
INNER JOIN pg_class t ON t.oid = i.indrelid
INNER JOIN pg_namespace n ON n.oid = t.relnamespace
INNER JOIN pg_stat_all_indexes psai ON i.indexrelid = psai.indexrelid
WHERE
n.nspname = current_schema()
ORDER BY 1, 2, 5;
SELECT
now()::TIME(0),
a.query,
p.phase,
round(p.blocks_done / p.blocks_total::numeric * 100, 2) AS "% done",
p.blocks_total,
p.blocks_done,
p.tuples_total,
p.tuples_done,
ai.schemaname,
ai.relname,
ai.indexrelname
FROM pg_stat_progress_create_index p
JOIN pg_stat_activity a ON p.pid = a.pid
LEFT JOIN pg_stat_all_indexes ai on ai.relid = p.relid AND ai.indexrelid = p.index_relid;
SELECT
a.application_name AS app,
a.query AS query,
l.locktype AS locktype,
l.mode AS mode,
l.granted AS granted,
r.relname AS relation
FROM
pg_catalog.pg_locks AS l
JOIN pg_catalog.pg_stat_activity AS a
ON a.pid = l.pid
LEFT OUTER JOIN pg_catalog.pg_class AS r
ON r.oid = l.relation
WHERE
a.pid != pg_backend_pid();