3 ways to get table size in Postgres

Get table size with pg_relation_size in Postgres

PostgreSQL provides a dedicated function, pg_relation_size, to compute the actual disk space used by a specific table or index. We just need to provide the table name.

To check the size of a table in a PostgreSQL database, you can use the following SQL query:

select pg_size_pretty(pg_relation_size('table_name'));

Replace “table_name” with the name of the table you want to check.

This query uses the pg_relation_size function to get the size of the table, and the pg_size_pretty function to convert the size to a human-readable format.

You can run this query in any PostgreSQL client or tool that allows you to execute SQL queries, such as psql, pgAdmin, or any other GUI tool.

Assuming you have a table named sales_data, you can use the following SQL query to get the size of the table in a more readable format:

select pg_size_pretty(pg_relation_size('sales_data'));

The pg_relation_size function returns the size of the table in bytes, while pg_size_pretty function converts the size to a more readable format like MB, GB, TB, etc. The result of this query will be something like:

pg_size_pretty
----------------
123 MB
(1 row)

This means that the size of the sales_data table is approximately 123 megabytes.

Get table size with pg_total_relation_size in Postgres

You can use pg_total_relation_size to check the total size of a table in a PostgreSQL database.

The difference between pg_relation_size and pg_total_relation_size is that pg_relation_size returns the size of the main data file of the table, while pg_total_relation_size returns the total size of the main data file plus any associated files, such as indexes or toast tables.

Here is an example query to use pg_total_relation_size:

select pg_size_pretty(pg_total_relation_size('table_name'));

Note that this query will return the total size of the table, including any associated indexes and toast tables. If you only want to check the size of the main data file of the table, use pg_relation_size instead.

Get table size with \dt+ in Postgres

You can also use the \dt+ command in psql to list all tables in a PostgreSQL database along with their sizes. Here’s how:

Open psql by typing psql in your terminal or command prompt.

Connect to your PostgreSQL database by typing \c your_database_name and press Enter. Replace your_database_name with the name of the database you want to connect to.

Type \dt+ and press Enter. This will list all tables in the database along with their sizes.

Here’s an example output:

List of relations
Schema | Name | Type | Owner | Size | Description
--------+------------------------+-------+----------+------------+-------------
public | customers | table | postgres | 16 MB |
public | orders | table | postgres | 12 MB |
public | products | table | postgres | 32 MB |
public | customers_id_seq | seq | postgres | 8192 bytes |
public | orders_id_seq | seq | postgres | 8192 bytes |
(5 rows)

In the output, the size of each table is shown in a human-readable format, which includes the size of the main data file.

How to get the largest table in Postgres?

We’ve looked at getting the size of a specific table, so now it’s time to widen the problem to related areas. Rather than having an absolute value for a specific table, let’s look at the relative sizes.

The following basic query will tell us the 10 biggest tables:

select quote_ident(table_schema)||'.'||quote_ident(table_name) as name, pg_relation_size(quote_ident(table_schema)|| '.' || quote_ident(table_name)) as size FROM information_schema.tables WHERE table_schema NOT IN ('information_schema', 'pg_catalog') ORDER BY size DESC LIMIT 10;

Here is a breakdown of what each part of the query does:

SELECT: This specifies the columns to be selected in the query result.

quote_ident(table_schema) || ‘.’ || quote_ident(table_name) as name: This concatenates the table schema and name to create a fully qualified table name and assigns it an alias of “name”. quote_ident() is used to ensure that the table schema and name are properly quoted and escaped to prevent SQL injection attacks.

pg_relation_size(quote_ident(table_schema) || ‘.’ || quote_ident(table_name)) as size: This function calculates the size of the table in bytes and assigns it an alias of “size”. The pg_relation_size() function takes a fully qualified table name as its argument.

FROM information_schema.tables: This specifies the table to be queried, which is the information_schema.tables system catalog table that contains information about all tables in the database.

WHERE table_schema NOT IN (‘information_schema’, ‘pg_catalog’): This filters out the system catalog tables from the query result.

ORDER BY size DESC: This orders the query result by table size in descending order.

LIMIT 10: This limits the query result to the top 10 largest tables.

The result of this query will be something like:

name | size
--------------------------------+----------
public.my_large_table | 2.3 GB
public.my_other_large_table | 1.5 GB
public.yet_another_large_table | 1.2 GB
...
(10 rows)

This means that the public.my_large_table is the largest table in the database, with a size of 2.3 gigabytes, followed by public.my_other_large_table with 1.5 gigabytes, and so on.