Postgres Table Sizes

README.md

Postgres Table Sizes

Calculating Postgres table sizes is a bit complicated, due to the variety of data sizes available.

• Table data
  • main - main data fork of relation
  • fsm - Free Space Map for relation
  • vm - Visibility Map for relation
  • init - initialization fork (if any) for relation
• TOAST data
  • mechanism for managing large data values like text, binary data (BYTEA), and JSON
• Index data
  • data required for table search indexes

The following data can be helpful when determining which data points to select.

Postgres Size Functions

• Total size on disk (main data, toast, free space/visibility maps, init, and indexes)
  • pg_total_relation_size(reltoastrelid)
• Primary table size: (main data, toast, free space/visibility maps, init) (not indexes!)
  • pg_table_size(pg_class.oid)
• Partial table data sizes: (either main, free space, visibility maps, or init)
  • pg_relation_size(pg_class.oid)
  • pg_relation_size(pg_class.oid, 'fsm')
  • pg_relation_size(pg_class.oid, 'vm')
  • pg_relation_size(pg_class.oid, 'init')
• Table index size
  • pg_indexes_size(pg_class.oid)

Sources

• StackOverflow: What's the difference between pg_table_size, pg_relation_size & pg_total_relation_size? (PostgreSQL)
• StackOverflow: How do you find the row count for all your tables in Postgres

postgres-table-sizes-function.sql

CREATE OR REPLACE FUNCTION get_table_stats_with_exact_count(
    schemas TEXT[] = ARRAY['public', 'audit', 'datasci', 'archive']
)
RETURNS TABLE (
    oid OID,
    table_schema TEXT,
    table_name TEXT,
    row_count BIGINT,
    row_estimate BIGINT,
    total_bytes BIGINT,
    table_bytes BIGINT,
    table_only_bytes BIGINT,
    relation_main_bytes BIGINT,
    relation_fsm_bytes BIGINT,
    relation_vm_bytes BIGINT,
    relation_init_bytes BIGINT,
    toast_bytes BIGINT,
    index_bytes BIGINT
)
LANGUAGE plpgsql
AS $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN
        SELECT
            c.oid,
            n.nspname AS table_schema,
            c.relname AS table_name,
            c.reltuples AS row_estimate,
            pg_total_relation_size(c.oid) AS total_bytes,
            pg_table_size(c.oid) AS table_bytes,
            pg_total_relation_size(c.oid)
                - pg_indexes_size(c.oid)
                - COALESCE(pg_total_relation_size(c.reltoastrelid), 0) AS table_only_bytes,
            pg_relation_size(c.oid) AS relation_main_bytes,
            pg_relation_size(c.oid, 'fsm') AS relation_fsm_bytes,
            pg_relation_size(c.oid, 'vm') AS relation_vm_bytes,
            pg_relation_size(c.oid, 'init') AS relation_init_bytes,
            COALESCE(pg_total_relation_size(c.reltoastrelid), 0) AS toast_bytes,
            pg_indexes_size(c.oid) AS index_bytes
        FROM pg_class c
        JOIN pg_namespace n ON n.oid = c.relnamespace
        WHERE c.relkind = 'r'
          AND n.nspname = ANY(schemas)
    LOOP
        RETURN QUERY EXECUTE format(
            'SELECT %L::oid, %L, %L, COUNT(*), %s::BIGINT, %s::BIGINT, %s::BIGINT, %s::BIGINT, %s::BIGINT, %s::BIGINT, %s::BIGINT, %s::BIGINT, %s::BIGINT, %s::BIGINT FROM %I.%I',
            r.oid,
            r.table_schema,
            r.table_name,
            r.row_estimate,
            r.total_bytes,
            r.table_bytes,
            r.table_only_bytes,
            r.relation_main_bytes,
            r.relation_fsm_bytes,
            r.relation_vm_bytes,
            r.relation_init_bytes,
            r.toast_bytes,
            r.index_bytes,
            r.table_schema,
            r.table_name
        );
    END LOOP;
END;
$$;

COMMENT ON FUNCTION get_table_stats_with_exact_count IS $body$
Calculate table size and row count statistics.

Optional parameters:

param 0: text[], Schema names to fetch table stats from.
$body$;

-- Usage -----------------------------------------

SELECT *
FROM get_table_stats_with_exact_count()
ORDER BY
	array_position(ARRAY['public', 'audit', 'datasci', 'archive'], table_schema),
	table_name ASC;

postgres-table-sizes-query.sql

-- NOTE: Table row count is an estimate only, as actual count is only possible via SELECT COUNT(*)!
-- NOTE: Cannot rely soley on 'pg_relation_size' as this only includes 'main' fork (not 'fms', 'vm', or 'init')!
--         Instead, the table size (excluding indexes/toast) must be calculated manually!
SELECT
	pg_class.oid,
	nspname AS table_schema,
	pg_class.relname AS table_name,
	s.n_live_tup AS row_count,
	pg_class.reltuples AS row_estimate,
	-- Total table size: main data, toast, free space/visibility maps, init, and indexes
	pg_total_relation_size(pg_class.oid) AS total_size_bytes,
	pg_size_pretty(pg_total_relation_size(pg_class.oid)) AS total_size,
	-- Table size: main data, toast, free space/visibility maps, init (not indexes!)
	pg_table_size(pg_class.oid) AS table_size_bytes,
	pg_size_pretty(pg_table_size(pg_class.oid)) AS table_size,
	-- Table size: main data, free space/visibility maps, init (not toast, indexes!)
	pg_total_relation_size(pg_class.oid) - pg_indexes_size(pg_class.oid) - COALESCE(pg_total_relation_size(reltoastrelid), 0) AS table_only_bytes,
	-- Individual fork sizes
	pg_relation_size(pg_class.oid) AS relation_bytes_main,
	pg_relation_size(pg_class.oid, 'fsm') AS relation_bytes_fsm,
	pg_relation_size(pg_class.oid, 'vm') AS relation_bytes_vm,
	pg_relation_size(pg_class.oid, 'init') AS relation_bytes_init,
	-- Toast and index sizees
	COALESCE(pg_total_relation_size(reltoastrelid), 0) AS toast_size_bytes,
	pg_size_pretty(COALESCE(pg_total_relation_size(reltoastrelid), 0)) AS toast_size,
	pg_indexes_size(pg_class.oid) AS index_size_bytes,	
	pg_size_pretty(pg_indexes_size(pg_class.oid)) AS index_size
FROM
	pg_class
LEFT JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
LEFT JOIN pg_stat_all_tables s ON s.relid = pg_class.oid
WHERE
	relkind = 'r'
	AND nspname IN ('public', 'audit', 'datasci', 'archive')
ORDER BY
	array_position(ARRAY['public', 'audit', 'datasci', 'archive'], nspname::text),
	table_name ASC;