PG14 Index Bug, View Permissions, Logical Replication Conflicts, AlloyDB Under the Hood | Scaling Postgres 218

In this episode of Scaling Postgres, we discuss a severe bug with index creation in Postgres 14, view permissions options, dealing with logical replication conflicts and looking under the hood of AlloyDB.

Content Discussed

• 5mins of Postgres E20: An important bug in Postgres 14 with REINDEX / CREATE INDEX CONCURRENTLY, and using the amcheck extension
• Revert changes to CONCURRENTLY that "sped up" Xmin advance
• amcheck
• View permissions and row-level security in PostgreSQL
• How to handle logical replication conflicts in PostgreSQL
• AlloyDB for PostgreSQL under the hood: Intelligent, database-aware storage
• AlloyDB for PostgreSQL under the hood: Columnar engine
• Using a virtual environment with pl/python3 in PostgreSQL
• New ORDER BY and LIMIT OFFSET Pushdown Features Increase Agility in mysql_fdw
• Effective PostgreSQL Cluster Configuration & Management Using PGO v5.1
• Virginie Jourdan
• Rubber Duck Dev Show Episode 44 | Reviewing the 2022 Rails Community Survey

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 "5mins of Postgres E20: An important bug in Postgres 14 with REINDEX/CREATE INDEX CONCURRENTLY, and using the amcheck extension". This is from pganalyze.com and this is a very important notice. Lukas used a notification that was on Twitter and some other sites to give insight into this bug. Basically, there's a chance for index corruption if you've done a CREATE INDEX CONCURRENTLY or a REINDEX CONCURRENTLY in version 14. He has the link to the Git commit here where they mentioned that when doing index creation or reindexing with the CONCURRENTLY option, it can quote "...miss heap tuples that were HOT-updated and HOT-pruned during the index creation".

So someone said that they identified this bug when they had an active database that they were doing a REINDEX or CREATE INDEX operation. So unfortunately, you are at risk for experiencing this as well and they are actively working on a fix. Now, what you can do to see if your indexes are corrupted is there is this amcheck extension that you can use and then there's a specific function that you can run called bt_index_check to be able to check your indexes for consistency. So that's kind of the information we seem to know about now. And presumably, they are looking to accelerate releasing a new version with this patch included. But if you want to learn more, definitely check out the most recent episode of Five Minutes of Postgres.

The next piece of content- "VIEW PERMISSIONS AND ROW-LEVEL SECURITY IN POSTGRESQL". This is from cybertec-postgresql.com, and they're talking about the new feature in postgres 15, talking about "security_invoker views". So basically, views typically run with the permissions of the person who created them. So that way you can create a view, give it access to multiple tables, and then grant a role or a user the ability to use that view without having permission to the base tables. He also says as a quick security note that if you're doing this, you may want to add a security_barrier when you're creating the view. So definitely check out this documentation from Postgres on that if you want to learn more. But this kind of works differently than row-level securities. It's possible to kind of bypass the row-level security if you're using views.

So he has an example where a user here creates a table, inserts two values into the table, then enables row-level security, and adds a policy where a user can see, essentially, the row that that user owns since it was inserted into the RLS user column. Then that user creates a view and grants permissions on it to this new user. And now when this user tries to query the view, he can see both rows. So essentially, this view allows him to bypass the row-level security. But Postgres 15 has security_invoker views, so basically apply the security of the person who is invoking the view as opposed to who created the view. So here he altered the view and he set the security invoker to be on. When Joe goes to query the view, it can only see his row. So if you're using row-level security, having a security_invoker set for views seems to be important, but there may be other use cases as well that you may want to use. So if you want to learn more about this, definitely check out this blog post.

The next piece of content- "How to handle logical replication conflicts in PostgreSQL". This is from postgresql.fastwear.com and they're talking about a feature that enables you to identify a replication conflict and then essentially skip it. Now that has a certain set of dangers applied to it because you may forget a row or include a row. And then can you replay future actions that need to be played back on that replica? But this blog post goes through the process of how you can do that. So basically, they have a publisher database and when an update happens it gets sent to the WAL, the WAL sender sends it to the subscriber's apply worker and if there's some sort of inconsistency it can produce an error. So in Postgres 15, there's a new option to disable_on_error. So essentially it disables logical replication altogether for that subscriber if that happens and then you can address how to deal with it as opposed to it trying the same thing again and again.

So they also mentioned that Postgres 15 has a new pg_stat_subscription_stats view that you can look at more information about the disable_on_error option and it also gives additional context information with regard to an error. So in the example, they created a table on a publisher, inserted a value, created the publication, then on the subscriber node they created the table there, and they created the subscription with the disable_on_error=true. So it's going to fail if anything happens. Now they begin a transaction and then they insert one value into it, they commit it, then they INSERT a second transaction and in this one, they are actually inserting a value that already exists in the table because they already inserted it in the publisher and it got sent to the subscriber. So essentially they're going to be sending a duplicate value. Now, when they created the table on the subscriber they made the ID unique.

So they're going to run into a conflict on the subscriber when they try this, but not on the publisher because it doesn't have that unique constraint when they create the table here. So basically on the publisher, you see this information. You see rows 5, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10. So all of the additional inserts are done without error. When you look at the subscriber, they check the pg_stat_subscription_stats view and they can see this is the name of their subscription- mysub. You see the apply_error_count, so they couldn't apply a change. So that's an error. So then they also query the pg_subscription table and they see that the sub has been disabled, it's subenabled false and that it is set to disable_on_error. When they select from the table, they can only see the original five that were inserted during the initial sync. And they see the first transaction was done, but nothing from the second transaction was done. So they had a conflict and in that conflict, this is the error that showed duplicate key value violates the unique constraint that was applied to the table.

And they say ID 5 already exists which we knew about. It says "processing remote data for replication origin" with an Identifier "during 'INSERT' for replication target relation". The table finished at a particular LSN and it says "logical replication subscription 'mysub' has been disabled due to an error". Now, how you can resolve this and again, you need to be very careful about doing this. But on the subscriber side, they did a pg_replication_origin_advance. Then they put in the replication origin and a particular LSN. Now they happened to just increment it one from what was here. I don't know if you can always just do that. They may have used, as they mentioned here, pg_wal_dump to identify the particular LSN. I'm not sure if he did that or just assumed that they could just increment it by one. But upon doing that, it skipped over that transaction that had the problem and it was able to replicate 9, which was a third transaction that was done on the publisher but not sent to the subscriber.

So basically, they are showing that the subscription was able to skip over that whole transaction, but it was able to publish a subsequent transaction to the subscriber. This is a very important thing to look at- what can go wrong if I specify the wrong parameters? So this is the danger of skipping transactions and getting the LSN right to advance to the right one. And I believe from what they're saying here, they use pg_wal_dump to be able to identify what transaction to do. So this is a feature to be able to identify errors and understand exactly where they're coming from and shows an example of how to skip over them. But again, there are risks associated with skipping over. You may want to do something different like resolving this some other way. Maybe you remove the unique constraint on the subscriber. Maybe you want to delete the road that's there and correct it manually in order to get replication up and running. It's pretty much up to you. But if you want to learn more about this feature, definitely check out this blog post.

The next piece of content- "AlloyDB for PostgreSQL under the hood: Intelligent, database-aware storage". This is from cloud.google.com and they're talking about AlloyDB. This is the new hosted Postgres service by Google that they've done a lot of work on, putting a particular emphasis on the disaggregation of computing and storage. Now they use this word about twelve different places in this blog post. I would call it a separation of computing and storage, frankly. But basically, they get some advantages by separating the compute nodes from the storage nodes and the storage system is kind of where all their secret sauce seems to be located for this and that. You still have a primary instance, you still have replicas. But of course, the primary is only going to be receiving writes and it goes through a WAL log process to store that log immediately.

And then they have log processing servers that actually take those logs and apply them to the block storage for all the other primaries and replicas across the different zones to read. And they talk about a bunch of different benefits of doing this. Now, one big advantage that this technique has, and they go through a lot of detail on how this is constructed, is that because you separate the compute units from the storage units, you can have as many replicas as you want without having to duplicate all the storage. So essentially, what they're saying is you have all of your data replicated essentially across three different zones and maybe there's some duplication within a zone, I'm not sure, but all the data is replicated that way for the safety of the data.

But you can have as many replicas as you want to read from that data within each zone. Whereas a normal replication that was set up by Postgres, a replica has its own independent storage. So if you want to have five replicas, you're going to have to have five copies of your data and that can get pretty expensive if you have a terabyte-scaled database. Whereas with this, you are able to store essentially fewer copies. But you can have as many computing nodes serving as a replica as you want. So that is definitely an advantage of this type of infrastructure and it would be interesting to see Postgres eventually support something like this in an open-source method in the future.

But related to this, there's been another post called "AlloyDB for PostgreSQL under the hood: Columnar engine". In this one, they're talking about the ability for AlloyDB to look at the queries that are being sent and use artificial intelligence to dynamically assign data to a column format so it can keep some data in a column format to be able to answer aggregate queries very quickly. They're talking about some acceleration anywhere from 100x improvement for certain queries like this is doing a sum. So aggregations like sums or averages or things of that nature can get a big boost from a column-oriented format of the data. They saw a 19x improvement, 8x improvement 2.6. So it shows different advantages of the column format compared to just storing all your data in rows. And apparently, it does this dynamically based on the queries that it's seen. If you want to learn more about that, definitely check out this blog post as well.

The next piece of content- "Using a virtual environment with pl/python3 in PostgreSQL". This is from enterprisedb.com. So Python enables you to set up virtual environments for different versions of different projects potentially, that you're working on. The thing about Postgres, of course, is that it can run Python. And they say there's a way to actually set up a particular virtual environment for your Postgres installation to determine what Python version it's going to be running. And you can do a server-wide configuration for it. Basically, you go into the system D configuration and you can actually append a source command to be able to set the Python environment before running the actual executable for the Postgres postmaster.

So by doing that, you can set up a particular Python version for your Postgres installation. Then down below, there is a way in a development environment to set a per-session configuration. So you use a Python utility called virtualenv, and they work through the process of setting that up and then creating a particular extension within your Postgres database and a way to activate a particular Python virtual environment with this function here. So if you're interested in setting up virtual environments for Postgres in terms of your Python environments, definitely check out this blog post.

Next piece of content- "New ORDER BY and LIMIT OFFSET Pushdown Features Increase Agility in mysql_fdw". This is from enterprisedb.com. So the concept of pushdown is that when you have a Foreign Data Wrapper, so Postgres is talking to another database, maybe it's a Postgres database, and you're using a Foreign Data Wrapper to do it. In this example, you're using MySQL. When you use a pushdown feature, you're able to ask that database to do the query in terms of ORDER BYs, OFFSETS, or joins, have that database do it, and just give you the results of that query.

Now they're talking about new features that were added where an ORDER push down, a LIMIT OFFSET push down, and then the combo of both being able to support a pushdown. And they've looked at the performance impact of this. Prior to implementing this pushdown functionality, a particular query would return in 48 seconds or 48,000 milliseconds, whereas with the pushdown functionality, it ran in 31 milliseconds. So that's a huge performance benefit. So if you're using Postgres with mysql_fdw, you definitely want to upgrade to get some of these performance benefits from these pushdown capabilities, but if you want to learn more, definitely check out this blog post.

The next piece of content- "Effective PostgreSQL Cluster Configuration & Management Using PGO v5.1". This is from crunchydata.com. They're talking about a few enhancements that have been added to their Postgres operator for Kubernetes. The first one is rolling database restarts. You used to have to manually trigger these to do it, but now it automatically happens. So if you do a configuration change that requires a restart of the database to get that configuration change enabled, apparently the system will now automatically do that for you. The second is pod disruption budgets. So basically you can specify how many replicas you want to keep around and available to maintain the availability of your cluster if there's any kind of incident that happens with a pod going down. Then third, supporting manual switchover and failover. So if you want to failover or have a particular Kubernetes instance be the primary, you should be able to designate that now. But if you want to learn more, definitely check out this blog post.

Next piece of content, the PostgreSQL person of the week is Virginie Jourdan. If you're interested in learning more about Virginie and her contributions to Postgres, definitely check out this blog post.

And the last piece of content, we did have another episode of The Rubber Duck Dev Show this past Wednesday evening. This one was on "Reviewing the 2022 Rails Community Survey". So this was recently published and we kind of go through the survey and give our reaction to the different responses that different people made, as well as our perceptions of what that means for the Rails community. So if you're interested in that content, we welcome you to check out our show.