From the Trenches: My Path Through Postgres

Hello fellow Postgres Enjoyer. I'd like to do something a little different this week and talk a bit about how I got ensnared by the Postgres ecosystem, and what I've done with it over the years. Maybe you have something better to do with your Friday than listen to some old guy reminiscing about Postgres, but I promise you it'll be worth the read.

There's a reason I've been a dedicated Postgres zealot for over 20 years now, and it definitely isn't because of the name.

Postgres Sucks!

One of the blessings and curses of the internet is that anything you say within its confines lives in perpetuity. Or perhaps in my case it's better to describe it as infamy. Now, before I start incriminating myself, I actually contributed to Postgres in a tiny way as early as 2002 with Postgres 7.3 when I submitted a reindexdb utility.

The actual story behind that utility is something else entirely. It all actually started in this thread where I adamantly defended Oracle (lol) and then moaned constantly about how much VACUUM sucks. Here's a fun excerpt:

Yes, I've always been sort of obnoxious. Yes, I've mellowed out immensely over the years. And yes, it's hard not to giggle looking back, given that all of this started over 2GB of bloat. But thanks to that thread, I started this one regarding reindexdb.

Essentially what happened is that there was a bug in VACUUM way back in the 7.x days that prevented it from cleaning up indexes. So even though a table may have been reduced from 2GB to 100MB through a VACUUM FULL, the index would continue to grow. To get around this, I took the vacuumdb script, renamed it to reindexdb, re-tooled it a bit to rebuild indexes, and ran it on occasion to reindex every table in the database. And by "on occasion", I mean:

Yes, this particular system was vastly improved by reindexing every table in the database every two hours. I can't stress enough just how bad Postgres was back then. Up until 7.1, every VACUUM required an exclusive table lock, for example. Can you imagine? Not VACUUM FULL, just regular VACUUM. An exclusive lock. Ridiculous.

Eventually I pushed to remove Postgres from our environment entirely in favor of MySQL. Our MySQL deployment had its warts, but it didn't need hourly vacuums and regular reindexing to remain operational, despite hosting 20x more data at the time. We had a celebratory lunch and everything! If not for my pursuit of work in the Chicago area, I probably would have never touched Postgres again. And good riddance.

Never Say Never

Fate, it would seem, had other plans. The company I interviewed with in 2005 was an exclusive Postgres shop. So I grabbed a copy of "The Purple Book", an intimidating 976-page tome titled only PostgreSQL by Korry and Susan Douglas. I read what looked like the important parts to prepare for the interview. Once I secured the position, I proceeded to read the entire thing prior to my start date to avoid making any costly mistakes.

As it turns out, a lot had changed in the Postgres world since late 2002 when I metaphorically washed my hands of it. Unfortunately, my new employer's environment was still on 7.4 and in dire need of an upgrade. I was still, at least temporarily, stranded in Hell.

What I didn't know until my first day was that their max_fsm_pages GUC was still the default of 200,000, meaning only that many pages were tracked in the free space map. That parameter no longer exists for good reason. Imagine you have a very busy database and it just happens to update rows across more than 200k pages. Now imagine what happens as Postgres forgets the oldest pages. You end up with a sliding window of infinite bloat. In fact, that server was mere days away from exhausting its available disk space, and all of its local drive trays were full.

Fixing that meant building a list of all tables in the database in order of ascending size, and then scheduling a VACUUM FULL on each. Every de-bloated table made more room for the next, until finally the largest ones got cleaned up. I forget the exact numbers, but the database was easily 90% smaller than when I started. One crisis down, many to go. The upgrade from 7.4, for instance.

The problem with upgrades back then was that pg_upgrade didn't exist yet. It wasn't even in its initial pg_migrator incarnation until 2010 when I wrote about how awesome it was. Nope, back in those days it was still pg_dump + pg_restore or maybe judicious use of Slony. So dump and restore were the recipe of the day, and became an annual process until the end of my tenure there. 

Working directly with Postgres for five years taught me a lot about the engine. Enough by that time that every other database became irrelevant. I posted semi-frequently in the mailing lists and even helped out on occasion if I knew the answer. But I was still firmly embedded in the operator side as a DBA—purely an end user use case. Little did I know, that was about to change.

Into the Fire

The manager for the infrastructure department at a Chicago financial firm approached me in 2010 and gently suggested I apply to replace a senior DBA that was leaving soon. I still have no idea how or why he decided to contact me and essentially bypass their hiring pipeline, but it definitely piqued my curiosity. After a perfunctory interview process, I was hired to tame an actual maelstrom.

The previous database I ran was of medium size and existed primarily to host site tracking data we then distilled into a star schema I designed for reporting purposes. Nothing critical or live, with suitably large available maintenance windows. Easy peasy. What I'd been dropped into was something else entirely:

• A 1TB database that backed an active Java Hibernate frontend + backend. The instance crashed frequently, and they didn't know why.

• 15k peak TPS trading hours. By the end of my tenure, this would reach 35k. Even with application-side pooling, the peak Postgres connection count often reached 1000.

• Daily backup restores + redaction in Dev and QA environments for testing. These were currently failing on a frequent basis, and each restore took a minimum of two hours.

• An Incredibly sophisticated HA management system managed by LifeKeeper.

• Postgres Plus Advanced Server (PPAS) 8.4. A "not really Postgres, but close enough" proprietary analog from EnterpriseDB. They used it to migrate off of Oracle, who had slapped a $1M license cost increase in their lap and refused to elaborate. EDB was still expensive, but less so.

I was way out of my comfort zone, but knew enough about Postgres to make some immediate improvements.

I fixed the restore + redaction system.

Ultimately I had to write my own harness to pull the pg_start_backup() and pg_stop_backup() admin function levers. To solve the speed problem, I used a combination of filesystem copy and hard links to implement "incremental" backups. The harness made use of xargs for massively parallel operations. Then I added hooks for invoking arbitrary post-restore redaction. All in all, I reduced backup time to about 20 minutes, and restore time wasn't far off. Whatever I did also solved the failed restore issue. I wrote this before Barman and pgBackRest existed, or I would have used those instead.

I stopped the almost daily crashes.

Many of their postgresql.conf settings were simply wrong for that scale of a production system. The shared_buffers and work_mem parameters were too low, as was effective_cache_size. The random_page_cost value was still at its factory default of 4.0. And checkpoint_completion_target was also at its default of 0.5, causing more storage IO spikes. Finally, checkpoint_segments was too low for their checkpoint_timeout, causing too many forced checkpoints.

These are all things seasoned Postgres DBAs look for in troublesome systems on day one. I was honestly stunned they were getting 15k TPS out of the system in its current state. Back then it was still the Wild West and I'd somehow become a rare niche expert on Postgres.

But that was the easy stuff. I kept tweaking things.

I replaced the slow storage.

Listen, 2011 was a long time ago. State-of-the-art hardware at the time was a RAID-10, and if you were lucky, you got at least a dozen drives. Not including the hot spares, we had 8. This was all fine while the filesystem cache remained hot, but an errant crash (PPAS was a bit buggy) meant cold caches for as long as the warmup period lasted. Getting slammed with 15-30k active users left no time for this. Trading hours were sacrosanct, and 30 minutes of slow queries and timeouts were incredibly conspicuous.

I experimented with short-stroking the RAID. That gave a modest 20% improvement, but it wasn't nearly enough. Eventually we settled on NVMe PCIe devices from Fusion-io. It's hard to ignore the promise of 150k IOPS when you're used to about 1000. And indeed, it solved the problem entirely. No more cold starts. That became the subject of my first ever conference talk, presented to Postgres Open 2011: NVRAM for Fun and Profit.

I helped invent an entire high availability stack.

Eventually we needed to address the proprietary LifeKeeper failover management suite. We needed to perform a hardware migration, and they insisted on sending a consulting team to do it for us. Instead, a coworker and I cooked up a complete replacement, and this is partly where my reputation as the "HA guy" started.

The full stack ended up like this:

• Shared storage across both nodes using DRBD. Streaming replication didn't arrive until 9.0, and rather than waiting on each 16MB WAL segment, a block-device mirror sounded like the best alternative. Page-level replication, baby!

• A LVM physical device, volume group, and volume to ensure strict write control. As a shared block device, we needed direct management. It also brought along snapshot capabilities.

• Cluster communication through Corosync.

• Pacemaker as an event resource manager.

• A VIP that followed the current primary. I also included a gratuitous ARP to refresh routing tables.

• Primitives for all components of the stack in Pacemaker for orderly idempotent standup and teardown.

This stopped all of our previous failover problems cold. No more expensive proprietary engine, and the stack was purely off-the-shelf. This became the basis for my second conference talk and live demo at Postgres Open 2012: High Availability with PostgreSQL and Pacemaker. And that, in turn, became the PostgreSQL High Availability Cookbook. Not nearly as impressive a tome as "PostgreSQL" was, but I tried.

Remember, there was no Patroni back then. When it finally appeared a few years later, I thought it was a neat idea.

I built a tool to manage our Postgres fleet.

Eventually we had about a dozen Postgres clusters, all with their own dedicated hardware and specialized configurations. I needed a way to perform coordinated switchovers for all of them, check backup status and health, bounce or rebuild systems that needed it, and so on. And thus ElepHaaS (Elephant Herd as a Service) was born. This too became a conference talk at Postgres Open in 2016.

It used a status agent to push regular updates to a centralized coordinator server. I used a Salt pillar to bootstrap each Postgres server with the agent, and they magically showed up in the coordination server. No HA stack I know of does this kind of fleet management; it's always on a per-cluster basis. ClusterControl from Severalnines eventually appeared in this space, but it's still a relatively deserted field. Certainly nothing like that existed in 2016.

And this continued. It seemed like I was always concocting some new technique out of duct tape and bailing wire just to keep everything running. Miraculously, this almost always worked out, and I left the system in a better state than I found it. The automation and documentation I added kept it that way long after I left.

PG Phriday Was Born

Managing such a challenging environment needs a proactive stance, so I looked outward.

Devs make mistakes. So do DBAs. I needed to convey cool Postgres tips and tricks to the internal dev teams in a way they could reference later. So I started posting weekly articles to the engineering Confluence page about various antipatterns I encountered in production. Then it became potentially useful information about Postgres features or query techniques I thought might improve performance.

Eventually I received permission to post these publicly. And finally I had a way to contribute back to the community that made my career.

Sure a few conference talks are great. I monitored the mailing lists a lot back then and helped out frequently. But these articles could be a persistent resource as long as anyone found them useful. I can code, but I'm no dev. Maybe I don't have the patience for it. I went from a DBA to a Postgres subject matter expert purely through managing one of the most terrifying clusters I've ever encountered. Because I had to. Necessity is, after all, the mother of invention.

These articles I post, about all topics of Postgres big and small, are a continuing love letter to the database I've come to respect. It's certainly a far cry from where my relationship with Postgres began. They're the musings of a DBA who dove into the trenches, wearing every imaginable hat, forced to learn every intricate detail of how Postgres operates.

And so PG Phriday has existed in some form or another since 2015. Postgres is a rich fountain of functionality, techniques, concepts, extensions, and many other things under the sun. I'll always have something to say about it, and sharing that knowledge is what I do best.

The Road Ahead

What happened after the financial company? I went to work for 2nd Quadrant in 2017 where I became a direct participant of the Postgres world. Then EnterpriseDB bought them and acquired all their experts as part of the package. Now I work at pgEdge doing the same advocacy, development, and content production that everyone has come to expect from me.

I've developed extensions and tools, shared tutorials and webinars, presented at conferences, served on boards, moderated forums, mentored, fostered, and encouraged users and devs alike. Postgres is worth all of that and more. With the advent of AI tools, much of my expertise will become either irrelevant or something anyone can access without the thousands of hours I needed to acquire it.

Honestly? I'm pretty stoked about that. I wrote the HA cookbook because I would have loved to have that kind of resource when starting at the trading firm. Now LLMs are almost trustworthy enough to provide quick answers to all the same questions and problems I had back then. Building stable, trusted engineering, using well-tested best-practices is now mundane. But the process still needs someone at the helm who understands the bigger picture. At least for now. 

Thus my path through Postgres continues, as I hope yours will. It gets better every year, as my recent series on Postgres 19 should attest. At the end of the day, I'm just some old guy that somehow developed an unhealthy infatuation with a piece of software over the course of 20 years. Your mileage in that regard may vary, but I think I can speak for the community at large when I say: I hope you join us.

Come to the Postgres side; we have cookies.