Forecasting Table and Index Growth from Row Width and Page Fill

Capacity questions arrive as a single number, how much disk will be needed next year, and the honest answer is a sum of several terms that grow at different rates. Extrapolating from the current file size and the current row count gives a number that is wrong in both directions at once: it underestimates index and log growth, and it overestimates the heap if the table has accumulated bloat that a rewrite would remove.

The forecast is worth doing from first principles because every term in it is derivable from the page layout. What follows is the arithmetic, then the effects that break it.

From a row to a page

A table’s heap file is an array of fixed-size pages, and a page holds a header, an array of line pointers, and the tuples themselves. In the PostgreSQL layout the page header is 24 bytes, each line pointer is 4 bytes, and free space is kept between the pointer array and the tuples growing toward each other from opposite ends of the page.

Each tuple carries a header of 23 bytes, plus a null bitmap whose size depends on the number of nullable columns, and then the column values. Values are aligned to their natural boundaries, with the whole tuple padded to a multiple of 8 bytes so that the next tuple starts aligned. Alignment is not a detail to skip: a schema with a smallint followed by a bigint and then an integer wastes six bytes per row to padding, where the same columns in the order bigint, integer, smallint waste two.

For a table with ten columns, four of them nullable, a tuple header costs 23 bytes plus a 1-byte bitmap, and a row of 40 bytes of actual data therefore totals 64 bytes before alignment. With mixed column widths the padding between values and the final round-up to a multiple of 8 typically add about 8 more, so 72 bytes is the figure to plan with. Adding the 4-byte line pointer gives 76 bytes of page occupancy per row.

The usable space per page is not the full page size. A page of 8192 bytes reserves the 24-byte header, and each tuple must also find a free line pointer, so the arithmetic below works from 8168 bytes of capacity per page, slightly optimistic, because a page that is completely full cannot accept another insert until something is removed. At 72 bytes per tuple plus a 4-byte pointer, that gives 107 rows per page.

def rows_per_page(row_bytes: int, page_bytes: int = 8192,
                  page_header: int = 24, item_pointer: int = 4) -> int:
    # line pointers share the page with the tuples; solve for a fixed point
    usable = page_bytes - page_header
    n = usable // (row_bytes + item_pointer)
    while n * row_bytes + n * item_pointer > usable:
        n -= 1
    return n

print(rows_per_page(72))   # 107 on an 8 KiB page

The row-per-page figure converts directly to bytes per row: 8192 / 107 is about 77 bytes, so a hundred million rows occupy roughly 7.7 gigabytes before any index exists. That is the number the forecast starts from, and it is already larger than the naive sum of column widths suggests, by a factor near two for narrow rows. The overhead is not waste that a rewrite could remove; it is the header, the pointers, and the alignment, and it is present in every engine in some form.

Fill factor and the difference between built and grown

A table built by a bulk load fills pages completely. A table grown by inserts with a clustered key fills pages completely too, if the key is monotonically increasing. A table grown by inserts at random positions, as with a UUID key or a heap with no ordering, fills pages partially, because the engine splits a full page rather than appending, and a split leaves both halves partly empty.

The steady-state occupancy of a randomly inserted B-tree or heap is around seventy percent, which is the figure that follows from the distribution of split points rather than from any configuration. A heap with no index-directed insertion is simpler: new tuples go wherever the free space map finds room, so pages stay near-full until a vacuum creates internal holes.

The planning consequence is that the same row count can occupy the predicted size or half again as much, depending on how the table was loaded. A bulk-loaded archive is close to the arithmetic above. A table that has churned for a year is above it, and the excess is the bloat described in dead tuples and vacuum reclamation, which a rewrite would return but which the forecast should assume persists.

The right procedure is to measure both: the live tuple count and the file size. Their ratio gives the effective bytes per row, which includes bloat and free space. Projecting that figure forward is conservative and correct for capacity, while projecting the arithmetic figure forward assumes a cleanliness the table may never reach.

Index amplification

Indexes are not a rounding error. A B-tree over a hundred million rows with an 8-byte key and a 6-byte tuple identifier holds about 14 bytes of payload per entry plus page overhead and internal nodes, which lands near 20 bytes per entry at typical occupancy, roughly a quarter of the heap’s size for that table, measured against the 77 bytes per row above. Four such indexes cost about as much as the table.

The amplification factor depends on the key, not the table. An index on a wide text column stores the key bytes plus overhead per row and can exceed the heap it indexes. An index on a composite of three columns multiplies accordingly. The internal nodes of the tree add a fraction of a percent over the leaves at a few hundred entries per node, and the leaf cost is the entry size divided by occupancy. An index added by a later migration therefore changes the forecast, and the concurrent build path that adds it also temporarily doubles the object’s space requirement.

Three index effects are easy to omit. An index with a fill factor below 100 reserves space on every page for future inserts, increasing its size by the reciprocal of the factor. A partial index covers only the rows matching its predicate, so its size is the fraction of rows times the entry size, and that fraction can change. A unique index on a monotonically increasing key behaves like a bulk load and stays dense, while one on a random key settles at lower occupancy.

The total footprint to forecast is therefore the heap plus the sum over indexes of their per-entry costs, and the number of indexes is a schema property that changes with every release. A forecast that assumes the current index set is a forecast that is invalidated by the next migration that adds one, which is a reason to express capacity headroom as a percentage rather than an absolute figure.

The four effects that break linear extrapolation

Linear growth from the current size is the wrong model in four routine cases.

Deletes without a rewrite do not reduce the file. A retention job that removes ninety percent of a table’s rows leaves the high-water mark intact, and the file stays at its maximum size forever unless a rewrite reclaims it. A forecast built from the row count then understates storage by whatever the bloat is, and a forecast built from the file size overstates future growth because the dead space will be reused by new rows rather than added to.

Wide values move out of line. Values above a threshold of about two kilobytes in PostgreSQL’s TOAST scheme are compressed and, if still too large, stored in a separate table with a pointer left behind. The threshold means that adding a jsonb column with large documents does not add its full width to the heap, but it does add it somewhere, and the compression ratio is content-dependent. Forecasting a column of variable-size documents from an average width is the weakest estimate in the whole exercise, and it needs a sample rather than an assumption.

Log and temporary space are separate budgets. Write-ahead log volume is not a function of table size but of the rate of change, and bulk operations such as index builds and table rewrites produce log proportional to the data they touch. Temporary space is driven by sort and hash operations whose size depends on the query mix, not on the stored data. A capacity plan that budgets only for table files will run out of disk during a maintenance operation rather than during normal growth.

The replication multiplier is the fourth. A standby holds a copy of the primary’s files, an archive holds the log, and a slot can pin log beyond its configured retention if the limits are unset. The retention behaviour of replication slots is the term most often omitted, because it grows only when something is already wrong.

Building the forecast

The forecast is a table with one row per object and columns for the measured per-row cost, the projected row count at the horizon, and the ratio between them. Measured cost comes from the current file size divided by the current live row count; projected count comes from the growth rate, which needs enough history to separate a trend from a seasonal pattern.

Two corrections are applied at the end rather than per object. The first is a bloat allowance, which should be the observed ratio of file size to the arithmetic minimum, because that ratio is what the table will actually occupy at steady state. The second is operational headroom, which covers the transient doubling of a rewrite or an index build, an operation that needs free space equal to the object’s size in addition to the object.

The horizon should be checked against the smallest of three constraints: disk, the time to restore a backup, and the time to run a maintenance operation. Disk is the obvious one. Restore time grows with the data set and is often the constraint that arrives first, because a backup that takes an hour to restore is an availability problem regardless of how much disk is free. Maintenance time grows the same way, and the operations that scale worst are the ones that rewrite or scan a whole table, which is why the per-object figures matter more than the total.

Where the arithmetic stops being useful

Every number above is an estimate with a distribution around it, and the distribution is widest for variable-length data. A forecast that reports a single figure invites a decision as if the figure were exact; a forecast that reports the arithmetic floor, the measured current cost, and the growth rate separately lets the reader see which term dominates.

The one measurement worth repeating rather than extrapolating is the per-object file size and live row count, because it captures every effect the arithmetic misses (alignment, bloat, compression, out-of-line storage) in one ratio. Everything else in the forecast is that ratio multiplied by a row count, and the row count is the term with the least uncertainty.