Tuning PostgreSQL for performance: the role of random_page_cost

While some PostgreSQL configuration parameters have a direct effect on resource allocation, such as the well-known shared_buffers, others exert an indirect influence on database performance, as is the case with random_page_cost.

When we query the database, an important PostgreSQL component called “planner” is responsible for creating an execution plan for it. To do this, it evaluates possible paths using different strategies and calculates the practical cost for each of them. In other words, it calculates the “effort” required to locate and return the data requested by the query. Finally, it defines the most efficient plan, or the one with the best cost-benefit ratio.

This calculation is purely mathematical and considers both statistical data, such as the cardinality of available indexes, as well as certain variables used to provide hints to the planner and help it estimate the cost of accessing or using a given resource, such as the amount of memory available on the server for data caching (effective_cache_size, which, in addition to shared_buffers, also considers the memory used by the file system cache). Another example is the cost of accessing a given page following the one that is being or has just been read from disk (seq_page_cost), compared to the cost of accessing any other page (random_page_cost).

Historically, these two variables were added to PostgreSQL at a time when servers were still equipped with hard disks, where sequential access is often much faster than random access. Nowadays, servers are equipped with disks that use flash memory (without moving parts), where the access time to any page is similar. Yet, PostgreSQL continues to be shipped configured with a default value for random_page_cost (4) four times greater than that of seq_page_cost (1).

When discussing tuning PostgreSQL performance on modern servers, is it correct to equate these cost values, or are there still situations where a difference exists and we should inform the planner about it?

This presentation clarifies what needs to be measured to answer this question and presents the results of tests conducted for this purpose.