Category: Administration

Matt Gantz deletes the elephant:

Purging data from a table is a common database maintenance task to prevent it from growing too large or to stay in compliance with data retention. When dealing with small amounts of data, this can be accomplished by a simple delete with no issues; however, with larger tables, this task can be problematic. Deleting records requires a lock that can block other processes from writing or even reading the data (depending on your isolation level). In this article I will share a technique I have used to work with some very large tables.

I’ve followed exactly this pattern many a time, and it works quite well if you have an appropriate supporting index.

Dave Stokes shares some queries:

 I am (slowly) adding handy PostgreSQL queries to my GitHub, and Vacuum is the newest category.  The end goal is to have a compilation of queries for those of us who need to keep an instance healthy.

I’ve always liked these compendia of helpful queries. The downside is that these are PDF images, making them harder to use. But even so, Dave is accepting pull requests.

Stephen Planck offers some advice:

Database snapshots are one of those features that’s been around forever, but still solves real-world problems with very little setup. In a single statement you can capture a point-in-time, read-only copy of any user database, use it for reporting or off-load testing, and—if disaster strikes—revert the source back to that snapshot in minutes. This guide explains how snapshots work under the hood, walks through day-to-day tasks (including creating the original database), and highlights the pitfalls you should plan for before using them in production.

One additional tip that I’d offer: limit yourself to one database snapshot per database. There’s a performance cliff you can hit when you get fancy and try to have multiple database snapshots going at the same time off of the same database.

Ajay Dwivedi switches out log files:

In my organisation, we have started building PostgreSQL Clusters with Patroni + Consul. In PostgreSQL we enable a few extensions like pg_stat_statements to ensure we don’t miss any performance impacting query.

But this generates too much log in active servers, leading to PostgreSQL log bloating. Thus, it becomes important to ensure log files do not consume beyond an agreed amount of disk space. For this reason, I implemented the following log rotation steps for Postgresql –

• Ensure to set proper log file name for PostgresSQL logging_collector.
• Add a logrotate policy on linux system for postgres logs directory.
• Add a cron job for running logrotate policy more frequently.

Click through to see how.