PostgreSQL Index Size Estimator

Purpose

Estimates the storage footprint of a proposed B-tree index before creating it. Useful during index design to evaluate the storage cost before committing — particularly on large tables where an index on wide or numerous columns can add significant volume.

The script resolves each proposed key column to its byte width using pg_attribute and pg_type, adds the per-tuple overhead for a B-tree index leaf entry (6 bytes ItemPointerData + 8 bytes IndexTupleData header = 14 bytes, rounded to 8-byte alignment), and projects total size against the table’s live row count adjusted for the fill factor.

PostgreSQL B-tree indexes do not support INCLUDE columns prior to version 11. From version 11 onward, INCLUDE column sizes contribute to the index tuple size and are included in the estimate.

Supported Platforms and Versions

PostgreSQL 13 through 18. Uses standard pg_catalog views available across all supported versions.

Required Permissions

SELECT on pg_catalog views — available to any database user by default.

Preconditions

• The target table must already exist.
• pg_class.reltuples is used for the row count estimate. This value is updated by ANALYZE and autovacuum. Run ANALYZE <table> if the table has had significant changes since the last vacuum cycle.
• The script uses the declared type width (pg_attribute.attlen for fixed types, atttypmod for length-qualified variable types). For text and unconstrained varchar, a configurable average length assumption is used — adjust it to match your actual data.

Risk Level

Low. Read-only. No index is created. No changes to schema or data.

Script

-- ============================================================
-- PostgreSQL Index Size Estimator
-- Permissions : SELECT on pg_catalog (any user)
-- Risk        : Read-only — estimates only, no index is created
-- ============================================================

WITH params AS (
    SELECT
        90          AS fill_factor,         -- btree default fill factor
        8192        AS page_size,           -- default PostgreSQL page size
        'public'    AS schema_name,
        'orders'    AS table_name,          -- target table
        50          AS text_avg_length      -- assumed average byte length for text/varchar(max)
),
index_columns AS (
    -- Define the proposed index columns.
    -- Set is_included = true for INCLUDE columns (PostgreSQL 11+ only).
    -- is_included does not affect the size estimate — all columns contribute to tuple width.
    SELECT column_name, is_included
    FROM (VALUES
        ('order_date',   false),    -- key column
        ('customer_id',  false),    -- key column
        ('status',       true)      -- INCLUDE column (pg 11+)
    ) AS t(column_name, is_included)
),
col_sizes AS (
    SELECT
        ic.column_name,
        ic.is_included,
        CASE
            -- Fixed-length types: use attlen directly
            WHEN a.attlen > 0                            THEN a.attlen
            -- varchar(n) / char(n): atttypmod = max_length + 4
            WHEN t.typname IN ('varchar', 'bpchar')
             AND a.atttypmod > 0                         THEN a.atttypmod - 4
            -- text, varchar without length modifier: use configured average
            WHEN t.typname IN ('text', 'varchar')        THEN p.text_avg_length
            -- numeric(p,s): approximate from precision
            WHEN t.typname = 'numeric' AND a.atttypmod > 0
                THEN CEIL(((a.atttypmod - 4) >> 16) / 2.0) + 2
            -- uuid stored as 16 bytes
            WHEN t.typname = 'uuid'                      THEN 16
            -- fallback
            ELSE 8
        END AS col_bytes
    FROM index_columns AS ic
    CROSS JOIN params AS p
    JOIN pg_class AS c
        ON c.relname = p.table_name
    JOIN pg_namespace AS n
        ON n.oid = c.relnamespace AND n.nspname = p.schema_name
    JOIN pg_attribute AS a
        ON a.attrelid = c.oid AND a.attname = ic.column_name
    JOIN pg_type AS t
        ON t.oid = a.atttypid
    WHERE a.attnum > 0
      AND NOT a.attisdropped
),
row_estimate AS (
    SELECT
        SUM(col_bytes)                              AS data_bytes,
        -- B-tree leaf tuple: 6 (ItemPointerData) + 8 (IndexTupleData) = 14 bytes overhead,
        -- rounded up to nearest 8-byte alignment boundary
        SUM(col_bytes) + 16                        AS tuple_bytes_aligned
    FROM col_sizes
)
SELECT
    p.table_name,
    p.schema_name,
    c.reltuples::bigint                                         AS estimated_row_count,
    re.data_bytes                                               AS column_data_bytes,
    re.tuple_bytes_aligned                                      AS estimated_tuple_bytes,
    p.fill_factor                                               AS fill_factor_pct,
    ROUND(
        re.tuple_bytes_aligned * c.reltuples
        / ((p.page_size - 24)                       -- 24 = page header
            * (p.fill_factor / 100.0))
        / 1024.0 / 1024.0,
    2)                                                          AS estimated_index_size_mb
FROM row_estimate AS re
CROSS JOIN params AS p
JOIN pg_class AS c
    ON c.relname = p.table_name
JOIN pg_namespace AS n
    ON n.oid = c.relnamespace AND n.nspname = p.schema_name;

Safe Execution Guidance

1. Edit the params CTE: set schema_name, table_name, fill_factor, and text_avg_length.
2. Edit the index_columns VALUES list to reflect the proposed index definition. Match column names exactly as they appear in the table (case-sensitive in PostgreSQL).
3. For text or unconstrained varchar columns, set text_avg_length to a realistic average byte length for your data. The default of 50 is a placeholder.
4. Run ANALYZE <table> first if pg_class.reltuples may be stale.
5. The estimate covers the leaf level of the B-tree. Internal (non-leaf) pages and the metapage add a small additional overhead, typically less than 1% for large indexes.

Expected Output

Single row:

Limitations

• Variable-length types: text and unconstrained varchar use the text_avg_length parameter. The accuracy of the estimate depends entirely on how well this matches your actual data distribution.
• TOAST: Values wider than ~2 KB are stored out-of-line in a TOAST table. An index on a TOAST-able column stores a pointer (18 bytes), not the full value. For columns that regularly exceed 2 KB, the actual index row is smaller than this estimate.
• Tuple overhead: The 16-byte overhead used here is a reasonable approximation for aligned B-tree leaf entries. Actual overhead varies slightly by alignment requirements of the specific column types.
• Internal pages: The estimate covers leaf pages only. B-tree internal pages (the navigation structure above the leaf level) add a small additional overhead, typically a few MB for even very large indexes.

Rollback Steps

Not applicable — read-only script.

Official References

• PostgreSQL: CREATE INDEX
• PostgreSQL B-Tree Indexes
• pg_attribute
• pg_class