How to Build a Fast Database Layer

Why a Fast Database Layer Matters

When a reader opens an article, the server typically reads the content, related stories, comment counts, and navigation from the database. If those reads do not finish within a few milliseconds, time to first byte (TTFB) grows. Slow TTFB hurts both the reading experience and how efficiently search engines crawl your site.

The problem gets worse under bursty traffic. When a story goes viral, thousands of requests run the same queries at once. If the layer is poorly designed, the database becomes the bottleneck and the site falls over. The goal is low latency under normal load and stability under sudden spikes.

There is also the matter of resources the database shares with the web server. While the application server handles a request, if the database is slow to respond, the thread assigned to that request waits; as waiting requests pile up, even a server that looks resource-rich stops responding. In other words, database latency directly caps how many requests you can handle concurrently.

Choosing the Right Database Model

For most news sites, a relational database (PostgreSQL or MySQL/MariaDB) is the right starting point. Articles, categories, authors, and comments have clear relationships, and relational engines store this kind of structured data consistently. NoSQL options are valuable for specific workloads, but they are not required for every problem.

Schema Design and Normalization

Speed often starts with a good schema. Normalization reduces duplicated data and preserves consistency. But over-normalizing can force many joins per page. In practice you aim for balance: keep core data normalized, and deliberately denormalize a few frequently read fields where it pays off.

Pick the narrowest correct data type for every column. Using a date/time type instead of text for dates, or a small integer or enum for short states, cuts both storage and comparison cost. Keep primary keys small and immutable.

When you design the schema, think about the access pattern, that is, how you will read the data. Tables should not only represent the "correct" form of the data but also make the reads that pages need easy. If a story's summary, title, and cover field for the home page all live together in one row, list pages fill from a single query. A schema that is "clean on paper" but ignores the access pattern can lead to many unnecessary joins in production.

• Plan the fields you filter on most (publish date, category, status) up front.
• Move large text and media into separate tables or object storage.
• Split heavily written fields such as counters off the main row to reduce locking.
• Denormalize only for a measurable gain, and do it deliberately.

Indexing: The Foundation of Fast Queries

An index lets the database find a value without scanning the whole table. The right index can turn a query that takes seconds into one that takes milliseconds. As a rule, columns used in WHERE, JOIN, and ORDER BY clauses are candidates for indexing.

In composite (multi-column) indexes, column order matters. If you sort articles within a category by date, an index that covers both the category and the date column can satisfy the filter and the sort in a single pass.

-- Index for filtering by category and paging by date
CREATE INDEX idx_articles_category_date
    ON articles (category_id, published_at DESC);

SELECT id, title, summary, published_at
FROM articles
WHERE category_id = 12
  AND status = 'published'
ORDER BY published_at DESC
LIMIT 20;

Query Optimization and Execution Plans

Behind a slow page there is usually one problematic query. The EXPLAIN command (EXPLAIN ANALYZE in PostgreSQL) shows how the database runs a query: is it using an index, or scanning the entire table?

-- See the real execution plan and timing of a query
EXPLAIN ANALYZE
SELECT id, title
FROM articles
WHERE category_id = 12
ORDER BY published_at DESC
LIMIT 20;

• If you see a sequential / full-table scan, a suitable index may be missing.
• Select only the columns you need instead of SELECT *.
• Avoid the N+1 query trap: do not fire a separate query per row in a list.
• For paging, prefer keyset pagination over very large OFFSET values.

Also remember to help the query planner. Running a function on a column (for example, computing the year of a date) often makes the index on that column unusable; instead you rewrite the query so the condition applies to the raw column. These small-looking details decide the difference between a full-table scan and indexed access.

Connection Pooling

Every database connection consumes resources. Opening and closing a new connection per request adds latency and, under heavy traffic, drowns the server in connection count. A connection pool reuses a set of already-open connections to cut that cost.

Keep the pool size realistic. A pool that is too large can backfire by keeping the database busy with too many concurrent requests. The right size depends on core count, disk speed, and query profile, and is best found by measurement. Systems like PostgreSQL can also sit behind an external pooler.

Write Load and Transactions

Speeding up reads matters, but keeping the write side under control matters just as much. Every comment, every view counter, and every new article is a write. Keep transactions as short as possible: a transaction that stays open a long time locks the rows involved and makes other requests wait.

Counters that increment very frequently (views, likes) are hard on the database because they create heavy concurrent writes to the same row. Buffering such values in memory first and writing them in batches at intervals significantly reduces lock contention. For bulk imports, use batched inserts rather than one-by-one writes.

The Caching Layer

The fastest query is the one you never run. Data that is read often and changes rarely (home page lists, category pages, popular stories) can be held in a cache, so the same data is not recomputed from the database on every request.

The critical issue with caching is validity: when data changes, you must invalidate the related cache entry. When an article is updated, the lists and pages that contain it should be refreshed. For details, see how caching works.

• In-process cache: small, hot data kept in memory.
• Shared cache store: data common across multiple servers (e.g. Redis).
• HTTP / edge cache: full pages or fragments cached at the CDN level.
• Materialized views: precomputing heavy aggregate queries.

Read Replicas and Scaling

News sites are read-heavy; writes (new articles, comments) are a small share of traffic. That shape suits scaling with read replicas: writes go to the primary, and reads are spread across one or more replicas.

Most replication setups are asynchronous, so data can take a brief moment to appear on a replica. When you must show freshly written data to the same user immediately, read it from the primary for that case.

Hardware, Storage, and Memory

A database runs fastest when it can keep its hot data (actively read tables and indexes) in memory. Enough RAM reduces disk reads. For storage, using SSD/NVMe instead of spinning disks noticeably improves random-read performance.

• Aim to keep the working set (frequently accessed data plus indexes) in memory.
• SSD/NVMe storage is far better suited to database workloads than spinning disks.
• Keeping the database and web server on separate processes/resources aids predictability.
• Take backups regularly, and actually test the restore process.

Monitoring, Maintenance, and Longevity

You cannot improve what you do not measure. Continuously track metrics such as query times, connection counts, cache hit ratio, and disk usage. That way you see where a slowdown comes from with data, not guesswork.

• Keep the slow query log on and review it regularly.
• Keep statistics up to date; the planner's decisions depend on them.
• Periodically clean up unused indexes and bloated tables.
• Apply schema changes off-peak, and test them in staging first.

Practical Notes for News Sites

Because the news feed changes constantly, home and category lists are the most-read queries; caching them makes a big difference. When ingesting external RSS and XML feeds, use batch writes; group items instead of opening a separate transaction for each one.

In a traffic surge from a headline story, that single page's query can become the bottleneck for the whole system. Caching popular content aggressively and spreading reads across replicas absorbs these spikes.

Do not overlook the archive either. Stories accumulated over the years grow the table, and old, rarely read content can slow current queries. Treating frequently accessed recent data as hot and handling old data separately, and using date-based partitioning where needed, are effective ways to preserve performance as tables grow. In short, a fast database layer is not a one-time setup but ongoing, measurement-driven maintenance.