l0stmyself
10/10/2019 - 11:09 AM

Postgresql Table Commands

--Get size of a table
SELECT pg_size_pretty( pg_relation_size('<tablename>'));

--Get 10 largest tables
SELECT table_name,pg_relation_size(table_schema || '.' || table_name) as size
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'pg_catalog')
ORDER BY size DESC
LIMIT 10;

--Estimate the number of rows in a large table
SELECT (CASE WHEN reltuples > 0 THEN pg_relation_size(oid)*reltuples/(8192*relpages) 
ELSE 0
END)::bigint AS estimated_row_count
FROM pg_class
WHERE oid = 'mytable'::regclass;

--Get sizes of all indexes in the database
\di+

--Cluster a table using an index
CLUSTER t_random USING idx_random;