Project

General

Profile

Feature #11287

« Previous | Next »

Leverege the pg_prewarm function to improve cold server performance

Added by Artur Școlnic 4 months ago. Updated 4 months ago.

Status:
New
Priority:
Normal
Assignee:
-
Target version:
-
Start date:
Due date:
% Done:

0%

billable:
No
vendor_id:
GCD
case_num:
version_reported:
version_resolved:
reviewer:
production:
No
env_name:
topics:

History

#1 Updated by Artur Școlnic 4 months ago

pg_prewarm is a PostgreSQL extension that loads tables or indexes into memory (the shared buffer cache) before they are accessed. Normally, PostgreSQL keeps recently accessed data in shared buffers for fast access. After a restart, or for large tables that haven’t been accessed recently, the cache is “cold,” so the first query must read from disk. pg_prewarm preloads specified tables or indexes into memory to avoid that initial disk hit.

The two main use cases I have in mind:
  • On FWD server restart: identify relevant tables and preload them.
  • Before frequent or critical requests: identify the most used tables or indexes and preload them.

Preloading takes a relatively small amount of time, depending on table size (roughly 50–500ms). In experiments with a customer application on a cold FWD and PostgreSQL server, using pg_prewarm improved performance across all use cases, with gains ranging from 10% to 50%.

I don’t have the implementation details in place yet, but I believe this could be a valuable asset for FWD.

#2 Updated by Artur Școlnic 4 months ago

An extension of this task is fine tuning the shared buffer cache size, the default is quite modest at 128Mb, I think the customers that have control over their PgSQL server could benefit from increasing it, especially if we find a way to keep it full with relevant data.

Also available in: Atom PDF