Posts

PG Phriday: Around the World in Two Billion Transactions

Transaction IDs (XID) have been something of a thorn in Postgres’ side since the dawn of time. On one hand, they’re necessary to differentiate tuple visibility between past, present, and concurrent transactions. On the other hand, the counter that stores it is only 32-bits, meaning it’s possible to eventually overflow without some kind of intervention. […]

I am Developer! (And You Can Too!)

A while back, 2ndQuadrant notified a few of us that we should get more involved in Postgres Development in some capacity. Being as I’ve essentially fallen off the map in corresponding with the mailing lists in general, it would be a good way to get back into the habit. But wait! Don’t we want more […]

PG Phriday: PgBouncer or Bust

What is the role of PgBouncer in a Postgres High Availability stack? What even is PgBouncer at the end of the day? Is it a glorified traffic cop, or an integral component critical to the long-term survival of a Postgres deployment? When we talk about Postgres High Availability, a lot of terms might spring to […]

PG Phriday: Studying Stored Procedures in Postgres 11

Studying Stored Procs in Postgres 11 With Postgres 11 looming on the near horizon, it’s only appropriate to check out a recent beta and kick the tires a few times. Whether it’s improvements in parallelism, partitions, stored procedures, JIT functionality, or any number of elements in the release page, there’s a lot to investigate. It […]

On Rocks and Sand

When working with database capacity planning, there are a lot of variables to consider, and Postgres is no different in this regard. One of the elements which requires management is storage. However, there’s an aspect of storage that escapes inspection almost without exception, and it’s hiding in the shadows between the columns themselves. Alignment Basics […]

PG Phriday: BDR Around the Globe

With the addition of logical replication in Postgres 10, it’s natural to ask “what’s next”? Though not directly supported yet, would it be possible to subscribe two Postgres 10 nodes to each other? What kind of future would that be, and what kind of scenarios would be ideal for such an arrangement? As it turns […]

PG Phriday: Securing PgBouncer

We all love PgBouncer. It’s a great way to multiplex tens, hundreds, or even thousands of client connections to a small handful of Postgres sessions. What isn’t necessarily so endearing, is that it can’t pass authentication from itself to Postgres, as each Postgres session may exist before the connection to PgBouncer is established. Or can […]

PG Phriday: Adventures in BAR Management

Backups are a critical component to a fully covered Postgres database infrastructure. In some ways, it’s fair to say a database without a backup is no database at all—sometimes literally. 2ndQuadrant’s Barman tool is aptly named as a Backup And Recovery Manager for Postgres, and it exists primarily for encouraging a stable and robust backup […]

PG Phriday: Postgres on ZFS

ZFS is a filesystem originally created by Sun Microsystems, and has been available for BSD over a decade. While Postgres will run just fine on BSD, most Postgres installations are historically Linux-based systems. ZFS on Linux has had much more of a rocky road to integration due to perceived license incompatibilities. As a consequence, administrators […]

PG Phriday: Getting RAD with Docker [Part 4]

PgBouncer is a popular proxy and pooling layer for Postgres. It’s extremely common to reconfigure PgBouncer with repmgr so it always directs connections to the current primary node. It just so happens our emerging Docker stack could use such a component. In our last article, we combined Postgres with repmgr to build a Docker container […]