A connection pool amortizes setup across several uses of a physical connection. It does not make setup disappear, and creating a pool does not necessarily open anything. Distinguish the application’s short-lived lease from the underlying database connection: closing the former normally returns the latter for reuse. ...
The schema of a database is not a one-time design; it evolves with the software that uses it. Instead of editing the schema ad hoc, migrations apply a named, ordered set of changes that any environment can replay. Schema as code Treat the schema as source code: each migration is one small, named, ordered step that moves the database from one state to the next, and the sequence itself lives in the repository. A fresh environment replays the steps from an empty database to the current version, which is exactly how tests, CI, and new servers all converge on the same shape without ad-hoc ALTER TABLE edits on a live box. The schema stops being an undocumented fact of a single instance and becomes a reviewable artifact with history, blame, and a pull request. ...
A retry can recover a transaction rejected because of concurrent work. It cannot guarantee success, and it does not automatically make external effects idempotent. The retry unit should include every database read and application decision that determined the writes. Reusing an amount calculated from the first attempt’s snapshot can defeat the purpose of starting a fresh transaction. ...
A migration that rewrites a busy table in one committing statement is long and blocking. A sequence of small, backward-compatible changes moves the same schema forward with no maintenance window, because each step is separately deployable and separately reversible. What is being managed is a lock, and the question is how long it is held — which depends on how much data the statement touches. ...
SQLite is a database engine linked into the application rather than a separate database server. Its main database file stores schema, tables, and indexes in a portable format. That makes it useful for local application state, embedded devices, and services whose write workload fits its concurrency model. The file is still governed by transactions and locking; ordinary file-copy intuition is not enough to operate it safely. ...
A PostgreSQL replication slot records retention requirements for a consumer. Once the slot has reserved a WAL position, losing the consumer’s connection does not automatically release that position. The primary may keep generating log while the slot’s requirement remains unchanged. The operational risk is retained storage and an increasingly expensive catch-up or rebuild decision. Slot usability depends on retained WAL availability and the configured invalidation rules. ...
Window functions let a report retain individual readings while adding information from related rows. A sensor reading can show its predecessor, its change, and its share of the sensor’s total without reducing the sensor to one output row. Correctness depends on defining the population, the partition, and the ordering before interpreting those extra columns. ...
A queue with a hundred ready jobs and thousands of completed jobs can benefit from an index containing only ready work. PostgreSQL calls this a partial index: its predicate determines which table rows have entries. The benefit depends on the actual query population and write pattern, not simply on making every index smaller. ...