--- title: "02 — Backend Performance: Databases (The 14-item Atlas of Where Slowness Hides)" uid: backend-performance-databases tags: ["n-plus-one", "indexes", "sharding", "postgresql", "roadmap:backend-performance", "backend", "databases", "pagination", "performance"] excerpt: "14 of the 45 backend-performance items are about databases, and one thread holds them: most backend slowness is the database, and most database slowness is asking it for the wrong things in the wrong way." date: 2026-08-13T04:50:28+0000 source: https://www.aveshina.my.id/en/blog/backend-performance-databases --- The largest section of the performance roadmap is also the one where most real-world slowness hides: databases, with 14 of the 45 items [1]. The thread that organizes them for me: **most backend slowness is the database, and most database slowness is asking it for the wrong things in the wrong way.** The 14 items split cleanly into five clusters. - **The connection layer** (#4, #5) — pooling, before any query runs. - **What you ask for** (#6, #7, #8, #10, #12) — indexes, ORM traps, lazy/eager, projection, JOINs. The biggest cluster. - **The shape of results** (#9, #11) — pagination and denormalization. - **Maintenance** (#13, #14, #17) — cleanup, slow-query log, profiling. - **Scale-out** (#15, #16) — replication and sharding. ## The connection layer Every query pays a connection cost. Two items before any SQL runs: - **Use connection pooling to reduce connection overhead** — opening a database connection is expensive: the TCP handshake (the two sides introducing themselves over the network), the TLS encryption setup, the DB forking/spawning a backend process (Postgres) or allocating a session (MySQL), auth, and an initial transaction state. Do this per request and a fast endpoint becomes a slow one. A pool (PgBouncer in front of Postgres; HikariCP for the JVM; pgx's built-in pool for Go) holds a set of warm connections and hands them out. The win is both latency (no connect cost per query) and capacity (the DB doesn't fork a process per client). - **Fine-tune connection pool settings** — defaults are almost always wrong. The knobs that matter: max_connections (too high and the DB spends its time context-switching; too low and requests queue), idle_timeout (reclaims long-idle connections so a quiet night doesn't hold the pool forever), and connection_reuse (a leaky pool that returns broken connections looks fine until load arrives). The rule I use: max connections roughly matches the DB's max_connections divided by the number of app instances, with headroom for migrations; idle timeout a minute or two; borrow timeout short enough that pool exhaustion surfaces as a 503, not a hang. Two items, one idea: connections aren't free, and the defaults are for laptops not production. ## What you ask for The biggest cluster — five items — is all about the query itself. - **Create efficient database indexes** — the single highest-leverage move in this whole section. An index turns a read-every-row scan into a direct jump to the right rows — like a book's index sending you straight to the page instead of flipping through the whole thing. The mental check is to run EXPLAIN on every slow query and look for Seq Scan on large tables; that's where an index is missing. For queries that filter on several columns, a composite index covers all of them at once, ordered so the column that narrows things down the most comes first. Don't index everything — every index costs writes and disk — but the queries on the hot path deserve indexes. - **Keep an eye on and fine-tune ORM queries** — ORMs hide SQL behind method calls, and they hide _N+1_ worst of all. The classic N+1: fetch a list of 100 entities, then for each entity fetch its related entity. 1 + 100 queries. The fix is eager loading (selectin/joinedload in SQLAlchemy, Include in EF, select_related/prefetch_related in Django ORM). N+1 hides because the per-query time looks small; the total time bleeds because of round trips. Watch the SQL log when something feels slow. ```figure N+1 (LAZY LOADING) EAGER (ONE JOIN) 1 query: SELECT * FROM orders → 100 rows returned × 100: SELECT * FROM customer WHERE id=? one query per row → 100 round trips 1 query: SELECT o.*, c.* FROM orders o JOIN customers c ON o.cust = c.id → 100 rows, with customers attached 101 round trips × ~100 ms = 10.1 s 1 round trip × ~100 ms = 0.1 s ~10 seconds The per-query time looks fine — it's the count that kills you. ~100 ms Same data, one round trip, 100× faster. ``` - **Utilize lazy loading, eager loading, and batch processing to optimize data retrieval** — the N+1 fix on the previous line, generalized. Lazy: load relations on first access (right when you don't always need them). Eager: fetch related rows up front in a JOIN or IN (…) (right when you almost always need them). Batch: load a set of relations in one query for a set of parent rows (selectin strategy in SQLAlchemy, WHERE id IN (…) in Django). The decision is per-relationship, per-endpoint; the wrong default (always lazy) is where N+1 lives. - **Avoid `SELECT * queries and fetch only required columns** — two reasons. First, bytes: pulling 20 columns when you use 3 wastes bandwidth, memory, and serialization. Second, indexes: covering indexes can satisfy a query without touching the heap only if you projected the columns the index holds. The fix is explicit column lists in the query (and in the ORM, a only(…)/load_only(…) modifier). SELECT *` is the lazy default that quietly defeats every column-aware optimization. - **Optimize JOIN operations and avoid unnecessary joins** — JOINs are how relational databases _work_, but each JOIN is a candidate for slowness, especially on unindexed foreign keys or with many-way joins. Two moves. First, ensure the join columns are indexed (foreign keys are not auto-indexed in Postgres). Second, question the join itself: if you need 3 columns from a 100-column table, denormalize those 3 rather than paying the join on a hot path. The rule of thumb from EXPLAIN: a nested loop join with no index on the inner table means every row of one table scans every row of the other — N rows times M rows — and will hurt. ## The shape of results Two items on what comes back. - **Implement efficient pagination for large datasets** — LIMIT/OFFSET is the simple pagination and the wrong default for large pages. OFFSET 100000 still scans 100,000 rows before skipping them; cost grows with page depth. **Keyset pagination** (WHERE id > ? ORDER BY id LIMIT ?) takes the same time on page 10,000 as on page 2 — the index gets you straight to the cursor. Right for infinite scroll, comment threads, log viewers. Save OFFSET for cases where you genuinely need random page access and the offset is bounded. - **Consider denormalizing schema for read-heavy workloads and reducing JOIN operations** — the relational correctness gospel is "normalize." The performance reality is that every read fans out across joins, and read-heavy workloads pay that cost per request. Denormalization duplicates data so a single read needs one table: a order_summary materialized from the join, or a user_badges_count column maintained by a trigger. Trade: writes get more complex (every write to a source has to update the denormalized copy). Right when reads vastly outnumber writes; never for transactional correctness. ## Maintenance Three items — the quiet work that keeps the DB fast over time. - **Regularly clean up unused data and perform maintenance tasks like vacuuming, indexing, and optimizing queries** — Postgres in particular needs VACUUM to reclaim space from deleted/updated rows (its MVCC design keeps the old version of every changed row around until you sweep it up) and ANALYZE to refresh planner statistics so the query planner picks the right index. Autovacuum handles it but the defaults are conservative; tune for write-heavy tables. Old indexes on dropped queries, bloat from churned tables, orphaned data — a quarterly maintenance pass. - **Enable slow-query logging and monitor it** — turn on log_min_duration_statement (Postgres) or the slow query log (MySQL), set the threshold to your SLA, and route the output somewhere monitored. The slow log tells you, continuously, which queries the planner thinks are slow. It's the cheapest, most underused observability signal in the database. - **Use profiling tools offered by your database** — Postgres' EXPLAIN (ANALYZE, BUFFERS), MySQL's EXPLAIN ANALYZE, the pg_stat_statements view for aggregate stats. Profiling a slow query shows you where the time actually goes (planning vs. execution, heap vs. index, cache hits vs. reads), and the question "is this slow because of a missing index or because of returning too much" has a concrete answer in the plan. The throughline of these three: tune measured, not speculative. Run the planner, read the slow log, fix the things the data points at — not the things you suspect. ## Scale-out The last two items move past a single database. - **Set up database replication for redundancy and improved read performance** — replication (a primary with one or more read replicas) gives you redundancy (failover when the primary dies) and read throughput (route reads to replicas). Right when reads dominate writes — most web apps — and replication is a smaller step than sharding. Trade: replicas are eventually consistent; writes need to go to the primary, and a read from a lagging replica immediately after a write can show stale data. Plan for it: read-after-write from the primary for the user's own writes, replicas for everyone else's. - **Use DB sharding for data distribution if required** — the last resort. Sharding splits a table across multiple databases by a shard key (user_id, region). It scales writes (each shard takes a slice of the load) and lets storage grow beyond one box. The cost is enormous: cross-shard queries become hard or impossible, joins across shards are out, transactions are per-shard, resharding later is a six-month project. Right when a single write-primary genuinely can't keep up; almost never the right move before that point. The roadmap phrase "if required" is doing real work in that item. ## Why databases dominate this section Fourteen of 45 backend items live here, more than any other section. The reason is structural: a backend service's slowest leg is almost always the database, and a backend service's _fastest_ available fix is also almost always the database. Indexes, eager loading, keyset pagination, keyset joins, slow-query log, replication. These are not exotic — most of them are a config change or a method-call modifier. The thing they have in common is that the database is the only backend component where _asking the wrong way_ costs a tenth of a second per request instead of a millisecond. Get the asks right and the rest of the stack usually follows. If I had one rule out of the 14 it would be: run EXPLAIN (ANALYZE, BUFFERS) on the slow log's worst queries, every week. The plan tells you which of these 14 items you actually need — and which you don't, which is just as valuable. ## References - [1] roadmap.sh, "Backend Performance Best Practices — Databases," roadmap.sh, 2024. [Online]. Available: [https://roadmap.sh/backend-performance-best-practices](https://roadmap.sh/backend-performance-best-practices) - [2] PostgreSQL, "Using EXPLAIN," PostgreSQL Documentation, 2024. [Online]. Available: [https://www.postgresql.org/docs/current/sql-explain.html](https://www.postgresql.org/docs/current/sql-explain.html) - [3] Use the Index, Luke!, "The Indexing Site," 2024. [Online]. Available: [https://use-the-index-luke.com/](https://use-the-index-luke.com/) - [4] SQLAlchemy, "Relationship Loading Techniques," SQLAlchemy Docs, 2024. [Online]. Available: [https://docs.sqlalchemy.org/en/20/orm/queryguide/relationships.html](https://docs.sqlalchemy.org/en/20/orm/queryguide/relationships.html) - [5] PGbouncer, "PgBouncer Documentation," 2024. [Online]. Available: [https://www.pgbouncer.org/](https://www.pgbouncer.org/) - [6] Citus Data, "Sharding vs Partitioning," Citus Blog, 2024. [Online]. Available: [https://www.citusdata.com/blog/2018/09/11/sharding-vs-partitioning-whats-the-difference/](https://www.citusdata.com/blog/2018/09/11/sharding-vs-partitioning-whats-the-difference/)