Booster's Blog

PostgreSQL engineering notes

Posts WIKI About me Photos Repositories
2026-08-23

Huge Pages in PostgreSQL

Your server keeps a page table — a structure in RAM that only describes where other memory is located. Each process has its own, with one 8-byte entry for every 4 KB page it has actually used. In PostgreSQL, every connection has its own backend, and each backend is a separate OS process — so each one holds a private description of the same shared_buffers. The more memory you give the database and the more connections you run, the more RAM is used just to describe memory you already own. On a typical production server with 32 GB of shared_buffers and 600 backends, this can easily reach 10–20 GB: RAM wasted on describing RAM.

Read more...
2026-01-18

AWS RDS Proxy for PostgreSQL

This overview does not compare RDS Proxy and other poolers. It is only focused on describing RDS Proxy so that readers can compare it on their own with other open-source connection poolers.

Read more...
2026-01-02

How to Handle Bloat in PostgreSQL

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.

Read more...
2025-12-20

How Golang Talks to PostgreSQL (Simple vs Extended Protocol)

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.

Read more...
2025-12-01

PG_DUMP - Choosing the Right Format

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.

Read more...
2025-02-10

How to Update RDS PostgreSQL with Minimal Downtime

Disclaimer

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:

  • RDS Proxy is not currently supported.
  • Instances with subscriptions can’t use it.
  • Your database must have the last minor version
  • You don’t get a built-in rollback option after cutover, so reverting to the old version without losing data isn’t possible.
Read more...
2024-12-08

Automated Password Rotation in RDS

Introduction

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.

Read more...
2023-08-18

Intentional Data Corruption for Backup Testing

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.

Read more...