How to find the size of a table in SQL?

As a SQL developer or database administrator, it is essential to have a good understanding of the size of your database tables. Knowing the size of a table can help you optimize your database performance, plan for storage requirements, and troubleshoot issues related to database performance. In this blog post, we will guide you through the process of finding the size of a table in SQL.

Here’s how you can see the size table sizes for public schema

SELECT
    table_name,
    pg_size_pretty(pg_relation_size(quote_ident(table_name))),
    pg_relation_size(quote_ident(table_name))
FROM
    information_schema.tables
WHERE
    table_schema = 'public'
ORDER BY
    3 DESC;

See sizes of tables of all schemas:

SELECT
    schema_name,
    relname,
    pg_size_pretty(table_size) AS size,
    table_size
FROM (
    SELECT
        pg_catalog.pg_namespace.nspname AS schema_name,
        relname,
        pg_relation_size(pg_catalog.pg_class.oid) AS table_size
    FROM
        pg_catalog.pg_class
        JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid) t
WHERE
    schema_name NOT LIKE 'pg_%'
ORDER BY
    table_size DESC;