How to Check the Size of a PostgreSQL Database (SQL + psql)

CodexFuse Team · 2026-11-04T04:16:18.528Z · 11 min read min read

The fastest way to check the size of a PostgreSQL database is a single SQL query: SELECT pg_size_pretty(pg_database_size('mydb')); — it returns the database's full on-disk footprint, every table, index, and TOAST table included, formatted as something readable like 66 MB. The postgres database size question comes up constantly: before a migration, when a disk alarm fires at 2 AM, or when a managed-database bill suddenly doubles and someone has to explain why.

PostgreSQL is the database most teams self-host, usually on a VPS hosting plan where disk space is the hard limit rather than an abstraction. This guide covers the three fastest ways to check a database's size, how to see every database on a server at once, how to drill into individual table and index sizes, why a database is always bigger than the sum of its tables, and how to check size with no SQL at all in pgAdmin. Every command is verified against the official PostgreSQL 18 documentation — 18 is the current stable major version as of November 2026 — and each section opens with the direct answer.

How Do I Check the Size of a PostgreSQL Database?

Run SELECT pg_size_pretty(pg_database_size('mydb')); in any SQL client — it returns the complete on-disk size of the mydb database in human-readable units. If you landed here searching for how to check the size of a postgres database before a migration or a capacity review, start with this query and pick your lane below. The inner function measures the size in bytes; the outer one converts it to kB, MB, GB, TB, or PB. These are the five ways to do it, fastest first:

1. One database, readable output: SELECT pg_size_pretty(pg_database_size('mydb'));
2. The database you are connected to: SELECT pg_size_pretty(pg_database_size(current_database()));
3. Every database on the server, biggest first: SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size FROM pg_database WHERE datistemplate = false ORDER BY pg_database_size(datname) DESC;
4. Inside psql, no SQL needed: \l+ — the Size column lists every database.
5. No terminal at all: in pgAdmin, right-click the database, choose Properties, open the Statistics tab, and read the Size field.

What Does pg_database_size Actually Measure?

pg_database_size computes the total disk space used by one database: all of its tables, indexes, TOAST tables, and system catalogs, measured in bytes and returned as a bigint. The official PostgreSQL 18 documentation defines two forms — pg_database_size(name) and pg_database_size(oid) — so you can pass either the database name or its internal object ID.

Two details matter in practice. First, the raw number is bytes, which is why you almost always wrap it: pg_size_pretty converts bytes into a readable string using powers of two — 1 kB is 1024 bytes, 1 MB is 1024² bytes, and so on up to PB. Second, you need permission: the docs require CONNECT privilege on the database (granted by default) or the pg_read_all_stats role. Without it you get a permission-denied error, and in psql the \l+ Size column simply shows blank for databases you cannot connect to.

How Do I See the Size of All Databases in PostgreSQL?

Query the pg_database system catalog and call pg_database_size once per row — one query returns every database with its size, sorted largest first:

SELECT datname AS database, pg_size_pretty(pg_database_size(datname)) AS size FROM pg_database WHERE datistemplate = false ORDER BY pg_database_size(datname) DESC;

Filtering out templates (template0 and template1) keeps the output to databases people actually created. This pattern is the workhorse of disk-capacity reviews: it is the canonical answer for how to see size of all databases in postgres from a single SQL client, and the same query DBAs schedule into monitoring — it works unchanged on every supported PostgreSQL version. If you prefer the terminal shortcut, \l+ in psql prints the same information as a List of databases table with a Size column — Aiven's vendor documentation shows a real example returning 66 MB for a test database, matching the SQL result exactly.

How Do I Check the Size of a Table Instead of the Whole Database?

To check postgres table size including everything the table owns — its indexes and its TOAST data — run SELECT pg_size_pretty(pg_total_relation_size('orders'));. In psql you can also use \dt+, which adds a Size column to the table listing. The four relation-size functions slice a table's storage differently, and picking the wrong one is the most common source of confusing numbers:

The practical rule: use pg_relation_size when you want to know how big the raw data is, pg_table_size when you want the table's own footprint, and pg_total_relation_size when you are hunting the space hog that is filling your disk. All four accept an optional second fork argument (main, fsm, vm, init) if you ever need to measure a single storage fork — you will rarely need it, but the option exists in the official function reference.

Which Size-Checking Method Should I Use?

Pick the method that matches what you are actually trying to learn — each one answers a slightly different question:

Why Is My Database Bigger Than the Sum of Its Tables?

Because summing table sizes only counts the main data forks — it misses indexes, TOAST storage, dead tuples, and the free-space and visibility maps. A database is legitimately larger than its tables, and each gap has a name.

Indexes are separate storage. Every b-tree, GIN, or GiST index is its own on-disk object, and a heavily indexed table's indexes can rival the table itself in size. This is the single most common explanation — pg_relation_size never sees indexes at all.

TOAST moves wide values out of the row. PostgreSQL stores rows in fixed 8 kB pages and does not let a row span pages, so any value wider than about 2 kB (the TOAST_TUPLE_THRESHOLD) is compressed and moved to a separate TOAST table. Text columns, JSONB documents, and bytea blobs live there — invisible to a main-fork-only sum.

Dead tuples are MVCC's leftover. Updates and deletes do not free space immediately; they leave dead row versions behind until VACUUM reclaims them. A table with heavy churn can carry substantial bloat — reclaiming it for real needs VACUUM FULL, which locks the table, or an online tool like pg_repack.

Two things people expect to find but should not: the write-ahead log is not counted — WAL lives in the cluster-wide pg_wal directory shared by all databases, and pg_database_size only stats relation files. And if the gap is truly enormous, compare pg_database_size() against the sum of pg_relation_size over pg_class — AWS's official re:Post guidance notes that a large unexplained difference points at orphaned files consuming storage.

How Do I Check Database Size in pgAdmin Without SQL?

Right-click the database in pgAdmin, choose Properties, open the Statistics tab, and scroll to the Size row — it shows the database size in megabytes, matching what pg_database_size reports. For the whole server at once, select the Databases node instead: its Statistics tab lists every database with a Size column, which is the GUI equivalent of the all-databases query. Individual tables work the same way — select a table, open its Statistics tab, and read its size without writing a line of SQL.

How Do I Check the Size of a Postgres Database Running in Docker?

Run psql inside the container with docker exec — the same SQL works, you just reach the client through Docker first. This pattern covers the overwhelmingly common case of Postgres running as a container:

docker exec -it pg psql -U postgres -c \"SELECT pg_size_pretty(pg_database_size('mydb'));\"

Replace pg with your container name and mydb with your database. If you run several Postgres containers — say one per project — the all-databases query from the earlier section is the fastest way to compare them: exec into each container, run it, and you have a full inventory in seconds. If you are new to containers, our Docker beginner guide walks through the docker exec pattern step by step.

Does the PostgreSQL Version Change How Size Checks Work?

No — pg_database_size, pg_size_pretty, and the relation-size functions behave identically from PostgreSQL 14 through the current release, so every query in this guide works on any version you are likely to meet. The version note that matters is freshness, not syntax: PostgreSQL 18 is the current stable major version as of November 2026 (released September 25, 2025, per the project's press FAQ), while PostgreSQL 19 is still in release candidates — Beta 4 shipped September 24, 2026, RC1 on October 15, with general availability targeted around November 19 after several features were reverted for bugs. Size-checking syntax is one of the most stable surfaces in Postgres; when 19 lands, none of these queries will need to change.

How Do I Monitor Database Size Over Time?

One-off checks answer today's question; monitoring answers next month's. Set it up once with this five-step arc:

1. Create a history table holding a timestamp, database name, and size in bytes from pg_database_size — raw bytes, not pretty strings, so you can do math on them later.
2. Log daily with a scheduled job that runs the all-databases query and inserts one row per database. On your own VPS, a cron entry calling psql is enough; on managed Postgres, use the platform's scheduler.
3. Alert on disk percentage, not bytes. Warn at 70% of the volume and page at 85% — a database growing 5 GB a week needs a different response than one growing 50 MB a month, and only the percentage tells you when the wall arrives.
4. Do not poll aggressively. pg_database_size stats every fork of every relation, which is expensive on a large database with many extents — once a day is plenty; every minute is a self-inflicted load problem.
5. Track the growth rate. A week of daily readings gives you a slope, and the slope gives you the date the disk fills. That date — not the current size — is the number that belongs in your capacity plan.

Sources

• PostgreSQL 18 official documentation, Database Object Size Functions (via the pgEdge mirror of the official docs) — pg_database_size / pg_size_pretty signatures, byte units, CONNECT-privilege requirement
• PostgreSQL 18 official documentation, TOAST storage (postgresql.org/docs/18/storage-toast) — 8 kB pages, 2 kB TOAST threshold
• PostgreSQL press FAQ (postgresql.org/about/press/faq) — PostgreSQL 18 is the current version, released September 25, 2025
• The Register, October 16, 2026 — PostgreSQL 19 delayed to November after feature reverts
• pgsql mailing-list thread on pg_database_size internals (spinics.net) — WAL exclusion, fork-stat cost, permission behavior
• Aiven PostgreSQL docs, "Check the size of a database, a table or an index" — \l+ Size column, verified 66 MB example output
• Linuxize, "How to List Databases and Tables in PostgreSQL" — \l+ Size column requires connect privilege
• PostgresGuide psql utilities reference — \dt+ table Size column
• ScriptDBA, "PostgreSQL: query database and table size" — all-databases query pattern, dead-tuple/bloat explanation
• pgAdmin 4 official documentation, tabbed browser — Statistics tab Size row in megabytes
• AWS re:Post official article — comparing pg_database_size() vs summed relation sizes to find orphaned files
• The Linux Code, "Monitoring the Size of Your PostgreSQL Database" — pgAdmin Properties flow, pg_stat_database overview

Frequently Asked Questions

What is the fastest way to check a PostgreSQL database size?

Run SELECT pg_size_pretty(pg_database_size('mydb')); — one query, one readable number. Aiven's documentation shows the real output for a 68,895,523-byte database as 66 MB. If you are already in psql, \l+ is even faster for a glance.

How do I check PostgreSQL database size in MB?

pg_size_pretty picks the unit for you — anything in the megabyte range comes back labeled MB, using powers of two (1 MB = 1,048,576 bytes). If you need a raw number for a script or dashboard instead, divide the byte count yourself: SELECT pg_database_size('mydb') / 1048576.0 AS size_mb;

Why is my database bigger than the sum of its tables?

Because table-only sums miss indexes, TOAST storage for wide values, dead tuples from MVCC churn, and the free-space and visibility maps. pg_relation_size counts just the main data fork; pg_total_relation_size counts the table plus indexes plus TOAST, which is the number to compare against the database total.

Can I check the size without superuser access?

Yes, if you have CONNECT privilege on the database — which is granted by default — or the pg_read_all_stats role. Without either, pg_database_size raises a permission-denied error and psql's \l+ leaves the Size column blank for that database.

Why does du -sh on the data directory show a bigger number than pg_database_size?

The data directory holds more than one database's relation files: the write-ahead log (pg_wal) is shared across the whole cluster and is never counted by pg_database_size, and other databases' files live in the same directory tree. Compare per-database with pg_database_size and measure the whole volume separately.