A lock wait can be healthy coordination or an expensive queue. PostgreSQL’s lock_timeout lets an operation stop waiting, but it does not diagnose why the holder is slow or decide whether retrying is appropriate. Choose the timeout from the operation’s latency budget, and inspect the blocking transaction before increasing the limit or multiplying attempts. ...
Not every row deserves an index slot; a partial index keeps the tree thin exactly where the query never looks. Most tables have a skew the schema pretends not to see: ninety percent of orders are closed, nearly all events are processed, the flag column is false almost everywhere. A full index dutifully records all of it, charging maintenance on every write to protect lookups that only ever touch the small live slice. The partial index stops paying for the rows nobody queries. ...
Partition pruning excludes entire partitions that cannot contain matching rows. It is different from using an index to find rows within a remaining partition, and it is not a page-level cache trick. PostgreSQL can perform this exclusion while planning a query or later when parameter values become available during execution. ...
Range partitioning turns a boundary definition into a write-routing rule. PostgreSQL places a row in the partition whose bounds accept its key, whether the row arrives on time or months late. Correct boundaries, compatible timestamp types, and a plan for missing ranges matter before any query can benefit from pruning. ...
Ranking functions attach a position to each input row within a partition. They do not collapse a team, department, or customer into one aggregate row. The important choice is what a tie means: a unique position, a shared competition rank with gaps, or a shared rank counting distinct score groups. ...
A busy table produces obsolete versions at a rate its maintenance must accommodate. Lowering an autovacuum trigger can make that table eligible sooner, but eligibility is not completion. A worker must still reach the table, acquire the required locks, and finish enough useful cleanup before the backlog grows again. Measure that whole cycle instead of treating one configuration value as a throughput guarantee. ...
A surrogate primary key separates row identity from a business value that can change. A customer’s email can be corrected while orders continue referencing the same customer identifier. This is useful when the business name is mutable or wide, but stability comes from schema and application policy. A generated number is not automatically immutable, secret, globally unique, or an authorization decision. ...
A formerly fast query can become slow with the same data, statistics, and access plan. Another transaction may hold a lock, the storage cache may be colder, or the application may wait before PostgreSQL even receives the statement. Rechecking the plan is useful, but it belongs inside a broader comparison of where the elapsed time went. Begin by distinguishing time spent doing work from time spent waiting to do it. ...