Database Caching: Query Caches, Buffer Pools, and Materialized Views

In this tutorial, you will learn about Database Caching: Query Caches, Buffer Pools, and Materialized Views. We cover key concepts, practical examples, and best practices to help you master this topic.

Databases have built-in caching mechanisms: buffer pools cache data pages in memory, query caches store result sets, and materialized views pre-compute expensive joins and aggregations. These operate transparently to application code but require careful tuning.

flowchart TB
    Query[SQL Query] --> Parser[Query Parser]
    Parser --> Optimizer[Query Optimizer]
    Optimizer -->|Cache Check| QueryCache[Query Cache]
    QueryCache -->|Hit| Result[Return Cached Result]
    QueryCache -->|Miss| Executor[Query Executor]
    Executor --> BufferPool[Buffer Pool]
    BufferPool -->|Page Hit| Data[Return Data Page]
    BufferPool -->|Page Miss| Disk[Read from Disk]
    Disk --> BufferPool
    BufferPool --> Data

What You'll Learn

• How database buffer pools cache data pages in memory
• Query cache mechanics and why modern databases deprecated them
• Materialized views for pre-computed query results
• Read replica offloading strategies

Why It Matters

Database caching is invisible to application code but can improve query performance by 10-100x. Tuning the buffer pool size is the single most impactful performance adjustment for a database server.

Real-World Use

An analytics platform uses PostgreSQL materialized views to pre-aggregate daily sales reports. The materialized view refreshes every hour. Queries against it run in 200ms instead of 30 seconds against raw transaction data.

Database Caching Techniques

MySQL Buffer Pool Tuning

-- Check buffer pool size and hit ratio
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';
-- Set buffer pool size to 70% of available RAM
SET GLOBAL innodb_buffer_pool_size = 8589934592;
-- Enable multiple buffer pool instances for concurrency
SET GLOBAL innodb_buffer_pool_instances = 8;

Expected output:

Buffer pool read requests vs reads shows hit ratio. 8GB buffer pool with 8 instances reduces contention on multi-core systems.

PostgreSQL Materialized View

CREATE MATERIALIZED VIEW daily_sales_summary AS
SELECT
    DATE(ordered_at) AS order_date,
    product_id,
    SUM(quantity) AS total_units,
    SUM(price * quantity) AS total_revenue
FROM orders
JOIN order_items ON orders.id = order_items.order_id
GROUP BY DATE(ordered_at), product_id
WITH DATA;

-- Refresh on schedule (hourly)
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales_summary;

CREATE UNIQUE INDEX idx_daily_sales ON daily_sales_summary (order_date, product_id);

Expected output:

Materialized view stores pre-computed aggregation. REFRESH MATERIALIZED VIEW CONCURRENTLY rebuilds without blocking reads. Queries against the view are sub-second.

Read Replica Query Routing

const mysql = require('mysql2/promise');

const pools = {
  master: mysql.createPool({ host: 'master-db', ... }),
  replica1: mysql.createPool({ host: 'replica-1', ... }),
  replica2: mysql.createPool({ host: 'replica-2', ... })
};

let replicaIndex = 0;

async function query(sql, params, options = {}) {
  if (options.readOnly !== false) {
    const replicas = ['replica1', 'replica2'];
    const pool = pools[replicas[replicaIndex++ % replicas.length]];
    return pool.execute(sql, params);
  }
  return pools.master.execute(sql, params);
}

// Usage
const products = await query('SELECT * FROM products WHERE id = ?', [id], { readOnly: true });
await query('UPDATE products SET stock = stock - 1 WHERE id = ?', [id], { readOnly: false });

Expected output:

Read queries (SELECT) are load-balanced across replicas. Write queries (INSERT, UPDATE, DELETE) go to the master. Replica lag is the main trade-off.

Common Mistakes

• Assuming the query cache will solve all performance problems — MySQL query cache is deprecated and harmful under write-heavy workloads.
• Setting the buffer pool too large, causing out-of-memory conditions for the OS or other services.
• Not monitoring buffer pool hit ratio — aim for >99% for OLTP workloads.
• Using materialized views without understanding refresh costs — a full refresh can take minutes on large datasets.
• Routing read-after-write queries (e.g., display the record just inserted) to replicas with Replication lag.

Practice Questions

1. What is the difference between a database buffer pool and a query cache?
2. Why was query cache deprecated in MySQL 8.0?
3. When should you use a materialized view instead of an application-level cache?
4. How does replication lag affect read replica caching strategies?
5. What is the ideal buffer pool hit ratio for an OLTP database?

Challenge

Design a caching Strategy for a reporting dashboard that runs 10 complex aggregation queries against a 10GB PostgreSQL database. Propose which queries to materialize, the refresh schedule, and how to route read vs write queries.

FAQ

Mini Project

Set up a PostgreSQL database with a materialized view for a sales aggregation table. Seed 1 million rows. Write queries comparing raw table scan, indexed query, and materialized view performance. Implement a Node.js query router that sends SELECTs to a replica and writes to the primary.

What's Next

Continue with Cache Invalidation to master strategies for keeping cached data consistent with the source of truth.