How to Increase PostgreSQL Performance with Prefetch, Read, and Buffer Strategies

When your PostgreSQL server restarts, whether due to crashes, maintenance, or reboots, it loses a key performance booster: the shared buffer cache. This cache is vital for quick data access, and warming it back up after a restart can take minutes to hours, depending on your workload.

This is where the pg_prewarm extension comes to the rescue.

What is pg_prewarm?

pg_prewarm is a built-in PostgreSQL extension that allows you to preload relation blocks (table/index pages) into memory before they're queried. This reduces cold-start latency and avoids expensive disk I/O, especially important for high-throughput or low-latency workloads.

But not all warmups are the same, pg_prewarm gives you three modes to choose from:

• prefetch
• read
• buffer

Each mode determines how the data is loaded and where it ends up, and using the right one can dramatically impact your startup performance.

How pg_prewarm Works Internally

The function signature:

SELECT pg_prewarm('your_table_name', mode := 'buffer');

It has several optional parameters, including fork type and block range, but for most use cases, the relation name and mode are sufficient.

Behind the scenes, pg_prewarm does the following:

1. Opens the relation (table or index).
2. Depending on the mode:
   • Tells the OS to bring data into the file cache (prefetch).
   • Actually reads the data into the OS cache (read).
   • Loads data directly into PostgreSQL’s shared buffers (buffer).
3. Reports the number of blocks successfully warmed.

Modes Explained — With Analogy

Imagine you're cooking in a kitchen. The food ingredients are in the fridge (disk), and your cooking surface is the shared buffer.

prefetch: "Assistant, bring the ingredients to the counter"

• What it does: Requests the OS to load the file blocks into its page cache using posix_fadvise().
• Is it fast? Yes. It doesn’t actually read data, just hints the OS to fetch soon.
• Who benefits? Report generation, analytics workloads.
• Downside: No guarantee data is available when you need it.

read: "Go get the ingredients now"

• What it does: Forces the system to actually read() the data, which the OS places into its page cache.
• Is it fast? Slower than prefetch, since it performs actual I/O.
• Who benefits? Slightly more reliable than prefetch if you know exactly what data will be needed soon.
• Downside: Still doesn’t touch PostgreSQL shared buffers.

buffer: "Put the ingredients on my stove right now"

• What it does: Loads data straight into PostgreSQL’s shared_buffers.
• Is it fast? Slower than others, but it gives an immediate performance boost.
• Who benefits? High-traffic OLTP apps, dashboards, and frequently accessed indexes.
• Downside: Uses shared memory, can evict useful pages if overused.

When to Use Each Mode

Real-World Usage Example

Scenario 1: Your OLTP app restarts nightly at 3 AM. The first 10 minutes after a restart are slow due to cold buffers.

Let’s warm up the most-used index:

-- Load index into shared buffers
SELECT pg_prewarm('idx_orders_customer_id', mode := 'buffer');

You can also automate this using a cron job or call it from an extension like pg_cron.

Scenario 2: You're running a daily analytics query at 4 AM

-- Hint OS to warm up blocks beforehand
SELECT pg_prewarm('large_fact_table', mode := 'prefetch');

Advanced: Fork and Block Options

Want to warm up just the first 1000 blocks of a table's main data fork?

SELECT pg_prewarm('my_table', mode := 'buffer', fork := 'main', first_block := 0, last_block := 999);

You can target main, fsm, or vm forks, useful when tuning visibility maps or free space maps.

Pro Tips

• Pair with pg_stat_statements to find frequently used relations to prewarm.
• Use pg_buffercache to check current memory residency before deciding to prewarm.
• Avoid overusing buffer mode, it can evict useful data from shared_buffers.

pg_prewarm is an underused gem. With careful planning and mode selection, you can shave seconds to minutes off cold-start performance. Whether you're prepping analytics reports, running an online shop, or feeding dashboards, picking the right prewarm mode, prefetch, read, or buffer can make a big difference.