PostgreSQL tables and indexes tend to accumulate bloat over time due to how the MVCC model handles updates and deletes. This guide helps you detect and safely remove bloat using proven, production-ready techniques. It covers lightweight and in-depth analysis methods, explains when action is needed, and shows how to clean up bloated tables and indexes with minimal disruption — before performance or storage become a concern.
Most articles about PostgreSQL’s Extended Protocol focus on the ability to parse and plan a query once and reuse it — and that’s important. But they often overlook another powerful feature: binary format for data transfer. That’s what I want to explore in detail in this article, and how it’s handled by the two main PostgreSQL libraries in Go: pgx and pq.
When it comes to PostgreSQL backups, choosing the right dump format can significantly impact both backup and restore times. This guide explores the practical differences between TAR, CUSTOM, and DIRECTORY formats based on real-world testing.
When working with Amazon RDS for PostgreSQL, minimizing downtime during updates is essential to maintain high availability.
One of the most effective ways to achieve this is by using the Blue/Green deployment technique.
Although AWS introduced this feature for PostgreSQL in December 2023, it still has some limitations that may prevent you from fully leveraging it:
This article covers the setup of Automated password rotation for a PostgreSQL user using AWS Secrets Manager and Lambda. This process enhances security by ensuring that credentials are regularly updated without manual intervention.
By the end of this article, a fully functional system for automatically rotating PostgreSQL user passwords will be in place.
There are many strategies for migrating a regular table to a partitioned table. Today, I’ll implement one of them. The main approach involves the following steps:
It’s crucial to avoid running out of disk space in PGDATA, but if it does happen, we have several options to manage the situation.
We have a configured cluster with Patroni, >=2 nodes. The task is to preserve the state of the database before deployment and, in case of failure, roll back to it.
Sometimes, when testing backup utilities like WAL-G, Pgbackrest, and others, it’s necessary to intentionally corrupt data. This is because the features advertised by these tools don’t always correctly verify checksum corruption, for example, as in the case of WAL-G, where the tool fails to detect bad checksums when using the -v flag.