How to Check Table Size in PostgreSQL

How do I check table size in PostgreSQL?

Use built-in size functions (pg_total_relation_size, pg_relation_size) to see how much disk space a table and its indexes consume.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What is the quickest command to see one table’s size?

Execute SELECT pg_size_pretty(pg_total_relation_size('schema.table_name'));. The result is the total on-disk size, including data, indexes, and TOAST.

How do I view data size versus index size?

Call both functions:
SELECT pg_size_pretty(pg_relation_size('schema.table_name')) AS data_size, pg_size_pretty(pg_indexes_size('schema.table_name')) AS index_size;

Can I list every table ordered by size?

Yes—run:
SELECT relname AS table, pg_size_pretty(pg_total_relation_size(oid)) AS total, pg_size_pretty(pg_relation_size(oid)) AS data, pg_size_pretty(pg_indexes_size(oid)) AS indexesFROM pg_classWHERE relkind = 'r'ORDER BY pg_total_relation_size(oid) DESC;

What do the size functions return?

pg_relation_size() = table heap only. pg_indexes_size() = all indexes. pg_total_relation_size() = heap + indexes + TOAST. All return bigint bytes; wrap with pg_size_pretty() for readability.

Best practices for monitoring table growth

• Schedule the above query in cron or a monitoring tool.
• Alert when size jumps unexpectedly.
• Vacuum & analyze large tables regularly.
• Partition or archive when growth exceeds retention policies.

Why might my table appear smaller after vacuum full?

VACUUM FULL rewrites the table, removing dead tuples and shrinking on-disk size. Measure again afterward to confirm reclaimed space.

Example: human-friendly size report

SELECT table_schema || '.' || table_name AS table, pg_size_pretty(pg_total_relation_size(table_schema || '.' || table_name)) AS totalFROM information_schema.tablesWHERE table_type = 'BASE TABLE'ORDER BY pg_total_relation_size(table_schema || '.' || table_name) DESCLIMIT 10;

Why How to Check Table Size in PostgreSQL is important

How to Check Table Size in PostgreSQL Example Usage


SELECT relname                                   AS table,
       pg_size_pretty(pg_total_relation_size(oid)) AS total,
       pg_size_pretty(pg_relation_size(oid))       AS data,
       pg_size_pretty(pg_indexes_size(oid))        AS indexes
FROM   pg_class
WHERE  relkind = 'r'
ORDER  BY pg_total_relation_size(oid) DESC
LIMIT  5;

How to Check Table Size in PostgreSQL Syntax


-- Single table (total size)
SELECT pg_size_pretty(pg_total_relation_size('schema.table_name'));

-- Single table (data vs indexes)
SELECT pg_size_pretty(pg_relation_size('schema.table_name'))  AS data_size,
       pg_size_pretty(pg_indexes_size('schema.table_name'))  AS index_size;

-- All tables by size
SELECT relname, pg_total_relation_size(oid)
FROM   pg_class
WHERE  relkind = 'r';

Common Mistakes

Using pg_relation_size alone when total size is needed. Fix: call pg_total_relation_size so indexes and TOAST are included.
Quoting the table name incorrectly in functions. Fix: wrap identifiers in single quotes and schema-qualify: 'public.orders'.

Frequently Asked Questions (FAQs)

Does pg_total_relation_size lock the table?

No. These size functions are read-only and acquire only lightweight locks, so they do not block writes.

Can I see database-wide size instead?

Yes. Run SELECT pg_size_pretty(pg_database_size(current_database())); to view the total size of the current database.

Is autovacuum enough to control table bloat?

Usually, but high-churn tables may still accumulate bloat. Monitor size trends and schedule manual VACUUM FULL or partitioning if growth is excessive.