Constraint Triggers, Simple Shards, Data Streams, OIDs | Scaling Postgres 61

In this episode of Scaling Postgres, we review articles covering constraint triggers, simple shards, data streams and OIDs.

Content Discussed

• Triggers to enforce constraints
• [YouTube] New and Maturing Built-in Features in PostgreSQL to Help Build Simple Shards
• Waiting for PostgreSQL 12 – Support foreign keys that reference partitioned tables
• Data Stream Processing for Newbies with Kafka, KSQL, and Postgres
• OIDs demoted to normal columns: a glance at the past
• Generating Fractals with Postgres: Escape-Time Fractals
• Webinar: Security and Compliance with PostgreSQL [Follow Up]

YouTube Video

Podcast Audio

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 isfrom the CyberTech Postgresql.com blog. And they're talking about a scenario where you have aparticular constraint of where they're using prisons and prison guards and you always want tohave a guard on hand. Therefore, to do this type of constraint, you can't really use tableconstraints to do it. So they look to use a trigger to do it, what he's calling a naiveimplementation of it. He did a before delete trigger and wanted to ensure there were more thantwo on duty tables so that guards are guarding prisons, otherwise raise an exception. Now, theysay this has a problem if you have things happening more concurrently. So if one person starts atransaction, does a delete from that table, but then before the commit of this particulartransaction, a new one is started and committed. So basically, because this process hasn'tcommitted yet, this one will still see the row as being there even though it's actually beendeleted. So this will run to completion and then this section will commit. So this is the kind ofthing that can happen with race conditions. So basically it depends on how fast you're insertingupdating or deleting this row. You could have these types of issues occur. Now, he does mentionthat normal constraints don't really have this problem because PostgreSQL quote also checksrows that would normally be visible to the current transaction. So it's something against MDCCrules, 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 usingoptimistic locking. So basically, he's using this much more complicated trigger to do it using forupdate of and accept to ensure that he's locking rows in the correct order. The other way to do itis optimistic locking. And here you're basically for this transaction, you're moving it from what thedefault is repeatable read to a serializable to ensure that only one transaction can happen at atime in a serial fashion. Now, this does come with a performance hit and you will have to retry itbecause you may get a serialization error if you do have a conflict. So his suggestion is if youdon'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 triggersand he says these constraint triggers respect the MVCC rules, so they cannot peak atuncommitted rows of transactions, but it can be deferred until the end of the transaction. Sobasically they're the last phase. So it's after each row, so you could do it that way. But as hesays, the problem is still there. In other words, we've maybe narrowed the time frame in which itcan happen, say right after the delete, but it can still happen. And these other two methods arethe best way to avoid this type of issue, using this pessimistic locking method or using anoptimistic locking method. And he says, quote, constraint triggers are not a solution for this. So ifyou have this type of use case and wanting to create a constraint using triggers, definitely a blogpost to check out.

[00:03:54] The next post is actually a YouTube video and it's called New and Maturing built infeatures in PostgreSQL to help build simple shards. So this is basically about sharding and it'son the Procona Database Performance Blog and this is a pretty short video, 15 minutes. But hegoes into kind of the different features of the postgres foreign data wrapper partitioning and allthe features that are being added, including parallelism, and how we're moving towards beingable to support essentially native sharding in PostgreSQL, maybe getting to the point eventuallyof current extensions that can already do this sort of thing such as Citus. And uses an examplehere where you can already create a foreign table that is a partition of a parent table. So aparent table in one database you can create a foreign table on another database server thatserves as a partition. So you could do this for multiple databases and create a simple sharding s--olution. Now, you still have to do a lot of the work yourself, but the hope is over the yearsthey'll continue adding this feature set to it. So if you're interested in the native sharding that maybe eventually coming to PostgreSQL, definitely a video to check out the next post. Waiting forPostgreSQL twelve, support foreign keys that reference partitioned tables. So with partitionedtables there's been, as he mentions here, quite a lot of limitations and one of the ones is that youcouldn't add a foreign key constraint that would point to a partition table. So with PostgreSQLtwelve, a patch has been added that allows you to do that and he checks both range partitioningand also hash partitioning. And he got this feature to work where he could create a foreign keyconstraint to a partition table. So if you use partitions and you're interested in this type of featurecoming in PostgreSQL twelve, definitely blog post to check out the next post is Data streamprocessing for Newbies with Kafka, KSQL and postgres. This is from the Haya Alpha blog andthis is basically the subline says here step by step tutorial for dipping your toes into the datastream. Now, I think this is kind of similar to change data capture. So you're wanting to streamchanges from say, a PostgreSQL database into a Kafka cluster or even general applications intoa Kafka cluster that then other databases or applications can consume. So there's a lot ofdifferent tools that are set up here and it's done as a docker container that's available, but usingKafka Zookeeper KSQL, Kafka Schema Registry, kafka Connect PostgreSQL in conjunction withDebesium. So there's a lot of moving tools in this tool set, so it is in a docker container toexamine. So this is a little bit beyond my knowledge of PostgreSQL using Kafka in this way, butif you're using Kafka or looking to do change data capture or data streaming, perhaps this is apost 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 isfrom the PostgreSQL verite pro blog. And it basically says right here in the first sentence inPostgreSQL twelve OID columns in system tables will lose their special nature and the optionalclause with OIDs will disappear from Create table. Now, I believe part of the reason why they'vemoved this, as they've mentioned here, is with the move towards pluggable storage that wasmentioned in a previous episode of Scaling Postgres and using potentially things like Zheep. Ithink 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 originsof object orientation in postgres and how initially it was really thought of to a high degree interms of an object oriented database. And that's why we have tables called PG class asopposed to PG tables for example. And that's why they are OIDs. They're essentially object IDsand intended to each row intended to be an instance of an object. Now a lot of times they are interms of orms. But I just found this very interesting post from that perspective. So if you want alittle bit to get into the history of PostgreSQL and some of the changes that are coming withOIDs, definitely blog post to check out the next post is generating fractals with Postgres escapetime fractals. And this is from Malic Me blog. And basically this is using PostgreSQL SQL togenerate fractals these designs that you're seeing here. And with it he's using a lot of CTEs todo it. So definitely if you want to flex your knowledge or test your knowledge of SQL, this isdefinitely a blog post to check out to see how you can use SQL to generate these fractaldesigns.

[00:09:01] The last piece of content is Webinar security and compliance with PostgreSQL followup. And this is from the second Quadrant.com blog, and this is a follow up to a webinar that theyhosted and you can just click this link to get registered to it. It's about an hour in length and ittalks a lot about security from the perspective of there was an expense application and an LDAPseries of users and they had certain security and compliance issues they wanted to address, sowhat permissions users could do and not do. And it talks about Row security, table security, aswell as things related to PCI compliance or even GDPR compliance. So if you're interested inthat type of content, definitely a webinar to sign up a --nd go ahead and watch.

[00:09:51] That does it. For this episode of Scaling Postgres, you can get links to all the contentmentioned in the show notes. Be sure to head over to Scaling Postgres, where you can sign upto receive weekly notifications of each episode, or you can subscribe via YouTube or itunes.Thanks. --