Resumo:
Indexes are one of the first performance tools every PostgreSQL user learns — and one of the easiest ways to accidentally slow a system down.
This article explains how indexes actually work, why they are never free, how different index types behave in real workloads, and how fillfactor quietly determines whether your database ages gracefully or slowly collapses under write pressure.

Indexes Feel Like Magic (At First)

You run a query on a table with millions of rows.

It’s slow.

You add an index.

Suddenly:

  • milliseconds instead of seconds,
  • CPU drops,
  • dashboards turn green.

It feels like magic.

And this is where many systems start to degrade — not because indexes are bad, but because they are too effective, and therefore overused.

Indexes trade write performance, disk space, and maintenance cost for read speed.
Understanding that trade-off is the difference between a fast database and a fragile one.

What an Index Really Is

An index is not a performance flag you turn on.

It is:

  • a separate data structure
  • stored on disk
  • cached in memory
  • maintained on every data change

Every time you:

  • insert a row,
  • update an indexed column,
  • delete a row,

PostgreSQL must update every relevant index.

Reads get faster.
Writes get heavier.
There is no free lunch.

The Default Workhorse: B-Tree Indexes

B-Tree is PostgreSQL’s default index type, and for good reason.

It supports:

  • equality lookups
  • range scans
  • sorting
  • prefix matching
  • index-only scans

This is why most production databases are built almost entirely on B-Trees.

CREATE INDEX idx_users_email
    ON users (email);

If you are unsure which index to use, B-Tree is almost always the correct first answer.

But B-Trees also have a cost:

  • they grow with data volume,
  • they fragment under heavy updates,
  • they must stay balanced.

Which brings us to scale.

When Tables Get Big, B-Tree Isn’t Always the Best Tool

On very large tables — tens or hundreds of millions of rows — maintaining row-level index entries becomes expensive.

This is where BRIN indexes exist.

BRIN Indexes: Indexing Patterns, Not Rows

BRIN stands for Block Range Index.

Instead of indexing every row, BRIN indexes:

  • a range of pages,
  • storing only min/max summaries per range.

The result:

  • index size drops dramatically,
  • maintenance cost is minimal,
  • performance scales with table size.
CREATE INDEX idx_logs_lud_brin
    ON logs
    USING BRIN (last_update_datetime);

This works beautifully only when data is physically ordered.

Think:

  • append-only logs,
  • time-series data,
  • ETL history tables,
  • partitioned datasets.

BRIN does not accelerate everything — it accelerates patterns.

Correlation: The Hidden Requirement for BRIN

BRIN depends on physical order.

PostgreSQL exposes this via pg_stats.correlation.

SELECT correlation
FROM pg_stats
WHERE tablename = 'logs'
  AND attname = 'created_at';

Interpretation:

  • ~1.0 → ideal for BRIN
  • 0.6–1.0 → usually good
  • <0.5 → BRIN likely useless

No correlation means PostgreSQL still has to scan many blocks — defeating the purpose.

A Common ETL Mistake

BRIN does not help with join-based diffs like:

WHERE source.last_update_date > target.last_update_date

The join happens first.

For ETL diffs, the real performance driver is:

CREATE UNIQUE INDEX idx_entity_id_rev
    ON business_entity_dataset (id, rev_number);

This index determines whether your pipeline scales or stalls.

Partitioned Tables: Indexes Multiply

Partitioning feels like a silver bullet.

But remember:

  • PostgreSQL has local indexes only
  • one index on the parent = one index per partition

That means:

  • index count grows with partitions,
  • write cost grows silently.

Unique indexes must include the partition key:

CREATE UNIQUE INDEX idx_unique_year_id
    ON my_partitioned_table (year, id);

Partitioning reduces scan scope — it does not eliminate indexing costs.

The Real Problem: Too Many Indexes

At some point, every system reaches this state:

“Reads are fast, but writes are suddenly slow.”

The cause is almost always the same:

  • too many indexes,
  • added incrementally,
  • never revisited.

Each additional index:

  • increases write latency,
  • increases I/O,
  • increases vacuum pressure,
  • increases bloat.

A table with 10 indexes can easily be an order of magnitude slower to update than one with 1–2 well-chosen indexes.

Enter Fillfactor: Controlling How Pages Age

Fillfactor is one of the least understood — and most powerful — tuning tools in PostgreSQL.

It answers a simple question:

How full should pages be when they are created?

Pages, Splits, and Why They Hurt

PostgreSQL stores data in 8 KB pages.

For B-Tree indexes:

  • leaf pages store index entries
  • once a page is full, PostgreSQL must split it

A page split:

  1. allocates a new page,
  2. moves entries,
  3. updates parent nodes,
  4. fragments the index.

This is expensive and accumulates over time.

What Fillfactor Actually Does

A critical clarification:

Pages do not split at fillfactor %.

Pages split at ~100% full.

Fillfactor simply:

  • leaves free space in advance,
  • so future inserts fit,
  • delaying splits.

Example:

  • fillfactor = 80
  • pages start 80% full
  • 20% space reserved for growth

Choosing the Right Fillfactor

Read-mostly data

  • historical tables
  • append-only datasets
fillfactor = 100

Compact, cache-friendly, minimal overhead.

Write-heavy workloads

  • ETL upserts
  • frequently updated dimensions
  • status-tracking tables
fillfactor = 70–90

More space, fewer splits, predictable latency.

Applying Fillfactor Correctly

On indexes:

CREATE INDEX idx_entity_id_rev
    ON business_entity_dataset (id, rev_number)
    WITH (fillfactor = 80);

Changing fillfactor requires rebuilding:

ALTER INDEX idx_entity_id_rev SET (fillfactor = 80);
REINDEX INDEX idx_entity_id_rev;

Table Fillfactor and HOT Updates

Tables also support fillfactor.

Lower table fillfactor:

  • leaves room on heap pages,
  • enables HOT updates,
  • avoids touching indexes entirely.
ALTER TABLE my_table
    SET (fillfactor = 80);

This is one of the most effective ways to stabilize write-heavy systems.

Final Thoughts

Indexes are not just performance tools — they are long-term architectural decisions.

Good systems:

  • index intentionally,
  • measure write cost,
  • tune fillfactor early,
  • revisit assumptions as data grows.

Bad systems:

  • index reactively,
  • never remove indexes,
  • ignore bloat,
  • blame PostgreSQL when writes slow down.

Speed is easy to add. Sustainability is what takes engineering.

💬 Let’s Talk

If you work with:

  • PostgreSQL at scale
  • ETL pipelines
  • write-heavy systems

and care about predictable performance, I enjoy these discussions.