PG Phriday Archives - 2ndQuadrant

Posts

PG Phriday: Postgres 12 Revs up Vacuum

September 6, 2019/0 Comments/in Shaun's PlanetPostgreSQL /by Shaun Thomas

Any long-time user of Postgres is likely familiar with VACUUM, the process that ensures old data tuples are identified and reused to prevent unchecked database bloat. This critical element of maintenance has slowly, but surely, undergone incremental enhancements with each subsequent release. Postgres 12 is no different in this regard. In fact, there are two […]

PG Phriday: Around the World in Two Billion Transactions

April 19, 2019/0 Comments/in Shaun's PlanetPostgreSQL /by Shaun Thomas

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!)

April 5, 2019/0 Comments/in Shaun's PlanetPostgreSQL /by Shaun Thomas

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

January 11, 2019/4 Comments/in Shaun's PlanetPostgreSQL /by Shaun Thomas

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

October 12, 2018/6 Comments/in 2ndQuadrant, Shaun's PlanetPostgreSQL /by Shaun Thomas

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

September 21, 2018/8 Comments/in Shaun's PlanetPostgreSQL /by Shaun Thomas

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

May 4, 2018/2 Comments/in Shaun's PlanetPostgreSQL /by Shaun Thomas

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

April 20, 2018/0 Comments/in Shaun's PlanetPostgreSQL /by Shaun Thomas

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

January 19, 2018/0 Comments/in Shaun's PlanetPostgreSQL /by Shaun Thomas

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

December 29, 2017/17 Comments/in Shaun's PlanetPostgreSQL /by Shaun Thomas

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 […]