Hazards of logical decoding in PostgreSQL | POSETTE 2024

Key Takeaways

[Music] so hello my name is pal I work at zando which is a European e-commerce platform I am a senior software engineer in a team which is responsible for internal database as a service platform and today I want to share with you our findings and experience when adopting a supporting our internal CDC solution based on logical coding so uh as I said today we will look into the logical decoding in postr SQL and actually the hazards behind it and the plan for today is uh just to quickly look into what CDC is what are the approaches for poql and then go directly to the logical decoding the concepts behind it and the hazards which it may bring to you um so first CDC is essentially change data capture uh it's a set of design patterns which used to track changes that occur in your data when and what kind of changes and then notify the external systems uh about those uh which basically helps you to maintain uh consistency CS all of those systems for postgress we mainly have three approaches to do that uh first is to run queries against dedicated time Stam columns such as um created ad or updated ad then uh you can use all good triggers uh just to create them for the events you want to not be notified about and then the maybe most modern and advanced uh solution is to use logical decoding which I want to talk about today so logical decoding is effectively process of extracting uh all changes persisted to write a headlock back to the format which is human readable or the application friendly uh and can be further used without knowing the internal state or structure of the database and my idea um what I want to tell you today is that even though this um approach is very uh Advanced and it actually enables a lot of new use cases for you it can be dangerous and it's rather complex it requires you to understand all the um implementation details maybe not all but the most important that can uh be dangerous and um it's not only about database administrators but also about the end users and and to cover those um I would like to split my talk into three parts uh talking about the three main components uh so to say of The Logical decoding and we will start with the decoding process itself um what is important to know is that um post scill cluster has a single wrer head log uh it's for everything not splitted per database or per table and the second part um a bit more tricky but you need to remember that all by default all um transactions are sent to the consumer only on Commit uh of course you may say that since postgress SC 14 there is a streaming of inog progress transactions mod but um by default it's only implemented if your consumer is another post riscal instance but of course um for other implementations for your and applications you would need to implement it yourself and it's rather questionable if you want to implement it and store Data before they are actually committed before this data is actually committed on your application side or it's better to keep it on the database side this way um I will concentrate on the default behavior and to better understand why it's dangerous um I would like to quickly look into to the process and um for every connection for logical decoding there's a separate um wall Center process point which reads sequentially all the wall records and decode each of those and uh store those in a specific buffer basically cues it in every order buffer when a commit message is reached and decoded then and the associated transaction starts to be played and for every record there's a specific callback called which is basically the place where the customization takes place and um for example output plugins which implement this customization can transform your data into any um format you want it to be and also what is important on this step there's a filtering taking place uh the so-call publication Define if you need to stream this data further or it can be filtered out and why it is so important to know that is uh imagine you have a transaction number one which is opened and then suddenly another transaction cames and comes and there is a this transaction is very right intensive so for example it in inserts a lot of data and then it commits uh this way it it doesn't matter how quickly you commit the first transaction your receiver will first receive all the changes from the second transaction which needs to be decoded uh replayed filtered and only after that the changes from your first transactions transaction will be received as I said there is a rewarder buffer and it's stored in memory but of course memory is not endless and there's a limit after reaching which uh all the decoded changes not yet committed will be spilled to disk to a specific directory more insights on the process uh can be found in the PG replication slots uh system view um there you can find cumulative information uh about how much was decoded in general or what is more important how much was spilled and this is probably and I think yeah it's it's worth monitoring um and this is available uh since postgress 14 another important Nuance worth knowing uh is that for every decoding process there's a separate will send response uh consequently a separate decoding process happening um you would uh allocate every time this logical decoding workm and amount of memory and still uh decode the data for each um process and each of them reads the same W record stream so does the same work and another thing worth understanding is that um it's actually very difficult to scale this by just scaling your instance vertically because each decoding process will only use a single CPU quote and as an example how to easily get trapped by those um nuances those hazards is our favorite uh online vacuum full and cluster tool called PGC and actually it's initial stage initial step is to copy all the data from the target table to a so-called temporary table which is of course a very right intensive operation and it's done in a single transaction um then think uh of your database as a very as of your table as a very big table it means that of course you will copy a lot of data and of I think you probably have um full page rights enabled um consequently there's a lot of data written to wall so PG wall directory grow also memory is filled with the this um reorder buffer and starts to all the decoded changes starts to be um spilled to dis so this specific directory with spill files also grows um so your dis free disk space decreases uh very rapidly and what is the worst thing here is that it's not even needed for your consumer it all is decoded all the resources are used but uh of course as this is a temporary table you do not even need this so it also filtered out and never sent uh then to the second part and I think the most important part to understand is The Logical replication slot and this is essentially just a structure uh that defines the state of the associated consumer um and defines the resources still needed for it and by the resources I mean uh WR head log and uh tles affecting system catalog um that also needed for the decoding operation uh it is important to know that slot is persistent it's Crash safe and um it exists independently of the connection usern it uh so to say if you disconnect um your client uh slot stays in place but just does not update it State and does does not move the positions which Define the required resources um more insights on the process can be found in P application slots uh system view it gives you information about whether this slot is active so if uh a wall sender is running for this connection so the connection is established and uh the positions which I mentioned which Define the resources still needed um all this catalogic mean is about oldest transaction affecting the catalog uh system catalog which is still needed then uh restart alen is the oldest R head loock still needed and the third one and very important one is the consumer confirmed uh the position up to which the uh consumer confirmed processing the data uh this is called confirmed flash Ln uh why it is important to know that it's persistent uh the slot uh is because just think of the situation when you disconnected from the slot and then connected to it again and want to continue your decoding process uh for that you define the starting point which you probably proba have saved uh locally and uh the thing is that if persistent data of the slot defines a position um newer than what you provided Your Position will be disregarded and the saf Position will be used because it's essentially your actual state so it's important to properly Implement your client and properly store the current state on your side um and about the part that it um defines the resources still needed think of the situation when you have a slot that you disconnect it from and it stops at updating its state so posr thinks that all the resources created after this position are still needed and it's essentially means that uh your PG wall directory will grow endlessly before you connect to the slot again and start to update its um state and it's actually very easy to get into this situation uh we've seen a lot of uh cases when people were thinking they are doing better but in fact uh they were just creating an inactive slot for example they could uh downscale their consumers for cost-saving reasons over the weekend or after performing some tests and waiting for the production launch and many many other situations but it all is about the situation when slot is not um Advanced and uh PG wall just accumulating in the PG directory and disk space is running out so um one minor but very actually very important thing about uh those who make use of patroni uh patronic has a nice feature of logical uh slots failover and for that you will Define um slots in your patronic configuration as the permanent slots and when your users um even when they remember to delete Slots if the slot is still in the patron configuration it will be immediately recreated for that reason it's worth thinking about any kind of um interfaces for patroning configuration for your users or even better some automation around that and users will of course ask questions uh about the necessity of the slot deltion and uh the first will be I'm not producing any changes to my for my publication it's easy as I said W is a single stream of changes for the whole cluster and it's not necessary if you produce changes for your publication or for anything else in your database it just uh writes all changes into single WR ahead loock and if you're not deleting slot WR ah headlock size just grows the more sophisticated question would be um we they would say they are not expecting any right activity at all during the weekends um it's almost always not true because um you may not be aware of the sheduled um maintenance task um or some minor right activity can cause an out vacuum to kick in and this all any any right activity activates the process the which after an archive timeout elapsed uh of switching the wall segment so that uh database starts to write to a new um file and tricky part here is that every file regardless how much you written to it will be of the same size which is 16 megabytes by default um or the size you defined on the initialization State uh stage um and it's not only about slot being inactive to have problems you also can have your SL just moving very slowly in comparison to how quickly you produce right and uh one of the reasons for that it can be that that your consumer just underperforming uh for that just first of course tune your consumer application and uh remember to make use of Publications or actually filtering not only on the table or schema but also on the action you want to publish and the last thing about uh slots is um failover of course if your database fails over to a new instance you want to continue decoding process and um not to miss any changes unfortunately there's no outof thee box solution yet even though we're expecting it in the closest major release but for now there are a couple of solutions for that uh if you are using RDS or any uh system that uses file system replication that's not a problem for you for others uh you can use Patron logical Source failover uh mechanism as I've mentioned which has it cons it cons uh because of the function used internally or the more advanced solution um PG faer slots extension and now to the last part uh the replication protocol uh just quick overview of how it looks like there is a message with data um with changes sent and actually apart from data it also contains the position in the right of headlock which is associated with this change and this uh position should be used by consumer to update its internal State then the consumer is abl to respond back to the wall sender and um just inform about its current state which is in its turn used by W Senter to update the replication slot um State and looking into this schema you would probably say that um if there is no data if there is nothing produced in the publication and no changes produced for the publication uh slot will not be moved forward and for that reason there's a special message uh called primary keep life message which sends um the current position of the wall cender decoder to consumer and this position can also be used for updates in the internal State and respond back to the wall sender to allow replication slot to be Advanced but unfortunately it's quite often forgotten and for example up until to the latest version of not the latest but one of the latest versions of BJ DBC there was such an oversight that uh this message was received but the position was never used to update the internal state of the consumer and the position reported back to W sender was stale and replication slot was not moved forward and yeah it's basically create created a lot of problems um for occupying more and more dis space uh with walls but another problem uh was about the endless shutdown um you see when the post when you shut down postgress not in immediate but any other mode it will wait for clean shut down and clean shut down means that all consumers reported back to their W senders that they received changes up until the checkpoint shutdown checkpoint position and because of the oversight I mentioned it wasn't happening and pogress was just endlessly shutting down uh one of our engineers at zando f this in the 42.7 version of PG jtbc and uh also many a couple of years ago it was also fixed by another uh zando uh engineer in psychop to why it is what was painful for us um actually because bgj NBC is used by our favorite CDC tool called deum so it hit us very much um yeah this pretty much it as a conclusion um I would like to just sum all the set things up um first use this nice technology cautiously be aware of the hazards and uh make sure you understand how it works uh also monitor the slot uh State um if it's active it's if advancing it is advancing and uh also check your free dis space you probably all to do this but remember about this special directory for spill files uh always delete and used slots not leave them behind um another thing uh I've not mentioned but also very important know your workload and sometimes it can be better to use the old good solution based on triggers for example pjq and um it may happen that it will create less problems for you it will be easier to maintain and lastly just make sure that you use the proper uh protocol implementation remember about the story about pjj DBC so that's pretty much it uh thank you for your attention and I will be happy to answer your questions