An index trades storage and write work for a possible reduction in read work. A full scan can win when the table is small or a predicate selects much of it, but neither row count nor selectivity decides alone. Projection, ordering, caching, row width, and available access paths change the comparison. Start with the query’s actual result contract. ...
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. ...
EXPLAIN shows the execution plan selected for a statement. It helps locate expensive work and mistaken estimates, but a slow request can also involve locks, network transfer, or application behavior outside the plan. Use plan evidence to test a specific hypothesis rather than assuming that every performance problem requires a different scan or another index. ...
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. ...