PostgreSQL Shared Buffer

Giriş

Açıklaması şöyle

When you are sending a read or write request to Postgres, you are never interacting with the files directly. In order to read something, first those pages need to be loaded in shared_buffers! 

Proper Amount for Shared Buffer

Açıklaması şöyle

It is recommended in the docs: If (RAM > 1GB) shared_buffers = 25%

Official Docs: Values larger than 40% of RAM might NOT Help

Açıklaması şöyle

Pro tip: If you are setting a PostgreSQL or MySQL, don't use the default database settings because those are meant for personal computers or notebooks.

One example is the Buffer Pool size:

- For MySQL, increase the innodb_buffer_pool_size

- For PostgreSQL, increase shared_buffers and effective_cache_size to match your OS cache size

Örnek

postgresql.conf dosyasında şöyle yaparız

shared_buffers = 256MB
effective_cache_size = 1GB

Örnek

Şöyle

What's the worst PostgreSQL configuration mistake you've seen in production?

I'll start: shared_buffers = 128 MB on a 64 GB server.

Default PostgreSQL configuration. Running in production. For two years. On a database serving 50,000 queries per second.

The database was using 128 MB of its own cache and relying entirely on the operating system's page cache for everything else. It worked — PostgreSQL is remarkably resilient — but it was leaving enormous performance on the table.

Changed shared_buffers to 16 GB (25% of RAM), adjusted effective_cache_size to 48 GB, and query response times dropped 40% overnight.

Other configuration horrors I've seen:

• 𝗺𝗮𝘅_𝗰𝗼𝗻𝗻𝗲𝗰𝘁𝗶𝗼𝗻𝘀 = 𝟭𝟬𝟬𝟬 with no connection pooler. Each connection using 10 MB of RAM. Server running out of memory under load.

• 𝗿𝗮𝗻𝗱𝗼𝗺_𝗽𝗮𝗴𝗲_𝗰𝗼𝘀𝘁 = 𝟰.𝟬 on an NVMe SSD. The planner thought random reads were 4x more expensive than sequential reads, avoiding index scans that would have been faster. Should have been 1.1.

• 𝘄𝗼𝗿𝗸_𝗺𝗲𝗺 = 𝟰 𝗠𝗕 on an analytics workload. Every complex query spilling to disk for sorts and hash joins. Changed to 256 MB and query times dropped from minutes to seconds.

• 𝗮𝘂𝘁𝗼𝘃𝗮𝗰𝘂𝘂𝗺 = 𝗼𝗳𝗳. Yes, someone turned it off. The table bloat was spectacular.

The PostgreSQL defaults are intentionally conservative. They're designed to run on a Raspberry Pi, not to perform well on your production server. Five settings — shared_buffers, effective_cache_size, work_mem, maintenance_work_mem, random_page_cost — capture 80% of the performance gains.

PG_BUFFERCACHE Extension

Shared Buffer kullanımını görmek için kullanılabilir