Tuning

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. ...

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 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. ...

After a large deletion, a PostgreSQL table may retain a large file even when only a small fraction of its rows remain. Ordinary vacuum makes obsolete space reusable; VACUUM FULL rewrites the surviving contents into a new file and rebuilds indexes. That stronger reclamation has a different lock and capacity budget, so it belongs to planned maintenance rather than routine reaction to a large size number. ...