PostgreSQL In-Depth Training: Aurora Part 1

so welcome to Wednesday which is Aurora day today is going to be structured again I'm each day has been a little bit different and today again it'll be structured a little bit differently from the previous two days I kind of mentioned this earlier you heard me but not everybody did that as I was preparing last night and running through the content that I I was hoping to present today I have realized that I was running through the what a large part of the content is I want to go through we have a whole bunch of content which is just sort of that we've already put together that's a deep dive into a world Postgres it's really good content and it's been a while since I've looked closely at it and when I was looking last night I was kind of thinking this is a lot for me to catch up on and so what I decide is that is that when I get to that part rather than me running through the slides and trying to sort of remember exactly what each slide was about what I'm what I'm going to do is as I'm going to play the video of Grant McCallister presenting that from reinvent last summer because basically there's only about two slides that have content that's changed so I'm gonna run through his presentation take down any questions just like you've been doing all week you can put them in the chat room and then we'll circle back to them at the end and after the presentation finishes I'll come back and just sort of add on mention the one thing that's different and then after that so I'll do I'm gonna talk quick a little bit at the beginning we'll do the video and then I'm going to actually get on the whiteboard for a little while and explain a couple of I think really important concepts in Aurora that I haven't had time to get onto slides yet but there are things that a lot of people were asking about and asking for me to cover and they're just questions that we get a lot of it okay so diving into Aurora Postgres in order to set the stage for first let's take sort of step back and take a look at at sort of the the bigger picture here and thinking about sort of RDS an RDS first of all you have RDS Postgres which has right is basically a database running with EBS storage underneath it then you've got Aurora Postgres which has the Aurora storage underneath it and these are very different things right EBS is very different from Aurora storage but one one really important kind of point is that from your clients perspective they look very similar and actually grants gonna touch on this and his talk to write there are a lot of shared there are a lot of shared components too so in addition to the fact that so to your clients like if you design an application to work on Postgres whether it's open source Postgres was already as Postgres that application will continue to work the same on Aurora Postgres that's part of the way that we've built it in addition to that client level compatibility there's another thing which that there actually are a lot of shared components in the orchestration part as well so like the infrastructure that automates and manages a lot of these things and that's also part of the reason that it's that it's that we're able to do this so easily like for example things like I am authentication things like s3 integration which isn't on the list here because there's a lot of similar there's a lot of this really the same moving parts behind the scenes that are involved in managing both of these so because of that I'm actually going to just before we dive into the aurora stuff i just want to mention two or three things on the RDS Postgres side because you'll see that they kind of relate to each other so first just talking about backups and this is also important because on the happiness sense slide that I have every day I have two I have a couple points about backups one of the things that we're always telling people is like we're please if this is a production system just says do the maximum backup window which is 35 days in RDS that's the same window whether you're on Aurora or an RDS backups work differently between the two on RDS it's basically using EBS snapshots right and then you see you're sitting on top of he BS volumes and then the wall logs themselves are being archived off separately and they're being there copied off on a continual basis and then recovery involves taking that snapshot image and replaying the logs back on top of it yeah and and the reason well tomorrow we'll talk a little bit more about some of the different ways that things can break or things can go wrong and then what you do about that but it's it's really it's turns out to be really useful just to have 30 a whole 35 days worth of backups I mean not the least of which is that sometimes you don't even discover that things have gone wrong until a little while later right I mean if somebody had gone in and like changed some data and one of your tables for example you might not catch it you might not realize it until like a week or so afterwards there's a lot of different reasons that that's important from an availability perspective we haven't talked about this a ton yet but on on the happiness hints slide two like you'll see on there to use multi a-z this is going to be especially relevant tomorrow I I do need to just quickly introduce multi AZ and RDS for people that aren't familiar with it I mean you guys already are but there's there's gonna be a very broad audience for the class and I kind of want to make sure that everybody gets introduced to it so in RDS Postgres actually in in RDS with all of the the non aurora engines it's except for sequel server that one uses always a different technology but all the rest of the engines right we're talking storage level replication so it's it's it's underneath the date it's underneath the way that Maas works specifically is that the database talks to the storage and then at the storage tier the storage manages rep maintaining a second copy in a different easy and then what happens is your application is pointed at the primary application and both of your availability zones are pointed at the primary database and your database is physically maintaining a copy of that and then if something goes wrong and your primary database has a problem then what happens is we stop that replication we switch the primary over to the second availability zone that's managed by the control plan software there's DNS server and then what happens is the the database signals the DNS to switch what it's pointing to once the DNS has changed and your clients pickup that the clients reconnect to a different IP address and they're able to reconnect to the parameter and this all works this all works we've been doing this for a long time it works well this is this is multi AZ on RDS now or worse of course going to work different but it's important to know what you're comparing against I think just to sort of set the stage for at that point in time were of course free to replace the secondary as well so we can this the secondary database can be replaced and then synchronization is this is all done automatically under the covers right by RDS so that's that's mass Aurora's gonna be different there are some things though again they're going to be common it is a a well established best practice well architected principle in AWS to be multi a-z right when you architect applications this is just something that you'll hear us saying all the time when you architect applications to always architect over AZ's and you want to architects so that for your production systems if there if there were some sort of an issue that one AZ was unavailable your application would continue to operate with the other AZ's that's that's a just an important principle in general right one more thing to talk about is the idea of rear up because this is especially important and the reason this is important well I'll come around to that starting with RDS so they read replicas so this is showing a mass cluster down here in the bottom right we've got our primary or secondary these are across two different a Z's and what a read replica is is that we then maintain another copy that is somewhere else a synchronously whereas so what synchronously means I'm you guys know terminology right synchronously means that like when I have a transaction when I say commit that commit operation is not acknowledged back to your application until it has been committed in both AZ's like the data is on the disk in both a ZZZ and then you and then your application receives an acknowledgement that your data is durable that it's safe that's that's what synchronous means asynchronous means that the that your your message will go back to your application right away and then in the background it'll continue to be synchronized across but it's you're not going to be blocked waiting for it that's asynchronous there's a couple different use ko and the one other thing that I think is important to point out about this that people miss a lot of times is that in the Postgres I'll just start with Postgres is that's what we're here to talk about an RDS Postgres these read are these are physical replicas meaning that if you were to open up the data files on a read replicas and Postgres the data file bit 4-bit would look pretty much the same as your primary database that is not true of my of RDS my sequel right so it depends so that differs from one engine to another inside of RDS RDS my sequel their logical meaning that like if you open the file the bits would not be the same now if you select from the table you'll see the same data but if you open the file it's not the same bits that said both in the case of maths and in the case of replicas it's also the other kind of architectural thing that's important to understand is that it's it each of these has their own copy of the data so we're maintaining a full copy of the data we have to the primary has to send messages to the replicas that can tell you then the replica has to do I owe after receiving those messages you're going to see that that's a really key difference for Aurora the there's kind of like two or three use cases I always kind of skip over these because I pulled these slides from somewhere else and this was kind of talking more about why you would use read replicas the availability case where you can maintain it like that upgrades are another reason I'm your read replicas stay up all the upgrades opening modifications on replicas so you see that like one of the use cases that people have for replicas is just increased availability the second one is latency you can do replicas across regions with Postgres as well it's still one click there is a one more thing I want to mention real quickly about this do you remember on the very first day we talked about like hot updates we talked about kind of the overview on architecture and one of the really important things that we talked about was like how you have those old ro versions that have to get cleaned up right and that this is what vacuum does that vacuum is going to come in but remember one of the other things we talked about was your longest-running transaction and how that you know Postgres is always keeping track of what's just the oldest connection was the oldest transaction in the system because anything that's outside of that range that's older than that is it's it's eligible to be cleaned up it's safe so if I have a version of the row from yesterday but the oldest transaction started an hour ago well that data from yesterday that was deleted yesterday I don't need at this point it's safe to or truly delete it but if a date if there's some data that was deleted 15 minutes ago and there's a report that started running an hour ago and it hasn't finished yet I need to keep that even though that data's been deleted I have to keep it around until the reports done right because the report needs to see a consistent picture of the database so that's really important and what happens if that report that I'm talking about that I kicked off an hour ago what if it's running on a read replica this is actually a really important question because if it's running on a read replica then how is vacuum supposed to know that it can't delete that record well the answer to that question actually is completely different between cross region and non cross region did you know that let me just back a slide and this is it's kind of it actually may not always be this way I'm pretty sure it I have to I should probably double-check the defaults is this does this sounding right to you yeah okay yep it is different physical logical that was what I was thinking because we built physical first no no it's not physical logical cross region is so physical the slots yes yes that's exactly what it is yep the reason was that we built the it was actually as literally the order that would build things when we built the cross region stuff later and when we built the cross region stuff at by that point in time Postgres had a really fantastic new capability that put the post crest community actually enhanced the database so that a master database could actually keep track of the oldest transaction on a replica and that way vacuum could actually know that didn't used to exist back when we when we've built the in region replication feature it was before they had done that and we haven't we still support those old versions of Postgres for in region replication and at the end so the way that it works is within now this is configurable you can you can configure all of this so you could configure it yourself in region and a parameter group but the default settings are that if you're in region you could have a report running and what will happen is your reports running and vacuum will clean it up and then what's going to happen is the vacuum will send those messages to the read replicas saying okay now you clean up your records the read replicas at least it is smart enough to know when it tries to clean it it's like oh wait a minute I've got an open transaction so what will actually happen in that case is that your read replicas will just pause it'll stop and it's just going to wait for the report to finish but what that means is that it can't do anything it can't replay anymore data from the master so your replica is slowly going to fall behind until the report finishes when therefore finishes then it'll catch up and this is something we see all the time in the Postgres fleet by the way we have alarming because we want to watch for replication getting broken and we need to know if something wrong with our our control planner orchestration software and then also there are cloud watch metrics on this so it's fairly easy to observe and yeah we see we see replicas that lag because of that so when we built cross region on the other hand see this is in US East one and then here in this picture there's one and in West one cross region was able to use replication slots exactly what you mentioned a moment ago there and what replication slots or another another thing another name for the the key feature is called hot standby feedback cost and by feedback is the name of the parameter that you need to change in order for Postgres to use something called replication slots and hast and my feedback me is that this is called a hot standby database it's a it's hot because it's open for queries that's what makes it a hot standby and then it it will send feedback to the master which tells vacuum oh hey there's a transaction running so vacuum then is smart enough to like just keep going and you but just leave those rows out there and then the replicas will never fall behind this will also become important as we start talking about our worth so cross region they're talking about reasons you would do that latency going down doing dr's and dr moves things like that so kind of illustrating somebody moving across you can promote replicas to become new primaries which is kind of cool one last thing that I'm going to take from the RTOS Postgres deck before we dive into or because this works the same on Aurora but it's not in the Aurora deck and it's not talked about there and it's really important is upgrades so I'll talk a little bit more about this at the toward the end of this particular session but so Postgres today I might have mentioned this yesterday or the day before too but Postgres has the post the way the community organizes their releases is that they have with what they call major versions and then when they call minor versions the minor I actually prefer to call them quarterly updates because that's what they are so they don't put new features they're not going in building that what they're doing is just they have a schedule and four times a year on a certain date they just take the critical bug fixes like stuff that had to be backported and then the security vulnerabilities so any any security issues that happened are bundled together and then those are they ship basically an update to the old major and if you read the language they're very clear that like they are only putting the stuff that they're putting in those those maintenance updates are things where they believe it would be more risky for you to not upgrade and that it's less risky for you to just get that patched code into your system so the the consistent recommendation with Postgres is to is to say very much on top of installing those quarterly updates it's very similar like in the Oracle world to SPU's or PS use that kind of it that that's how I immensely think of it the other important thing to understand about majors and minors in Postgres lingo is that so a minor version upgrade never there's no upgrade script there's an you never run a script so it never involves changes to the data it's it is purely a binary swap out and that's it so what that means first of all is that minor versions are are not going to take a long time right a minor upgrade is always going to it's just the time to restart the database that's basically it this slide isn't even quite right because we don't shut down the instance and start the instance when we do a minor upgrade I mean I have to tell them about that in RDS it's I'm ándale with Aurora both a minor upgrade is is literally will provision the software out ahead of time and then it's just like shut down the old start up the new and that's all the time that's the only actual outage that you have to deal with and then once it's back up we can clean out the old software and free that space backup on the binary volume major version upgrades are very different a major version upgrade always involves running at utilit well and RDS we run a utility called PG upgrade the other way you could do a major upgrade is with like a dump and load or something like that which you could also do an RDS you could provision a new instance and replicate it or dump and load when you click the button that says upgrade or actually what you're doing is you're going to a drop-down and choosing a version and saying save you do a modify what RDS Postgres is doing under the covers is using a utility called PG upgrade PG upgrade does not have to scan your data files and to date so far there has never been a version of Postgres that we've shipped on RDS where upgrade had to let go in and change the block format of the data that would be very very expensive however what it does do the way that PG upgrade works is that the data stays in place the data doesn't he abused using actually hard links to to just create new file entries that point to the same files but the metadata there's all the data dictionary stuff so that all those catalog stuff like the PG class view we looked at in the hands-on lab yesterday what Postgres what PG upgrade actually does is it basically does a dump and load of the metadata it's similar to like what transportable tablespaces would do in Oracle what that means and why that's important is that the time to run a PG upgrade is very much impacted by the number of objects in your schema in your database so the place where we have seen customers run into issues is like customers that have hundreds of thousands or even like millions of objects in their database that can start to take a while so a major version upgrade I mean you should test your miners too but you need to probably have a greater level of rigor in the way that you test a major version upgrade and you're not just touching testing new functionality and features you're also testing just like the timing of it right so you want to you know make a copy of your database make sure that copy is completely initialized or hydrated and then you want to run the upgrade on that copy and see how long it takes it's an important thing to do I mentioned also that you can use replication as well which is another strategy for a way to do upgrades this is a using DMS and that's it all right so at this point I'm gonna sort of pause and this is where I'm gonna sort of stop and I'm gonna put the video up of grant now while the video is playing are you in the chime room by the way okay good I was going to say we're you we've been I want to use that chime room as the great thing about putting questions in there is that other people see it as well and I mean there's been there's been there has been like sometimes other people just been able to answer the question but the the bigger thing that I'm hoping for is it just sort of stimulates thinking in that you know like you might ask a question that makes somebody else like oh you know I saw something similar to that just last week on my database and I have a question about this so that I see a lot of value to getting the questions punched out if you're able to it's about a 50 minute video good evening everyone I think it's evening it's always hard to tell in Vegas put in a nice dark room no windows my name is Graham McAllister I'm a senior principal engineer work for AWS as you might have guessed from the logo I work on RDS and most of the time I spend with the Postgres engines today what we're going to do is we're going to dive into aurora Postgres we're gonna look at the changes we've made why we made them and why you might care about those we'll also talk about the things we've delivered in the last year four or Postgres and how you migrate to it so one of the first things I want to talk about was RDS in general there's a lot of confusion that Aurora is not part of RDS and it actually is it just has separate branding marketing folks wanted to brand it separately but it's run under the same management platform that all seven of our engines are - Aurora engines are to commercial engines and are three other open-source engines so all these capabilities that you get from a managed RDS system are true with Aurora as well but because it's our engine we've made some other changes right so from an RDS perspective we actually have two flavors of Postgres we have RDS Postgres which is community Postgres with just a few small changes for security and it runs on ec2 runs on an EBS volume right so this is pretty much standard the same way you would do it if you were just running an ec2 on the other side what we have is Aurora Postgres and this is quite a bit different because of the storage so we've Aurora storage and so this is database aware storage so that's fundamentally different than a block store and I'll get into what those changes mean for you running your application but the cool thing from your perspective if you have a client you want to connect to either one of these it's gonna work exactly the same they talk the same protocol you've whether you're connecting with P sequel ODBC JDBC whatever it's gonna look exactly the same same objects same commands and just to run through like in more detail you can see that we support Postgres 9 6 and 10 for both of these engines we have the same extensions on both of them we have the same backup recovery and P ITR capabilities we have a high availability and durability story for both these engines we they're both secure by default and actually this is one of the cool new features that we've just released which is our I am or Identity and Access Management ability to use those credentials to log into your database so you don't have to have passwords anymore so that's a really nice feature on both of these engines they both support read replicas they both support cross reads and snapshots and scale compute and online scale storage now the storage on the RDS one is up to 32 terabytes 64 terabytes on Aurora there's two features that we're still working on to get to parity one of them is cross region replication and the other is outbound logical and I'll talk more about those in presentation I want to highlight that right now the max version that we support is version 10 for both these engines but for RDS Postgres we have a preview region where we have version 11 and 11 0 in it and we're rapidly working on getting 11 for Aurora as well so that's coming so let's start with one of the fundamental differences this is log based storage and so because of that there's a lot of fundamentals that change with how it all works so the first one is that we have no log buffer and to explain that I'm going to walk through what happens on Postgres and or or Postgres so the queued work there in the read those eight blocks you can think of them as eight commits for example that have happened on different sessions so they're already everybody hits commit at once modern databases have this really nice feature group commit so they can all go into the log buffer at the same time but one of the problems with the log buffer is once it's getting ready to flush nothing else can go into it right so if more acute work comes in it's got to wait until that log buffer gets flushed down to storage and acknowledged right and then the queued work can go so you can see how this is a single point of bottlenecking right now on Aurora it's quite different when the transactions come in they just flow down to the storage as they're happening right and so it's an ordered system and you want it to be durable so we actually have to keep track of these things so we have what I like to call durability tracking so we use a 406 quorum in Aurora so means we need four acknowledgments from our storage system to say we're good right so we keep track of these and I'm illustrating that we're you know after a tiny little bit of time we've got a couple of them in two places some of them in zero some of them one and then finally we get a two four right so then you would acknowledge the commit back to the client but you'll notice that C is also at four well we're good right we should be able to mark that one durable but this is an ordered database right and because B happened before C we need it to get to for durability as well before we can actually mark C as durable so you know and you can see the same thing is happening with either where we have to wait for D to get committed so the other thing with aurora is we write a lot less let's walk through the differences so on Postgres if you have a tuple you know just a row in your heat block that's what I'm showing that block in memory and you go do an update well an update in Postgres is essentially a delete and insert right so you end up touching both of those tuples and those are gonna get logged to the wall right so this is for crash durability but you'll notice something else showed up there right a full block why did I get a full copy of the block in log well this is for another form of crash recovery and I'll talk about why we do that now we only do that the first time you touch the block after a checkpoint so if I go do another thing like update the tuple again or insert a new row that's just gonna get just the log vector is gonna go into the storage right so this is all normal Postgres at some point you want a checkpoint right you want to make that block durable on disk right so you don't need the recovery log anymore and the wall has to be archived and that archive has to be copied s3 so it's backed up and then we take snapshots of the data file right but when you do a checkpoint it's actually not just a single write write in Postgres the default block size is a K but for example Linux which is what we run on is 4k iOS so if you're in the middle of doing that checkpoint and guess what the system crashes you might have only got half of that right to disk guess what would happen some point later you'd find out you have a crop block right so Postgres handles this by taking that full block and during crash recovery using it to repair this split block right this works great it's been used for a long time but it involves a lot of writing on the Aurora side it's quite different we have no checkpointing no full-page writes so when we do the update we get the same thing happening the two tuples changed we eject those log vectors to storage and that's it right if we do another one same thing so there's no full blocks there's no check pointing there's no writing of data blocks right we only write log vectors so we back this up continuously from Aurora storage to s3 so that's how we get you point in time durability for recovery and again no checkpoints no full page rights so you're saying well how does this all magically happen well we have this really intelligent storage layer right and this is comprised of a lot of different storage servers in the backend and I'm just illustrating one of them here so if you have a read/write node and it doesn't update that log vector is gonna flow into the incoming queue and this is an in-memory queue once it's there and then gets processed into the update queue which is basically the durable form on disk once that's done we can actually acknowledge back to the client you know one of the one of the nodes is acknowledged right so the interesting thing here this is the only synchronous thing that happens all the other things I'm going to show you our background pieces right so at this point that vector will go into the hot log which can be used for a peer-to-peer gossips and we also do coalescing of the block right so we read blocks so we actually have to apply the log vectors so you can think of this happening on a block by block basis that we're essentially doing recovery continuously right so as I said the peer-to-peer storage we do repair via these logs and then we also push both the log vectors and the blocks out to s3 so that you can have a point in time to any point you want within 35 days so again when you read you don't read log vectors you got to read a block right that's how post rest works so if the block has been coalesced then that's what you read if it hasn't then we do an on-the-fly coalesce and coalesce whatever log changes we need to make a block right so you never have a long crash recovery with Aurora because we're doing this continuously to kind of show what this looks like in practice I created a little test I do an insert test I built a table with nine columns they're all different kinds some are random some are right-leaning different object types and then I indexed every column now that's not normal right but it is normal to have a lot of indexes on a big table I mean I've had tables I've seen tables that have 75 indexes on two or three hundred columns right so this isn't really unusual so when we go run that insert workload vertical axis is inserts per second and so you know bigger is better right the blue line is sort of regular Postgres right and we see that we get like 25,000 writes per store inserts per second right off the bat it's pretty good right but it drops really quickly and the reason for that is as the database gets larger as you've inserted more data you have more blocks right and your indexes get larger so the chance that you're touching a block between checkpoints gets higher and so that full page rights increase increase increase as your database gets larger so your performance basically slowly goes down so if your DBA you say well I can fix that I can make my checkpoints longer right I'll just increase the max wall segments and so that's what I did in the purple line right and you can see that help for about 20 minutes but then the database got large enough that again we started getting full page rights and we see this degradation of performance right will notice on the yellow line there is that this is Aurora we don't have checkpoints we don't have full page rights so we have very stable performance even as the database grows and grows right so not only do we have stable performance but we have better performance right and this is the same if we do updates I did the same test same tables updated to columns and you can see we're getting about three times the performance with Aurora that we were with even tuned Postgres and again this is because we don't have a log buffer we don't that single point of contention we don't full-page writes so the other critical thing about writing more is that you don't necessarily want to take a long time to crash recover which is very typical for most databases to illustrate this I have a chart where the vertical axis is recovery time you'd like that to be low and then the horizontal axis is writes per second you want that to be high so you want that to be far down the range so when we start pushing post grass what we see is that as we start we got like three gig of redo generated on the first arrow right we're doing about 18,000 TPS it's not bad but as I add more clients to push the database harder guess what my recovery time goes up by quite a bit to 50 seconds and I've only doubled my throughput so if I keep pushing the database what you see up here we're generating 30 gig of redo between checkpoints and my recovery time is taking over two minutes right this is a dramatic increase and so this has always been one of the trade-offs with most relational databases is you had to make you had to say what I like to be able to write really fast or what I like to have low crash recovery so can you guys see that little dot out to the far right it's really hard to see so I circled it for you that's Aurora so reward it does continuous recovery right so we don't actually have to do crash recovery so it takes three seconds for a roar to come back up in this case but you'll also see we're doing dramatically more writes like 3x right compared to a Postgres even at the largest scale so this is one of the nice things with Aurora you don't actually have to make this trade-off of these two different capabilities so let's talk more about the base architecture of Aurora Postgres so I'm demonstrating or showing here three availability zones that's very typical for our regions and the blue block is essentially Aurora storage you want to think of it sort of virtually right it's across all the availability zones the little blue ones inside of those are individual storage servers now I'm just showing six in each one but usually there's hundreds or thousands right when you go and provision an application and you say I want a database Aurora Postgres what's gonna happen is we're gonna go take ten gig chunk segments and put them on these storage servers right and that's what I'm illustrating with the six different colors because we get six copies right so you can have you can connect applications from multiple AZ's that's always a good idea for availability when we do writes again we write log records right so they're gonna get written to all six places we only need four of them to get a commit done but you'll see that though all the different colors are involved right when we read we read blocks back and typically we're gonna read from the local AZ because it's gonna be fastest but we actually do that on a periodic basis to see which one's going to be faster so we don't have to do a quorum read or anything you know crazy like that that's gonna take a long time so sometimes you actually have problems with getting quorum right sometimes two of those rights may not show up so what do we do then well we can actually do repair as I said peer-to-peer gossip between the different nodes and they'll actually send the missing log records to the other node right in the case of a failure of a whole node will actually make a copy of that segment onto a different node right we'll also do this for hotspot management so if you're pushing hard will actually move things around so there's enough resources on that node stuff you don't actually have to worry about so the other thing that's really cool about Aurora is that there's read-only nodes and the difference between sort of regular Postgres is when you make a read-only node you have to copy the data but here you don't because the storage is shared right and so what I call clustered storage right so as soon as you fire it up you can read the data out of that storage you don't have to make a copy now you'll notice that little sort of purple line going between the readwrite node and the read-only node we do communicate we do send information from the readwrite node to the read-only node but we only do to invalidate things in cache we don't actually have to send it over so it can be written and you can have more than one read only note in fact you can have up to fifty you can have a lot right and so you can have different applications using different read only notes for different purposes now the big thing about having multiple nodes is that in the case of a failure what's really nice is the good thing is your data is durable no matter what right so you don't actually have to worry about data durability for having extra nodes but if you want a fast failover you want to have a note so what we're gonna do is we're gonna promote one of your read-only nodes to a readwrite node and it's gonna start being able to write so this this happens in typically about 3035 seconds with including the DNS propagation and you can actually connect to all the nodes so you don't actually have to wait for DNS propagation so let's talk a little bit about why we picked 406 quorum so when we started RDS backed like almost 10 years ago we we knew that we probably want to do some form of durability but when you first released it it was basically just a single AZ product right so when you would do a commit you're basically gonna write to EBS and get a response back but it's actually a little more complicated than that because EBS behind the scenes actually has two machines right it's mirroring your data so when you're writing you're actually writing to the first machine then you're writing to the saut writing to the second machine getting acknowledgment back and all the way back to your server right and at that point you're good you're committed but guess what this only works if you don't have a problem in that AZ right so this is where you want to have a multi easy solution that actually replicates the data synchronously so we actually looked at having one or two different you know two or three copies so first we said well okay let's look at just having a secondary or maybe having a tertiary so we actually played with having you know multiple copies so just illustrate what happens when you have three locations you basically do that commit and you send out rights and the read rights are the remote ones so the black ones are local so the red ones are going to be slower and in some cases you're AZ's are farther apart right so the rights take longer to happen so as we're progressing you'll see that the local copy is actually moving along quite well and the remote ones are just getting started right so at this point you've got basically two of them done right but you have to wait for the third one if it's synchronous replication so we got to wait for a couple more you know maybe milliseconds for this to finally finish now the other thing with this kind of replication is the more copies you have the more chance you can have for one of them not to respond right so if this last right doesn't actually come back you never get that acknowledgement at some point you time it outright and at that point you actually have to fence the system to figure out who's the live nodes and so you'd be like oh well the primary secondary are still alive we'll leave them in the you know sort of group and we'll kick out the tertiary when the tertiary er comes back it actually has to catch up all the stuff that was missing right it's not just one right that you skip it's all of them right and this is quite different on our world so we did a bunch of testing and these are numbers from quite a while ago EBS has gotten quite a bit faster and network latency has gotten better but this is just to illustrate sort of the difference when you have two nodes and four copies or three nodes and six copies so the blue is the two nodes the green is the three so latency on the vertical axis you'd like it to be lower so the 50th percentile writes you can see that really there's not a lot of difference it's six to seven milliseconds right you're like hey that doesn't seem expensive I love this give me three copies right but the problem occurs when you start looking at like the four nines percentile like this is only one in 10,000 iOS but the difference is basically four acts on going to the third copy and that's because you have more jitter in a system when you have more copies right so we've looked at this and in the end we said now we're just going to do two-way multi Z for standard RDS but when it came to Aurora we thought we can do better because we have a different storage system so what you'll notice here is I have only three availability zones again and I have my primary but you'll notice I don't have a tertiary or you know secondary node because I don't need those right my my storage is my durability so when I do a commit on Aurora essentially we're gonna send out six simultaneous write requests and they're gonna go out to all the storage nodes and then they're gonna start getting responses back and as soon as we get the first four back you'll notice we're able to commit so if these other two never show up that's okay because we'll do peer-to-peer replication to catch up but if they just miss one right we don't stop writing to that node right we'll just be behind that one little transaction and it'll get fixed by one of the other replicas right or storage nodes to be clear so this is quite good that we don't actually have this really coarse model right it's a very granular system of repair so what does this result in this results in much better latency so this is suspend the p95 response time right this is a thousand clients so this is a high skill workload and we got a thirty gig working set so the blue line is RTS Postgres single AZ no backups the yellow line is Aurora and what you can see is that Aurora is very consistent right over time it's very good it doesn't have a lot of variability now the blue is all over the place can anyone guess what that these blue kind of sections are where it goes up and down like that that's checkpoints exactly correct right so what's happening here on regular Postgres is the checkpoints are basically fighting for the iOS with that log buffer being flushed right so at the bottom of the graph it's pretty good that's when it's not check pointing and the you know it's pretty good but you'll also notice this is single a Z we're not actually durable here across multiple a Z's this is actually not even a really a good fair comparison because if you made this multi Z the blue line would be even higher right so let's talk a little bit more about what we do for a replication and a cool feature called clones so in Postgres RDS Postgres if you ask for a replica we're gonna basically take a snapshot of the EBS volume we're gonna restore that EBS volume then we're gonna fire up a read-only ec2 instance on it and great but you still have to catch up from that all happening right and that takes a bit of time on a really high right workload this could take an hour or two for it to catch up once it's all caught up if you do an update essentially what you're gonna do is you're gonna write to EBS you're gonna get that response back so that's all good and at the same time you're sending an asynchronous request across to the read-only node now it might actually have to read that block into memory before it can write it out again so that's a lot of work that has to happen on Aurora this is quite different we have just a row of storage so when you want to read only node BAM we just pop one up it takes a couple minutes right because it's just firing up an ec2 instance and attaching to the storage when you do an update on Aurora that same thing has to happen we write to storage right and we have the asynchronous replication going across but it's just doing it for anything in memory so it's only updating the blocks in memory and it doesn't have to write on the other side because the data is in the storage right it's shared so I wanted to show this and I thought well I'll use PG bench and it has a read write mode in a read-only mode so I was gonna run the readwrite node with the read or the write workload and on the read-only node already read from it so there's four tables they all get modified on the readwrite workload on the select only workload it's only the accounts table it gets read so you start off post trace it looks like this but then as soon as you start doing asynchronous requests guess what that note actually has to load those other tables into memory because it's got to be able to apply the changes to those tables as they happen in Aurora this is quite different in that we're only doing this stuff in memory so the accounts table is the only one that ever gets touched because it's being read by the the select only benchmark so this is actually really important for what happens about replication lag to illustrate that I took a replica I'm running 8000 TPS writes on the primary and on the read-only note I'm doing 200,000 read-only requests right so that's pretty impressive and this is stock Postgres right RDS Postgres this is the cloud watch metrics from the replicas and you'll notice the sort of the thin orange line is the rights that are happening so it's going along quite well and then where the big arrow is I did a really not nice thing to my database i back filled the whole PG bench history table I've dated every row right in one transaction so that's not something you'd like to see but guess what it happens in production where people need to do backfill right what you'll notice is the green line so the Green Line is the replication delay in seconds and so the reason people ask me why is it in seconds well when we first started RDS it was my sequel my sequel had kind of poor replication and we you know the lag was always in many seconds and so we said seconds is a fine number to use as granularity nowadays for Postgres is not really applicable but we haven't gone back to fix this yet so as soon as I do this backfill what happens is that is essentially like a snake trying to eat a watermelon right it's got to go through the whole system and it clogs it up and what you start to see at that red arrow is that we're losing 30 seconds for every wall clock minute of replication delay and after 19 minutes were 10 minutes behind on this replica but you'll also notice another thing that blue line starts to pick up about halfway through and it's doing a lot of reads on the replica why is it doing that well because it didn't have all the PG bench history in RAM so it actually had to start loading it off disk which slows the replication down even more and now we're losing almost 40 seconds for every wall clock minute right so this is how on a regular RTS Postgres instance you can actually have a lot of replication problems if you do things like backfills or have any other issues this is the same benchmark running on auroral so the big difference is we use milliseconds for our latency for a replication lag and if I didn't have the red arrow there would you be able to tell where I did the backfill you wouldn't right because the replication lag basically didn't change because I'm back filling a table that it's gonna send those rights over and then it's gonna find that they're not in memory and not do anything with them so there's no update that happens the other really cool feature that we launched in the last year is called fast clones so I'm illustrating sort of the same setup we've had before I've just kind of changed the storage to look a little different where I have blocks instead of the storage nodes just illustrate some of the concepts here so let's say your business wants to do a lot of reporting but they want the data frozen at midnight well you could do a point-in-time recovery fire that up that'd be great but you have to you know allocate all that storage let's say it's 20 terabytes instead what you can do you can have your reporting application create a clone and so this is a really cool thing in that you get clone storage what do I mean by clone storage well clone storage doesn't actually exist all it is is pointers to the primary storage to start with so if you clone a 20 terabyte database you're not paying for 20 terabytes of storage you're paying for nothing until you modify so in actuality what happens with when you start actually running your application here is when you do a read it's gonna go find the address in the clone storage but it's actually gonna go read from the primary storage right so you don't actually have to duplicate it so that works but what happens when I try to do a write I don't want to modify the primary storage so we do copy-on-write at that point so before the block is modified it gets copied down to the clone storage right and we basically unlink the relationship between those two blocks you can create new blocks they're just going to go in your clone stories and there's no relationship to the primary storage there when the original rewrite master basically writes a log record you'll see that it only updates the primary storage it doesn't actually make changes to the clone storage because they're separated at the time you clone right it's not any kind of lagging updates or anything and when the read write node modifies the current block that's shared by both of them basically we do that same copy-on-write in Reverse and you know give the new block two or new block for the primer the old block goes to the clone storage right so this is a fantastic tool for using for reporting for testing for benchmarking to illustrate this I ran a PG bench readwrite benchmark and I set a target rate of 10 or 20,000 TPS and it's a 10,000 row or 10 scale 10k sorry which results in about 150 gig so one of the things that people ask me is well this clone stuff sounds great but does it impact my performance so I'm running along on purple right that's my primary that I started and I requested a clone at about I think 20 minutes in right you'll notice there's no degradation in performance when I did that and about 15 minutes later my clone was finished being created I wasn't actually keeping absolute track of it so about 18 minutes or 20 minutes later I fired up the same sorry I fired up the same benchmark on the blue and you can see that we actually get the exact same performance on the clone right and we see no degradation in the performance of either of them so this is a way where you can go get a copy of your production system and then go run tests on it for example and it's quite inexpensive so as I mentioned earlier to the features that we're still working on for parody our replication related ones that I'm going to talk about them now so the first one is logical replication support so this how many people are familiar with logical replication and Postgres some not too many so this is the ability for Postgres to take the physical that go into the wall stream the right ahead log and convert them back to sequel so that you can take them from your instance and use them to you know a data warehouse or a lot of different things so we support this in RDS Postgres today and we're working on it for a while so if you have an Aurora instance what you're gonna be able to do in the future is you're gonna necessarily you could fire up an ec2 instance and you can enable logical decoding plug-in and we support three of them today you can choose which one you want to use and the ec2 instance basically going to talk to the Aurora instance and get those logical changes and then it could ship it to something like Kinesis for example right the other thing you can do with this is you can fire up our DMS service our data migration service which is really a replication service at heart and you can have it talk to your instance and pull changes off and you can send those two already asks you can send them to s3 you can send them to dynamo you can send them to redshift there's all kinds of places that you can move your data to right now this is really nice but as you can see it involves having another box and some stuff happening right if you just want to replicate between Postgres instances in v10 we're gonna be able to support publish and subscribe so this is if you have an ec2 instance running Postgres and RDS instance you can actually set up publish on one side and subscribe on the other you don't have to have any secondary boxes and you can move logical data between the two of them again this is for you can use an RDS instance for Postgres or another Aurora instance will be able to work like this so this is really handy if you want to move data around so the other thing customers really talk to us a lot about is being able to have dr and so the feature that is going to support that is our cross region replication so here I'm showing region a with an Aurora cluster what you want is region B to have a copy right so we're introducing some new new features the first is that we're having a replication server in a replication replication agent that's the little boxes in purple and the goal of those is to support this cross region replication when you do a log write which is all we do right on the primary it's gonna flow to a bunch of different places so goes to the read-only notes do that invalidation as I talked about it flows down to the storage level for the durability and it goes to the replication server the replication server is going to forward that on to the replicate replication agent on the their side and then it's going to apply it to the read-only node for again cash and validation purposes or updates and it's gonna flow to Aurora storage right so this way you can actually physically get all the physical changes on both sides it's gonna be very low latency and very efficient now one of the things that can happen is you can sometimes lose log vectors and the nice thing is that we actually can handle that we can also handle having multiple really nodes in the future we're going to support multiple regions as well at the same time so the replication server and agent talked to the storage servers as well and they can actually pick log vectors or blocks from the one side and move them to the other right so we can actually do catch-up in multiple ways for repair purposes so you can use this in a dr scenario where you know something's happen to a region or you could move use it to move one region to another we had a lot of customers when new regions pop up sometimes they're closer to their customers than our current regions and this is a really nice way where you can just basically go and promote the new one to be the new writer and at that point you know you have your same cluster you can have all the same read-only nodes and everything else so this can also be used for you know remote reads in other regions as well so caching we made a bunch of changes in Aurora for caching we'll walk through that so on on our 416 Excel very large box 180 gig of ram right so when we set up RDS Postgres we basically allow about 25% of that ram for post rest processes in the OS about another 25 percent goes to the shared buffers for inside the database and the rest goes to the Linux page cache so this is actually where Postgres is different than most databases in that it uses two caches right most just have something like shared buffers now there are advantages and disadvantages to that so the first is when you're selecting data the Postgres process is going to go look for and shared buffers and if it doesn't find it there it's gonna ask for storage it might find it in the page cache if it doesn't it's gonna go to EBS and pick it up and then it has to return all the way back up the stack so this works well but there's a little overhead the other thing is because you have two caches you get duplicate buffers so even though we have 75% of the space for caching in actuality we only end up cashing about 50% of the blocks right so only about 240 gig of cash space is used there on Aurora it's quite different because we still need the space for Postgres in the OS but we don't have a page cache because we don't have a Opera era filesystem we'd write directly to our storage so we're going to use all of that remaining RAM for shared buffers so 75% when we do a read from Postgres again the same thing we're gonna look at shared buffers but if it's not there we're gonna directly get it from Aurora storage and return it so Postgres can die right as a process and that's fine because you know it's durable but you want it to come back up and be fast right so you want to have your buffers but your buffers also go away when post rest eyes but the Linux page cache doesn't so guess what you're happy because your blocks are in cache and your databases backup and it's all working fine right so we had the difficult decision of being like well we don't have a file system cache so we're not going to get that benefit so we actually had to go build a feature called survivable cache so this is where we go and do invalidation on the cache when the Postgres process dies we actually have the shared buffer separated from the Postgres process and therefore they can survive we just need to invalidate a little bit of metadata and then bam it's all good right so this gives us the exact same sort of story around caching but there are some advantages so to show that I ran a read-only benchmark right so this is just reads scale 22,000 so that's 350 gig working set right but it should fit in Ram because we have 488 gig right so in our 460 to excel and the blue I'm showing Aurora with a 75% cache we're getting 600 almost six hundred and ninety thousand TPS so this is really cool when you just think about as a raw number I mean that's like a very fast system right so I went around that on RDS Postgres with it's 25% shared buffers and I got 1.6 times slower and I was like what's going on here I didn't expect that well it turned out I was doing 18,000 read items and the reason was because this size of working set didn't fit because of the double counting of buffers right that we had the overhead of that so to fix that what a lot of people recommend in the Postgres universe is to make the cache smaller right make the shared buffer smaller give it all to Linux page cache and so that's what I did I went down to a 10% cash so it would all fit in cache and I got a lower number and I was like my hand I'm doing something wrong so I went and said what's going on here why am I being slower well as it turns out because the overhead of Haffner Reede both the shared buffers and check for it there and then go to the file system cache and then go back up through those layers burn CPU this is a heavy CPU benchmark right and so I'm stealing CPU cycles to do that which means I can't do more transactions and so this is why I'm actually slower and to demonstrate there's no goofy business with Postgres if I make the cache 75% the shared buffers we get the basically the exact same number as Aurora because reading blocks from memory is essentially the same on both of them but guess what if you configure RDS Postgres like this and the Postgres process dies no survivable cache right so this is one of the again the cool differences that we've improved on with Aurora so this is all great when you know just the Postgres process dies but what happens when I have to do a failover right what about my cache then so to illustrate that I'm running PG bench with a 20x read-only to a 1x readwrite benchmark on a node right it's 160 gig in cache and vertical accesses transactions per second and I'm doing about I think what was it like 350,000 total transactions between the reads and writes and at 10 minutes in I basically do a failover right and it takes 32 seconds to come back up and that's including DNS and I just had PG bench and a little loop just trying to connect right so as soon as DNS propagated it connected and it was all good right but if you look at the 90th percentile of my performance right I should be much closer to about 320,000 if that's what my application needs right so it actually took three hundred and forty seconds to get back to having the cache all warmed up and to get back to that baseline performance right so when we talk about failover we're actually not being very good when we say oh yeah it's failover in 32 seconds but you know from an application users perspective it really didn't write it took a lot longer so to get around this we built a new feature called cluster cache management or CCM as I like to call it so again we got the same standard setup and I'm we introduced a new term which is failover priority so this is a feature that we've had in Aurora and you can actually designate your nodes for which failover priority with zero being the highest priority I know that sounds kind of backwards but it's easier to figure out you know which one to go to so you notice that the readwrite one and the one read-only no turn our failover priority 0 so this is gonna be where we go first all these other read-only nodes I mean I've labeled failover priority 1 now that doesn't mean we won't failover to them it just means that we'll consider them less likely to happen because we're gonna try the other nodes first right so once you've done this and if you turn on a PG CCM enabled in your parameter group at the cluster level essentially we're gonna start doing extra stuff and the first thing is the read-only node is gonna send a bloom filter of what the replicas cache looks like back to the readwrite node the readwrite node is gonna compare that with what it's got in cache and then it's gonna send the addresses of the blocks that it wants to load on the read-only node and then the read-only node in the background is basically going to slowly read those blocks in I shouldn't say slowly over a little bit of time read them in and you'll now notice the color on the read-only node is the same as the readwrite node because the caches are basically very similar now now we don't do this for every block every change because you know if you had a lot of cache churn you only really want the stuff that's hot in the cache to be coming across right because it is extra read workload on your read-only node but there's a reason why you want to do this so I ran the exact same benchmark same failover at 600 seconds and the blue is with CCM enabled and the red is the original right so we have the 340 seconds so now we're back to 32 seconds for failover but we're 32 seconds for failover and back to our 90th percentile performance right so now we truly have failover at 32 seconds not 340 right so this is a very important feature it's also going to allow us over time to try to get that 32 seconds down further because there's less disruption to the customer when you do these fail overs so one of the other things that we've worked on is performance to illustrate performance one of the other tools we built performance insights this is available across a lot of our engines but our posters was the first one we had and this is a really nice tool for being able to see what's going on with performance in your database right so what I'm illustrating here is that my application was running along fine and then at some point it kind of went off the rails right so the green is CPU usage and that little black line across the top is my actual number of CPUs so it basically means I have the box pinned to the wall right it's just flat out running but I'm like well it wasn't doing that before what's what's changed so I drill in and I look at the current run what's going on right now all the CPU usage and I can see it's this query and it's hopefully you can see that it's it's basically a an analytics query right that I'm running in SPG bench history and you know that seems kind of horrible but wait let's go look at it before well it was running before and it was running fine so what happened right so as it turns out the plans have changed so before what I was doing was I was getting a nested loop bitmap heat scan a bitmap index scan right afterwards I'm now getting a hash join and I'm getting sequential scans right so this is a dramatically worse plan well why did that happen well it could be a stats change could be analyzed it could be a config change it could be an index I mean it's obably not an index change in this case right but all these things can cause plans to change on you and dramatic fashions right as it turns out the reason why this happened was me I changed a couple parameters right not that anyone's ever inadvertently set the wrong parameter in their database right so I did this deliberately just to show that you know the plans can dramatically change and you know that's a bad thing right so if you come from enterprise databases you know running fast is great but running consistently is actually probably more important right your manager if your if your your database running 2% faster it's gonna be happy if on the other hand your database blows up because it goes 500 percent slower right your manager is gonna be in your office yelling at you right so to get around this we've got a new feature out in our new latest release of 10 10 5 it's also going to come on our 9 version in our next release called query plan management or qpm so what this allows you to do is capture statements right so as the statements are running in the database you can actually capture them so in this case I'm showing query a plan version one query B in the sort of pink plan v1 right so you can then approve these plans you can do this automatically or manually and once you say they're good they're marked as approved then you can institute a baseline by instituting a baseline you're telling the optimizer to basically use these plans right and to not deviate from that so this allows for planned stability right so you're not going to get weird plants so when a new version of this plan shows up it's not going to automatically be used by the optimizer right it's it's gonna be discarded because it's not the approved plan it's not in the baseline right but the challenge with this if you do this right now someone comes along and it says I'm gonna build an index now to make that query better right and this plan v3 might actually be better but it won't use it right because I've got the baseline well we allow you to evolve to better plans and we do this by having a comparison utility that allows you to compare plans and figure out based on elapsed time and cost whether it's better and if it is then you can just approve that plan and it's going to be used in place of the original plan right to illustrate like what happens when you use baselines let's go back to my PI screen and you'll see that this is what I instituted the baselines back on right my plans go back to exactly how they were before and my performance gets very predictable right so this this shows you that you can actually have you know control of your plans and a much more predictable database performance than you had before one of the other things around predictable performance you need vacuuming if you're running Postgres right so this is just a benchmark I ran a long time ago that shows what happens if you turn off vacuuming right the red line is where you'd like to be at and the black line is sort of what happens over time it just drifts down because you have more bloat in your system right so you need to vacuum to maintain performance the other thing is you need to maintain the cleanup of transaction IDs Postgres has a limit and if you run out of them you basically are going to be down and tell you vacuum so this is a very important thing so as part of the building of Aurora Postgres it was great you know we got the benchmarks we're writing three times faster this is all great and then we sat there and said we'll wait a minute if we're writing three times faster and vacuums running at the same speed that's just gonna be a recipe for disaster right so we went and did vacuum improvements so the one that we've done so far is called intelligent vacuum prefetch so in Postgres when it's vacuuming it has two things a visibility in a frozen map and these are great improvements that we're done in a while back in Postgres that allows Postgres to not to have to scan the entire table to figure out what to vacuum and in this case I'm showing basically an illustration of the frozen map where the blue are things blocks that are frozen or and the red ones are not that names still need to be vacuumed okay so Postgres is going to go and read the ones that it needs and it's gonna go vacuum them right so you'd expect it to do basically what I've Illustrated for the red arrows right but because Postgres uses a file system cache it's trying to get read ahead so it actually has an instruction that says if those red blocks are within thirty two of each other then just read them all right so this actually causes a lot more effort to have to happen right and so I'm not actually sure it on modern SSDs if this still makes sense but it's what Postgres does today I think it's you know we may want to look at that in the future but for us we don't have a file system cache we don't do read ahead in the sort of normal way it's quite different so on Postgres that took 402 seconds when I did this vacuum on Aurora what we do is we collect the block addresses of the things that need to be vacuumed we gather those all together and then we submit one i/o right and it's got one up to 256 blocks can't return from this one il right and so this is much more efficient the other thing with Aurora Postgres is because we're not doing checkpoints or full-page writes vacuuming costs less to actually do so the combination of those two things means that Aurora Postgres did the same vacuuming in 163 seconds so more than twice as fast as regular Postgres so this was a nice improvement that really changed for a lot of our customers they used to have problems with vacuums on Postgres and they're not having any on reward now so how many people are aware of rora serverless cool well I'm very happy to announce that we now have Aurora Postgres as serverless in preview yeah it's very exciting very exciting yeah this is a really cool feature I think is gonna you know dramatically change for a lot of people how they run their databases so what this is is a little different model though you have a database endpoint just like you always have an RDS we have this new thing called a request router in that sort of light purple box and you'll notice that I have Aurora there but there's no server right now and there's just the storage so when you provision something that's all you start with right you don't actually have a server running it's not until your application goes to do work that you actually fire up a server right I mean you can configure it to always be there but you don't have to so at that time we're gonna pull an instance out of our warm pool and we're gonna attach it to your storage and then it's gonna start executing queries for you right and as you push harder essentially we'll go through and we'll actually scale this up or scale it down so you know if you just keep pushing harder you're gonna get a bigger bigger instance right and this happens quite quickly right so I'll show in a second the it takes about five minutes of you know increased CPU to cause that to trigger but the cool thing is because you have this request router in the middle you don't actually draw up sessions right you don't lose connections when we do this so this is all seamless from an application perspective it just goes up and down right the other cool thing is you pay per second in one minute minimums when you spin up right so if you have a application that only runs for four hours a day it'll just basically go to sleep and then the next time somebody connects to it will fire back up so from a economics perspective this is very helpful for those kind of applications and the other cool thing of course is Aurora Storage can grow on its own so you don't have to worry about that and as I said as soon as your application goes away guess what so can your box right to illustrate this we ran a benchmark where we just sort of we're pushing harder so the the blue line is the number of service compared units that have capacity and the orange line was CPU so I started the benchmark I'm basically at zero right so I'm not doing anything as soon as I connect and start pushing we see right and then I continued to increase the benchmark so it continued to ask for more and more CPU and we just kept scaling up right till we got to the largest size that I capped it at 64 units and you can go all the way up to 256 units ie the largest blocks we have like an hour for 16 Excel and then basically I ramped the same benchmark down and it scaled back down and then I stopped it and guess what my instance went away right so this is really useful as a tool for lots of different ones especially for a lot of people who do IOT based stuff where it's completely in predictable demand this is a great great solution so this is all great people say or I like it how do I get to it well so that's migration right so there's four primary methods to come in if you're on Postgres one of the things you can do is pg dump restore I'm not going to talk about that in great detail because most people are pretty familiar with those data migration service I'll cover snapshot import was our initial implementation for moving from RDS Postgres but I'm gonna really talk about the read replica model because I think it's a superior method for moving in so DMS is a really cool tool I was so excited when we built this we had a lot of customers talk to us about moving from either one database the same you know moving from Postgres to Postgres but in a lot of cases customers wanted to move from oracle or sequel server to Postgres right so any of these engines are supported on the left there and all you do is you fire up a Postgres instance and or or Postgres instance and then you fire up a DMS instance and you configure DMS and you tell it go connect to this database figure out what things to you know pull from it and once you do that it will basically do a consistent select where it does a full load right now this is fine but that takes quite a while to run right and so in the meantime your application is still doing stuff that's the magic of logical replication and DMS is it uses the change data capture and all these engines to basically allow you to catch back up from when you started that copy right so then your database gets to the same state as on both sides so once it's essentially caught up you stop the application and you started on the other side right and this is a great way to migrate in especially if you're coming from a different engine on the other hand if you're in RDS there's a easier way if you're an RTS Postgres you have your application you already has Postgres instance you basically asked us to do a migration or be in a reed replica in aurora we take a snapshot then we have to do some conversion to make it into aurora so that's what happens there and once that's done you know that takes a tiny bit of time tiny bit of time a little bit of time but you again have done more transactions right so we actually make it a reed replica so it catches up via a synchronous wall replication just like a normal Postgres instance so at this point it's just like you have a normal reed replica just happens to be Aurora right so you can keep running it for a while you can choose when to migrate but once it's all caught up you essentially need to just stop your application let the last bit of wall flow through the system right and then you can stop the replication and promote the aurora Postgres instance right so we've had people do this migration in a couple minutes from RDS Postgres so this is really nice if you want to move from RDS Postgres to aurora Postgres yeah and so that's how you can get into Aurora as a note we have some more breakouts on these subjects the first one obviously Tuesday that one's already gone by tomorrow we have a talk by some of my colleagues on RDS Postgres and sort of Postgres in the AWS universe it's gonna talk more about Identity and Access Management and some of the other features we've done across both engines and then my colleague David who's sitting here in the front he's going to be with another one of my colleagues Jim doing a deep dive on performance on Thursday in Oriya so with that thank you very much [Applause] I'll happy to take some questions off to the side now and the other thing is if you have any other general RTS questions we have people down at the booth all all-conference long so we have lots of engineers there so if you have other questions you can come down there and talk to us there

Channel: Amazon Web Services

Views: 8,591

Rating: 4.7916665 out of 5

Keywords: AWS, Amazon Web Services, Cloud, cloud computing, AWS Cloud, PostgreSQL, Amazon, Aurora, RDS

Id: --jED3W8Qy4

Length: 75min 24sec (4524 seconds)

Published: Mon Jun 17 2019