An index is a bet: you pay write and storage cost up front so reads can skip most of the table. When a query genuinely needs most of the table, the bet loses: the engine descends a B-tree per row instead of reading the pages once in order. ...
A composite B-tree index orders tuples by its first key, then by the next key within each first-key group. This structure can make a tenant-specific time range cheap to locate. The useful rule is about how much index space a predicate narrows, not a prohibition against mentioning later columns or writing WHERE conditions in a different textual order. ...
A read replica can serve queries while another database handles writes. That can move reporting, search, or other eligible reads away from the writer, but routing a connection is only part of the design. The application must specify which committed changes a result needs to include. This article uses a PostgreSQL physical hot standby, whose normal recovery mode permits reads but does not accept ordinary writes. ...
A sequential identifier makes nearby values easy to guess. Seeing order number four thousand gives an observer a useful place to probe, and collecting identifiers over time may reveal allocation activity. It does not establish that four thousand orders exist, that all numbers represent successful purchases, or that the largest identifier committed last. Those conclusions require information that a sequence does not contain. ...
A foreign key makes a relationship enforceable during ordinary database writes. A task can reference an existing project, and the database can reject an unknown project identifier even when an import or a new application code path forgets to check it. The constraint defines referential integrity; it does not decide whether the selected project is the correct one for the business operation. ...
A slow query is a route problem before it is a tuning problem. EXPLAIN shows the planned route node by node, so the fix targets the step that actually costs time rather than a guess. Read the plan as a route map EXPLAIN prints a tree, and the root node is the answer while the leaves do the real work. Each node names an operation, a row estimate and an estimated cost, so instead of guessing you can look for the outlier: a scan whose row estimate dwarfs the number it eventually returns, or an estimate that is wildly off what the query actually touches. The arrow of blame is worth reading the way a map is: follow the node that accounts for most of the work, not the first one listed in the output. Comparing EXPLAIN alongside EXPLAIN ANALYZE shows the gap between what the planner predicted and what actually ran, which is where slow queries usually hide. ...
A parameterized query sends the database instruction and its values through distinct API arguments. The database interprets the instruction as SQL and uses each parameter where a value belongs. This boundary works in embedded engines such as SQLite as well as client-server databases; it does not depend on a network exchange or on an application explicitly managing named server statements. ...
A transaction lets related database changes commit together or be discarded together. Debiting an account, crediting another, and recording a ledger entry belong to the same decision when the operation requires all three. PostgreSQL provides that boundary through BEGIN, COMMIT, and ROLLBACK. The application must still place the correct statements inside it: atomic execution does not discover a forgotten credit or invent a missing business check. ...