161 Database Essentials interview questions with detailed answers, sorted from basic to advanced. Free to practice, with progress saved automatically.
A curated shortlist of database questions that interviews for several roles tend to touch, centred on SQL with some MongoDB. It covers SQL basics, join types, indexes and B-trees, reading query plans with EXPLAIN, transactions and ACID, locking, schema design, ORMs, SQL versus NoSQL, replication, and the CAP theorem.
and 79 more topics.
A basic SELECT has the form SELECT columns FROM table WHERE condition. For example, to get the name and email of users aged 18 or over: SELECT name, email FROM users WHERE age = 18. SELECT picks which columns to return, FROM says which table…
The three core commands for changing data: INSERT adds new rows, UPDATE modifies rows matching a condition, DELETE removes rows matching a condition. INSERT lists the columns and then the values in that same order; columns you leave out get their DEFAULT or NULL. UPDATE…
An ER Diagram is a sketch of the schema before you code — like a house blueprint before building, it helps avoid costly tear-down-and-rebuild later. Three main components: - Entity (= table): drawn as a rectangle. - Attribute (= column): the key is underlined. -…
CASCADE defines "what happens to child rows when the parent is deleted/updated". The ON DELETE options: - CASCADE: deleting the parent → auto-deletes children too (delete a user → lose all orders, comments, sessions). Convenient but dangerous if set by mistake. - SET NULL: deleting…
Two ways to generate a primary key: - Auto-increment (SERIAL/BIGSERIAL): an increasing number, compact (4/8 bytes), keeps the B-tree index tight, easy to debug. Cons: leaks information (rivals can guess record counts from the id), and is unsafe for distributed systems because it needs a…
It's not "which is better" but "which fits the data". Choose PostgreSQL when: data has clear relationships, you need ACID transactions across tables, a stable schema, complex multi-JOIN queries. PostgreSQL also has the JSONB type (+ GIN index) for fast JSON storage/queries — a middle…
An ORM is a middle layer that maps DB tables to objects in code, so you operate via a programming language instead of hand-writing SQL. Two styles: Active Record (the model holds its own DB logic — Rails, Sequelize) and Data Mapper (the domain object…
Prisma is a next-generation ORM for Node.js/TypeScript, built schema-first: you describe models in schema.prisma, and Prisma generates everything from it. Strengths: - Auto-generated, type-safe client: each prisma generate produces TypeScript types that exactly match the schema → autocomplete and error-catching as you type. - Prisma…
schema.prisma is where Prisma reads its whole database configuration: it generates Prisma Client and migrations from this file. It has three sections: - datasource: the DB type (provider). Since Prisma 7 the connection URL lives in prisma.config.ts. - generator: Prisma Client configuration. - models: table…
Sequelize is a long-established Node.js ORM that supports MySQL, PostgreSQL, SQLite, MSSQL and MariaDB. You define models in JavaScript/TypeScript code and query through the model's methods. A few features worth knowing: - Migrations: sequelize-cli generates files with up/down, and history is stored in the SequelizeMeta…
Mongoose is an ODM (Object Document Mapper) for MongoDB on Node.js. MongoDB does not require documents to have any particular shape, so Mongoose adds a Schema layer on the app side: you declare fields, types and constraints, and every write goes through that layer. What…
Use Redis as a cache when: data is read-heavy, computations are expensive (complex DB queries, API calls), you need to reduce DB load, and brief staleness is acceptable. A few caching strategies: - Cache-aside (most common): check cache → on miss fetch DB → write…
Drizzle is a TypeScript ORM with two defining traits: the schema is plain TypeScript code, and the query API reads almost like a SQL statement. TypeScript infers the types directly from the schema, with no code generation step. That query produces select "id", "name", "age"…
The relational model organizes data into tables (relations): - Column (attribute): a typed field (int, text, timestamp...). - Row (tuple): a single record — one concrete entity. - Table: a set of rows sharing the same columns. Relationships between tables are expressed with keys: each…
- Primary key (PK): uniquely identifies each row; NOT NULL, one PK per table. Usually auto-indexed. - Unique key: guarantees no duplicates, but allows NULL and a table may have several unique constraints. - Foreign key (FK): a column referencing another table's PK → enforces…
Normalization structures tables to reduce redundancy and avoid insert/update/delete anomalies. - 1NF: each cell holds a single atomic value — no repeating groups or arrays in a column; each row is unique. - 2NF: in 1NF and every non-key column depends on the whole primary…
Denormalization deliberately introduces redundant data (duplicating columns, storing precomputed values) to reduce JOINs and speed up reads. Trade-offs: - Pros: faster reads (fewer JOINs/aggregations at query time), good for read-heavy reporting/analytics. - Cons: writes get harder — you must update several places to avoid drift;…
ACID is four guarantees for a transaction — a group of operations run as one unit: - Atomicity: all operations succeed or the whole thing rolls back, never half-applied. - Consistency: a transaction moves the DB from one valid state to another, preserving all constraints…
A transaction wraps several statements into one atomic unit: - BEGIN (or START TRANSACTION): open a transaction. - COMMIT: confirm — make all changes durable and visible to others. - ROLLBACK: undo every change since BEGIN, returning the DB to its prior state. A savepoint…
An index is an ordered data structure holding sorted column values + pointers to the real rows. The most common type is the B-tree (balanced tree): - Root → branch → leaf: the tree is balanced so every leaf is at the same depth; reaching…
Index a column when it: - Appears in WHERE, JOIN, ORDER BY, GROUP BY of frequently run queries. - Has high selectivity — many distinct values (email, userid) so the index filters out lots of rows. - Is a foreign key: it speeds up JOINs…
EXPLAIN shows the execution plan the planner chose for a query; EXPLAIN ANALYZE also actually runs it and reports real timing + row counts (careful with UPDATE/DELETE on production — it really writes). cost is only a relative estimate, not milliseconds; actual time is the…
N+1 happens when you fetch a list then loop, issuing a sub-query for each element: 1 query for N parent records + N queries for children → N+1 round-trips to the DB. Common with ORMs when accessing a relation inside a loop (lazy loading). Example:…
A JOIN combines rows of two tables by a condition (usually a key): - INNER JOIN: keeps only rows that match on both sides. Unmatched rows are dropped. - LEFT (OUTER) JOIN: keeps every left-table row; where the right has no match, fills NULL. -…
- View: a stored query saved under a name — it stores no data. Each time you query the view, the DB re-runs the underlying SQL on the current data → always fresh, no storage cost, but heavy if the query is complex and called…
SQL (relational): data in tables, fixed schema, relationships via PK/FK, standardized SQL language, strong ACID, with multi-table JOINs and transactions built in. The tendency is to scale vertically (a bigger machine) for writes, but that is not the only option: PostgreSQL still scales reads with…
Four main NoSQL families, differing by data model: - Document: self-describing documents (JSON/BSON), flexible schema; fits catalogs, profiles, content. E.g. MongoDB, Couchbase. - Key-value: a map of key → value, extremely fast read/write by key; fits caches, sessions, counters. E.g. Redis, DynamoDB (in its simple…
Choose by query patterns + consistency needs + scaling model, not by hype. Choose SQL when: - Data is well-structured, relational, needs flexible JOINs. - You need ACID transactions and strong consistency (payments, inventory, bookings). - Diverse ad-hoc queries where you don't know all access…
Opening a DB connection is expensive: TCP handshake, authentication, server-side resource allocation. If every request opens then closes a connection, latency is high and the DB runs out of connections. A connection pool keeps a set of open connections ready and reuses them: a request…
Replication copies data across multiple nodes. The common model is leader–follower (primary–replica): - The leader takes all writes, records them in the WAL, then streams the changes to followers. - Followers apply the changes and serve reads. It solves: - High availability (HA): if the…
A read replica is a read-only copy of the primary DB used to offload reads from the write node. The app sends INSERT/UPDATE/DELETE to the primary, while SELECTs that tolerate slightly stale data route to a replica → the primary is freer and the system…
Postgres has two JSON storage types: - json: stores the raw text, preserving whitespace/key order/duplicate keys; every query must re-parse it → fast writes, slower reads/operations. - jsonb: stores a parsed binary form, dropping whitespace, not preserving key order, removing duplicate keys; faster to query/index,…
MongoDB is a document database: - Document: a JSON-like unit of data (field–value pairs), which can be nested (objects, arrays). Each document has an id primary key (an ObjectId by default). - Collection: a group of documents — the equivalent of a "table" but with…
The aggregation pipeline processes documents through a sequence of stages, each transforming the document stream and passing it to the next — like a pipe, similar to SQL GROUP BY/JOIN/transforms but step by step. Commonly used stages: - $match: filter (like WHERE) — put it…
Like relational DBs, MongoDB uses B-tree indexes to avoid scanning a whole collection (COLLSCAN). Every collection has an index on id. Main types: - Single field: index one field. - Compound: several fields in order — follows the leftmost prefix rule like SQL; field order…
A replica set is a group of MongoDB nodes holding the same data for high availability: - Primary: the single node that takes writes; writes are recorded in the oplog (operation log). - Secondary: replicates the primary's oplog and applies it → keeps a copy…
MongoDB models relationships between data in two ways: Embedding: put child data inside the parent document (nested arrays/objects). - Pros: read everything in one query, no joins; atomic writes at the single-document level. - Fits: 1–1 or 1–few relationships, child always read with the parent,…
A cache keeps hot data in a fast layer (RAM, e.g. Redis) → offloads the DB, cuts read latency, handles more load. Fits read-heavy/write-light data, expensive-to-compute results, or data that tolerates slight staleness. Read/write strategies: - Cache-aside (lazy loading): read cache first; on a miss…
Two data workload types, optimized for different goals: OLTP (Online Transaction Processing) — the transactional system running the app: - Many small, fast transactions: read/write a few rows (place an order, update a profile). - Needs ACID, low latency, high concurrency. - Normalized data, indexes…
OFFSET/LIMIT (LIMIT 20 OFFSET 100000) forces the DB to scan and discard every row before the offset before returning 20 → the deeper you page, the slower it gets (cost grows linearly with the offset). Extra risk: if rows are inserted/deleted between page loads, rows…
- Natural key: a key taken from real business data that already has meaning (email, national ID, product code, countrycode). - Surrogate key: an artificial, meaningless key generated purely to identify — SERIAL/IDENTITY (auto-increment), UUID, or Mongo's ObjectId. Trade-offs: - Natural key: no extra column,…
- DELETE: removes rows by condition (DELETE FROM t WHERE ...), can delete a subset. It's DML, logs per-row, runs in a transaction (rollback-able), fires triggers, respects FKs. Deleting many rows is slow and leaves dead tuples (needs VACUUM in Postgres). Does not reset sequences.…
A CTE (Common Table Expression) is a named subquery declared with WITH, reusable like a temp table in the main statement: Versus a subquery nested in FROM/WHERE: - A CTE makes complex queries readable, splitting them into named steps and avoiding repeating the same subquery.…
- LIKE / ILIKE: simple string pattern matching. col LIKE '%from%' with a leading % can't use a B-tree index → full table scan, slow on large data. It has no language awareness: no word stems, ranking, or punctuation handling. - Full-text search (FTS): understands…
SQL is grouped into command families by purpose: - DDL (Data Definition Language) — defines structure: CREATE, ALTER, DROP, TRUNCATE. Acts on the schema (tables, indexes, constraints). - DML (Data Manipulation Language) — manipulates data inside tables: INSERT, UPDATE, DELETE. (SELECT is classed as DML…
NULL means an unknown / missing value — distinct from the number 0 or the empty string ''. Because it is "unknown", SQL uses three-valued logic: every comparison yields TRUE, FALSE, or UNKNOWN. Practical consequences: - NULL = NULL is not TRUE but UNKNOWN →…
Set operations combine the results of two SELECTs (which must have the same column count and compatible types) vertically: - UNION: combines then removes duplicate rows → must sort/hash to deduplicate, so it costs extra. - UNION ALL: combines and keeps every row (including duplicates)…
Three common string types: - CHAR(n) — fixed length, always occupies n characters (space-padded if shorter). The padding is part of the value and travels to the application — except on MySQL, which trims trailing spaces on read, so the same data reads back differently…
Three relationship types (cardinalities) between two entities and how keys implement them: - One-to-one (1:1): each A row maps to at most one B row (user ↔ profile details). Implement with a UNIQUE-constrained FK on one side, or merge into one table. - One-to-many (1:N):…
A self join joins a table to itself — using two different aliases for the same table as if they were two separate tables. Use it when rows within the same table relate to each other. Classic example — an employee-manager relationship (both are rows…
A subquery is a nested query inside another (in SELECT, FROM, or WHERE). - Non-correlated subquery: runs on its own, referencing nothing from the outer query. The DB typically runs it once, takes the result, and uses it in the outer query. - Correlated subquery:…
A window function computes over a set of rows related to the current row but without collapsing rows — each original row is kept, with the computed value added. You recognize it by the OVER (...) clause. Versus GROUP BY: - GROUP BY collapses many…
Redis is an in-memory key–value store (data lives mainly in RAM) → extremely fast reads/writes (typically sub-millisecond). Its strength is that it stores more than strings — it is a "data structure server" with several native types: - String: a single value (page cache, tokens,…
BASE is the consistency philosophy of many NoSQL/distributed systems, positioned against relational ACID as a trade-off. BASE stands for: - Basically Available: the system always responds to requests (availability first), possibly with stale data. - Soft state: the system's state may change over time even…
- Stored procedure: a block of code (SQL + procedural logic like PL/pgSQL) stored and run inside the DB, called by name (CALL proc(...)). It packages multiple statements into one unit running next to the data. - Trigger: code that runs automatically on a table…
SELECT fetches every column of a table. Handy while exploring data, but in production code it causes several problems: - Transferring extra data: pulls columns you don't need (blobs, large text) → wasted bandwidth, RAM, and I/O. - Defeats index-only scans: if you only need…
UPSERT = "insert if absent, update if present" in one atomic statement. It avoids the classic broken "check-then-write" pattern (SELECT → if missing, INSERT), which has a race condition: two requests both see "absent" and both INSERT → a duplicate-key error or doubled data. PostgreSQL…
Floating-point numbers (float, double, real) store values in binary IEEE 754 → many decimal values can't be represented exactly (the classic 0.1 + 0.2 !== 0.3). For money, tiny errors accumulate across many additions/multiplications and throw off balances and reports — unacceptable. The right way:…
Both are the most popular open-source RDBMSs, but they differ in architecture and focus: - Connection architecture: MySQL uses a thread per connection (light on RAM); PostgreSQL forks a process per connection (heavier with many connections → needs a connection pooler sooner). - SQL standard…
Two warm-up problems that appear in almost every SQL round: Second-highest salary (must handle duplicate salaries and the missing case): Use DENSERANK (not ROWNUMBER/RANK) because tied salaries must share one rank with no gaps. Since several people tied at rank 2 still produce several rows,…
SQL injection is the vulnerability where user input is string-concatenated straight into a SQL statement, letting data execute as code: Impact: data exposure (login bypass, reading other tables via UNION SELECT), data modification/deletion, sometimes full server compromise. Parameterized queries (prepared statements) stop it by separating…
- IN compares a value against a list or subquery result: x IN (SELECT ...). Fits small lists or concrete values. - EXISTS checks "does at least one matching row exist" and stops as soon as it finds one (short-circuits). Fits correlated subqueries tied by…
All three are window functions numbering rows by ORDER BY within each partition. They differ on ties: - ROWNUMBER(): a continuous unique number 1, 2, 3, 4... even for equal values (order among equal rows is arbitrary). - RANK(): tied rows share a rank but…
These key concepts all revolve around uniquely identifying a row: - Super key: any set of columns that uniquely identifies a row — it may be redundant (carrying extra unneeded columns). - Candidate key: a minimal super key — removing any column loses uniqueness. A…
- DBMS (Database Management System): software that manages data in general — storing, querying, administering. It covers every model: flat file, hierarchical, network, relational, document... - RDBMS (Relational DBMS): a DBMS following the relational model — data in tables with columns, keys and relationships between…
Core mapping rules: - Entity → a table; attributes → columns; the entity key → primary key. - Multi-valued attribute → split into its own table linked by a foreign key. - 1–1 relationship: embed a foreign key (with UNIQUE) in one of the two…
A SELECT is written as SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY, but is logically evaluated in nearly the reverse order: 1. FROM + JOIN — build the source row set. 2. WHERE — filter individual raw rows. 3.…
Because WHERE is evaluated before SELECT, while ORDER BY is evaluated after. When WHERE runs, the alias does not exist yet. Three fixes for WHERE: The three fixes differ only in how they read: PostgreSQL and MySQL do not merge identical expressions across SELECT and…
They differ in whether NULLs are counted: - COUNT() — counts rows, including all-NULL rows. - COUNT(col) — counts rows where col IS NOT NULL. - COUNT(DISTINCT col) — counts distinct values, ignoring NULL. Every aggregate except COUNT() skips NULL. So AVG(rating) divides by the…
COALESCE(a, b, c, ...) returns the first non-NULL argument — used to supply defaults. NULLIF(a, b) returns NULL when a = b, otherwise a — used to turn a "bad" value into NULL. The pair is commonly combined to guard against division by zero: Plain…
Because NOT IN expands to a chain of ANDed inequality tests (<), and comparing anything to NULL yields UNKNOWN, not TRUE/FALSE. A single NULL in the list makes the whole predicate never TRUE, so no row passes. Three fixes, in preferred order: The key difference:…
Because a filter on the right-hand table was placed in WHERE instead of ON. LEFT JOIN produces rows whose right-hand columns are all NULL; WHERE then runs and discards exactly those NULL rows, leaving the same result as an INNER JOIN. Rules of thumb: -…
Because joining a one-to-many relationship duplicates parent rows. Each extra copy of the parent row makes its value be added one more time. Three fixes: Option 3 rescues COUNT only — it cannot rescue SUM, because two distinct rows may share the same amount and…
Because ORDER BY is non-deterministic when several rows share the same sort value. SQL makes no promise about the relative order of tied rows, so two runs may arrange them differently — a row at the end of page 1 can reappear at the top…
LAG(col, n) reads the value of a previous row in the same partition, LEAD(col, n) a following row. That lets you compare periods without a self join. Four common follow-ups: - The first row has no predecessor → LAG returns NULL, so growthpct is NULL.…
They compute several aggregation levels in a single table scan instead of writing multiple GROUP BY queries joined by UNION ALL. - GROUPING SETS ((a,b), (a), ()) — explicitly lists the groupings you want. - ROLLUP(a, b) — hierarchical totals: (a,b), (a), (). Fits year…
An index is a parallel data structure alongside the table. Reads are fast because a lookup walks a few B-tree levels instead of scanning the whole table, but every write must update the table and every index on it. Concrete write cost: - INSERT: the…
Because the index stores the raw column values, not the results of a function. When the column is wrapped in a function, the database must evaluate that function for every row before comparing — which means reading the whole table. Such a condition is non-sargable…
Usually not, because such a column has low cardinality — too few distinct values, so selectivity is poor. If 90% of rows have isactive = true, going through the index and back to the table for 90% of rows is slower than a sequential scan,…
Because a B-tree is ordered by the value left to right. With LIKE 'abc%' the database knows all matches sit contiguously in the range ['abc', 'abd'), so it jumps straight there — a prefix search. With '%abc%' the leading characters are unknown, matches are scattered…
When the two sides of a comparison have different types, the database must coerce one side to the other. If the coerced side is the column, the index on it becomes unusable — exactly like wrapping the column in a function, except here the database…
The question is whether the index covers both the filter and the sort order. If it matches only the filter, the database must collect every matching row and sort all of them before taking the first 20 — LIMIT does not help. With the composite…
It depends on the database, and this is a common trap when moving between systems. PostgreSQL: B-trees do store NULL, so WHERE col IS NULL can use the index normally. It also means the index covers every row of the table, NULL rows included. Oracle:…
A transaction groups statements into one unit: either all of them take effect or none do. Without one, a transfer can debit account A and then die before crediting B — that money is simply gone. ACID in operational terms: - Atomicity — on a…
The four SQL-standard levels, loosest to strictest, each blocking one more anomaly: Level Dirty read Non-repeatable read Phantom ------------ READ UNCOMMITTED possible possible possible READ COMMITTED no possible possible REPEATABLE READ no no engine-dependent SERIALIZABLE no no no - Dirty read: seeing another transaction's uncommitted…
A lost update happens in a read — modify — write flow: two transactions read the same old value, compute from it, and both write back; the second write overwrites the first, so one update disappears. Three defenses: 1. Atomic single-statement update — let the…
This answer describes PostgreSQL; MySQL is called out explicitly where it differs. A plain UPDATE/DELETE takes a row-level lock only on the rows it actually changes, held until the transaction ends. Other transactions can still SELECT those rows (reading the old version via MVCC); only…
Optimistic locking holds no lock at all. You read the row together with a version number, then include that old version in the WHERE of the write; if someone changed it first, the update affects 0 rows and you know there was a conflict. The…
The snippet below looks tidy but is wrong in two ways: Problem 1 — locks held for the duration of a network call. The row lock on orders is released only at COMMIT. A payment gateway that takes 5 seconds holds that row (and a…
SAVEPOINT marks a point inside a transaction so you can roll back part of it instead of discarding everything. Takeaways: - Relational databases have no true nested transactions. There is one transaction; a second BEGIN does not open a new one. - ORMs (Hibernate, EF…
Three common traps, all producing code that looks transactional while each statement really commits on its own. 1. Autocommit — outside an explicit transaction each statement is its own transaction. Two writes in a row outside a transaction() block mean the first is already committed;…
Because an order is a historical record, while products is live data. If you only keep productid and join to products for the price, changing the price or name tomorrow silently rewrites yesterday's invoice — the printed total no longer matches what the customer actually…
Split the product (marketing info) from the variant (the thing you actually sell). A t-shirt is a product; "black t-shirt, size M" is a variant, and the variant is what carries SKU, price and stock. Key point: carts and order lines always reference variantid, never…
Stock is a warehouse × variant relation, not a stock column on the product table. Separate onhand from reserved. Sellable quantity is onhand - reserved. Placing an order increases reserved; only physical shipment decreases onhand and releases the reservation. Two customers can no longer buy…
Separate the promotion rule, the code, and the redemption — three distinct concepts. Merging them blocks you the moment one campaign needs many codes. Enforcing the limit cannot rely on SELECT count() then INSERT — concurrent requests both read the stale count. Two reliable options:…
A monetary value needs three pieces of information, not one: the amount, the currency, and (when converting) the exchange rate at transaction time. Amount: use numeric/decimal, or an integer count of minor units (cents, dong). Never float/double — binary floating point cannot represent 0.1 exactly,…
The join table stops being a "technical link table" and becomes an entity with its own name. Name it after the business concept (enrollments, memberships, subscriptions) and give it columns like any other table. Composite primary key or a surrogate id? - primary key (studentid,…
A bigint sequence is faster and smaller; a UUID trades that for being client-generatable and for not leaking row counts. What random (v4) UUIDs cost: - Size: 16 bytes vs 8. The primary key is embedded in every secondary index and every foreign key, so…
This is the classic soft-delete trap: the "deleted" row is still physically present, so it still occupies a slot in the unique index. Option 1 — partial unique index (cleanest on PostgreSQL): apply the constraint only to live rows. Deleted rows leave the index entirely,…
Separate two concerns: the customer's current address and the address used by a specific order. The order snapshots the address because it records where the goods were actually delivered. Joining to addresses instead means editing an address silently rewrites every past order, leaving disputes with…
Yes — this is legitimate denormalization (a counter cache), but you must answer the follow-up: who is responsible for keeping the number correct. When denormalizing is right: the read/write ratio is heavily skewed (constant reads, occasional comments), the same computation is repeated in many places,…
Four options, differing in who owns the list of valid values. Integer codes (0, 1, 2...) — smallest, but raw data is unreadable and the meaning lives in application code. If someone reorders the enum in code, all historical data silently changes meaning. Rarely worth…
TTL is how long an entry lives before being evicted automatically. There is no single right number — pick it from how stale the business can tolerate: - Near-static data (catalogs, config): hours to a day. - Read-heavy, moderately changing data (article lists, profiles): 1-10…
A key must be self-describing and contain every parameter that affects the result. A common convention: <app:<entity:<id:<variant. Rules: - A missing parameter is a bug: if the response varies by locale, currency, or user role and the key does not encode it, two users will…
Do not cache when the cost of one stale read exceeds the speed gain: - Permissions and account state: roles, permissions, banned/locked flags. An admin revokes access, the cache holds for 10 more minutes → the user keeps acting for those 10 minutes. - Balances,…
A low hit ratio means most requests still reach the DB while you keep paying to run a cache. Check in this order: 1. Are the keys over-fragmented? Keys carrying unnecessary parameters (timestamps, request ids, tracking params) mint a fresh key per request — hit…
Default to deleting (invalidating) and letting the next read reload from the DB. Why deleting is safer than overwriting: when two requests update the same record, the order of cache writes can be the reverse of the DB commit order (request A commits later but…
This is a cold cache: traffic that relied on the cache suddenly lands on the DB. Address it in three layers: 1. Do not let the cache die with the deploy. Keep the cache in a separate process (Redis), not in-process in the app, so…
Three approaches, from simplest to most durable: 1. Dual write in application code. After the DB commit, index into ES. Simple but not atomic: if the DB commits and ES fails, the two diverge permanently and silently. Only for small systems that can afford a…
Postgres full-text search (tsvector/tsquery with a GIN index) is enough for most mid-sized applications: searching titles/descriptions over hundreds of thousands to a few million rows, combined with regular relational WHERE clauses. The biggest advantage: one source of truth, no sync pipeline, no lag, and direct…
This breaks read-your-writes: an asynchronous replica always trails the primary by some replication lag, and the read hits the replica before the WAL is applied. Options, chosen by how strict you need to be: 1. Session-based routing (simplest and most common). After a user performs…
From closest to the user outwards — each layer absorbs part of the traffic for the next: 1. Browser — Cache-Control: max-age, local HTTP cache. Cheapest: no request leaves the client. 2. CDN / edge — static assets and even public API responses. Absorbs traffic…
A CDN stores responses under a cache key. By default the key is method + host + path + query string; a Vary response header adds request headers to that key. Practical consequences: - Extra query parameters (?utmsource=...) create different keys for identical content →…
A view is a named SELECT used like a virtual table. A function takes parameters, returns a value or a set of rows, and is called inside a SQL statement. A stored procedure is a block of logic run with CALL/EXEC; it can perform several…
Four tables are enough for a first version: users, lists, tasks, and a listmembers join table for sharing. A user has many lists, a list has many tasks, and the many-to-many relationship between users and lists goes through the join table. Decisions worth explaining in…
A foreign key guarantees no orphaned data: orders.userid is sure to point at a real user, and the DB blocks deleting a user who still has orders. Use them by default; dropping them is a decision that needs a reason, not the default. What you…
