Here’s how to get the size of any PostgreSQL table, as stored on disk. We show how to get the table’s size in an human-readable form like “5 MB”. We also show to get a table’s size as just a raw number of bytes, e.g. “5242880″, which can be more useful for scripts.
The SQL here to get the size of a table’s data has been tested on Postgres 9.0 (9.0.3).
What You Will Need to Get a Postgres Table’s Size
To get the size of a Postgres table, you will need login details for your database and a tool where run SQL statements on a PostgreSQL database.
For common tools for working in SQL with Postgres see Postgres SQL Tools.
I’ll use psql here as it’s available with every installation of Postgres. For more information on using psql, see psql.
How to Get a Postgres Table’s Size
Run the following Postgres table size SQL query, substituting the name of your table:
SELECT pg_size_pretty( pg_relation_size('your_table') );
This query shows the Postgres table’s size on disk:
pg_size_pretty ---------------- 504 kB
The function pg_relation_size() gets the size of the data for the named table.The pg_size_pretty() function puts the size in readable units (bytes, kilobytes, megabytes or gigabytes).
Check Postgres Table Size in Bytes
To just get the size of the table’s data in bytes, just use the pg_relation_size() function on its own, without the pg_size_pretty() function. Here’s the Postgres table size SQL to get the size on disk in bytes:
select pg_relation_size('your_table_name');
pg_size_pretty
----------------
512096
The size in bytes might be useful over the more human-readable form if you’re using the table size in a script, or if you’re storing keeping historical table sizes in a database table.
Size of An Empty Postgres Table
A new empty Postgres table consumes zero bytes on disk. This is tested here by creating a test table with no data:
$ psql
psql (9.0.3)
Type "help" for help.
taz=# create table test(a int);
CREATE TABLE
taz=# select pg_size_pretty(pg_relation_size('test'));
pg_size_pretty
----------------
0 bytes
(1 row)
Why is the Table Size in Nice Round Binary Numbers?
If you look at table sizes in bytes, you may notice that the bytes are in nice round numbers. Well, they’re round binary-wise: they’ll be in some multiple of 1024 bytes (i.e. 1KB).
This is because Postgres allocates data storage in pages. For example, I created a new table and put three small rows in it, but the table size is 8192 bytes, which is exactly 8KB. From this, you can derive that storage in allocated in 8 KB blocks by default for PostgreSQL 9.0 on Mac.
You can see this in the table size query below. When a single row is inserted into our empty test table, the table’s size is suddenly 8192 bytes or 8KB.
taz=# insert into test values (1);
INSERT 0 1
taz=# select pg_size_pretty(pg_relation_size('test'));
pg_size_pretty
----------------
8192 bytes
(1 row)
Postgres Table Indexes Size
The table size from the SQL above does not include the size of the table’s indexes. If you want the size of a table’s indexes as well, please see Get the Size of a Postgres Table’s Indexes.
Was the Table Larger than Expected?
If the size of the table was much larger than the data in it, chances old row versions haven’t been vacuumed out of the table. For an explanation of why this happens and how to fix it, see Get the Size of a Postgres Table’s Indexes.