Episode Transcript
[00:00:00] In this episode of Scaling Postgres, we talk about constraint triggers, simple shards, data streams and OIDs. I'm Kristen Jameson and this is Scaling Postgres, episode 61.
[00:00:20] Alright, I hope you're having a great week. We don't have too much content this week, so we'll go ahead and get started. The first article is triggers to enforce constraints. And this is from the CyberTech Postgresql.com blog. And they're talking about a scenario where you have a particular constraint of where they're using prisons and prison guards and you always want to have a guard on hand. Therefore, to do this type of constraint, you can't really use table constraints to do it. So they look to use a trigger to do it, what he's calling a naive implementation of it. He did a before delete trigger and wanted to ensure there were more than two on duty tables so that guards are guarding prisons, otherwise raise an exception. Now, they say this has a problem if you have things happening more concurrently. So if one person starts a transaction, does a delete from that table, but then before the commit of this particular transaction, a new one is started and committed. So basically, because this process hasn't committed yet, this one will still see the row as being there even though it's actually been deleted. So this will run to completion and then this section will commit. So this is the kind of thing that can happen with race conditions. So basically it depends on how fast you're inserting updating or deleting this row. You could have these types of issues occur. Now, he does mention that normal constraints don't really have this problem because PostgreSQL quote also checks rows that would normally be visible to the current transaction. So it's something against MDCC rules, but it guarantees that constraints are not vulnerable to this race condition. And basically, he says there's two ways to solve it. One is using pessimistic locking and another using optimistic locking. So basically, he's using this much more complicated trigger to do it using for update of and accept to ensure that he's locking rows in the correct order. The other way to do it is optimistic locking. And here you're basically for this transaction, you're moving it from what the default is repeatable read to a serializable to ensure that only one transaction can happen at a time in a serial fashion. Now, this does come with a performance hit and you will have to retry it because you may get a serialization error if you do have a conflict. So his suggestion is if you don't anticipate a lot of conflicts, maybe do optimistic locking as long as you can retry them, otherwise use pessimistic locking. Now, he also mentions what about these constraint triggers and he says these constraint triggers respect the MVCC rules, so they cannot peak at uncommitted rows of transactions, but it can be deferred until the end of the transaction. So basically they're the last phase. So it's after each row, so you could do it that way. But as he says, the problem is still there. In other words, we've maybe narrowed the time frame in which it can happen, say right after the delete, but it can still happen. And these other two methods are the best way to avoid this type of issue, using this pessimistic locking method or using an optimistic locking method. And he says, quote, constraint triggers are not a solution for this. So if you have this type of use case and wanting to create a constraint using triggers, definitely a blog post to check out.
[00:03:54] The next post is actually a YouTube video and it's called New and Maturing built in features in PostgreSQL to help build simple shards. So this is basically about sharding and it's on the Procona Database Performance Blog and this is a pretty short video, 15 minutes. But he goes into kind of the different features of the postgres foreign data wrapper partitioning and all the features that are being added, including parallelism, and how we're moving towards being able to support essentially native sharding in PostgreSQL, maybe getting to the point eventually of current extensions that can already do this sort of thing such as Citus. And uses an example here where you can already create a foreign table that is a partition of a parent table. So a parent table in one database you can create a foreign table on another database server that serves as a partition. So you could do this for multiple databases and create a simple sharding solution. Now, you still have to do a lot of the work yourself, but the hope is over the years they'll continue adding this feature set to it. So if you're interested in the native sharding that may be eventually coming to PostgreSQL, definitely a video to check out the next post. Waiting for PostgreSQL twelve, support foreign keys that reference partitioned tables. So with partitioned tables there's been, as he mentions here, quite a lot of limitations and one of the ones is that you couldn't add a foreign key constraint that would point to a partition table. So with PostgreSQL twelve, a patch has been added that allows you to do that and he checks both range partitioning and also hash partitioning. And he got this feature to work where he could create a foreign key constraint to a partition table. So if you use partitions and you're interested in this type of feature coming in PostgreSQL twelve, definitely blog post to check out the next post is Data stream processing for Newbies with Kafka, KSQL and postgres. This is from the Haya Alpha blog and this is basically the subline says here step by step tutorial for dipping your toes into the data stream. Now, I think this is kind of similar to change data capture. So you're wanting to stream changes from say, a PostgreSQL database into a Kafka cluster or even general applications into a Kafka cluster that then other databases or applications can consume. So there's a lot of different tools that are set up here and it's done as a docker container that's available, but using Kafka Zookeeper KSQL, Kafka Schema Registry, kafka Connect PostgreSQL in conjunction with Debesium. So there's a lot of moving tools in this tool set, so it is in a docker container to examine. So this is a little bit beyond my knowledge of PostgreSQL using Kafka in this way, but if you're using Kafka or looking to do change data capture or data streaming, perhaps this is a post you'd like to check out.
[00:07:02] The next post is OIDs demoted to normal columns. A glance at the past. And this is from the PostgreSQL verite pro blog. And it basically says right here in the first sentence in PostgreSQL twelve OID columns in system tables will lose their special nature and the optional clause with OIDs will disappear from Create table. Now, I believe part of the reason why they've moved this, as they've mentioned here, is with the move towards pluggable storage that was mentioned in a previous episode of Scaling Postgres and using potentially things like Zheep. I think they're kind of rolling back some things that make that easier and this may be one of them. But I also found this very interesting from a history perspective because it talks about the origins of object orientation in postgres and how initially it was really thought of to a high degree in terms of an object oriented database. And that's why we have tables called PG class as opposed to PG tables for example. And that's why they are OIDs. They're essentially object IDs and intended to each row intended to be an instance of an object. Now a lot of times they are in terms of orms. But I just found this very interesting post from that perspective. So if you want a little bit to get into the history of PostgreSQL and some of the changes that are coming with OIDs, definitely blog post to check out the next post is generating fractals with Postgres escape time fractals. And this is from Malic Me blog. And basically this is using PostgreSQL SQL to generate fractals these designs that you're seeing here. And with it he's using a lot of CTEs to do it. So definitely if you want to flex your knowledge or test your knowledge of SQL, this is definitely a blog post to check out to see how you can use SQL to generate these fractal designs.
[00:09:01] The last piece of content is Webinar security and compliance with PostgreSQL follow up. And this is from the second Quadrant.com blog, and this is a follow up to a webinar that they hosted and you can just click this link to get registered to it. It's about an hour in length and it talks a lot about security from the perspective of there was an expense application and an LDAP series of users and they had certain security and compliance issues they wanted to address, so what permissions users could do and not do. And it talks about Row security, table security, as well as things related to PCI compliance or even GDPR compliance. So if you're interested in that type of content, definitely a webinar to sign up and go ahead and watch.
[00:09:51] That does it. For this episode of Scaling Postgres, you can get links to all the content mentioned in the show notes. Be sure to head over to Scaling Postgres, where you can sign up to receive weekly notifications of each episode, or you can subscribe via YouTube or itunes. Thanks.
