Optimizing PostgreSQL Performance: How the Fill Factor Setting improve I/O performance

What is PostgreSQL Fill Factor?

The `fillfactor` is a storage parameter in PostgreSQL that defines the percentage of space on each page of a table or index that can be filled with data during insert operations. The remaining space (the percentage left after the fill factor) is reserved for future updates, reducing the need for page splits or row movement, which can be expensive in terms of performance.

How Fill Factor Works?

-Default Value: The default `fillfactor` for tables is 100, meaning that PostgreSQL will attempt to fill each page completely during initial inserts. For indexes, the default is often set to 90, meaning 90% of the page will be filled.

• Setting the Fill Factor: You can set the `fillfactor` for a table or an index when creating or altering them. For example:

CREATE TABLE my_table (
 id serial PRIMARY KEY,
 data text
 ) WITH (fillfactor = 70);

This sets a fill factor of 70 for the `my_table` table, meaning only 70% of each page will be filled during initial inserts.

Impact on Performance:

-Low Fill Factor: If the fill factor is set low (e.g., 70%), there is more room for future updates on each page, reducing the likelihood of needing to move rows or split pages. This is particularly useful for tables with frequent updates.
-High Fill Factor: A higher fill factor (e.g., 100%) means less free space on each page. This might be efficient for tables that are primarily read-only after being populated, as it minimizes disk space usage. However, it can lead to more page splits or row movement if the table is frequently updated, potentially degrading performance.

Usage Scenarios:

-Frequently Updated Tables: Use a lower fill factor to minimize page splits and row movement, which can enhance performance.
-Read-Only or Append-Only Tables: A higher fill factor can be beneficial, as it maximizes storage efficiency.

Practical Example

1.Creating a Table with a Custom Fill Factor:

CREATE TABLE customer_orders (
 order_id serial PRIMARY KEY,
 customer_id int,
 order_date date,
 order_total numeric
 ) WITH (fillfactor = 80);

Here, the table `customer_orders` is created with a fill factor of 80, meaning only 80% of each page will be used during the initial data insertion.

2.Updating Fill Factor:

You can also alter the fill factor of an existing table:

ALTER TABLE customer_orders SET (fillfactor = 75);

This sets the fill factor of the existing `customer_orders` table to 75. However, this will only affect new inserts and will not retroactively apply to existing data.

Monitoring and Adjusting Fill Factor

If your tables are experiencing performance degradation due to frequent page splits or row movement, adjusting the fill factor might be a viable tuning strategy. However, it’s important to balance storage efficiency and update performance based on your application’s needs.

In summary, the fill factor is a useful parameter in PostgreSQL that allows you to optimize the storage and performance of your tables and indexes based on their usage patterns.

Let’s break down the relationship between the fill factor and updates in PostgreSQL more clearly:

Understanding Pages and Rows:

-Pages: PostgreSQL stores data in fixed-size blocks called “pages” (usually 8 KB in size).
-Rows: Each row of data is stored within a page. Multiple rows can fit in a single page.

Fill Factor

-Fill Factor: The fill factor controls how full PostgreSQL will make each page when initially filling it with rows.
-Example: If the fill factor is set to 100, PostgreSQL tries to completely fill each page with rows during inserts, leaving no extra space.
— If the fill factor is set to 70, only 70% of the page will be filled with rows, leaving 30% of the space empty.

The Relationship Between Fill Factor and Updates:

When a table is created with a lower fill factor, more space is left on each page for future updates. Here’s how this works:

1.Row Updates and Page Splitting:
— In PostgreSQL, when a row is updated, the new version of the row must be stored somewhere. If the updated row is larger than the original (for example, if a `text` field is modified to hold more data), it may no longer fit in the space occupied by the original row.
— If the page where the row is located is already full (fill factor 100), PostgreSQL might have to move the updated row to another page or split the page to accommodate the new row size. This operation is called a “page split,” and it can be costly in terms of performance.

2.Fill Factor and Reducing Page Splits:
— If you set a lower fill factor (e.g., 70%), PostgreSQL leaves 30% of each page empty. This extra space allows PostgreSQL to store updated versions of rows in the same page.
-Example: Suppose you have a table with a fill factor of 70. Initially, each page is only 70% full. Later, when rows are updated and become larger, there’s a good chance the updated rows can still fit within the same page because of the extra 30% space. This reduces the need for PostgreSQL to move rows to different pages or split pages, which can significantly enhance performance, especially in tables with frequent updates.

Visualizing the Concept:

-High Fill Factor (100%):
— Each page is filled completely during initial inserts.
— If a row is updated and becomes larger, and there’s no space left on the page, the row might need to be moved to another page or cause a page split.
-Impact: This can lead to fragmented data, more I/O operations, and potentially slower performance for update-heavy tables.

-Low Fill Factor (e.g., 70%):
- Each page is only 70% filled during initial inserts, leaving 30% free space.
- When a row is updated, there’s a higher chance it can still fit in the same page because of the reserved space.
Impact: Fewer page splits, less row movement, better performance for tables that experience frequent updates.

When to Use a Lower Fill Factor

Frequent Updates: If your table is frequently updated, setting a lower fill factor (e.g., 70% or 80%) can help prevent performance issues related to page splits and row movement.
Append-Only or Read-Only Tables: For tables that are rarely updated (e.g., logging tables), a higher fill factor (e.g., 90% or 100%) might be more efficient, as it maximizes the use of disk space.

Monitoring Update Frequency

You can start by identifying which tables are receiving frequent updates. This can be done using PostgreSQL’s built-in statistics collector, which tracks various activities per table.

SQL Query to Identify Frequently Updated Tables:

SELECT 
 relname AS table_name,
 n_tup_upd AS num_updates,
 n_tup_ins AS num_inserts,
 n_tup_del AS num_deletes,
 n_live_tup AS live_tuples,
 n_dead_tup AS dead_tuples
FROM 
 pg_stat_user_tables
ORDER BY 
 num_updates DESC;

Analyzing Page Splits

Page splits are a key indicator that a lower fill factor might be beneficial. You can monitor the rate of page splits using the `pgstattuple` extension, which provides information about table fragmentation.

Installing `pgstattuple`:
First, you need to add the extension to your database:

CREATE EXTENSION IF NOT EXISTS pgstattuple;

Using `pgstattuple` to Monitor Page Splits:

SELECT 
 table_name,
 approx_percent_free AS free_space_percentage
FROM 
 (SELECT 
 relname AS table_name,
 pgstattuple_approx(oid)::text AS table_stats
 FROM 
 pg_class
 WHERE 
 relkind = 'r') AS tbl_stats,
 jsonb_populate_record(NULL::record, jsonb(table_stats)) AS stats
ORDER BY 
 free_space_percentage;

This query provides an approximation of the free space in each table, which can help you understand how full pages are. Tables with low free space and high update activity are prime candidates for a reduced fill factor.

Monitoring autovacuum and Dead Tuples

High numbers of dead tuples and frequent autovacuum runs can also indicate that a table is heavily updated, which might benefit from a lower fill factor.

SQL Query to Check autovacuum Activity:

SELECT 
 relname AS table_name,
 last_autovacuum,
 n_dead_tup
FROM 
 pg_stat_user_tables
WHERE 
 n_dead_tup > 0
ORDER BY 
 last_autovacuum DESC;

This will help identify tables that are frequently cleaned up by autovacuum, a sign of frequent updates and deletions.

Conclusion

The fill factor is a balance between storage efficiency and update performance. Lowering the fill factor provides room for row growth within a page, which can prevent expensive page splits and row movement, leading to better performance for update-heavy tables.

Observe the performance impact of this change over time. If update performance improves and less page splitting occurs, the new fill factor setting is likely beneficial.

This proactive tuning can enhance overall database performance, especially for dynamic workloads.