การปรับแต่งประสิทธิภาพฐานข้อมูล

As SaaS scales, the database is usually the first thing to slow down. Application servers scale easily, but a single database can become a choke point.

This lesson covers practical techniques to keep queries fast under load.

An index is a sorted data structure that lets the database find rows without scanning the whole table.

Without an index, a lookup is a full table scan; with one, it is a fast tree search.

Databases show how they will run a query via EXPLAIN. Look for 'Seq Scan' on large tables (bad) versus 'Index Scan' (good).

A composite index covers multiple columns and helps queries that filter on several fields. A covering index includes all columns a query needs, so the database never touches the table.

Order matters: the leftmost column must be used to benefit.

A common bug: loading a list, then firing one query per item. This N+1 problem turns one page load into hundreds of queries.

Fix it by batching with a JOIN or an IN clause.

Opening a database connection is expensive. A connection pool reuses a fixed set of connections across requests.

Without pooling, traffic spikes exhaust the database's connection limit and everything stalls.

Most SaaS workloads are read-heavy. Read replicas are copies of the primary database that serve read queries, spreading load.

Writes still go to the primary, which replicates changes to the replicas.

Replicas update slightly after the primary, causing replication lag. A user who just wrote data might read a stale value from a replica.

Solution: route reads that need the latest data to the primary (read-your-writes).

OFFSET pagination gets slow on deep pages because the database still scans skipped rows. Use keyset pagination (cursor on an indexed column) instead.

Indexes speed reads but slow writes and consume storage, since every insert must update them.

Index the columns you actually filter and join on, and remove unused indexes. More is not always better.

Enable a slow query log to capture queries above a time threshold. Review it regularly to find new bottlenecks as data grows.

Performance tuning is continuous, not a one-time task.

You learned database performance tuning:

• Indexes (including composite/covering) and reading query plans
• Fixing the N+1 problem and using keyset pagination
• Connection pooling, read replicas, and handling replication lag
• Avoiding over-indexing and monitoring slow queries