Interpreting Explain Analyze, Bulk Loading Data, Limiting Update & Delete, pg_buffercache | Scaling Postgres 167

In this episode of Scaling Postgres, we discuss how to interpret explain analyze, the best ways to load data in bulk, how to limit rows affected by update or delete and using pg_buffercache.

Content Discussed

• How to interpret PostgreSQL EXPLAIN ANALYZE output
• PostgreSQL: Bulk loading huge amounts of data
• Simulating UPDATE or DELETE with LIMIT in Postgres: CTEs to The Rescue!
• Memory inspection thru pg_buffercache
• New Features in PostgreSQL 14: Bulk Inserts for Foreign Data Wrappers
• Enhancements to the target_session_attrs database connection parameter in PostgreSQL 14
• Tracking the refresh Performance of Materialized Views with mv_stat in PostgreSQL
• Speeding Up PostgreSQL Recovery with pgBackRest
• Using Postgres Row-Level Security in Ruby on Rails
• Postgres 14 highlight - Fun with Hashes
• Georgios Kokolatos

YouTube Video

Podcast Audio

Transcript

All right. I hope you, your friends, family, and coworkers continue to do well. Our first piece of content is "HOW TO INTERPRET POSTGRESQL EXPLAIN ANALYZE OUTPUT". This is from cybertec-postgresql.com. They're talking about how you can interpret EXPLAIN ANALYZE. Now, they first talk about what it is and Explain gives you the query plan that the planner tends to run on any query that you send against Postgres. So you can just do Explain and then the query you're going to run, and it shows you the plan it intends to take. When you use Analyze, if you say EXPLAIN ANALYZE, it will actually run that query. So again, they say, be cautious if you're going to be using it with INSERT, UPDATE, or DELETE because it's actually going to be doing those operations. But in addition to using EXPLAIN ANALYZE, which runs the query, you can also request an output on the buffers. You can indicate whether you want it verbose or not. You can indicate settings and then what WAL usage is occurring in later versions of Postgres.

But as they say here, the best way to call it is EXPLAIN ANALYZE with the buffers and do your statement. They also recommend using track_io_timing to be on to give you some more IO output in terms of the results from EXPLAIN ANALYZE. They give you an example of what it looks like when you run against a pretty simple query. It shows you the output that you see here. It gives the cost estimates from the planner of the time to return one row, the time to return all rows, how many rows will be returned, and the width. When you're using Analyze, it gives you the actual time. Then each of these arrows indicates what nodes were processed. So you can have, with a more complex query, many different nodes, and each of those has a timing indication of how long it takes to run. You may have more than one loop, for example, so timing needs to be taken into account for how many loops something takes. You can see where data was filtered out using a filter, and you'll get different nodes showing up whether it's index scans or not.

Then he shows when you have the BUFFERS option, it shows you how many buffers were read from in the IO timing if you enable that track_io_timing setting. Then he talks a little bit about how to read it. Then, of course, the question is what to focus on in EXPLAIN ANALYZE output. Basically, this is the most important thing to pay attention to, to me, is to find the nodes where most of the execution time is spent. So basically, you're looking for the bottleneck, what node is taking the longest? You then want to look at that and say, all right, how can we optimize that? I think these two points are related to this. So if you're looking at that slowest node and notice that the planner expects to return so many rows and takes so long, and the actual results are significantly different by a factor of ten or more, they say very often this is a cause of bad performance. I haven't noticed this a lot but I've seen this when there's something going on with Analyze.

So, for example, the database or that particular table is not being analyzed effectively. So maybe you have a problem with vacuum that's causing Analyze step to not happen as frequently. Or your sampling, like your statistics target is set too low based on how many rows you have in the database. But that could be a cause that you could alleviate by analyzing the data in the table more frequently or adjusting your statistics for that table. Then of course, for long-running sequential scans, you could probably add an index to remove some of those filter conditions and make it more efficient.

But again, it all starts with, okay, what is the slowest node, and focus your attention there. So even if you have an enormously long query, just look for that slowest node. If you improve that, you should improve the overall query time. Just keep in mind that you can have multiple loops. So when you're looking at it, make sure that you're checking for how many times that particular node is processed. Now they talk about there are some GUI tools available if you don't like interpreting the text output. There's explain.depesz.com where you can just paste in your query and it focuses you on the slowest part. There's also the Dalibo's EXPLAIN ANALYZE visualizer as well that you could use. So it's a great post talking about EXPLAIN ANALYZE and I encourage you to check it out.

The next piece of content- "POSTGRESQL: BULK LOADING HUGE AMOUNTS OF DATA". This is from cybertec-postgresql.com. They're talking about best practices when loading a lot of data. Now, normally what you may start with is doing an INSERT, but that is incredibly slow due to all the overhead of inserting one row at a time. Now you can make that faster by doing multirow INSERTS. So here we're just inserting one row with each statement. You can do INSERT into a table value and have multiple rows inserting per insert statement. That is faster than doing single-row INSERTS, but of course the fastest is a COPY. So here they just took a look at some sample data and they were able to see an 80.1-second to a 2.6-second improvement just by using COPY as opposed to individual INSERT statements. Then somewhere in between this would be a multirow INSERT statement. The other thing to check when you're loading a lot of data is that you're not checkpointing too frequently.

So this is important for your database when you have a checkpoint warning set up because it will actually tell you if checkpoints are occurring too frequently or more frequently than you want to. He says what you can do here is extend the max_wal_size, we'll push out those checkpoints as well as adjust the checkpoint completion target to something like 0.9 or something like that. Basically, the checkpoint is actually what writes from the memory to the actual heap that is on the disk. The other thing that you can do when you're loading data is if you're doing this for the process of loading a bunch of data into the database, processing aggregations, and then inserting it, that would be a great use case for doing an unlogged table. So with an unlogged table, it's not writing anything to the WAL. So if you have a crash of the system, you're going to lose all that data. But they're great for temporary tables and temporary tables are also not logged.

So you could load your data into this temporary table or unlogged table very quickly, do your processing of it, insert the data into the ultimate destination, and then delete that table. The other two points when you're thinking about importing data is that what else does the database system have to do when you're inserting data? So, do you have indexes? So every index needs to be updated. It's much faster to actually drop indexes and then insert all of that data and then apply indexes after the fact if you can do that. The other thing is what triggers are running. So it's also generally faster to drop triggers, insert all the data into a table, and then reapply those triggers.

So I know that's not possible in every use case, dropping indexes, dropping triggers, but if you can do that, you will definitely see a performance improvement. Then they mentioned optimized column order for bulk loading. So this is where you put fixed-size data types. Larger first, followed by variable data type lengths at the end will allow you to compact the table and allow for faster loading. Lastly, he mentions two different tools that can also help you do loading if you're interested, PGLoader and pg_bulkload. So if you're interested in loading data quickly, maybe you want to check out this post.

The next piece of content- "Simulating UPDATE or DELETE with LIMIT in Postgres: CTEs to The Rescue!". This is from blog.crunchydata.com, and they're talking about wanting to limit some DELETE or UPDATE operations. It would be great if you could do DELETE from some table limit to say, 10,000. Frankly, I'd love to have this because if I'm going to run a DELETE and I expect one row, it would be great to just say limit one to know that okay, I know no matter what, I'm only going to delete one. But sadly, that's not part of the SQL standard, nor is it a part of Postgres. But a way you can do that is do a SELECT to grab the rows you need and then do a DELETE from it. You can use a CTE, which this post describes, but you can also just use a subquery.

Because with the SELECT, you can use the LIMIT clause as well as the ORDER BY clause to help you get exactly what you need. So here's the example that they're using here. They are creating this CTE or this WITH clause and they're saying define it as rows and select something from a table, maybe an ID limit it by ten. So you're only going to get ten IDs from this table, for example, and then you say DELETE from the table where that ID column, or whatever it is, is in your ID from the CTE. So you could just place this into here without a CTE and it would work the same way. So again, you don't need to use CTEs, you could just use a subquery, which of course this has to use anyway. They are showing how you can use this with DELETE. You can also use it with the ORDER BY clause just by putting in the SELECT. It shows you using UPDATES as well as ORDER BY. So if you ever want to limit your UPDATES or DELETES that you're processing, maybe you want to check out this post.

The next piece of content- "Memory inspection through pg_buffercache". This is from fluca1978.github.io and pg_buffercache is an extension that looks at the shared buffers and what's being stored there. What he's done is created a number of functions that help you quickly retrieve useful information from it. So he has a link here to a SQL file and if you run it, it actually creates a separate schema called memory and then creates these functions to run certain queries, basically. So now you can just do "select * from memory.f_memory" to run that function and it will return the total number of shared buffers, how much is being used currently, and how much is free.

It also includes a number of utility functions, to check whether pg_buffercache is installed, et cetera. Then it also lets you show memory usage by very high, mid, low, very low, and usage by database. So you can check what is using the buffer cache out of multiple databases on your cluster. You can check it out by table to see how much a table is in the buffer cache as well as a table cumulative. So this is a great set of utility functions for analyzing the buffer cache. If you're interested in that, definitely check out this blog post.

The next piece of content- "New Features in PostgreSQL 14: Bulk Inserts for Foreign Data Wrappers". This is from percona.com. They're talking about a new feature in 14 where they've altered how foreign tables work so that inserts don't just happen one at a time because you're dealing with a foreign server that's probably connected on another server across the network, and doing bulk loads of inserts one at a time, as we saw with the first post, is going to be very slow. If you can batch them and there's this new batch size defined for a foreign table, you can send more than one row at a time, which speeds things up. Here, they went from about 4,600 seconds for a set of inserts. Using a batch size of ten, it went to about 800 seconds, so maybe about six times faster using this new batch method. So if you're interested in learning more about this feature and an example of how it's used, definitely check out this post.

The next piece of content- "Enhancements to the target_session_attrs database connection parameter in PostgreSQL 14". This is from postgresql.fastware.com. They're talking about the enhancement coming with 14 with different options for target session attributes (target_session_attrs). So in versions prior to 14, you could define that when a connection is made, do you want it to be a read-write connection only, or do you want any other type of connection? So there wasn't a lot of flexibility with this. So they actually did a patch in 14 that enabled these additions. So you can define your PostgreSQL client as only connecting to a database that is read-only, connecting to one that is a primary only, connecting to one that is standby, or connecting to one preferring standbys. If none are available, then go ahead and connect to any other type. So this definitely gives a lot more flexibility, but it helps the PostgreSQL client now act as a high availability point because you can point to multiple servers and it will choose which one based upon what you said in here. So if you want to learn more about this feature that's been added and an example of how it works here, which they show in this blog post, definitely check this out.

The next piece of content - "Tracking the refresh Performance of Materialized Views with mv_stat in PostgreSQL". This is from ongres.com, and they're talking about a new extension that they developed called mv_stats, and basically it is a statistics extension for tracking materialized view processing. So it gives you an indication of when the last time a materialized view was modified, the time of last refresh, how many times it's been refreshed, how long it took to do the refresh, as well as a min-max track times of that refresh time. So if you use a fair number of materialized views and you want to track the statistics relative to it, you may want to check out this post and this new extension.

The next piece of content- "Speeding Up PostgreSQL Recovery with pgBackRest". This is from seiler.us and he's talking about he had a problem where he had a replica but had trouble catching up with the primary. It looked to be because the WAL recovery was taking too long. Now, because he's using pgBackRest, I'm assuming that they were directly storing it on a file storage system like maybe S3, and it was trying to pull down a file each time. pgBackRest has a number of settings you can do to speed that up. But how it speeds it up is by enabling an archive async and then defining how many files to pull down ahead of time to have on the local system ready to be replayed against the database.

By having more files in place, basically, you're downloading files in advance of when it's needed and storing them in the spool path. This enables recovery to be faster. Now, this covers pgBackRest, but the same practice can be used with normal WAL replay in Postgres. If you're actually trying to pull those WAL files one at a time from something like S3 or Google Cloud Storage, you're going to want to batch-pull WAL files down ahead of time so they're on the replica on the disk, ready to be replayed immediately. But if you're interested in more details about this, definitely check out this blog post.

The next piece of content- "Using Postgres Row-Level Security in Ruby on Rails". This is from pganalyze.com and this is a good overview on showing how row-level security works and how you can enable it. Basically, they showed an example here of where you can enable row-level security for a particular table. You can create a role and then create a policy. So this one is for account managers on the accounts table, such that managers can only view rows where the supervisor equals whatever the current user is. Now, you can also define policies based on a session variable that is set. That's what they did in this other implementation here, where they set a customer ID, and then they can insert data for that customer ID. Otherwise, if it's not there, they get a new row violation of that security policy.

So basically, this is a way to create multi-tenant apps using row-level security. Not only do they show you how it works at the Postgres level, but they also show you for Ruby on Rails, an application framework for Ruby, how you can set that up in Ruby on Rails to get it working. Now, I've never really used this because of the performance implications of it and they do have a whole section when they're talking about it. And really there's been a lot of unknowns about it, so I've never felt there's been enough visibility of it from a performance perspective to entrust using it. I mean, it definitely works. So if you have a high-security application that maybe is not as performance-centric. Maybe you would want to explore using row-level security, but there are definitely performance hiccups that could potentially happen. But if you're interested in learning more, definitely check out this blog post.

The next piece of content- "Postgres 14 highlight- Fun with Hashes". This is from paquier.xyz and he's talking all about hashes in Postgres and going into a little bit of the history of how MD5 support happened, as well as SCRAM-SHA-256 and how those different implementations are done, some internally in Postgres, some relying on the presence of OpenSSL. So he's been in the process, from my understanding, doing a refactoring of how this is done to make things easier to maintain and more consistent for the future. So if you're interested in that, definitely check out this blog post.

The last piece of content, the PostgreSQL person of the week is Georgios Kokolatos. So if you're interested in Georgios and his contributions to Postgres, definitely check out this blog post.