A buffer pool is the area of main memory in which a database engine keeps copies of data and index pages read from disk, so that later queries can find them in RAM instead of waiting for storage. In the performance work of relational databases, it is usually the single most important memory setting, because the share of page requests served from memory decides how much disk I/O a workload causes.
Different engines use different names for the same idea: the InnoDB buffer pool in MySQL and MariaDB, shared buffers in PostgreSQL, the buffer cache in Oracle and the buffer pool in SQL Server and Db2. The principle is the same everywhere: a fixed set of page-sized frames, a lookup table that maps page identifiers to frames, and a replacement policy that decides which page leaves memory when a new one must come in.
How a buffer pool serves page requests
A buffer pool works as a page cache managed by the database itself rather than by the operating system. When the executor needs a row, the storage engine asks for the page that holds it, checks a hash table of resident pages and either returns the frame immediately (a buffer hit) or reads the page from disk into a free frame (a buffer miss).
Each frame carries metadata: a pin count that prevents eviction while a thread uses the page, a dirty flag that marks pages changed in memory but not yet written, and usage information for the replacement policy. Modified pages are not written immediately; they are flushed later by background writers and checkpoints, while durability is guaranteed by the write-ahead log.
The following table lists the main components that every buffer pool implementation contains, whatever the engine calls them.
| Component | Role | Effect on performance |
|---|---|---|
| Page frames | Fixed-size slots, typically 8 KB or 16 KB | Define how many pages fit in memory |
| Page table | Hash map from page ID to frame | Makes the hit check a constant-time lookup |
| Replacement policy | Chooses the victim frame on a miss | Decides which data stays hot |
| Dirty page list | Tracks modified, unwritten pages | Controls flushing and checkpoint cost |
| Free list | Frames ready for immediate reuse | Avoids synchronous eviction during reads |
| Latches | Short locks protecting frames and lists | Limit concurrency under heavy load |
Replacement policies and the hit ratio
The replacement policy decides which page is evicted when the pool is full, and it matters most when the working set is larger than memory. A plain least recently used (LRU) list is easy to reason about, but one large table scan can push every useful page out of memory, an effect known as cache pollution.
Production engines therefore use variants. InnoDB splits its LRU list into a young and an old sublist and inserts new pages at the midpoint, so a scan touches only the old part. PostgreSQL uses a clock sweep with usage counters and small ring buffers for bulk reads and VACUUM. Oracle and SQL Server use comparable touch-count or LRU-K schemes.
| Strategy | How it chooses a victim | Strength | Weakness |
|---|---|---|---|
| Simple LRU | Least recently accessed page | Simple and predictable | Polluted by large scans |
| Midpoint LRU | New pages enter the old sublist first | Protects hot pages from scans | Needs tuning of the old ratio |
| Clock sweep | Rotating hand decrements usage counters | Low overhead, good concurrency | Approximates recency only |
| LRU-K | Uses the K-th most recent access time | Separates frequent from one-off reads | More bookkeeping per page |
The usual health indicator is the buffer hit ratio, the share of logical reads served from memory. For OLTP workloads it is commonly above 99 percent, but the ratio alone can mislead: a high value can hide an inefficient query that reads millions of cached pages, and an analytical workload may run well with a lower ratio because it reads data sequentially.
Sizing and configuration parameters
The size of the buffer pool is the first parameter to set, and the right value depends on the engine’s relationship with the operating system page cache. InnoDB normally uses direct I/O and wants most of the memory; PostgreSQL deliberately relies on double buffering with the OS cache and keeps shared buffers smaller.
| Engine and parameter | Common starting point | Main consideration |
|---|---|---|
| MySQL innodb_buffer_pool_size | 50-75 percent of RAM | Leave room for connections and the OS |
| PostgreSQL shared_buffers | About 25 percent of RAM | The OS page cache holds much of the rest |
| SQL Server max server memory | RAM minus an OS reserve | Prevents memory pressure on Windows |
| InnoDB buffer pool instances | Several on large pools | Reduces latch contention between threads |
These values are starting points, not rules. The real target is the active working set: the hot pages of frequently used tables and indexes. A pool that already holds the whole working set gains almost nothing from more memory, while a pool slightly too small for it can turn a fast system into an I/O-bound one, because misses grow sharply once the hot set no longer fits.
Other relevant settings control flushing behavior: the number of background write threads, the target share of dirty pages, the checkpoint interval and options such as warming the pool after restart. InnoDB can dump and reload the list of cached pages, which shortens the cold-start period after maintenance from hours to minutes.
How the buffer pool relates to neighboring concepts
The buffer pool is often confused with other caches, but it caches pages, not results. It sits below the query layer, so every query benefits from it, while other mechanisms store finished answers or reuse connections.
| Mechanism | What it stores | Typical use |
|---|---|---|
| Buffer pool | Raw data and index pages | Every read and write path |
| Query cache | Complete result sets of queries | Repeated identical read queries |
| Materialized view | Precomputed query results on disk | Expensive aggregations |
| Connection pooling | Open client sessions | Reducing connection setup cost |
| OS page cache | File blocks managed by the kernel | Secondary cache below the engine |
Query optimization and indexing work together with the pool. A good execution plan touches fewer pages, which leaves more room for other data, so tuning queries often helps more than adding memory. Index fragmentation has the opposite effect, because half-empty pages waste buffer space.
Common problems and warning signs
Most buffer pool problems appear as rising read latency together with growing physical reads. The causes are usually predictable once the symptoms are matched with the right metrics.
- Undersized pool: physical reads climb as data grows, and latency follows the working set out of memory.
- Oversized pool: the server starts swapping, which is far worse than an occasional cache miss.
- Scan pollution: nightly reports or backups evict hot pages, and morning traffic starts slowly.
- Flush storms: too many dirty pages force synchronous writes, causing latency spikes at checkpoints.
- Latch contention: many threads compete for the same list or hot page, which more instances or partitioning can reduce.
The most frequent mistake is to treat the hit ratio as the goal. A better approach compares physical reads per second, wait events for page reads and the latency of key queries before and after a change.
A non-technical view
Imagine a library where most books stay in a distant warehouse. Fetching a book takes a long time, so the librarian keeps a shelf of the most requested books right behind the desk. The buffer pool is that shelf for a database.
When a question needs a page of data, the database first looks on the shelf. If the page is there, the answer comes back almost instantly. If not, someone walks to the warehouse. Because the shelf has limited room, rarely used pages are put back to make space. A bigger and smarter shelf means fewer slow trips, and that is why this memory area matters so much for speed.
A concrete example
An online store runs MySQL on a server with 64 GB of RAM. The InnoDB buffer pool was set years ago to only 8 GB, while the frequently used order, customer and product tables with their indexes now take about 30 GB. During sales peaks, page reads from SSD rise sharply and checkout queries slow from a few milliseconds to several hundred.
The administrator raises the buffer pool to 44 GB in eight instances, enables saving and reloading of the cached page list at restart, and moves the nightly export to a replica so that its full scans no longer pollute the primary. After the change, physical reads during peaks fall by more than an order of magnitude, and checkout latency returns to single-digit milliseconds without new hardware.