7 Administration bad practices

Putting PostgreSQL into production demands disciplined administration as much as good schema design or query tuning. This chapter highlights the “unexciting” but decisive practices that protect reliability: staying ahead of disk pressure, treating logs as a real-time health feed, instrumenting the database with meaningful monitoring, retaining statistics to understand long-term trends, and keeping both Postgres and the underlying system up to date. Neglect in any of these areas can cascade into outages, data loss risks, or opaque performance problems that are far harder to fix under fire.

One of the most common pitfalls is letting disk usage run wild. Running out of space on data or, worse, on the WAL volume can stall commits and even force a shutdown, and knee‑jerk fixes like deleting data or files inside pg_wal/pg_xact can be catastrophic. Growth drivers include table and index bloat, orphaned temp files after crashes, excessive WAL due to replication lag/slots, or failed archiving. Keep data and WAL on separate volumes, use tablespaces to redistribute pressure, and monitor with early alerts. Likewise, avoid logging to the PGDATA filesystem; error storms can fill disks fast. Move logs to a separate volume, enable log rotation, and enforce size/age limits to blunt both accidents and potential DoS vectors.

Equally risky is flying blind. Logs surface configuration errors (replication senders, connection exhaustion), memory mis-sizing (temp files), slow queries, checkpoint thrash, deadlocks, early signs of corruption, and security events; use them proactively and augment with analyzers when volume is high. Monitor essentials like disk, temp space, table/index stats, WAL volume, connections and blocking, replication health, transaction age, backups, and deeper views (pg_stat_statements, buffer/checkpointer/writer, locks, I/O). Track statistics over time for capacity planning—lightweight tools such as pg_statviz help you spot trends without heavy infrastructure. Finally, treat upgrades as routine risk management: apply minor releases for security and bug fixes, plan major upgrades to gain features and performance, read all intervening release notes, verify extension compatibility, and use pg_upgrade or logical replication to reduce downtime. Don’t forget the OS and libraries—stale system components can silently undermine performance just as surely as an outdated database.

pg_statviz locks analysis output: This visualization displays the mean number and type of locks in the database, with snapshots taken every minute over a period of several months in 2024.

• Running out of disk space can cause serious problems, so monitor your usage closely. Rash decisions like deleting what looks like logs or unnecessary files or resizing volumes can make the situation worse. Identify what’s consuming your space, and mitigate by employing multiple volumes.
• Storing PostgreSQL logs on a separate filesystem from the database helps you reduce the risk of excessive logging filling up your disk. You should also implement log rotation and enforce log size limits.
• Regularly checking PostgreSQL logs is crucial for identifying and addressing configuration issues, performance bottlenecks, data integrity concerns, and security incidents in near real-time. This can allow you to deal with problems before they escalate, and you can use pgBadger to help with the task.
• Not monitoring your PostgreSQL database can lead to undetected performance issues, security threats, and resource exhaustion that could impact its operation. By using the appropriate tools and metrics to look out for slowdowns, resource spikes, and maintenance needs, you can ensure optimal performance and mitigate risk.
• Long-term tracking of PostgreSQL statistics is important for understanding performance trends and making informed capacity planning decisions. You can use the usual monitoring tools, or the lightweight pg_statviz extension to capture and visualize these statistics over time without the need for heavy tooling or storage overhead.
• If you don’t upgrade Postgres regularly with minor releases, you can incur security risks or run into bugs that have been fixed. By not upgrading to the next major release you can and miss out on new features and enhancements. Upgrading is made safe and reliable by well-tested tools and thorough documentation. Don’t fear the upgrade — fear the alternative.
• Failing to upgrade the operating system and dependency libraries as well, can lead to obscure PostgreSQL performance issues, bugs and vulnerabilities, negating the perceived stability in pursuit of which you avoided to upgrade.

FAQ