When it comes to fine-tuning database performance, small settings often make a big difference. One such setting – frequently discussed but not always fully understood—is the fill factor. Both SQL Server and PostgreSQL support this concept, yet they approach it differently. If you’re managing databases in either of these systems, understanding how fill factor works can save you from headaches caused by page splits, fragmentation, and unnecessary disk I/O.

The Concept of Fill Factor

Let’s start by painting a picture. Imagine you’re organizing a bookshelf. If you cram it full, there’s no room to add new books unless you pull some out or rearrange things. This is essentially what happens with database pages. When a database page becomes full and needs to accommodate more data, it has to split, which costs time and resources. Enter fill factor, the setting that lets you leave a bit of breathing room on each page – like leaving space on your bookshelf for future additions.

Fill Factor in SQL Server

In SQL Server, fill factor is a setting specifically for indexes. Think of indexes as the table of contents for your data: they help the database find information quickly. When you define or rebuild an index, you can set a fill factor to control how full each page of the index gets. For example, if you set the fill factor to 90, SQL Server will leave 10% of each page empty, reserving that space for future inserts or updates. This can reduce the likelihood of page splits – a costly operation that can slow down your queries.

By default, SQL Server’s fill factor is set to 100, meaning it will fill each index page completely. This works well for read-heavy workloads, where the data doesn’t change much. But if your workload involves frequent updates or inserts, a lower fill factor can make a huge difference in reducing fragmentation and maintaining performance over time.

Consider a table called Orders with a clustered index on the OrderDate column. This table receives frequent updates and new records daily. Here’s how you can set a custom fill factor:

-- Rebuilding an index with a custom fill factor
ALTER INDEX idx_OrderDate ON Orders
REBUILD WITH (FILLFACTOR = 90);

In this example, SQL Server will leave 10% of each index page empty during the rebuild. This extra space ensures that future inserts or updates are less likely to trigger page splits, improving overall performance.

You can also set the server-wide default fill factor by using the following command:

-- Setting server-wide fill factor to 90
EXEC sp_configure 'fill factor', 90;
RECONFIGURE;

However, server-wide settings should be used cautiously and only if your entire workload benefits from a lower fill factor.

Fill Factor in PostgreSQL

Now, let’s turn to PostgreSQL. Here, fill factor has a broader scope: it can be applied to both tables and indexes. This makes sense when you consider PostgreSQL’s architecture, particularly its use of MVCC (Multi-Version Concurrency Control). When a row in PostgreSQL is updated, a new version of the row is created, while the old version remains until it’s vacuumed. If there isn’t enough space on the same page for the new version, PostgreSQL will have to write it elsewhere, leading to table bloat and additional overhead during vacuuming.

By setting a lower fill factor on a table, you leave room for updates to happen within the same page. For instance, a fill factor of 90 means that only 90% of each page is used initially, leaving 10% free for future updates. This reduces the likelihood of row movement and minimizes the need for PostgreSQL’s vacuum process to clean up after updates.

Imagine you have a table called customers that experiences frequent updates to its last_login column. Here’s how you can set a custom fill factor for this table:

-- Creating a table with a custom fill factor
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name TEXT,
    last_login TIMESTAMP
) WITH (fillfactor = 90);

This configuration reserves 10% of each page for future updates, ensuring that updates to the last_login column do not cause unnecessary row movement.

You can also adjust the fill factor for an existing table or index:

-- Altering an existing table’s fillfactor
ALTER TABLE customers SET (fillfactor = 90);

-- Creating an index with a custom fillfactor
CREATE INDEX idx_last_login ON customers (last_login) WITH (fillfactor = 90);

These settings help optimize performance by reducing bloat and improving the efficiency of future updates.

Default Values and Key Differences

Interestingly, both systems share the same default fill factor of 100, which might seem counterintuitive for write-heavy workloads. This default is a compromise, balancing storage efficiency and performance for general use cases. However, database administrators know that adjusting fill factor to match the workload can lead to significant gains in performance and lower maintenance costs.

The key difference between the two systems lies in their scope and purpose. SQL Server’s fill factor is focused on indexes, aiming to minimize fragmentation and page splits in specific scenarios. PostgreSQL, on the other hand, uses fill factor as a broader tool to manage not just indexes but also the table-level challenges that come with MVCC. This makes PostgreSQL’s fill factor particularly valuable in write-intensive environments, where updates and row versioning are frequent.

Practical Considerations

So, how should you approach fill factor in your databases? The answer depends on your workload. In SQL Server, a lower fill factor is essential for indexes that are frequently updated or inserted into. Meanwhile, in PostgreSQL, you might consider adjusting the fill factor for both tables and indexes that experience heavy writes to reduce bloat and optimize vacuuming.

Summary

Ultimately, fill factor is one of those settings that rewards experimentation and observation. By understanding how it works in each system and testing it in your specific environment, you can strike the right balance between performance and storage efficiency. After all, databases are like bookshelves: a little extra space can go a long way.

Thanks for your time,

-Klaus