Get Table Size in Postgres: Query & Examples

To get the size of a table in PostgreSQL, you use the pg_total_relation_size() function, which returns the total disk space used by the table including indexes and toast data. For the table data alone, use pg_relation_size(), and for a human-readable format, wrap either in pg_size_pretty().

Formula

The core calculation is not a mathematical formula but a call to PostgreSQL’s built-in system functions. The total size of a table is the sum of the table’s main data fork, any associated free space map, visibility map, and TOAST (The Oversized-Attribute Storage Technique) data, plus the size of all indexes on that table. In SQL terms:

SELECT pg_total_relation_size('table_name');

This returns the size in bytes. The pg_relation_size('table_name') function returns only the table’s main data fork (excluding indexes and TOAST). The pg_indexes_size('table_name') function returns the total size of all indexes on the table. The pg_size_pretty() function converts a byte count into a human-readable format like 123 kB or 45 MB.

Worked Example

Using the default values from the calculator above, suppose we have a table named my_table. To get its total size including indexes, we run:

SELECT pg_size_pretty(pg_total_relation_size('my_table'));

If the function returns 8192 bytes (which is one 8 kB page), the output will be 8192 bytes (the pretty formatter leaves small values as bytes). The calculator shows the raw byte count as the headline answer, which for the default is 8192 bytes. This matches the SQL output exactly.

Why You Need Table Sizes

Knowing the size of your tables is essential for database maintenance, capacity planning, and performance tuning. A table that has grown unexpectedly large may indicate a data retention issue, a runaway process, or simply a need for partitioning. Sizes also help you decide which tables to vacuum more frequently, which to move to slower storage, and whether an index is worth its disk cost.

How to Query Table Sizes

There are several ways to get table sizes, depending on whether you want a single table or all tables at once.

• Single table, total size (including indexes): SELECT pg_size_pretty(pg_total_relation_size('my_table'));
• Single table, data only: SELECT pg_size_pretty(pg_relation_size('my_table'));
• Single table, indexes only: SELECT pg_size_pretty(pg_indexes_size('my_table'));
• All tables in a schema, sorted by size: SELECT relname AS table_name, pg_size_pretty(pg_total_relation_size(relid)) AS total_size FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;

The pg_statio_user_tables view lists all user tables in the current database. You can also use pg_class with pg_namespace for more control.

Common Mistakes

One frequent error is forgetting to include the schema name when the table is not in the public schema. For example, pg_total_relation_size('my_schema.my_table') is correct, while 'my_table' alone may fail or return the wrong table if multiple schemas have a table with that name.

Another mistake is confusing pg_total_relation_size with pg_relation_size. The former includes indexes and TOAST; the latter does not. If you are comparing table sizes to decide which to partition, you almost always want the total size, because indexes often dominate the disk usage.

Also, note that these functions return the size of the table on disk, not the logical size of the data. After a large delete or update, the table may still occupy the same disk space until VACUUM FULL is run. The reported size reflects physical storage, not the number of rows.

Interpreting the Results

The raw byte count can be difficult to read, which is why pg_size_pretty() is so useful. It automatically chooses the appropriate unit (bytes, kB, MB, GB, TB) and rounds to two decimal places. For example, a table with a total size of 2,147,483,648 bytes will be shown as 2.00 GB.

When you see a table size, consider what it includes. The total size is the sum of the table data, all indexes, and the TOAST table (which stores large column values like text or bytea that don’t fit in a regular page). If you are trying to reduce disk usage, check whether the indexes are the main culprit using pg_indexes_size().

Limitations and Performance

These functions are fast because they read metadata from the system catalogs, not the actual data pages. They do not scan the table. However, they only give a point-in-time snapshot. If the table is being actively written to, the size may change immediately after the query.

For very large databases with thousands of tables, running pg_total_relation_size on every table in a single query can be slightly slow but still acceptable. The query shown above uses pg_statio_user_tables, which is a view that joins several catalog tables; it is efficient for typical use.

Frequently Asked Questions

How do I get the size of all tables in a PostgreSQL database?

Run: SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size FROM pg_tables WHERE schemaname NOT IN ('pg_catalog','information_schema') ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC; This lists all user tables with their total size, sorted from largest to smallest.

What is the difference between pg_total_relation_size and pg_relation_size?

pg_relation_size returns the size of the table’s main data fork only. pg_total_relation_size adds the size of all indexes, the TOAST table, and the free space and visibility maps. Use pg_total_relation_size for the full disk footprint of a table.

Why does my table size not decrease after deleting rows?

Deleted rows leave dead tuples that are not physically removed until a VACUUM reclaims the space. Regular VACUUM marks the space as reusable but does not shrink the file. To return disk space to the operating system, you need VACUUM FULL or CLUSTER, but note that these lock the table and rewrite it, which can be disruptive on large tables.

Can I get the size of a specific index?

Yes, use pg_relation_size('index_name'). To list all indexes for a table with sizes, run: SELECT indexname, pg_size_pretty(pg_relation_size(indexname)) FROM pg_indexes WHERE tablename = 'my_table';

Does pg_total_relation_size include the TOAST table?

Yes, it includes the TOAST table and its indexes. If you have large text or bytea columns, the TOAST data can be significant. You can get the TOAST size separately with pg_relation_size(reltoastrelid) from pg_class, but it is already included in the total.

How do I see sizes in a more readable format like MB or GB?

Wrap the size functions in pg_size_pretty(). For example: SELECT pg_size_pretty(pg_total_relation_size('my_table')); This returns a string like 25 MB.

Related

• Use Time to Walk Feature Apple Watch
• Texas HB 149 & EU AI Act: Engineering Duties by Role
• Star Trek Commander Reprint Value: Best Deck to Buy

in t | Table name | - | my_table | 0
in include_idx | Include indexes? | Yes=1, No=0 | 1 | 0
out total_size | Total size (bytes) | bytes | t / 1 | 0