Postgresql list and order tables by size

Diving deeper: Dissecting table sizes

A comprehensive understanding of table size is crucial for efficient database management. Let's unpack it.

Table size: data vs index

For a closer look at your data and index sizes, we have our handy dandy pg_relation_size() and pg_indexes_size() functions:

SELECT 
    tbl.relname AS "Table",
    pg_size_pretty(pg_relation_size(tbl.oid)) AS "Data Size", -- Just the table, not the parsley (indexes)
    pg_size_pretty(pg_indexes_size(tbl.oid)) AS "Index Size" -- All about that indexing bass
FROM 
    pg_class tbl
WHERE 
    tbl.relkind = 'r'
ORDER BY 
    pg_total_relation_size(tbl.oid) DESC;


Including TOAST tables in the mix

PostgreSQL utilizes "TOAST" (The Oversized-Attribute Storage Technique) tables to handle larger attribute values. To consider these in your total size accounting:

SELECT 
    tbl.relname AS "Table",
    pg_size_pretty(pg_total_relation_size(tbl.oid) - pg_relation_size(tbl.oid)) AS "Voluptuous Extras" -- TOAST and indexes included, extra thick!
FROM 
    pg_class tbl
WHERE 
    tbl.relkind = 'r'
ORDER BY 
    pg_total_relation_size(tbl.oid) DESC;


This includes TOAST tables and indexes!

Filtering by schema

Some PostgreSQL databases play host to multiple schemas. Here's how you can peer into a particular one:

SELECT 
    ns.nspname AS "Schema",
    tbl.relname AS "Table",
    pg_size_pretty(pg_total_relation_size(tbl.oid)) AS "Size"
FROM 
    pg_class tbl
JOIN 
    pg_namespace ns ON ns.oid = tbl.relnamespace
WHERE 
    tbl.relkind = 'r' 
    AND ns.nspname = 'public' -- Replace with your schema of interest
ORDER BY 
    pg_total_relation_size(tbl.oid) DESC;


Excluding system tables is as simple as avoiding schemas with the pg_ prefix.

Bulletproofing your queries

Finally, let's enhance your SQL toolkit by adding some protective measures and convenience features.

Prevent SQL injection and syntax issues

Using quote_ident() helps mitigate SQL injection hazards and syntax issues:

SELECT 
    quote_ident(tbl.relname) AS "Table",
    pg_size_pretty(pg_total_relation_size(tbl.oid)) AS "Size"
FROM 
    pg_class tbl
WHERE 
    tbl.relkind = 'r'
ORDER BY 
    pg_total_relation_size(tbl.oid) DESC;


Stay safe out there, folks; noir detectives teach us always to quote_ident() your sources! 🔎

Query adjustments on-the-fly

To adapt your query to different environments, leverage current_schema() and current_database(). It's like having GPS for your skyline!

SELECT 
    nspname AS "Schema",
    relname AS "Table",
    pg_size_pretty(pg_total_relation_size(tbl.oid)) AS "Size"
FROM 
    pg_class tbl
INNER JOIN 
    pg_namespace ns ON ns.oid = tbl.relnamespace
WHERE 
    relkind = 'r'
    AND nspname NOT LIKE 'pg_%' -- System schemas? Not today!
    AND nspname = current_schema() -- Your schema, sir/madam/non-binary monarch
ORDER BY 
    pg_total_relation_size(tbl.oid) DESC; 


How's that for a personalized city tour?

Measure database coastline to coastline

pg_database_size() gives you the size of an entire database. Talk about a birds-eye view!

SELECT 
    pg_size_pretty(pg_database_size(current_database())) AS "Total Database Size";


Heads up: PostgreSQL's pretty-size notation switches units at 10+ (e.g., 10240 bytes becomes 10 kB).