Databases Study Portal

Optimization — where the wins actually are

Indexes

An index is a separate, sorted data structure (usually a B-tree) that lets the database find rows by a column's value in O(log n) instead of scanning the whole table (O(n)).

• Clustered / primary — the table rows are physically stored in PK order (one per table). Range scans on the PK are cheap.
• Non-clustered / secondary — a separate structure mapping the indexed column(s) → a pointer to the row.
• Composite index (a, b, c) — sorted by a, then b, then c. The leftmost-prefix rule: it can serve WHERE a=…, WHERE a=… AND b=…, but not WHERE b=… alone. Order the columns most-selective / most-used-as-equality first.
• Covering index — includes every column the query needs (via the key or INCLUDE), so the engine answers from the index alone — an index-only scan, no trip to the table.
• Unique index — enforces uniqueness and speeds lookups.
• Partial / filtered index — indexes only some rows (WHERE status = 'active'), smaller and cheaper.

When NOT to index: low-cardinality columns (a bool / status with 3 values rarely helps), tables that are tiny, and write-heavy tables where the index maintenance cost outweighs the read gain. Every index must be updated on every INSERT/UPDATE/DELETE and costs storage. Unused/redundant indexes are pure overhead.

Query plans — measure, don't guess

EXPLAIN shows the planner's chosen strategy; EXPLAIN ANALYZE actually runs it and shows real timings and row counts.

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

Read it for:

• Seq Scan (full table scan) on a big table in a hot query → usually a missing index.
• Index Scan / Index Only Scan → good; the index is being used.
• Estimated vs actual rows wildly off → stale statistics (ANALYZE the table) → bad plan choices.
• Nested Loop over a large outer set → often the shape of an N+1 or a missing join index.

SARGability — let the index do its job

A predicate is SARGable (Search-ARGument-able) if the engine can use an index for it. Wrapping the indexed column in a function or doing math on it defeats the index:

-- ❌ not SARGable: function on the column → index ignored, full scan
WHERE YEAR(created_at) = 2026
WHERE UPPER(email) = 'A@B.COM'

-- ✅ SARGable: the column is bare, the index range applies
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'
WHERE email = 'a@b.com'   -- normalize/store lowercased, or use a functional index

Other index-defeaters: leading-wildcard LIKE '%term', implicit type conversions (comparing a varchar column to an int), and OR across different columns (sometimes better as UNION).

The N+1 problem

You run 1 query to fetch a list, then 1 more query per row to fetch a related thing — 1 + N round-trips. It's the single most common ORM performance bug. Fix by fetching the related data in one query: a JOIN / projection, or an eager-load (Include in EF Core). See the Data Access module for the EF Core specifics.

Pagination — keyset beats offset at scale

-- ❌ OFFSET: the DB still reads and discards the first 100000 rows
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 100000;

-- ✅ keyset / "seek": jump straight in using the last id seen
SELECT * FROM orders WHERE id > :last_id ORDER BY id LIMIT 20;

OFFSET gets linearly slower the deeper you page; keyset pagination stays constant-time because it uses the index to seek. The trade-off: keyset gives "next page" cleanly but not "jump to page 500".

Transactions & isolation levels

Higher isolation = fewer anomalies but more locking/aborts. The read phenomena, from worst to best prevented:

Pick the lowest level that's correct for the use case. Most OLTP is fine on Read Committed; reach for Serializable (or explicit row locks like SELECT … FOR UPDATE) when you have read-modify-write races such as inventory decrements.

Locking, deadlocks & MVCC

• Locks serialize access to a row/table so writers don't clobber each other.
• A deadlock is two transactions each holding a lock the other wants; the database detects the cycle and kills one (it gets a deadlock error to retry). Prevent by acquiring locks in a consistent order and keeping transactions short.
• MVCC (Multi-Version Concurrency Control, used by Postgres/InnoDB) keeps multiple row versions so readers don't block writers and writers don't block readers — a reader sees a consistent snapshot while a writer creates a new version. The cost is dead-version cleanup (Postgres VACUUM).

Connection pooling

Opening a DB connection is expensive (TCP + auth + session setup). A pool keeps a set of open connections and hands them out per request. Tune the pool size to the database's capacity, not the app's request rate — too many connections thrash the DB. For serverless / very high fan-out, put an external pooler (e.g. PgBouncer) in front.

Caching & replicas

• Read replicas — copies that take read load off the primary; reads can be slightly stale (replication lag).
• Application cache (e.g. Redis) — keep hot results out of the DB entirely; the hard part is invalidation when the underlying data changes. See the Redis module.

SQL vs NoSQL (the short version)

Relational/SQL: strong schema, joins, ACID, great for transactional data and ad-hoc queries. NoSQL (document/key-value/wide-column): flexible schema and easy horizontal scaling, you model around your access patterns and often denormalize, with weaker cross-document transactions. Choose by access pattern and consistency needs — not hype. "Boring" Postgres handles the vast majority of workloads.