Nikolay takes Michael through the different replication options for Postgres — looking at different use cases, the options for each, and some things to be careful with.
Show Notes
Here are links to a few things we mentioned:
- Replication (docs)
- 9.0 release notes (including built-in replication)
- Slony
- Londiste
- pg_is_in_recovery (pgPedia page)
- Patroni
- Multiple Databases with Active Record
- hot_standby_feedback (docs)
- max_standby_archive_delay or max_standby_streaming_delay (for WAL-shipping and WAL-streaming respectively)
- synchronous_commit
- Synchronous replicas
- Database Lab Engine
- Neon Branching
- Past, Present, and Future of Logical Replication (Postgres TV with Amit Kapila)
- Failover of logical replication slots in Patroni (Postgres TV with Alexander Kukushkin)
- PostgreSQL Subtransactions Considered Harmful (blog post by Nikolay)
- Why we spent the last month eliminating PostgreSQL subtransactions (blog post by GitLab)
- Fivetran
------------------------
What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, or by commenting on our Google doc.
If you would like to share this episode, here's a good link (and thank you!)
Postgres FM is brought to you by:
- Nikolay Samokhvalov, founder of Postgres.ai
- Michael Christofides, founder of pgMustard
With special thanks to:
- Jessie Draws for the amazing artwork
Creators & Guests
What is Postgres FM?
A weekly podcast about all things PostgreSQL
Michael: Hello, and welcome to Postgres fm, a weekly show about all things PostGresQ.
I'm Michael, founder of PG Mustard, and this is my co-host Nikolai, founder of Postgres ai.
Hey, Nikolai, what are we talking about today?
Nikolay: This week is my choice and I choose replication, but this is just one word.
But huge, huge topic.
Let's try to talk about replication.
Only trying to focus on areas which are interesting to backend developers and architects.
So for, for people who create something, build something, and so on.
Michael: Yeah, exactly.
Let's make sure.
it'd be cool to cover the different options people have and the problems they're generally trying to solve when they're looking into this.
We also had a couple of requests from listeners that cover this, so we've got.
The differences between physical and logical.
And also challenges on different managed services, especially rds.
So that would be cool.
And options like WAL shipping WAL streaming, if that came in via
Nikolay: sub, two subtypes of physical.
Right, right.
Well, let's start from the problem like why at all do we need replication?
I see.
areas where replication is needed and two of them are major.
And third is kind of controversial so first question.
Where replication might be needed is high mult What, for example, in cloud our VM can be done anytime, so we don't want to have.
Right.
So we, we want to have zero, almost zero downtime or downtime.
Less than one minute, always.
Michael: Yeah.
Or even if we self-host the machine can, like things can go wrong with one.
For it to fail over to a, a secondary is super helpful.
Sorry, Stan.
I see primary and standby as the, as the language in the ducks.
Nikolay: You have some service in data center in this case.
Perfect opportunity to go a different route and go blame your provider of hardware and so on.
So like in, in the case of clouds this is less possible.
So, usually, and, and issues happen more often actually.
Naturally you can have very good hardware.
So each server is running years and cloud is also possible, but less often.
And this is, this is the first option anyway.
Second is I have I CPU or like, I, I, I have almost saturated disc or everything is out of cash or something like I, I have resources which are close to saturation.
So one machine is not enough, and in my opinion, this second option in clouds, they, it's also definitely
possible, but you in clouds, it's easier to just increase resources switching to more powerful machine, right?
Which is , like less convenient to do out of clouds.
So this, this reason.
Triggers more, like maybe sooner, not if you're not, not, not on cloud.
But anyway, in cloud it also triggers and at some point you want to offload reads to, different server and POS allows it.
And the third option is using replication in addition or, instead of backups.
And usually it's a bad idea.
First of all, if you.
, for example, you'll lose, like if you lose whole one node, it, it's, it's about aha.
It's not about disaster recovery and backups.
But if, for example, someone deleted something, a replication immediately delivered this propagated this change to all nodes, so That's why we also always say like, replication is not for backups.
But in pogo for physical replication, there is an option to have a special replica, which will be delayed by several hours.
For example, like four hours.
And if someone deleted something, you can use that replica and actually promoted, not promote replay additional walls until the point right before deli.
It.
Right.
And if you know this, this time, because it's sometimes it's not easy to find, and then you can restore, manually, restore the data, which was deleted.
It's called delayed replica approach.
But I like it, not so much because it's, it's better to use branching or like for example Database Lab Engine.
and to have a specialized replica with snapshots every, like, for example, two or four hours or one hour.
And we have a recipe to, to do the same point in time recovery starting from some snapshot.
In this case, it's very fast but always, there is no option to restore from backups.
You should have backups with four point in time recovery.
We've con continuously Archived walls, so you can restore from there.
But if your database is, for example, one terabyte, you should be prepared to spend roughly one hour.
If it's 10 terabyte, 10 hours roughly, sometimes five.
so back to the main.
So we will focus on two main, use cases.
We want to offload some work.
To different server or we want also to achieve very low downtime if something bad happens with some node.
And of course obviously interesting.
Historically in pogs there was some idea this should be implemented outside of.
replication, I mean, It only, as I remember, in 9.0 replication was implemented in core physical replication.
Am
Michael: Oh really?
That recently?
Nikolay: Am I right about nine zero or it was eight something?
I, I, we should check, but it was not long ago.
Like in, in, in the middle of, like in, for example, in 2000.
we didn't have replication and still there are ongoing discussions like we, we shouldn't have it in the engine.
That should be outside.
And there was a system called sloany, which implemented logical replication actually based on triggers.
And it was very difficult to, like you, you, you need to ma to allocate a single hole.
DBA to maintain it.
So it was not easy to maintain.
And then sky Skype implemented long also based on triggers, slightly better in terms of maintenance.
and the good about those systems was, was that, You can add additional logic easily because you can specify
which tables you want to take and you can actually make transformations on the fly, like a lot of flexibility.
But of course this is based on triggers.
There is overhead you write to a table and trigger rights to additional table, right?
Like so, so, so to then consumers propagate changes.
That additional table.
So kind of significant overhead, but you can replicate from one major pro version to another major pro version.
Great.
But nobody guarantees that subscribers or recipient node will have the same data.
Like like there, there are many risk.
When you use the system.
So it's, it's not as reliable.
And then based on wall, wall existed, write a headlock.
So bogus writes any change.
First to write a headlock and then to, and, and synchronize it to with disks, right?
So it's it's guaranteed that right headlock has all physical instructions of all changes.
So based on the.
The idea is wall is is is used when we replay POCUS after crash, replace changes since LA latest last checkpoint.
And the idea was let's take this mechanism and have another note which will be always replaying these walls and it'll.
Constantly replaying them and that's it.
It, it won't be allowed for the snow to ride.
Itself always to just to replay.
So like constantly being in recovery mode.
That's why function to check if it's primary note or replica note, standby note, it's called PG is in recovery.
It's very confusing like each standby note each.
Replica, it's like kind of in recovery constantly, but it's, there is additional like recovery process for st by nose.
After crash, it also can crash, right?
So there is recovery and then we, like, there are two types of recovery state.
I, I would say like normal and not normal.
. So,
Michael: It's a really good point though, because I think some people come to this, haven't had experience of other databases and
wonder if there is a way of having multiple nodes that they can write to in Postgres, and that's not an option in Vanilla Postgres.
Whereas there's some kind of Postgres compatible database that do allow that.
We live in a world of you have one primary that you can write to and then you can have multiple replicas from that that you can read from as well.
Nikolay: Right.
For physical replication, physical application is based on the wall and it's just we are in constant in recovery, but accepting connections.
Okay, we have physical standby.
What we need to understand that it's everything.
Unfortunately, pog gras wall, it's like it's single thing for.
Postal server, so-called cluster and postal documentation.
So we cannot take only one database, unfortunately, although such projects exist, exist, and maybe it'll be implemented at some point.
Other database systems supported, for example, SQL Server.
So what we have here, we have node, which is constantly replaying walls.
It means that everything is there.
Everything all tables, even blo.
, which is good actually sometimes.
And physical.
This is called, called physical application.
And you mentioned in request there are two subtypes of physical application based on wall shipping and historically it was implemented first and second streaming.
Why We have two.
Because they're slightly different.
Wall shipping is more like, I would say it's more reliable.
And it also, you can ship walls from archive, from object store like s3, s3, and even primary won't know about you.
It's convenient.
You can have a lot of replicas like that.
But leg can be significant like some seconds already.
Michael: Okay.
Interesting.
Nikolay: Yeah.
And uh, all files are 16 max by default.
As I remember, RDS increased them to 64, if I'm not mistaken, but to get that file to store it like it takes some time.
So that's why streaming replication was additionally implemented to, in an effort to minimize the leg and also to get control, better control replication.
application slot is just the idea that it should be visible that we have a standby notes and we should be able to understand what are the leg there.
And you can see even detailed leg there.
So when you design some system, you can understand, okay, we will have standbys.
They will be asynchronous by nature.
So legs are possible and we can also see from the sitting on the primary what are the legs including phases of the leg,
like in transfer or like flashing to disc or replaying, like where like how like is we can zoom into like and see it on.
Under like closer.
So, so, standby notes are asynchronous, but it's possible to make them synchronous as well with obvious trade off.
If we say we need synchronous behavior, then we need to, our transaction at commit time will, will need
to wait until the, this transaction confirmed to be written, committed on both notes or on three notes.
It's very expensive in terms.
Writing transactions, right?
But with f Trick, you can design it your system.
Not every transaction will behave like that.
You can be, you can have node synchronous by nature, but then say, this transaction is super important.
So I want this transaction to be, to behave as as synchronous you.
It, it is a controlled vr uh, synchronous commit, right?
So you can say, I want this to be saved on multiple servers.
This is, this is strict.
Not everyone knows, right?
So,
Michael: Yeah, I didn't know that.
That's the main, that's cool.
Do you use that?
Nikolay: Uh, No, I actually, I, I see a lot of companies and people who, who u use synchronous standby notes.
And also there is an option to say, okay, I have ma many standby notes.
I, I want at least one of them to, to receive the.
of the change.
It's called quo commit.
You say how many nodes should receive it out of all existing nodes.
It's also possible, like there are many things that which are now possible for physical application, but somehow, we live with a synchronous, nodes and leg are very small.
We control them usually using slots and later newest post versions have protection.
There is like, I remember my ops people told me they are afraid of.
Slots because if some node is down, slot is accumulating a lot, big leg, and then you're out of disc on the primary.
Now we have protection.
I don't remember the parameter, but you can say not more than this number of gigabytes.
Then this node will be considered as completely lost and slot will be.
Not working anymore, but you are, you're safe, you're free space on the primary.
So like, it's, it's quite powerful.
But for if, if I'm a backend developer, I need to understand that nature is by default is asynchronous.
If I want synchronous, I won't be very careful.
I want to understand all network evidences because network is involved now.
Right?
And of course, if I use standbys only for a shape, high avail.
, it matters less.
And I of course should use Petro or something.
Petro is the most popular.
Autofill other, by the way, same idea, which should not be be in core.
That's why it's outside here.
Like in replication.
The idea, let's have it in core one for autofill, other not yet.
It's still outside my opinion, everything should be in court, but there are many things obviously that prevented from happening.
So if you.
You don't put load there, you have a lot of money.
Why not?
Then you don't deal with, like, you just need to control the leg in terms of the health of this node, right?
And primary is down, and leg is known to be small.
Okay?
Data loss is small.
If it exceeds, for example, in Petro, we control it.
We can set.
Allowed leg for failover event.
if it exceeds, if, if Patran knows that the leg is too high, failover won't happen because we don't want data loss.
So we can prioritize.
We, we have trade off here, what to do, failover and.
Have lower downtime or we want to save all our, our data.
We don't have, we don't want data loss, so we probably have, it's better to have downtime, but try to fix primary and save data.
There is, there is trade off here
Michael: If, if people want to like read up more about this kind of thing.
I've seen people using the, like if your, if your boss is demanding zero data loss and instant failover the,
I've seen the, the letters, the acronyms, R p o and R T O and people have written a lot of useful guides
Nikolay: Well, R P R two is good to understand, but it's more like ops terms,
Michael: Yeah, exactly.
Nikolay: How to define as slo sla, R P O R T O.
Yeah, returning to these subtypes.
Now we have wall shipping and we have streaming.
Of course, streaming should be preferred if you want smaller leg on your notes, and especially if you want to offload.
you, you will want to upload that also to reduce load.
Or very often it's CPU load.
Sometimes this scale you want to upload it to replicas, standby servers you probably would, would prefer streaming and with
replications lost for better observability, but, the trick everyone implements, everyone who deals with such system is okay.
We have, sometimes we have leg few seconds.
What if, and for example, we, we have a page or mobile app, we, and user created something and then immediately tries to search, trying to check the result.
If user is very fast and our standby node has some.
User might see in like, no new items in the results set.
Where is my, where is my post or comment or something?
In this case every, every experience backend developer implements the pattern, sticky reads.
I, I say, I, I said, I named it like this sticker.
Its so you say, okay.
In my session or this user or something, you need to identify somehow your user, if you have sessions it's session.
If you have like rest api, no, no context on server.
So maybe you need somehow to identify like, like customers, user id.
So you say, okay, some recent data change happened and we mark this user as.
Recently changed database.
some flag in cash.
In some cash, like ma'am, cash radius, anything.
And in this case you need to teach your backend to work only with primary for some period of time.
stick old.
res to the primary.
that's why I call it sticky, sticky reeds or sticky salads.
And the funny thing that couple of years ago, rails, Rubian rails, they implemented it so they, it's, it's there and you just can use it.
That's it.
It's, I think every framework should think about it because they should think about scale scaling.
and performance issues because if you don't scale, you will be, users will be suffering in terms of performance.
Clo, the closer you to situation, for example, of cpu, the bigger problems can happen.
So
Michael: also sometimes.
Nikolay: good?
Mm-hmm.
Michael: Yeah, that sounds, that sounds cool.
I've the other thing I see people using replicas for in terms of reads are.
Kind of read only parts of the app.
So like analytic, like dashboards or their own internal analytics, that kind of thing.
What, what risks are there?
There?
Nikolay: POCUS doesn't allow to do it.
Without consequences, you have two options and both are bad.
You just need to choose which is less bad in your case.
First option, you have regular standby which means host standby feedback is off.
Host standby feedback.
Signals to the primary, the state of our standby.
It's, it's controlled on standby.
On each standby you can define it and it says, okay, if it's off, I don't care.
Like primary do, doesn't, shouldn't know about where I am.
And, but this means that any transaction that lasts longer and all analytical queries tend to last longer.
Right?
They will pause replica.
and there is, there are parameters for streaming and for wall shipping separately, which define maximum duration because af after which we can say, okay, it's, it's enough, and we cancel.
Transaction in favor to replay.
New changes from wall uh, streaming or wall shipped, doesn't matter.
This is not good because it makes your note like single user because other users, like for example, one user is using, okay, I'm using it fir first.
Danger is that at some point transaction can, like my read, only cell can be canceled.
It's one can, but I can adjust this parameter and, and allow like couple of apps.
But other users coming to this, Observe.
Huge lag.
That's why I, I say like, virtually this is a single user note, like, you're alone there, like, others will suffer.
So it's, it's not convenient.
That's why in this case there is another option, which is also bad.
It's called constant by feedback being on, we are turning on and standby.
Reports to the primary, about Xmen Horizon.
Well, like we have transaction IDs and we have many transactions happening at the same time, like constantly because it's multiuser, multi multiuser system, right?
And there is a vacuum, which, which should clean that two poles, which already dead for all transactions.
But how to define for which, like all to define all here, we need to understand which is the oldest transaction still ongoing.
And this is called like Xin Horizon.
Xmen is because I remember you can select Xin from a table.
There is always such column there.
Our listeners who listened first episodes, not I, I I, I like this like hidden columns, Sy Xim x, Maxs i d.
So Xim Xmen is like birthdate.
In terms of transaction id, you can uh, see when each tuple physical row version was created in which transaction.
So we have multiple transactions and the oldest one is our Xmen Horizon, and this is reported to primary.
So primary auto working, all working workers on primary respect.
And don't delete the tules because they are considered still needed on some replicas.
And imagine if you do it some, you run some query on some node with hot, hot turned on for many hours.
It'll lead to auto vacuum.
Unable to delete the tules in all tables on the printer.
All tables.
It's like cluster white problem, So we will eventually.
Later, transaction finished, vacuum deleted, and we have gaps.
Gaps is blo.
Blood should be distinguished from dead tuple, but tuple accumulation of large volumes of lead to blo, and that's not good affecting both table and indexes health.
So constant back feedback on is bad, off is bad.
Choose what you prefer.
So I prefer my idea.
Two other options.
Both are good, but both are difficult to implement.
First is don't use Postgres for analytical queries.
use logical decoding, logical replication to other systems like click House or Snowflake.
Anything.
This is what second, but of course it means like, it's easy to say, but, but.
It, it's a lot of new expertise required and a lot of main maintenance efforts.
Of course, it's easier to create physical standby and work from there.
Second option is to have again, branching or database lab engine and provisions uh, clone or from branch and new one just released branching last week.
By the way, I'm super excited.
This should be.
Branching is super powerful and it's, it's, I'm most excited about capabilities for development environments for like non-production environments, but for
production environments, it's also good, like you have specialized replika, and when you provision branched or cloned pos it doesn't report to a primary at all.
You can do, like you, you have frozen state.
Of course you, you're not receiving fresh changes, but you can do whatever you want.
Like you are already detached from, from anyone.
So you cannot be a problem for others, it's good.
Well, of, of course you can have a logical replication.
It doesn't report about Xmen Horizon to the primary.
But there are, it reports about It learns about system catalog changes.
So if some DDL is happening, there will be a problem, which is problem in general in logical.
So, easy options above bad difficult options are above good , right?
But you need the additional.
You know, you know, in additional tools for it.
So that's, that probably covers physical basically just use streaming.
It's good, reliable.
You use wall ship replicas for standby notes for cases when you have, like, for example, you want to provision and keep it up to date, some environment where you do experiments, for example.
Like it can, it can be based on wall shipping from archive.
I prefer to not to touch the primary less often, and that's it.
So physical is reliable.
Battle proven everywhere.
It's working.
It's good.
Michael: How about logical?
Nikolay: Yeah, logical is good also, but it has a lot of limitations.
First of all, in logical can be.
In old-fashioned way, longest is still a tool to, to consider based on triggers or it can be used.
What we have logical decoding and replication pauses.
First of all like for developers, you should understand that logical is based on basically on on wall as well.
On wall is, is recording tuple changes.
This is new to pull, this is new whole page that changed and so on.
Very low level changes and logical is based on that.
So it's not purely logical, it's like logical deriv from physical, right?
And this leads to many, many issues, actually.
So many issues.
But biggest.
issues with logical that it, it lacks some things, but which currently under development, for example, DDL or replication of
sequences, there are, there is ongoing work, a lot of work and hackers which improves it and I mean, solves these problems.
So I'm quite positive that POG 16 will or 17 will be much better in terms of logical and recent Postgres versions.
14 and 15.
They also have improvements.
We had on POG tv we had guest Tamid Cap who described logical replication improvements, but logical in general is needed when your destination should differ from the source.
In logical replication terminology, they call the publisher and the s.
. like primary standby terms converted to publisher subscriber here.
So for example, you want different measure version, right?
Michael: Yeah, so if we're doing an upgrade,
Nikolay: or you want not hold database for example.
You want only a couple of tables or it's different database system at all.
Like click house.
It's also possible based on logical decoding, logical deco idea is, is quite easy.
Like let's Have logical application.
And it'll like stream our changes wall records and we will consume wall records and def decide what to do with them, either ignore or how somehow process and allow to consume.
So there are plug-ins so-called, which define this logic on the publisher.
And the problem, one of the biggest problems for logical for me, but it looks like practically it's not that big.
So it's.
Passing everything.
So like you cannot, it's, it's both performance and scalability problem.
It can be, it can lead to legs if you have complex records involved, like a lot, lot of columns involved and so on.
And also if you add additional stream and you want to say, okay, these tables will be there and those tables will be here like, and conquer.
You won conquer because consumption of CPU from of wall sender on, on the publisher side will, will be this.
Like, if it's, it was a hundred percent CPU of one core, now you will have two cores, both consuming a hundred percent.
It's quite the problem, but I, I see that it's, it hits at only at very under, very heavy.
Not like you, you should go before you'll probably hit the problem on the recipient side.
On the subscriber, for example, if you miss some index and want to replay changes, right?
And also splitting to multiple streams makes more sense here because definitely you can.
We have multiple wall receivers and beckons replaying changes on the recipient side, on the, on the subscriber.
So I also, for those who design and want to think about how to use logical, I wanted to highlight about one interesting point.
We can distinguish two big areas of use cases.
First is when we need all tables in po.
It can be different major version of S but still we need all of them.
And second everything else, like we need only partially our database or it's not positives at all.
So in the first case, I forgot to mention how we need to start with logical.
First, we need to take a copy of database, maybe partially.
And then second phase we stream changes.
It's called CDC change data.
. So there are two phases, initialization and already working state.
And the great news, which is not covered in documentation, that you can switch from physical to logical very easily.
So you just have physical standby and then you create replication slot, logical replication slot on the primary.
It'll provide you LSN while it post.
So this is a po a point.
it was created.
And we know that it's like the state is at this Ls, n ls, N is, it's like log sequence number.
It's the sequentially increasing number in, in, in the wall.
And then you can say recovery target LSN to reach the same LSN on the standby.
Right?
And then you just switch to logical from physical replication.
So it'll, it'll take a few seconds someday.
So you can have 10 terabyte database and traditional logical in the couple, like from physical, physical.
It is easy, like it's already very well known how to do it, but if you want.
Whole database.
You can do this trick.
And super, super cool to, to provision logical.
And this opens the path to various experiments.
For example, if you want to use logical for various migrations to different positive measure version or,
Michael: Yeah,
Nikolay: or something like fight with blood, I don't know, like change personal system, something like that.
Michael: that sounds super interesting for major version upgrades.
So physical replication.
Then could we do a, an upgrade?
Like then we, can we do the, the PG upgrade and then turn on l logical replication to catch up?
Nikolay: Exactly.
So you do this trick and from physical, you convert physical node to logical node and then you can stop it, upgrade it.
During this time, the primary will accumulate changes in the slot.
So once you are up again you will consume and catch up.
That's it.
Yeah.
Super . So I don't know why people don't use it often.
It's like, it's, I I would say this is secret of how it's called Secret of pollution.
Right?
Like very well known secret actually.
Like many people know it, but it's somehow keeping, it's not in the com.
Maybe it should be covered in documentation actually, but documentation is in general likes how to.
it's, it's very occasional to see, to, to see how to parts in documentation current form.
So that being said, recommendation is awesome.
Definitely.
So, and if you need part of it, of course it's, it won't work.
You can't do it.
For example, I won't half a database, I do this trick and then I will drop half of the can work.
But in other cases,
Michael: That's awesome.
I was wondering about two last things we, depending on which one you wanted to cover, either go into a
bit more detail on any of the issues on the, the lodge gratification side or potentially more interesting.
The, one of the questions included any specifics for managed service providers?
So like any limitations to be aware of in the popular ones like rds, Google
Nikolay: There are limitations everywhere, of course.
So how many nodes you can have.
For example, many projects prefer having like 10 or 15 physical standbys and upload a lot of reads there.
It's constant work for backend developers to find more.
Queries that can se uh, transactions that can be offloaded.
But in general, as I remember on rgs originally they distinguished two types of 10 buy and first one.
Was regular pogs standby, and it, it was supported reads.
And second was a checkbox, multi z, multi mult zone.
It was a hidden standby based, not on POGS replication.
It was before recent changes.
And it was not visible.
You couldn't use it.
So it's like, just for ha.
Now as I know, they support.
, they changed it I, I haven't looked recently, but I, I saw the news that now for this HR replica red queries are also supported, which is good.
So, but like in ideal world, of course we should have like we should have a share replica on other subject.
But some people think we should have at least three.
and don't care about which like symmetric, symmetric schema, when all replicas can be used for failover and all, all replicas can receive reads.
I actually prefer this schema symmetric because if you allocate specific note for ha well, Cassius problem.
You need to warm up, right?
For, for read queries, it's warm up automatically if you, if it's receiving read.
So it's like kind of things to think about.
Like I prefer this approach.
Patoni actually is good for it as well.
By the way.
In Patoni there is a problem with logical slots.
If a other physical follower happened, you don't have slots.
Michael: Interesting.
Nikolay: bad.
And also logical, like unlike physical, physical can be cascaded.
You can create interesting architectures, but for logical, it's always from primary, unfortunately for, from physical primary.
And there are many, there, there are many discussions how to enable logical replication from, uh, physical standbys.
It would be great.
, but like risks are mo shifted to, to standby, it's much better.
But unfortunately it's not yet implemented.
So if you have physical feller, you'll lose slots but there is some recipe and petro implemented and there is a talk from Kokosh.
Which is Mr.
Patro?
You, can you, yeah, you can, you can listen to, there are many interesting details there.
And actually on positive tv, Alexandro also presented the stock many interesting details.
So it's like how to avoid full initialization for logical replicas.
But for upgrades, we don't care about it because our replicas should live like many, some hours or a couple of days.
Michael: Yeah,
Nikolay: so,
Michael: just tempera really.
Nikolay: so we don't care about it.
So there are many different use cases, a lot of them.
Michael: Awesome.
Was there anything else you wanted to cover?
Nikolay: there are many things to cover.
For example, physical standard behavior may be very different from.
Primary.
For example, if you have sub transactions, you might want to check my article about it.
And the disaster.
GitLab head, they, they also blog post about it.
Very good article and it was quite difficult to troubleshoot so.
the idea that we have the same everything.
Well, yes, we have the same everything, but there are small details that can differ and if you have sub transactions and you should be prepared for growth, definitely worth checking that post.
Cause physical can be different.
can have different behavior.
Michael: I'll definitely include it.
Nikolay: So in general, summary is physical replication is very, very reliable and Postgres logical has issues, but it's people also use it.
There are also derived products like AWS has dms gcp.
Google also provides something in this area to migrate pogo to pogo, different versions or from different database systems to pos.
Also usually cloud providers provide logical replication connection, but no physical it's worth keeping in mind.
So you can migrate out of them.
Only using logical.
So logical is, it should be an arsenal definitely for various kinds of tasks.
And uh, uh, um, there are, there are products like I would mention a couple of them click former uh, at.
Maybe I'm pronouncing it wrong.
They have issues with duplicates.
They implement I communicated with them many times.
They just don't understand and don't bother, don't understand me because they, they create slot at SQL level.
So they don't, they cannot synchronize it properly.
So if a recipient.
Database like Snowflake don't have unique keys.
You will have duplicates if you need to when switching from initialization to CDC part, or there's also a newer company called Five Train.
This is quite good.
It's commercial company also works with on top of logical decoding and supports a lot of things, and it's very like SA cloud, cloud oriented.
But, and, and I think.
Pricing is also good, but it's sort of considering if you want to save time.
Michael: Yep.
And last, last thing on the car providers is that they do often have.
That just like a checkbook where you can turn on high mult Right?
So it's you don't need the physical replication for that if, if you'd use their own service, but normally costs double just as if you were paying for a replica, right?
Nikolay: Right, right, right.
But as, as I said, this is about rds, the checkbox doubled for price for primary, and you couldn't use for reads, but they changed it recently.
Worth checking.
The, the, there are recent changes.
So you can, you can have the checkbox and still use every day this node for, so
Michael: Cloud SQL is similar, I think in that you, you can just turn it on, but I'm not sure about the being able to use it for reads.
Nikolay: Right.
Okay, good.
Michael: Nice one.
Thank you so much, Nicola.
That was awesome.
Thanks for the suggestions for people that sent those in.
And thanks everyone for listening.
Nikolay: Thank you, Michael.
See you next time.
Bye.
Michael: See you.
Bye.