Technolila Webtools
2 views

Computed Truth

Indexes often consume more disk space than the actual data they index. A standard B-Tree index has an overhead of ~1.2x to 1.5x the raw data size due to Branch Pages, Page Headers (96 bytes), and Fill Factor gaps. Neglecting index growth is the #1 cause of unexpected storage blowouts in production databases.

SQL Index Size & Storage Estimator

Estimate Usage & Growth

Reserved free space per page (Leaf level)

The Technical Proof

This tool uses the standard B-Tree storage formula (approximate for Postgres/SQL Server):

$$ Size_{MB} = \frac{N_{rows} \times (Key_{size} + Header)}{FillFactor \times 1024^2} \times Overhead $$

  • Key Size: Sum of all column widths.
  • Header: ~10 bytes per row overhead + ~96 bytes per page header.
  • Overhead: 1.2 constant to account for internal branch nodes (non-leaf levels).
  • Compound Growth: \( Size_{future} = Size_{current} \times (1 + Rate)^M \) where M is months.

Step-by-Step Logic

  1. Calculate Row Width: Sum the byte counts of all selected index columns.
  2. Estimate Page Capacity: An 8KB page (8192 bytes) minus header (96 bytes) = 8096 bytes available.
  3. Apply Fill Factor: Usable space = \( 8096 \times (Fill / 100) \).
  4. Rows Per Page: \( Usable / (RowWidth + RowOverhead) \).
  5. Total Pages: \( TotalRows / RowsPerPage \).
  6. Total Size: \( TotalPages \times 8KB \). Add ~20% for non-leaf levels.
  7. Forecasting: Apply monthly compound interest formula for 12, 36, and 60 months.