 Is the video ready all good? It's nine o'clock. I guess we can get started Thank you all for getting up relatively early this is The sort of part two of yesterday's talk and the way I think about it so this is the second quadrant workshop as it's labeled logical application deep dive so the way this works here is that all the major sponsors of this conference get like a one slot to talk to about whatever they want and So you might have you know, yes yesterday morning Google had a had a talk like that and some of the other fine companies have had one so We've we figured just because there's so much interest in Logical application in Postgres and this is kind of our thing. We'll just kind of fill this Our with information about that and you know just kind of do some name dropping of our own so I have a lot of material here and You know depending on how it goes you might not cover everything and we'll see exactly where your questions are so please you know also, you know Ask questions throughout Because we might not really have too much time at the end, but I'm also available and my colleagues throughout the day at our booth Where is it upstairs from here, right? and You know, I also have a couple flyers here as as well as we have some upstairs If you want to just grab some more information So about us second quadrant has been around the postgres community for at least 10 years 15 years depending on how you might count it. I've only actually joined the company about a year ago but I've also been in Contributed to postgres for 7 18 years now, okay so Possibly longer than the company's been around and we have about 60 staff now in 22 countries. I think was the latest and we Do our main thing that we do for business-wise is 24 7 so production support for postgres and then consulting and other services and we are usually in most recent postgres releases one of the leading code contributors if you you know see you who the Patch authors have been paid or employed by again along with along with other fine companies who also present at this conference So this is the way I kind of think about in if you put it into context of how a replication in postgres came About this is kind of how I see the history and I got involved around 1999 just sort of at the end of the Area when postgres was quite broken in a way so Just like I The way I think about it, you know in 9.0. We got physical replication and it kind of took us You know a number of years to to fine-tune it and add additional features around it so it's it's quite nicely usable and and Has all the functionality that people generally ask of it so that took you know five six years and Just as I was mentioning yesterday at the panel and and also at my talk yesterday I could very well see logical application in postgres is taking us another You know five years perhaps or so to fine-tune and add additional functionality and then some of those things I mentioned briefly yesterday and I'll go into some of these details today so If if you've been around for a while you might have seen this kind of thing You know obviously people have been wanting to replicate databases since the very beginning and In the early days the only way this was really possible is to use a trigger based system And so this is kind of a thing you might come up with quite quickly and easily It's like I know I have a table and I want to keep a keep a log of what's going on So I'm going to put a trigger on the table and just write it all the changes to a log table And then I have some kind of a daemon or service To then take that information and stick it in some other host right so that you could easily sort of Cobble that together yourself quite quickly and then you know then all the edge cases start appearing and you have to work all That out, but they have been a number of systems like that as you some of you might well know And they surfed you know users quite well many years and so Slownie well our surf which was actually in the Postgres source tree for a while was sort of the very basic and simple example of that then Slownie was the first sort of real like production ready product that basically worked like that and In Laundice in Bucardo work in the same Principle I did had triggers to a table record those changes in some other table and then have a Daemon or a couple of daemons usually that you have to install that move that data around and stick it in the other table and that works So and they're still you know used for Some use cases today, especially you know Myself and my colleagues we keep using on this in Bucardo, especially for database upgrades. That's still a good use case So but we quickly realize what the problems with those things were right? Well, the one thing is it's extra software Which you know, it's generally fine, but it's people Generally like something building But the big problems are if as soon as you want to change tables your trigger is potentially gonna break So it's always a pain in these systems to to manage any kind of table changes and then There's no really good answer to that depending on how those triggers are implemented specifically That's what it's really hard and then because of that There's also locking issues in terms of what if you need to change something you need to lock things down in different nodes And it's really hard to get that working and the big one there is though that because you Because you write to the table and the trigger has to record those changes somewhere else and then usually in another table You have issues with managing that other table I do because first of all you have the twice the right volume immediately as everything you write you have to write twice, and that's Really big problem if you already have a database. That's really stressing the limits of the hardware and then usually that's the specific post-press problem that that that lock table has to be Emptied fairly quickly right you kind of append your changes, but you want to apply them and Remove the change records from that table. So you kind of have a moving window in that table Usually it's really hard to manage the space for that because of post-press storage management and the vacuum issues and all that kind of stuff So the people who wrote these systems They have tried some really clever things and to manage that but it's it's just generally a pain and Generally the systems work just fine if nothing happens, but if stuff breaks Or finding out if stuff is broken. That's it was generally difficult. So we it was just you know, you had to install extra stuff Managing schema changes was very difficult. You had to double write volume and it just generally managing was difficult So we wanted new stuff So next idea was well Postgres need built-in replication. So first thing we set out was Physical application. So this is kind of the timeline and most of this is known to you but this is kind of just the summary of how this came about so In a way, I always think about is it came about accidentally in a way like initially We just had a white a headlock as an internal feature initially. We had no right a headlock. We just had to F sync everything directly to this that was really slow And then you said like well, let's have a right a headlock and just f sync that one That was good and that for a long time. That was just an internal feature. You couldn't do anything with And then eight we kind of exposed that as a sort of a backup feature in a way So that you could take the right a log and store it somewhere else and then play it back So you had to point time recovery feature and around 8.3. Someone said well if I can store the archive somewhere and Recover I can also recover it live somewhere else Cobble that together with some scripts. You have replication and that's the PG stand people put that together themselves The scripts PG standby was just basically a packaged solution for that. I know that's when a lot of people kind of built Replication without of the box tools and then in 9.0. That was sort of the big win when we had Two totally separate features that came together hot standby So you can read from the standby and stream your application So you don't have to move these archive files around you just connect directly hook two pieces together and it's great You had replication PG standby well Was it also known as lock should lock to me as you guys you called that way But I don't think that's like an official term that we really use so there could be other ways other Solutions that might use that term too. So and we don't have to talk about all the other ones here So just you know again as I mentioned it took a while until actually, you know all the features that you want were implemented So every release there's a little bit more and people are still working on it But it's you know, it's pretty good now. I think you can do a lot of things with it So this is how it works, right? This is the first first The first implementation so you just have these take these wall files use archive command copy them somewhere else and I have a whole sort of sub talk that I sometimes give somewhere else where I tell you that almost all archive commands are wrong So that's why this is better You just connect have the receiving end connect directly say like I want to fetch These kind of wall this wall range, please give it to me and then ships it over and it's great And then you you know, this is another one of those things that came about that If you just do this the db2 in this case just really literally connects to db1 It says like I want you know start streaming from position 85 That's where I left off and then it ships and then maybe db2 gets the disconnected and Comes back half an hour later say okay. I left off at 93. So please continue at 93 and then db1 says well I'm sorry 93 was already removed. You had a luck Because the wall actually gets keeps getting recycled And so one important feature that kind of got overlooked a little bit what in 9.4 was something called a replication slot We're which you basically just say register yourself I'd db2 registers itself with a replication slot at db1. So, you know, I'm here. I'm interested in this so just Keep all the stuff that I need around Because in the before that system db1 didn't really know that there was a replication slave There all it knew was there's Some guy who connects once in a while and fetch system wall bytes But it didn't really have that context of like this is actually a replication system What we're doing. So with replication slots all you say is like, you know, I'm this guy give it a name and Then the the provider or the master knows there's someone who still needs that information and hangs on to it until it's actually fetched So, you know, this is a physical application works based backup give a slot write a recovery.com right Again with the reason it's called recovery.com initially just was a backup recovery feature And then it's just like any we can do application with this just just throw a couple more things in it, right? This is how postcards works in a lot of times you just take whatever you have and tweak it a little bit until you it does the thing And so yeah, as I mentioned also Yesterday physical application is is it's not going to go away It's it's great. If you just want to have a Complete backup or stand by that's not going to go away And it's fairly easy compared to logical application that we're proposing It's a fairly easy to manage supports all DDL. Everything's just there You don't have to say like make sure this also gets to everything. It's just there And there's also the drawback because he can only do everything And so These all kind of use cases that when people start thinking like yeah, we should you know, maybe we need something That's a little bit more fine grained You know when I want to do partial application Which we can't do with physical we can't do multi master because the way it works It just that doesn't make sense all it does is ship bits and bytes around you can't interpret them Another issue which is kind of specific to postcards is you can't use temporary tables on the stand by because that would require changing in catalog and Temporary tables are kind of useful in a lot of people do, you know, write plpgs scale functions It would require temporary tables and things like that. Yes question. What is it? What about it what is multi master replication where you can write on two nodes and they replicate to each other Yeah, and another a bunch of other sort of technical issues that were caused by it Because you replicate the bits and bytes if you vac if you run a vacuum on a master it might remove things that The slave is still querying because all it does is you know the vacuum cleans up certain bits and bytes and that exact Information is copied over so then we had to add a bunch of functionality over the years to make sure the slave actually tell That's called hot stand by feedback if you've seen that that the slave tells the master like actually I'm still using this just hold on to that and we can't do Major version upgrades with this which people like to do because again the bits and bytes are the same and you can't They're different in the next major version, so you can't copy that over So all these kinds of things that you know why people wanted to start with logical application and so at Second quarter long before my time, you know people got together and they got some funding and some enthusiasm and wanted to sort that out and So this is where the Postgres BDR project came about that you might have heard And that was sort of the umbrella for a lot of these initiatives so BDR the name stands for bi-directional application and It doesn't you know the name is kind of a little bit awkward Perhaps because it doesn't have to be by in a sense of to it could also be more than two nodes And then there are a couple other variants, but that's just the way the name that kind of stuck because people like that So currently I'll go into the details of what it does, but you know that that whole initiative will start around postgres 9.2 actually and And then at sort of 9.4 got to a point where it could be used so the current postgres BDR is a Is a fork of postgres 9.4 kind of beta Plus a plug-in So that's on this website you can download that and you use it. It's open source And and at some point in the past there was also this Notion that you could use BDR as a UDR like unidirectional replication If you just you know, so BDR is patched postgres plus plug-in if you just use stock postgres plus plug-in you get Unidirectional replication so you can't you can still use that to replicate, but you can't replicate in two directions because of certain small functionality that you need to You know track those conflicts is not available in the stock postgres at that time But that is not really supported anymore because other solutions such as PG logical and now built in logical application kind of to agree with that so that's not really a Configuration that people use anymore So that's what BDR can do and this is you know a couple slide flyers up here if you want to pick one up later That's basically what We use it for You can use it for so, you know, you can have up to 48 nodes and that's not really a specific reason why it's 48 just it's kind of what we recommend and They all you know, they could be in different locations And they all talk to each other and you can write anywhere and all that data gets replicated to all the other guys and that's basically the main use case of having multiple locations So this is not really a lot of people like look at this and say oh, this would be great for ha and That's not really the case because all these nodes really have to be Talking to all these other nodes not always necessarily, but the the system is generally Not designed for just like node B disappearing for a long time. Yeah So it's not really for that But it is for if you have multiple and you're just talked to a couple people around this conference too or interested in that just you know, we have different sites around the country of the world and We want to write locally for latency and all that kind of stuff, but we eventually want to replicate all data around So that's exactly what this is for so So, you know as as my my colleagues were were working on this, you know, we they were looking at you know We have Postgres here with 9.2 9.3 era. It's like what do we need to do in order to get logical application on this and and Basically, this is sort of the list of all the features that went into this and and did all of this is kind of ended up in Postgres throughout the 9.x series and All we actually did is in postgres 10 now is kind of put a user interface on top of that, right? So I'll go through what all these things mean, right? So You know, we had that picture back here This is how a physical application works. You just have these blocks and they get shipped so when We thought We the community or they people actually did that back then It's like how are we gonna do logical? Information shipping Right, so we didn't want to do the trigger stuff. That was that was bad We You know, basically there were two options you could think of one is we are going to Write another log like a logical log And so the way postgres is implemented is like everywhere you write to disk and a fairly low level code I everywhere you write to disk you have to write to the wider head log first And that's just really the way the code works, right? It's like right till x log if success then right to wherever table file and So the the option was like well if you want to and that's just the physical description of the chain, right? If we want to have a logical description of the change, are we just gonna patch all those sites and have another log logical log Could could could have worked, but you know, that seems like a lot of work You have to find all those places and basically write duplicate code to do a logical recording of the changes as well and Then you have to write manage that whole thing separately all these options that you use to configure Replication like all these delays and and you know about standby feedback and timeouts and all these things you would have to basically have A duplicate set of those so that didn't seem very attractive. So the solution that we came up with was What is called now logical decoding? so you actually take the right a log and basically Analyze it and decode it back to a logical description so which seems kind of weird because What the database does is take it takes your logical description of what you want to do like an insert command, right? And fiddles it down into the bits and by change writes it to this divides it to the right add log and then you have this other code Which actually puts it all back together and makes a logical description of that, right? So it seems kind of weird, but it's kind of the best Thing I think we figured because so you don't have to write a second log, right? So all of this is other than the computing power to do this Which is not trivial, but other than that this is kind of Free in terms of IO, right? Because you just have that one log you do some computing Fiddled it all back together, and then you just chip logical descriptions over the wire and The way this was implemented is you can write log load your own plug-in To do the decoding right you just get a callback like a C. You add a small C plug-in. It gets a Callback like here's a music insert come here's an insert What do you want to do with it? And then you can write C code that does anything you want and For for replication solution you basically you're just going to format that in some point of a wire format You want and send it across but there are other solutions I don't know if you some of you might have seen the talk I think it was two days ago now Where people take that and ship it off to Kafka that kind of thing and then you can listen to that and do other Things for that so there's there's a couple other options you can do so But that's basically one important piece and a lot of people thought when this was published in postgres 9.4 It's like oh great. It's logical application Unfortunately, not it's logical decoding only and you know all these other things have to be also put in order to actually have a Replication solution so we talked about the replication slots already. That's a way you need to register a a consumer with a Producing site of replication so that also had to be implemented We also needed a way to If you if we don't want to have a separate software running right the way sort of slow knee and on this the work You have all these extra daemons running But if you want to have something integrated we all we want to have these daemons run integrated with the main postgres so The idea of background workers also had to be implemented So you can spin up extra processes in the background of a postgres server that just to do extra work right and one Also visible use case of that is the parallel workers. That's exactly the same infrastructure that you know when you run a parallel career You have your main session process, but then in the background it spins up additional background workers just a moment and To do the the other work, right? So it's the same The same technology. Yes, please Yeah Decoded statement identical to the answer statement. Well, it's not it doesn't really come back to be a statement, right? So you don't really know what the user typed so in that sense. There are some information laws It's just a description of the change, right? It's just like here's an update This is the key of the update and these are the new values These are the old values depending on some other details, right? But it doesn't tell you like how you typed it or whether you specified the column names or whether you put like star or returning Anything on that on it. So it's a bit of an in-between in a way, right? Yeah, it's complete for that, right? It's complete to ship the data across But some of the sort of details that the user typed in this are not necessarily there Exactly. Well, yes, if you design your protocol properly, right? You need to make sure like your engineers and that kind of stuff. That's all up to you but the it's independent of Sort of the alignment issues and that kind of stuff perhaps, right? Yeah, so background workers have also other use cases right the main use case So sort of the first two for parallel query as well as for logical application workers But there's also some other ideas that people have been playing with and some stuff that's sort of pending for Postgres 10 or 11 Perhaps, you know, it's it's pretty easy. Just you register a plug-in and say, you know I here is a process I want this process to do that and then just runs in the background and you can look at it in PG stat activity and all those kind of things that so It's kind of neat and then you can there's sort of a lot of ideas people can play with there Origin tracking is especially for BDR is kind of a weird thing But you can think of it as you need if you have This kind of setup, you know stuff can like like move around in circles So when when a starts writing and then it sends it to you know D and then D sends it to be at some point It might get back to a and then a needs to know. Well, actually, that's the thing I sent out myself So that's a general idea that you tag Transactions specifically with just a you know, a small string essentially get like a node name or something and then The the main cases that you know if a sees oh this came from a then you just drops it Or you could also if you have some kind of cascading setups or some other specific things You could say like I only want to forward things that came from me or that guy But not that guy so you can do all kinds of weird things with that So it's a very sort of low-level feature. That's a little bit weird to understand But that's just sort of the general idea. So that also had to be implemented commit timestamp is you basically With every transaction you record the timestamp that sounds pretty simple and it is But normally you don't really need that because it you know the transaction So just very low level. You don't really care what time they were written But if you want to do conflict resolution, you might be interested in that So if you have the same change coming from due to two different places You know you want to have some kind of way to resolve those conflicts in the way the built-in Logical application in postgres 10 works. It's just errors in that case Kind of we briefly talked about it yesterday if you you know if it tries to write to the table as a constrained violation It just errors and then you have to fix that yourself but With BDR and Pgeological there's more options and they do so the same kind of options that will conceivably come to postgres you know 11 and and and the future that you might want to have a policy that says Whoever came first wins whoever came last wins Or you can combine that with origin tracking, you know remote wins local wins all those kinds of things are possible Basically in combination with combining these two features, right? So because you you add to each transaction this information like where it came from and when it ran and then you can Fairly easily decide on the receiving end based on some policy of like this guy wins or that guy wins. Yes, please The clocks have to be synchronized in order for this to make sense. Yes Different sources How does it know if it comes from two different originators that are now local Yeah In that at that point you you need to define a policy of some or in either you or the the particular system you're using would have to have some kind of a policy of Who you prefer or? You know, do you go by timestamp or just you know, maybe pick a random one I don't think that's actually one of the options, but you know, that's just up to what do you configure then? What is sent by network it is The custom protocol essentially Custom protocol that contains the decoded information. Yes, the decoding happens and wasn't clear on that in the picture Is the decode in all of these solutions and everything we talked about the decoding always happens on the sending side right that's kind of the the point of You you have the wall on the sending side and then you Decoded and ship it out and then the wall gets recycled. So There is no additional. You know, that's why we only have this one right at log Which you can decode many times in different ways for different subscriptions or things like that or Replicate to multiple places. You can do physical logical application at the same time. It all comes from the same wall So there's only one IO cost to all of that, right? That's important one IO cost one storage cost So we don't have to have multiple logs write multiple logs maintain multiple logs have storage for multiple logs Yes, it's basically Mostly the CPU cost is is the critical thing in logical decoding and There's some really deep implementation details of that because when the transaction commits If you had overlapping actions, you know, if you have concurrent Things happening your database which often happens, right a transaction commit it actually kind of has to serialize all that Which could be expensive to compute So that's what it has to do. So that's kind of the was it Because then the destination side is bound to the sending side in terms of major version and then hardware and Architectural that kind of stuff. So you don't really get that independence anymore. I guess you could do that I mean, there's also a thing that Some people have cobbled together where you do physical replication from one host to the next and then decode there That doesn't really work completely well for some other weird reasons, but that's kind of what you're describing essentially, right? Logical decoding on standby. If you just google for that, then you'll see your patches being thrown around around that Yeah, so that could that could be a thing But I guess that the CPU cost cost isn't really that bad that that's really an important thing that people want to do so event triggers was another features that was mentioned. That's basically for a DDL replication, which BDR does do Like that's actually their exact code So an event trigger is a general feature on postcards where you can register trigger on an event mostly a DDL command Schema change command and then you can write a procedure that does that and you can do that for, you know Just like audit logging perhaps. That's not exactly what it's meant for but This is what it's meant for it you Trigger on DDL command and in this case so whenever the DDL command is complete You just run this procedure that is implemented as part of BDR and then it takes that you know It looks at it and ships it across the wire and then on the receiving end It gets that and can apply that So BDR generally does do DDL replication There are some Educates where it doesn't work because not everything actually is hooked into event triggers So that's sort of an ongoing thing you every time you Make a change in postgres DDL commands. You have to make sure event triggers work correctly So some some new like you users I believe users and groups and roles that kind of thing is not replicated for that So here's a cool graph, which you can't see but that kind of Describe sort of the history of all that stuff. So in you know in 9.2 the BDR effort was forked up postgres and then all these features and some of the other ones were Implemented as part of that branch in a way and then fed back into postgres I had so it reads down here event triggers background workers replica identity, which I didn't talk about it's even more low-level stuff event trigger more event triggers replication slots logical decoding more event trigger stuff logical decoding stuff More DDL event trigger stuff replication origins commit timestamps logical war messages Some extension stuff. So that's like gets to like 9.6 back there, right? So all all of those features that you might have heard of or at least you heard Me just talking about them now came from this general BDR effort and we're just then fed back into postgres so and then This again was sort of slightly before my time, which I'm which is why I'm talking about in this sort of third person day They then figured like okay, we have a pretty good with all the core features now, right? A lot of stuff was shipped in the core postgres, but we don't really have a replication thing yet So let's just take BDR and just shrink it down to the smallest possible thing and submit that to the community So we don't let's not start with you know this kind of thing Let's just do the smallest thing possible that people might like just replicate from here here And so an a annex as sort of a second project was started, which is called PG logical Which is a plug-in that just does one-to-one replication basically and That was submitted to Postgres 9.6 says here, but didn't go in because then people said we would like Something that's even more built-in and and would like you know actual like create drop that kind of stuff so PG logical was sort of rejected in that sense and continues to live on as its own project and The stuff that I talked about yesterday, which is built-in is basically the same concepts But wrapped into something that looks like an SQL feature, right? And then that was written over the past year essentially using Not not code from PG logical, but essentially the same concepts internally and a lot of stuff looks identical internally But wrapped into a little bit SQL built an SQL interface and then committed into postgres That's the history of all these things. So Let's see. I got to go with some of these skip over some of these details because we're kind of short on time But this is kind of how you set up BDR you know load a library Turn on commit track commit timestamps if you want you can configure stuff with regard to how conflicts are handled And there's some more options than that create extensions And then you create a group in the terminology is different everywhere. So in BDR, it's a group Because these are like different nodes that are kind of working together. So a group is kind of the appropriate term So you create a group start put one node into it And then all the other nodes join the group Give it node names and some connection information and then they start talking to each other and then you can write stuff So that's just a very quick setup of BDR So Contrary handling options you can do by timestamp and I explain how it does that you can log to a table Or you can hook in your own function like a PLP. Just go function with business logic If you want it just depends on Site a always wins if you want maybe quite flexible there so Yeah, so all these features were this is all everything that BDR needed roughly speaking all that stuff was committed Except global sequences, which is still an open question So that's so that's the question of you have multiple nodes. What if they all need the sequence? How do you make sure they don't use the same sequence numbers? So BDR had or still has its own sequence implementation in a way That manages that in its own special way and that was also submitted to Postgres at some point, but didn't really get in So that's still pending but Basically what we are Doing now is just giving up on global sequences for now and you know because we're so close now Everything is in Postgres except global sequences. So we're just gonna give up on global sequences and the Next BDR is going to be just a plug-in So no global sequences. It just kind of manages it itself It does this kind of like node one uses offset one million no to use offset two million that kind of user space kind of Mangling that you might also have done yourself at some point so just gonna put that on the back burner a little bit will probably come back to that but BDR 2.0 as it's gonna be called will be just a plug-in for unpatched postgres 9.6 and future so that's something that is It's kind of it's ready It's kind of in a private beta with the second quadrant customers So if you're interested in that you can kind of talk to me or others and you can you can hook you up with that Yes, please Will it be forwards compatible forward compatible postgres plugins in general is a dangerous assumption so I it will be You know, it's probably gonna be have some code tweaks essentially So I don't expect that you can use the exact code and just compiled but there will probably be small tweaks and some if-deaths around but That your general point is yes, this will be maintained For future major versions and should work more or less the same then yeah so So that's the the status of BDR. So, you know, the road comes to sort of a Milestone here where all that stuff is it can function, you know a pure laser plug-in. So again, if you're interested in that You know talk talk to one of us and we can kind of hook you up into that so meanwhile I mentioned that you know Pete we forked off or kind of spawned the PG logical project for that just as a unidirectional fairly simple plug-in to do that and That's available, you know, that's purely a plug-in for stock postgres works with 9.4 and beyond and That's out there in production for wide use and use cases. So everything that you want to use, you know built in logical application for this will do and more and And you can definitely use that now if you are on postgres 9.4 or later for especially for upgrades Perhaps or any kind of aggregation or warehousing data accumulation partial replication any of those use cases that's Useful and you have like a couple of nice pictures that some of my marketing colleagues made Upgrades centralized data collection Scaling, you know, you can use hot standby for that as well But if you just want to do maybe some tables or anything like that cascading works Or any kind of data moving around that's what the pitch logic calls for So and you know set up is again similar Wall level a couple of numbers again in postgres 10 some of these defaults are better Load the library commit timestamps if you want if you want to have Conflict resolution based on them And off you go Load an extension with cascade in this case because that's one of the things that actually came out of peach logical that All the dependent dependent extensions are loaded automatically over this case Create a node some of that terminology is uselessly different and some of it's familiar okay, the node This in this case is called the replication set in postgres 10. It's called the publication. It's the same thing And again, you can if you were at my talk yesterday, you can you know add all tables or only some tables It's the same stuff. Just this is you know as a plug-in function and The other stuff is scale commands Subscriber familiar term perhaps now and subscription All right And off you go So and this is kind of the the nice thing about this whole thing what I mentioned earlier in terms of why use it You use logically coding versus triggers and this is kind of a measurement of clients versus transaction throughput And you know, it's more like the relative thing that matters. You're not the exact numbers That you can see so if you can't quite see it all, you know, there's the blue at the top is P. Geological and the red and orange ones at the bottom are slowly in London's to both trigger based system So as the number of clients grows past What is it now? You know just two or three or four the performance there completely dies and you know, P. Geological keeps growing You know up to was it no 16 or so So that's you know, pretty pretty nice and useful result. I mentioned that was you know rejected 9.6 So we kind of started over in 10 and just rewrote that as a Built-in something so that was the new plan and I talked about the details of that late yesterday Again based based on P. Geological P. Geological has is a lot more functionality in depth so to speak in terms of conflict handling and filtering Origin tracking all those kind of things will come to core postgres over the years You know soon as we you know get back from this conference and keep going basically Whereas there are some advantages for the built-in stuff because it's more built in and we could kind of tweak the core as we Developing that so one example is that the initial tables synchronization so if you make a new replication set or subscription or You you know the initial data in your tables is shipped over in a big copy and Postgres 10 can actually do that in parallel for for all these tables right we talked about that yesterday if you if You can configure how many tables sync workers you want to have per subscription So you can copy tables in parallel and there's magic with snapchat management and that kind of stuff that it all Sinks them all up at the end. So you're exactly at the same place that is not available in Postgres 9.6. So P. Geological can't take advantage of that So in P. Geological basically all that stuff has to happen sort of in one big transaction with all the possible Disadvantages of that so that's one of those things where maybe the one has has an advantage over the other But also quite, you know conceivably as soon as postgres 10 comes out that kind of functionality will probably be added to P. Geological so, you know, they both have sort of they move along a little bit in parallel P. Geological also have sort of let's say, you know, so practical features that are maybe not the purest thing That could go into core postgres in P. Geological if you create a subscription you can tell it to sync the table structure So even though P. Geological doesn't really do DDL replication as BDR does So if you change a table that kind of stuff is not there's no event trick or so anything like that What but what P. Geological can do is When you initially create the subscription It can just make that table for you based on the current state of the source table and the way it actually does It internally just calls PG dump and PG store for you. Okay, so that's kind of a little bit of a hackish thing But convenient, right? so Yeah, so that's you know, that's the setup I went through yesterday already So it's exactly the same except we don't load the plug-in anymore and these kind of commands. I already showed yesterday, right? Great publication for the great subscription. So all right. We're coming kind of to the end. So You know again a lot of things to work on DDL handling obviously, you know, ideally we want to get to the point where all everything happens automatically, right? Conflict handling There's a little, you know, BDR as well as PG logical show what's possible And if you really like interested and you have specific question We're probably not gonna have a lot of time for questions But you can maybe see me afterwards or down at our booth if you want to know exactly what kind of options they have but We talked about some of the options in terms of timestamps and origins and that kind of stuff. So Yeah, switch over failover slot that kind of stuff is what I also alluded to yesterday is currently if you have physical application you know Master standby and then you have a logical Replication pointing at the math. So you do fail over that kind of stuff that breaks now you have to kind of start over because of Certain replication slot information from the master is not moved across So it's just a small Matter of patching to get that to work and it's being looked at but it's always weird stuff I'm not sure if we'll fix it for this release So, you know a lot of work to do You know, I hope I gave you sort of an idea of what all the issues are and what all the Different components under the surface are that kind of inform those issues and these solutions and we have these you know these these pieces and Then the three sort of top-level views to all these pieces being BDR Pgeological and built-in stuff That make use of these pieces and all different in different stages of their evolution in a way so You know, there's also this thing if you want to pick it up later or we have more down ups upstairs, you know has a sort of a nice little chart of BDR hot standby long distance low in your book hard or pgeological And then these like check marks of what it all does So if you want to just see what is your use case you can go around here and see which one works for you I so over time we hope to kind of you know come up with like, you know one or two and a half solutions Maybe instead of the six we have there right now Yeah, so that this is kind of all the work we need to do to get there Yeah, so that's the end this is kind of the stuff that we at second quadrant want to pedal in terms of replications here We have a BDR Pgeological again. Everything here is open source. You know, this is just kind of our We develop sort of mostly internally We have rep manager for managing physical up replication if you want to don't do that manually and We also are main authors of postgres excel, which is a you know distributed distributed postgres with the global consistency and global, you know the distributed query that kind of stuff which At some point or another will also try to merge into postgres when we are all out of ideas with the other ones, right? So and if you're interested in that you can also talk to us or we have a couple flyers So it is exactly 9.50 now So I think we're not gonna have time for questions, but again, I'll be around I'll Probably head up head straight to our booth upstairs and then you can talk to me then All right, so thank you for coming here in the morning and enjoy the rest