How to Find the Size of PostgreSQL Databases and Tables

Knowing the size of your data helps you plan storage, spot growth early and keep performance steady. PostgreSQL has built-in functions for this. You can run them in psql over SSH or in any SQL tool.

Prepare a test table

DROP TABLE IF EXISTS employee;

CREATE TABLE employee (
    id SERIAL PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    salary NUMERIC(10,2)
);

INSERT INTO employee (name, salary)
SELECT 'Employee ' || n, (1000 + (n % 3) * 1000)
FROM generate_series(1, 1000) AS n;

This creates a table with 1,000 rows to measure.

Method 1: pg_size_pretty

pg_size_pretty turns a number of bytes into a readable size like 8192 bytes or 12 MB.

SELECT pg_size_pretty(pg_database_size(current_database())) AS database_size;
SELECT pg_size_pretty(pg_relation_size('employee')) AS table_size;

Method 2: pg_database_size

pg_database_size returns the size of a database in bytes.

SELECT pg_database_size(current_database()) AS database_size_bytes;
SELECT pg_size_pretty(pg_database_size(current_database())) AS database_size_human_readable;

To list all databases:

SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

Method 3: pg_tablespace_size

A tablespace is a storage location on disk. The default one is pg_default.

SELECT pg_tablespace_size('pg_default') AS tablespace_size_bytes;
SELECT pg_size_pretty(pg_tablespace_size('pg_default')) AS tablespace_size_human_readable;

Method 4: pg_relation_size

pg_relation_size returns the size of the main data of a table or index, in bytes. It does not count indexes or TOAST data.

SELECT pg_relation_size('employee') AS table_size_bytes;
SELECT pg_size_pretty(pg_relation_size('employee')) AS table_size_human_readable;

Method 5: pg_total_relation_size

pg_total_relation_size includes the table, its indexes and TOAST data. It is the real disk use of the table.

SELECT pg_total_relation_size('employee') AS total_size_bytes;
SELECT pg_size_pretty(pg_total_relation_size('employee')) AS total_size_human_readable;

Find the biggest tables

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
LIMIT 10;

psql shortcuts

\l+      list databases with sizes
\dt+     list tables with sizes
\di+     list indexes with sizes

Quick recap

• pg_database_size for a database, pg_total_relation_size for a table with indexes.
• Wrap any size in pg_size_pretty() to read it easily.