Postgres I/O Basics, and how to efficiently pack table pages

In E85 of “5mins of Postgres” we’ll talk about understanding Postgres IOPS and how Postgres structures its physical table structure.

Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and subscribe to our YouTube channel.

Transcript

Let's have a look!

What is IOPS in PostgreSQL?

We'll start with this blog post by John Dalton on the Crunchy Data blog. In his blog post, John explains why looking at I/O performance matters even when your data fits into the cache. He starts by looking at this from a high level.

1. When we think about IOPS on the database side, you can think of it in a way that the database will have individual processes.
2. Those processes interact with shared buffers in Postgres, and the OS page cache, both of which are in memory.
3. Then Postgres will issue I/O requests to the underlying disk storage. That might be locally attached to an actual physical server, or nowadays, in most cases, it's a network request to a network storage.

John goes into a few details on how this all works, but one important thing to remember is that you'll always have IOPS ongoing when Postgres works because Postgres does writes, for example to its WAL, as regular activities are happening. Even if most of your workload or all of your workload is in memory.

One important thing to remember is that you'll always have IOPS ongoing when Postgres works.

IOPS capacity and burst IOPS

John also talks about how we can think about IOPS capacity and burst IOPS. The gist of it is that if you use cloud provider disks, usually they have a certain burst capacity. What that means is that, for example on AWS, if you use their GP2 or GP3 volumes you'll sometimes have burst behaviors where it will be fast, but at some point you've exceeded your IOPS allocation and things will become slow.

Waiting on disk I/O

When your database is suddenly slow, it may make sense to look at the physical properties of the cloud provider's storage to really understand what's going on. Now, the most common situation you'll see when Postgres is slow because of I/O problems is when it's actually waiting on disk I/O.

There's ways to get that information by looking at the wait events in Postgres or by looking at the actual I/O wait on the processes. One way that is described here is by using the pg_cputime() function provided by the pg_proctab extension. This essentially gives you a measurement of what you could get from the server directly as well.

Measuring Postgres IOPS

There are various ways of measuring IOPS, but one of the things that you can also do is look at I/O statistics in Postgres. The most common things that I would use to understand whether IO is a problem is by looking at the I/O timing information collected by Postgres.

track_io_timing

You can enable tracking this information by using the track_io_timing setting, which, depending on your provider, may be enabled by default or maybe needs to be turned on. It is something that I would always turn on for production systems, unless you have a very slow time source.

The most common things that I would use to understand whether IO is a problem is by looking at the I/O timing information collected by Postgres.

EXPLAIN ANALYZE (BUFFERS)

Now, the other thing that you can do, is you can run EXPLAIN ANALYZE (BUFFERS), and if you have track_io_timing enabled, it will give you the I/O timing information as part of the output.

This lets you see I/O timings and we can actually know how slow it was to get that data from disk. Again, this would clearly show you a bottleneck on the I/O performance.

pg_stat_io

Now the other thing I want to point out, and we've talked about this previously in 5mins of Postgres, is "pg_stat_io" (also see here and here. If you're on Postgres 16, this is a really great improvement that will give you information on what part of your database was slow.

How Postgres stores tuples in a table

Another post I want to briefly touch upon has a great tip that Nikolay from Postgres.ai recently wrote about, where he explains how Postgres stores tuples in a table. Here, he walks through some of the physical on disk structure.

Really, the thing to remember is that ultimately Postgres data is stored in pages. We can actually know where in the page that information is. If we query the hidden ctid column, it will tell us the physical location of a tuple in a table. If we're running a query like this, we can actually add the ctid into our query, and this way you will know where in a table your data actually lies.

Load data faster with densely packed pages

Now, why does that matter for I/O performance? Because if you're looking at a lot of pages, if your data is essentially spread across a lot of different pages in your table, that means that it will be slower to load data. However, if you have your data densely packed in a smaller amount of pages, then it'll be faster to load that same data.

Postgres doesn't load individual rows. Postgres will always load the full page. Where your data is located in a table matters greatly for efficiency.

At the end of this tip, Nikolay describes how you can improve the packing of your tuples, of your rows into the pages in the Postgres database. First of all, it's always a good idea to maintain tables and indexes in good shape. That means make sure you watch out for table bloat, make sure you do index maintenance, you do regular re-indexes, that will make sure that your pages don't have empty space in them.

Another thing that sometimes happens is, if you rely on Index-only scans, that way you're not going back to the table and you're just accessing less pages.

CLUSTER and pg_repack

And then last, there is a way to structure your table in a way that will actually group data together. And so that way, if you know you're always sorting by a particular column, it may make sense to cluster your table. Now, CLUSTER is unfortunately a command that takes a full table lock, but you can use the pg_repack command to achieve the same in an online fashion. This will oftentime give you a huge performance benefit, but it is a bit tedious to maintain.

All in all, the physical data structure matters as much as your server configuration to achieve the best I/O performance in Postgres.

Thanks for joining us for E85 of 5mins of Postgres! Subscribe to our YouTube channel, sign up for our newsletter and follow us on LinkedIn and Twitter to get updates about new episodes!

What we have discussed in this episode of 5mins of Postgres

• Understanding Postgres IOPS: Why They Matter Even When Everything Fits in Cache

• John Dalton on Twitter/X

• John Dalton on Mastodon

• pg_proctab on GitLab

• Postgres 16: Cumulative I/O statistics with pg_stat_io

• How Postgres DBAs can use pg_stat_io

• Postgres 16: Buffer cache hit ratio and I/O times in pg_stat_io

• Understanding how sparsely tuples are stored in a table - by Nikolay Samokhvalov

• Nikolay Samokhvalov on Twitter/X

• How to debug slow Postgres UPDATEs and bloated tables with auto_explain & page_inspect