Database Performance: Managing Fill Factor for Write-Heavy Workloads

Last month, our primary order processing service started lagging during peak traffic. We were seeing latency spikes of around 300ms on simple INSERT operations into our orders_log table. After digging into the metrics, I realized the issue wasn't the query logic itself, but the underlying B-tree structure. Our index pages were splitting constantly because they were packed too tightly.

When you’re dealing with high-concurrency environments, default storage settings aren't always your friend. Most databases default to filling pages to 100% capacity. While this is great for read-only tables, it’s a recipe for disaster in write-heavy workloads. This is where fill factor tuning becomes a critical lever for database performance.

Understanding the mechanics of index bloat

To grasp why fill factor matters, you have to visualize how a B-tree index works. Data is stored in pages—usually 8KB in PostgreSQL. When you insert a new record, the database finds the appropriate page and adds the entry. If that page is full, the database must perform a "page split." It creates a new page, moves half the data over, and updates the parent nodes.

This process is expensive. It requires extra I/O, locks the index pages, and fragments your data. If you have a high volume of random inserts, you end up with significant index bloat. This not only consumes more disk space but also forces the database to read more pages into the buffer pool, which impacts your overall database performance: tuning buffer pool page eviction strategies.

Why we tried and failed with default settings

Initially, we ignored the fill factor and tried to solve the latency by scaling up our instance size. We figured the extra CPU and memory would mask the page split overhead. It didn't work. The I/O wait times actually increased because the database was spending more time shuffling data between pages than actually writing the records.

We then attempted to implement database performance: using summary tables for heavy aggregates to offload the read pressure, but the write-heavy nature of the orders_log table remained the bottleneck. We were still hitting the same wall: the index was becoming fragmented, leading to excessive page splits during every major traffic burst.

Tuning fill factor for write-heavy workloads

The fix was to leave "breathing room" on each page. By setting a fill factor of, say, 80%, you instruct the database to only fill 80% of each page during an index build or rebuild. This leaves 20% of the page empty, providing a buffer for future insertions to happen locally without triggering a split.

Here is how you would adjust this in PostgreSQL:

SQL
-- Set the fill factor for an existing index
ALTER INDEX idx_orders_created_at SET (fillfactor = 80);

-- You must reindex for the setting to take effect
REINDEX INDEX CONCURRENTLY idx_orders_created_at;

When I applied this to our high-traffic tables, the results were immediate. The CPU spikes associated with index maintenance dropped by roughly 40%. While we did trade off some disk space—our indexes grew by about 20% in size—the trade-off for consistent write performance was worth it.

When should you lower your fill factor?

It’s tempting to apply a lower fill factor to every index, but don't do it. There's a clear cost to this strategy:

1. Increased Storage: You are effectively wasting space. If your table is mostly static, a lower fill factor just increases your memory footprint.
2. Read Performance Degradation: Because your index is now spread across more pages, a full scan or a range scan has to traverse more nodes. You're effectively lowering the "fan-out" of your B-tree.
3. Maintenance Overhead: You need to monitor your index bloat regularly. If you set the fill factor too low, you might end up with sparse pages that don't get filled for months.

I generally reserve fill factor tuning for tables where the INSERT rate is extremely high and the index keys are sequential or randomly distributed across the range. If your table is mostly UPDATE heavy, you might also consider foreign key performance: balancing indexing and write throughput to ensure your constraints aren't fighting your storage layout.

FAQ: Common questions about fill factor

Q: Is there a "magic number" for fill factor? A: Not really. 90% is a safe starting point for many systems. If you have a high-concurrency write workload, 70-80% is often the sweet spot. Anything below 50% is usually overkill unless you have very specific, extreme write patterns.

Q: Does fill factor affect the table heap or just indexes? A: In PostgreSQL, FILLFACTOR applies to both. However, tuning the table heap fill factor is much more aggressive and usually reserved for tables with frequent UPDATE operations that trigger row versioning (MVCC).

Q: How do I know if I have index bloat? A: Use extensions like pgstattuple in PostgreSQL. It allows you to inspect the physical layout of your indexes and determine the "free space" percentage. If your index is 50% empty but you haven't tuned the fill factor, you're likely seeing high fragmentation.

Final thoughts

Tuning the fill factor isn't a silver bullet. It's a trade-off between write latency and read efficiency. If you find your database performance suffering under heavy writes, start by identifying the indexes with the highest split rates. Don't be afraid to experiment with the numbers—I've found that a bit of trial and error in a staging environment is the only way to find the right balance for your specific data distribution. Next time, I’m planning to look closer at how partitioning might help us manage these indexes even more effectively, but for now, this simple adjustment bought us plenty of breathing room.