Posts about Production Postgres

In our previous blog post, we talked about upgrading a PostgreSQL cluster to a new major version with pg_upgrade. This can be fast and with little downtime even for large databases, but in some instances zero downtime may be essential for doing a major upgrade. This method is called an "online upgrade" and can be achieved through logical replication

As a database grows and scales up from a proof of concept to a full-fledged production instance, there are always a variety of growing pains that database administrators and systems administrators will run into.

Very often, the engineers on the Crunchy Data support team

We've seen an example of how to set up PostgreSQL monitoring in Kubernetes. We've looked at two sets of statistics to keep track of it in your PostgreSQL cluster: your vitals

I am an accidental DBA, with a huge emphasis on "accidental." I came to PostgreSQL as an application developer who really liked to program with SQL and use the database to help solve my problems. Nonetheless, these systems would enter into production, and as such I had to learn to support them.

PostgreSQL monitoring and performance optimization is a vast topic

My professional background has been in application development with a strong affinity for developing with PostgreSQL (which I hope comes through in previous articles). However, in many of my roles, I found myself as the "accidental" systems administrator, where I would troubleshoot issues in production and do my best to keep things running and safe.

When it came to monitoring my Postgres databases, I initially took what I knew about monitoring a web application itself, i.e. looking at CPU, memory, and network usage, and used that to try to detect issues. In many cases, it worked: for instance, I could see a CPU spike on a PostgreSQL database and deduce that there was a runaway query slowing down the system.

Over time, I learned about other types of metrics

You don't need monitoring until you need it. But if you're running anything in production, you always need it.

This is particularly true if you are managing databases. You need to be able to answer questions like "am I running out of disk?" or "why does my application have degraded performance?" to be able to troubleshoot or mitigate problems before they occur.

When I first made a foray into how to monitor PostgreSQL in Kubernetes

PostgreSQL has supported streaming replication and hot standbys since version 9.0 (2010), and synchronous replication since version 9.1 (2011). Streaming replication (and in this case we're referring to "binary" streaming replication, not "logical") sends the PostgreSQL WAL

PostgreSQL 13 was released last week. I'm excited about this one, as the more mature partitioning plus logical replication features allow some long-requested deployment architectures. I ran 13 through my usual 144 test quick spin to see if everything was working as expected. Mainly boring stuff, but I was pleased to see that with the simple 128 client/4X RAM benchmark workload, Postgres 13 is driving 1.6GB/s of random read traffic requests to my PCI-e 4.0 NVM-e SSD. It keeps up with a whole RAID volume of the old SATA SSDs! (I checked)

Hardware is standard enthusiast stuff, AMD Ryzen 9 3950X with 32 cores running clients on the server itself, delivering 140K reads/s. Motherboard is a Asus Pro WS X570-ACE. The SSD is Microcenter's Inland brand, the "Performance 2TB", using the popular Phison PCIe 4.0 controller that legit delivers 5GB/s sequential reads. (I checked)

Upgrading to PG13 on Ubuntu/Debian

PostgreSQL 13 is a lucky release! There are some very impactful new features and the release is coming out at its regularly scheduled time in the fall.

One of my favorite parts around the PostgreSQL release process

tl;dr -- If you are not actively monitoring your PostgreSQL clusters, and alerting on anomalous conditions, you are "flying blind". Don't do that!

At Crunchy Data, we distribute and support PostgreSQL as a product, in multiple forms, to many enterprise customers. We also make our products available via GitHub