How much disk space does a table use?

The maximum supported table size in the default configuration is 32 TB and it does not require large file support from the operating system. The filesystem size limits do not impact the large tables, as they are stored in multiple 1 GB files.

Large tables can suffer performance issues. Indexes can take much longer to update and query performance can degrade. In this recipe, we will see how to measure the size of a table.

How to do it…

We can see the size of a table by using this command:

cookbook=# select pg_relation_size('pgbench_accounts');

The output of this command is as follows:

pg_relation_size
------------------
           13582336
(1 row)

We can also see the total size of a table, including indexes and other related spaces, as follows:

cookbook=# select pg_total_relation_size('pgbench_accounts');

The output is as follows:

pg_total_relation_size
------------------------
         15425536
(1 row)

We can also use a psql command, like this:

cookbook=# \dt+ pgbench_accounts
                        List of relations
 Schema |       Name       | Type  | Owner  | Size  | Description
--------+------------------+-------+--------+-------+-------------
 gianni | pgbench_accounts | table | gianni | 13 MB |
(1 row)

How it works…

In PostgreSQL, a table is made up of many relations. The main relation is the data table. In addition, there are a variety of additional data files. Each index created on a table is also a relation. Long data values are placed in a secondary table named TOAST, so, in most cases, each table also has a TOAST table and a TOAST index.

Each relation consists of multiple data files. The main data files are broken into 1 GB pieces. The first file has no suffix; others have a numbered suffix (such as .2). There are also files marked _vm and _fsm, which represent the visibility map and free space map, respectively. They are used as part of maintenance operations. They stay fairly small, even for very large tables.

There's more…

The preceding functions, which measure the size of a relation, output the number of bytes, which is normally too large to be immediately clear. You can apply the pg_size_pretty() function to format that number nicely, as shown in the following example:

SELECT pg_size_pretty(pg_relation_size('pgbench_accounts'));

This yields the following output:

pg_size_pretty
----------------
13 MB
(1 row)

TOAST stands for The Oversized-Attribute Storage Technique. As the name implies, this is a mechanism used to store long column values. PostgreSQL allows many data types to store values up to 1 GB in size. It transparently stores large data items in many smaller pieces, so the same data type can be used for data ranging from 1 byte to 1 GB. When appropriate, values are automatically compressed and decompressed before they are split and stored, so the actual limit will vary, depending on compressibility.

You may also see files ending in _init; they are used by unlogged tables and their indexes, for restoring them after a crash. Unlogged objects are called this way because they do not produce WAL. So, they support faster writes, but in the event of a crash they must be truncated; that is, restored to an empty state.