Forced Sequential Scans, Table Renaming, Terminology, PGSQL Phriday | Scaling Postgres 240

In this episode of Scaling Postgres, we discuss how sequential scans can be forced, the best way to rename a table without downtime, different Postgres terminology and the PGSQL Phriday blogging event.

Content Discussed

• Forcing Sequential Scans on PostgreSQL Using Large Integers
• 5mins of Postgres E42: A surprising case of very large integers causing a Sequential Scan
• Postgres: Safely renaming a table with no downtime using updatable views
• Postgres Insider Terminology
• PGSQL Phriday #002: PostgreSQL Backup and Restore
• Backups for Postgres - PGSQL Phriday #002
• How to do proper backups
• Validating Backups: PGSQL Phriday #002
• Adventures in PostgreSQL Backups
• pg_basebackup could not set compression worker count - unsupported parameter
• btree vs. BRIN: 2 options for indexing in PostgreSQL data warehouses
• Postgres Indexing: When Does BRIN Win?
• New Public Schema Permissions in PostgreSQL 15
• PostgreSQL: Are All NULLs the Same?
• Securing Patroni REST API End Points – Part 1
• Data Loading in Postgres for Newbies
• Version control for databases
• Adrien Nayrat
• Rubber Duck Dev Show Episode 64 | Where To Put Your Business Logic With Jason Charnes

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 "Forcing Sequential Scans on PostgreSQL Using Large Integers". This is from code.jeremyevens.net and he developed a tool called Sequel that gives a DSL, or domain-specific language for sending SQL to a database using Ruby. And he discovered this particular issue that has implications for a potential Denial of Service attack. So basically, when you have an integer value in an SQL query and it's outside the range of an integer or the bigint, it treats that column as numeric. Now, that might be a little bit confusing, but imagine you have a WHERE clause and you have a super large number here that's larger than a bigint. What does Postgres do? It converts it to a numeric and then to do the comparison, it converts the integer or the bigint to a numeric. Now, the problem with this is that it will no longer use an index on that particular column because it was indexed as an int or a bigint.

It can't use a different type when using the index. So essentially this causes a sequential scan. He has an example of it here, where he does a generate_series of about a million records, and adds an index to it, it just has a single column, which is an integer. He uses a bigint against it and you see you'll get an index-only scan that makes sense. But when he tries to use a number larger than a bigint, it converts it to numerics. So you can see it's compared to numerics. Then it does a sequential scan. So if you have a billion-row table and someone is able to send larger numbers than an integer to your table, they could trigger a bunch of sequential scans, either intentionally or unintentionally. But no matter what, as he says here, this is a potential Denial of Service vector. Now, he goes through some different ways to avoid this. One is to use bound variables. Another is raising an exception instead of running the query. A third way is explicit casting, but there seems to be a lot of potential issues with doing that.

A fourth way he explored is quoting, but even that doesn't work all the time. So it looks like the bound variables are the ideal way to go, or manually checking it and triggering an exception if that occurs. But the thing to note is that this isn't really considered a Postgres bug, it's basically how it was designed. He goes into more detail about whether you're potentially impacted because some other languages also could be impacted by switching a number to double precision, for example, or using floats. And he says for a large part, he doesn't see the other databases being vulnerable compared to Postgres, and he has some ideas on how that could potentially change and then how he ultimately changed his library. Now, of course, I was interested in this from a Ruby on Rails perspective because that's the application framework I use.

By default, they do use the bound variables or essentially the prepared statements for the ORM active record that it uses. So its behavior, if it sees a number larger than a bigint, it actually never issues the query. It just returns an exception saying that that record can't be found, it just never runs a query. So there's no risk of this happening. But the thing to keep in mind is that a lot of people who use PgBouncer in their Ruby on Rails applications or even other application frameworks have to turn off prepared statements and then essentially you lose that capability.

So I actually did a test in Rails and in my database configuration for talking to the database, I turned off prepared statements and it did issue the queries with the larger than integer. So this would be susceptible to this type of potential Denial of Service issue. So basically, this is definitely something to watch out for. Again, it looks like the ideal solution is to use bound variables, but if you can't do that for some reason, maybe you're using PgBouncer or your ORM doesn't support it, then you should be manually checking user input and firing an exception if it's outside acceptable bounds. So definitely check out this blog post if you want to learn more about that.

But this also happens to be the next episode of "5mins of Postgres E42: A surprising case of very large integers causing a Sequential Scan". This is from pganalyze.com, and he covers this topic in detail as well. So if you want to learn even more about this, definitely encourage you to check out Lukas's post as well.

Next piece of content- "Postgres: Safely renaming a table with no downtime using updatable views". This is from brandur.org. He's talking about an issue where you want to update a table, and he says people frequently don't do that because of all the potential moving parts that can occur. But with Postgres, there's a relatively easy way to do it if you can get by with it. Basically, within one transaction, you rename the table to what you want to rename it to, and then you create a view of the old table name pointing to the new table name. This should still allow you to use that old table name and still do your INSERTS, UPDATES, AND DELETES. It still should all work through that view. And then later on, if things are working, you can start using the new table name in all of your code and then you can drop that view. This is a great way to easily be able to rename a table but if you need to do that, definitely check out using this technique and this post.

Next piece of content- "Postgres Insider terminology". This is from crunchydata.com. They're talking about different terminology used within the Postgres community. One, when you're talking about databases, you frequently hear the word Tuple. What does that mean? It basically means a row or a record in a table. Now you may hear record or row, which basically means a row in a table. But in Postgres, you typically see records mentioned in terms of what's returned from functions. So you could see this RETURNS a record. And in terms of rows, you can frequently see it if you try to construct a value. You can use the ROW function here when issuing certain statements. They talk about arrays, they talk about relations.

And now typically, relation is considered a table, but it can also mean views. So views are relations as well as the results of queries are relations. So it's basically a set of Tuples that could be considered a relation. Then he gets a little bit more into the code of Postgres and there are references to a target list, which is generally the list of columns in a select query, and restrictions, which are generally what the where clause specifies. He talks about schemas, which are basically namespace areas. And to see what your schemas are, you basically select all from pg_namespace. Then a discussion about the term pages and what that means, as well as TOAST, which if you're not familiar, is The Oversized Attribute Storage Technique. So this is an interesting post covering different terminology in Postgres, so check it out if you're interested.

Next piece of content- "PGSQL Phriday #002: PostgreSQL Backup and Restore". This is from andreas.scherbaum.la. He's basically kicking off the next week of PGSQL Phriday, a community blogging event, and it's on Backups and Restores. So this is the kickoff post and then the next set of posts, I'll cover that topic.

This one- "BACKUPS FOR POSTGRES- PGSQL PHRIDAY #002". This is the blog from rustprooflabs.com. And in terms of his Backup and Restore philosophy, his first line of defense is actually his streaming Replication Replica. So basically if anything happens with a primary database, you just promote the Replica. And for nearly all my clients, this is the first line of defense. You want to promote that Replica and hopefully, at the most, you'll lose a few seconds of data if it doesn't get replicated for some reason. But in terms of actually doing backup, they like using pgBackRest. And you'll see this as a trend in all of these posts. A lot of them promote pgBackRest for managing their backup and restore process.

Next post- mydbanotebook.org is "How to do proper backups". This post actually lists some problems with the default tool that comes with Postgres pgbase_backup. Basically, it doesn't do what she feels a full-featured backup tool should do, and she thinks it should be named pg_clone_cluster. She does say Barman does exist, but her preference is pgBackRest like the other poster mentioned.

The next post- "VALIDATING BACKUPS: PGSQL PHRIDAY #002". This is from scarydba.com, and he basically says backups don't matter, it's the restores that matter. And I can agree with that. No matter what backup method you use, you want to restore.

Next post- "Adventures in PostgreSQL Backups". This is from softwareandbooz.com, and he also advocates that restores are more important than the backups themselves. Definitely agree, but he also mentions extensions here. So whenever you want to bring your server back up, you need to make sure to bring it back up to a consistent state. You want to make sure you know all your extensions and all the requisite packages are in place to restore that database to its fully functioning level. Now, some of the other posts mentioned Ansible, that's the way that I choose to be able to rebuild a full database server from scratch if that ever became necessary. It's also the technique to build up the Replicas as well. Now, one thing that wasn't mentioned in these posts is being able to do automated restores. So I actually have a few clients that actually do test restores on a regular basis, or they've entirely automated it in a cron job for a dedicated server to take the backup and automatically restore the database to a particular point in time. So those are other techniques you can do as well.

Next piece of content- "pgbase_backup could not set compression worker count- unsupported parameter". This is from rhaas.blogspot.com. He's talking about an issue where when you're running pgbase_backup in Postgres 15, you could run into this message that says "could not initiate backup: ERROR: could not set compression worker count to 4: unsupported parameter". Apparently, this is due to if you've set the compression either on the server or the client to use zstd. So basically you need to ensure that the zstd library is present. Libztd needs to be present for these features to work. So this is a quick post just to let everyone know that. And if you still have problems, make sure that the version you're installing is at least 1.5.0. So definitely check out this blog post if you want to learn more about that.

Next piece of content- "BTREE VS. BRIN: 2 OPTIONS FOR INDEXING IN POSTGRESQL DATA WAREHOUSES". This is from cybertec-postgresql.com and this post compares B-tree and BRIN indexes. Now, I was reading this and I was actually confused about a point and some results that they had and actually tried replicating it but I could not. So basically they created a table with a sorted integer and a random integer and then they populated it with 5 billion rows. So quite a lot of data. Now, in my test, I only did 100 million because I didn't want to wait for a long index build or long table generation or things like that, but I don't think that would have the same impact. So then they created an index on the ID sorted column and then created another index on the ID random column. These are B-tree indexes. Now, selecting a count from the table, where the sorted is between a range of about 10 million, returned in 358 milliseconds, so pretty quickly. It did an index-only scan in parallel. So that makes sense. They then did the same query, but they used the ID random column. The range was adjusted a bit to try and get the same amount of data because again, they're random numbers, so it's a little bit harder to do that.

But here they had an atrocious result. In using explain buffers, they had a huge number of Heap Fetches, which didn't make any sense to me, because essentially the index is in order and it shouldn't be going to the heap to get anything. Now, this is what I couldn't replicate because I did the same table, not the same data size, but the same indexes. The result for ID random was identical to the ID sorted. It was doing a parallel index-only scan, which makes sense. So I don't quite understand this result, because intuitively, the index is ordered, and you're not asking for any columns. It should do a parallel index-only scan, which is what I got when I tested it on Postgres 14, not 15. So I don't quite understand what's going on here. But then he tried BRIN indexes. Now, the thing to know about BRIN is that they are block range indexes. They are super small because they only specify ranges.

So when you look up the data, it knows a range of values and then if it needs to know specific ones in there, it needs to go to the heap to get specific values. So essentially, when you run the BRIN index, it's going to do a bitmap index scan, but then it's always going to have to go to the heap to find the specific rows because it's only operating in ranges. And with that, he actually saw worse performance up the BRIN compared to the B-tree. Now, that's not always the case. Sometimes BRIN is going to be faster, but I think the reason this is slower is because it has to go to the heap. The B-tree index up here, looking at the sorted, is an index-only scan and you basically can't get an index-only scan with a BRIN because you have to go heap to get the specific values. It's only looking at given ranges. So I think that's the reason why it's slower or explains what's going on.
Now, this next post was actually done back in July and it's "Postgres Indexing: When Does BRIN Win?". This is from crunchydata.com, and they demonstrated that at a small number of rows for B-tree sequential, it's going to trounce the BRIN and this is not using an index-only scan. So they were pulling another column. So it was typical you scan the index and then you go to the heap to get the data. So there were no index-only scans here like in the previous post. What they showed is that at small row counts you're pulling, the B-tree index will definitely win because it knows exactly where those rows are. It started to get less as they went up to 1,000 rows.
At 10,000 rows the B-tree and the BRIN indexes are near even, and then at 100,000 rows the BRIN starts winning and it basically goes up from there. The more rows that you're going to access, the BRIN is going to win because it's faster to access that index and then go to the heap to get the specific values needed compared to using a huge B-tree index because that's another difference between B-tree and a BRIN. A BRIN is super small because it only indexes ranges, whereas a B-tree indexes every value. So you can definitely check out these posts if you want to learn more about B-tree and BRIN indexes.

Next piece of content- "New Public Schema Permissions in PostgreSQL 15". This is from enterprisedb.com. They're talking about the schema changes to 15 that we've covered in previous blog posts on Scaling Postgres. Basically, users can no longer create any object they want in the public schema, but this doesn't happen by default, even after you upgrade. In order to get this behavior for existing databases, you actually need to do these two commands to do that, alter the schema public owner to the pg_database_owner and then REVOKE CREATE ON SCHEMA public FROM PUBLIC. And that'll essentially set your existing database to the way a new database would be created. But if you want to learn more, definitely check out this blog post.

Next piece of content- "PostgreSQL: Are All NULLs the Same?". This is from percona.com. They're talking about DISTINCT and NULLs. And then typically a NULL is an unknown value, so there could be distinct values, but there could not be. Basically, NULLs have typically been considered all to be distinct, but there's a new command in Postgres 15 that allows you to say NOT DISTINCT, so it considers all NULLs one value for that purpose. We've covered this in a previous blog post on Scaling Postgres, but if you want to learn more about that, you can definitely check out this blog post as well.

Next piece of content- "Securing Patroni REST API End Points- Part 1". This is from percona.com. They're talking about how you can set up usernames and passwords for the Patroni REST API, I believe, as well as HTTPS encryption to protect your Patroni install. So if you're interested in that, check out this blog post.

Next piece of content- "Data Loading in Postgres for Newbies". This is from crunchydata.com. They're talking about all sorts of different ways you can get data into your DB. The main one is definitely using a CSV import to do it using the psql \copy command, but there are also ways to do it using JSON, particularly using the jq operating system command and how they're doing it. Here, they're just loading it into one JSONB table, and then perhaps you want to transfer that data elsewhere using an INSERT INTO another table and selecting from the existing one. And typically this is how I load data. I load it into some sort of temporary table because you frequently need to transform the data or you have data quality issues.

You load it into a temporary table that is not as strict, and then you insert it into its final destination, doing all the transformations and data validations needed. They also talk about different GUI ways to get data in, ways to dump and restore the data, and using foreign data wrappers to get a connection from another database, to it to insert data. Even to run a query in order to generate the data that you want to produce. So if you want to learn more about all these different techniques, definitely check out this blog post.

Next piece of content. There was another episode of Postgres FM this week. This one was on "Version control for databases". If you want to learn more about that, you can definitely listen to the episode or check out the YouTube link below.

The next piece of content. The PostgreSQL person of the week is Adrien Nayrat. If you're interested in learning more about Adrien and his contributions to Postgres, definitely check out this blog post.

The last piece of content. We did have another episode of The Rubber Duck Dev Show this past Wednesday evening. This was on "Where To Put Your Business Logic With Jason Charnes". We tend to use Rails, so we discussed that as well as some other application frameworks where they tend to put them. But if you're interested in that type of long-form developer content, we welcome you to check out our show.