Strangely quiet around here

My colleague Bob Pacheco asked me to help with a strange problem he was witnessing for a client. A new Postgres cluster was getting created on a Kubernetes

On the Customer Success Engineering team at Crunchy Bridge, we run across customers with lock issues on their Postgres database from time to time. Locks can have a cascading effect on queries. If one process is locking a table, then a query can be waiting on the process before it, and the process before that one. Major lock issues can quickly take down an entire production Postgres instance or application.

In this post let’s look at why locks happen, and more importantly how to get to the bottom of a lock issue and the one process blocking everything else. That one process that blocks them all! Once you find the source of the lock, I’ll give you the options for terminating the process that created all your troubles in the first place.

Finding the source of the lock

You may have noticed a file called postmaster.pid inside your data directory. This file gets created when Postgres first starts up, and gets removed on a clean shutdown. It seems to contain some random numbers and strings, but what do they all mean?

The file will look like this:

2757
/home/greg/pg/17/data
176540940
5432
/tmp
*
   8675309        12
ready

I've always found distributed systems to be the most fascinating branch of computer science. I think the reason is that distributed systems are subject to the rules of the physical world just like we are. Things are never perfect, you cannot get everything you want, you’re always limited by physics, and often by economics, or by who you can communicate with. Many problems in distributed systems simply do not have a clean solution, instead there are different trade-offs you can make.

While at Citus Data, Microsoft, and now Crunchy Data, the focus of my work has been on distributed PostgreSQL architectures. At the last PGConf.EU

If you’ve ever dug under the hood of Postgres a bit, you’ve probably heard about the page. This is the on-disk storage mechanism and it's limited to an 8kb size. But what happens when you have data bigger than that 8kb? TOAST is made. Postgres TOASTs data by splitting it up into smaller chunks. TOAST stands for The Oversized Attribute Storage Technique.

TOAST happens automatically, you don’t set up anything, it just comes with Postgres out of the box. So why should you care?

Well TOAST can impact your query performance quite a bit. It adds some extra hurdles for Postgres to jump through when delivering data to your end users. So knowing how TOAST is made, when, and in some cases how to avoid it, is important for optimizing Postgres.

Rows bigger than your page size

Postgres is a robust data platform. Yes, it's more than a boring old relational database. It has rich indexing, data types (including JSON), and so much more. It also has support for a variety of extensions that can further broaden it's already great functionality. Two of those extensions when coupled together make Postgres a very compelling approach for IoT architectures. Today we're going to start from the ground up on how you would design your architecture with Postgres along with the Citus

If you're building a B2B app chances are it's multi-tenant, meaning one customer data is separated and doesn't intermingle with other customer data. When building the app itself you'll typically have some URL route defining the specific tenant. When it comes to your database there are a number of design patterns that you can use that offer different advantages, trade-offs, and scaling opportunities.

When you begin building your app, time to market is essential, but you don't want to make decisions that are hard to reverse. We're first going to take a look at the most common design patterns for multi-tenant database design in Postgres, followed by some guidance on how to design your data model so that you're prepared from day 1 for massive scale (should you need it).

Three approaches to multi-tenant database design

Recently we published an article about some of the best sql subquery tools and we were talking about all the cool things you can do with CTEs. One thing that doesn’t get mentioned near enough is the use of CTEs to do work in your database moving things around. Did you know you can use CTEs for tuple shuffling? Using CTEs to update, delete, and insert data can be extremely efficient and safe for your Postgres database.

PostgreSQL 15 included the MERGE

Version 1.21 of PgBouncer, the Postgres connection pooler, has added a long awaited feature: support for prepared statements inside of transaction mode. Prior to this, one had to choose between using prepared statements (a performance win), and using PgBouncer's transaction mode (also a large performance win). Now, we can have our cake and eat it too 🎂 🎉.

What are prepared statements?

Disk IOPS (Input/Output Operations Per Second) is a key metric for measuring the performance of any disk system. It represents the number of read and write operations that can be performed per second. For PostgreSQL, which relies heavily on disk access, understanding and optimizing disk IOPS is crucial for achieving optimal performance. Today I want to go through the key topics involved with IOPs - what is it, how does it impact Postgres, how to measure it, and what to change for better performance.

What Are IOPS for Postgres?