Scaling Heights: Mastering Postgres Database Vertical Scalability... Gabriele Bartolini & Gari Singh

Summary

Gabriele Bartolini and Gari Singh dive deep into the world of PostgreSQL database scalability on Kubernetes. They explore the nuances of vertical and horizontal scalability, emphasizing the importance of efficient storage management and leveraging Kubernetes' capabilities. They further discuss the use of cloud native PG and demonstrate its practical applications with real-life scenarios and benchmarking tests. The talk champions innovative scalability solutions while stressing the necessity of consistent benchmarking to tailor solutions to specific environments.

Highlights

• Discover how PostgreSQL harnesses Kubernetes for enhanced scalability 🌟.
• Gabriele and Gari highlight the importance of PostgreSQL's extensibility in its success 🔧.
• Learn about vertical vs. horizontal scalability within Kubernetes environments 🔍.
• Explore real-world applications of PostgreSQL scalability through detailed benchmark tests 🧪.
• Unveil the secrets of using cloud native PG for seamless PostgreSQL management in Kubernetes 🎇.

Key Takeaways

• Learn how to effectively scale PostgreSQL databases on Kubernetes with Gabriele and Gari 🚀.
• Vertical scalability focuses on maximizing single-node resource potential, unlike horizontal scaling which involves multiple nodes 🔄.
• PostgreSQL's extensibility is a key factor in its wide adoption and adaptability 📈.
• Cloud Native PG facilitates PostgreSQL deployment on Kubernetes, simplifying complex processes 🌐.
• Benchmarking is crucial to adapt and optimize database performance for unique organizational needs 🏅.

Overview

PostgreSQL has been a dominant force in the database management arena, thanks to its extensibility and adaptability. Gabriele Bartolini and Gari Singh emphasize how PostgreSQL thrives by leveraging Kubernetes for both vertical and horizontal scalability. They delve into strategies for optimizing database performance while ensuring efficiency and reliability.

The talk introduces the concept of cloud native PG, a tool that simplifies the deployment and management of PostgreSQL databases on Kubernetes. By integrating Kubernetes' native features, users gain enhanced functionality to manage database operations effectively, making it a preferred choice for many organizations.

Benchmarking emerges as a crucial practice advocated by the speakers. Whether it's testing in varied environments or tweaking configurations to suit organizational needs, consistent benchmarking can help tailor PostgreSQL performance to meet specific demands. This holistic approach ensures that systems not only scale effectively but also maintain high performance.

Chapters

• 00:00 - 01:30: Introduction and Initial Questions The session begins with a warm welcome and appreciation for the attendees' participation at 5:25 on a Thursday. The speaker acknowledges their presence and energy at this time. The session promises to be exciting and begins with the introduction of the speakers. The intention is to start the session with a few questions for the audience. There's a light-hearted moment about using a 'manual clicker.' The first question posed to the audience is about the number of participants currently running databases on Kubernetes, indicating the session will delve into topics related to Kubernetes.
• 01:30 - 03:30: Overview of PostgreSQL on Kubernetes The chapter introduces the topic of deploying PostgreSQL on Kubernetes. It addresses the interest of the audience in this subject, possibly indicating a lack of familiarity or experience with it. The chapter promises to cover various deployment methods, including scaling techniques and handling single primary deployments using Kubernetes. There's an emphasis on the intriguing aspects of PostgreSQL when integrated with Kubernetes.
• 03:30 - 05:00: Speaker Introductions The chapter begins with the introduction of speakers involved in the discussion on scaling databases. Gary Singh, a product manager at Google, introduces himself modestly, mentioning his secretive nature. He then introduces his co-speaker, Gabriella, acknowledging her as the cooler of the two.
• 05:00 - 06:00: Agenda Overview In this chapter titled 'Agenda Overview', Gabriel Bini, a vice president of cloud and Kubernetes, introduces himself. He shares his enthusiasm for using pgus, a technology he has contributed to and been involved with since the early 2000s. He positions himself as a data on Kubernetes Ambassador and a DevOps professional, expressing how being part of this community is a dream come true for him.
• 06:00 - 07:00: Recognition of PostgreSQL The chapter discusses the evolution and recognition of PostgreSQL within the Kubernetes ecosystem. It begins with the speaker recounting attendance at their first KubeCon in 2019 alongside Marco, a maintainer, where they brainstormed the creation of an operator for PostgreSQL leveraging local storage, which was unconventional at the time. They faced skepticism from the community but persisted. The chapter marks the birth of Cloud Native PG in August 2019 as a significant milestone in this journey.
• 07:00 - 09:00: Vertical Scalability in Databases The chapter discusses the background and experience of the speaker, who is a co-founder and maintainer of cloud npg. The speaker has a history with pogus and Barman, contributing significantly to these projects. The chapter highlights how the experience gained from Barman has been integrated into Cloud PG.
• 09:00 - 11:30: Cloud Native PostgreSQL Architecture The chapter 'Cloud Native PostgreSQL Architecture' begins with a focus on introducing the concept of vertical scalability within PostgreSQL, an area where the speaker has significant development experience. The agenda outlined includes managing PostgreSQL in Kubernetes using a tool called cloud npg, and exploring techniques for vertical scaling of PostgreSQL through storage solutions. The chapter also involves Gary, who will present benchmark results, concluding the session with those analytical insights.
• 11:30 - 15:00: Techniques for Vertical Scaling The chapter discusses the recent recognition of a database, referred to as 'pgus,' which has been named 'Database of the Year' by DB Engines. It also holds the top rank as the most popular database management system according to a recent survey by Stack Overflow. A significant point for its success is identified as one of its foundational features. The transcript, however, does not specify what this feature is, leaving its nature open to interpretation.
• 15:00 - 19:00: Introduction to Table Partitioning PostgreSQL has always been extensible, allowing for customization through user-defined data types and functions.
• 19:00 - 24:00: Benchmarking PostgreSQL Setups The chapter titled 'Benchmarking PostgreSQL Setups' discusses the evolution and adaptability of SQL over decades, particularly highlighting its persistence as a reliable database language amidst emerging technologies. It emphasizes PostgreSQL's ability to integrate structured and unstructured data formats, such as XML and JSON. The chapter also mentions PostgreSQL extensions like PostGIS, Timescale, and PG Vector, showcasing these as innovations that keep PostgreSQL at the cutting edge of database technology. These tools help enhance PostgreSQL's functionality and ensure it remains a relevant and powerful option for diverse data handling needs.
• 24:00 - 29:00: Benchmark Results and Analysis The chapter titled 'Benchmark Results and Analysis' primarily discusses the demand for AI and analytics workloads. It emphasizes the importance of enhancing pogus databases to address critical use cases associated with these technologies. The goal is to integrate as much data as possible into AI workloads and analytics to improve their efficiency and effectiveness.
• 29:00 - 32:00: Conclusions and Takeaways The chapter discusses vertical scalability within the context of managing a Kubernetes node, whether virtual or physical. It highlights the importance of the node's resources such as CPU, RAM, and especially Storage, which is deemed the most critical component for a database. The chapter also specifies a focus on directly attached storage.
• 32:00 - 37:00: Q&A Session The chapter titled 'Q&A Session' discusses Kubernetes and its approach to storage. It highlights the flexibility and freedom offered by Kubernetes, including the ability to run bare metal with locally attached disks. The main objective is to maximize the potential of a single node within a database framework, and if needed, increase the resources. The discussion emphasizes the concept of scalability and resource optimization within a Kubernetes environment.

Scaling Heights: Mastering Postgres Database Vertical Scalability... Gabriele Bartolini & Gari Singh Transcription

• 00:00 - 00:30 hey thanks everybody for uh joining us at uh 5:25 on uh on uh Thursday I guess it's good to see you're still awake um we've got a pretty exciting session for you today you can read the title here um we'll introduce ourselves in a second but we just wanted to start out with a few questions hopefully we can click it to it that way maybe we have there I have a I have my new manual clicker um how many people here hopefully you're here for this are running databases on kubernetes
• 00:30 - 01:00 today awesome and then on the next one of course if you'll be interested how many of you are actually running postgress on kubernetes today I guess that's why you're here because that's what this talk is about um all right so what are we going to sort of cover today uh there's a couple of interesting ways of deploying postgress um so we're want to talk about how do we actually scale postgress and some of the cool things we do kubernetes so how can I deal with like a single primary deployment how do I scale that right many people may think
• 01:00 - 01:30 just add more replicas um so that's going to be the key thing and then the second part we're going to talk about is just in general how do we scale these sort of databases now who are we uh my name's uh Gary Singh I uh I don't describe myself very much because I'm very secretive so I'm just a product manager at Google and I'm going to pass it over to Gabriella who is much cooler than me thank you Gary let's see if this works I don't see yeah it doesn't work so need to stay here yeah it's okay I'll
• 01:30 - 02:00 stay here that's okay so I'm Gabriel bini I'm vice president of cloud and kubernetes ATB and uh I I don't know I mean this is kind of a dream for me you know I've been using pgus for many many years uh since uh early 2000 I'm a pus contributor I'm also data on kubernetes Ambassador and devops is actually what
• 02:00 - 02:30 led me to kubernetes and my first Cube con was in 2019 and I was with Marco was here with me one of the maintainers and we started to think about these operator for Posas using local storage you know like we had it been done for many years outside kubernetes and people thought we were crazy so I'm really happy to be here today you know after this journey that's when Cloud native PG basically was born there was August 20 2019 so and I'm a
• 02:30 - 03:00 proud co-founder and maintainer of cloud npg and previously I don't know if you're familiar with uh pogus how many of you need know Barman okay I'm the one that came with the came up with the name and you know and uh again with Marco I'm one of the creators of this project and we put basically all the experience we did with Barman we've put it in Cloud PG as well as the the experience we we gained with
• 03:00 - 03:30 rep manager of which I was one of the early developers that's what we've put inside so the agenda for today is uh we'll introduce vertical scalability with Posas first and then how to manage pogress in kubernetes with cloud npg and then show some techniques to vertically scale pogress through storage uh then with with Gary we'll show some Benchmark results and uh we we'll will'll uh finish with the
• 03:30 - 04:00 takeaways so pgus has recently uh received significant recognition and it's been named database of the Year by DB engines and it's also holding the top spot as the most popular database management system according to stack overflows uh lasest survey in my opinion a key factor for this you know pogus success is a foundational feature that
• 04:00 - 04:30 pgus has had from day one extensibility so uh with extensibility we can basically tailor our database using uh data types that we create or or functions that use these data types so basically what I've seen in you know real life over the years is that pogus has continu continuously evolved and it's learned from all the Technologies from different domains that were were r ing throughout you know these two three
• 04:30 - 05:00 decades um and the constant has been SQL so I've seen for example xtml coming up Json you can actually mix structured data and structured data in pogress and extensions like postgis time scale and the latest addition PG Vector that H keeps Posas at the Forefront of innovation so given the increase ing
• 05:00 - 05:30 demand for AI and analytics workloads our Focus today I had to put AI sorry okay our Focus today is to offer insights on uh how to enhance pogus databases um to cover these critical use cases okay so the idea for us today is to bring as much data as we can to to um uh Ai workloads and Analytics so let's start with the
• 05:30 - 06:00 vertical scalability in the context of Fus so imagine this scenario you you are managing a kubernetes node could be virtual uh virtual machine or physical doesn't matter this nod comes equipped with uh uh its own set of resources CPU RAM and most importantly uh Storage storage is the most critical uh component for a database and I'm also talking about directly attach storage don't think that
• 06:00 - 06:30 in kubernetes you know storage is needs to be shared you can run bare metal uh with locally attached dis you can do everything it's Pure Freedom so our objective here is to fully maximize the potential of this single node within a database uh framework and if necessarily upscale the resources so uh this concept in database
• 06:30 - 07:00 technology and also I mean computer Computer Sciences is known as vertical scalability however when we think about kubernetes the prevailing notion suggests that scaling a database uh across um multiple nodes is actually simpler but that means coming to compromises compromises in terms of consistency availability of performance for example but do we really need that so this is this is kind of the question
• 07:00 - 07:30 and this approach is referred to as horizontal scalability um so in in any case today we focus on vertical scalability and you can scale vertically uh using all the resources of of a single node CPU Ram but today we'll focus on storage so before we delve into uh the the specific I want to quickly recap
• 07:30 - 08:00 what cloud native PG uh does and some you know a a reference architecture for running pogus in kubernetes if you want to know more there's a QR code there you can scan it and you can get redirected to a Blog article that I wrote uh in the cncf blog about the recommended architectures for pgus in in kubernetes so today we'll mention this um architecture for a single cluster so I
• 08:00 - 08:30 mean we're talking about the free availability zone or plus uh kubernetes cluster so it means that your single point of failure with just this setup is a region which is huge for a database and kubernetes simplifies all the business continuity uh plans for you okay thanks to this self feeling and high availability approach and the what I'm going to show you is um is is provide very uh High
• 08:30 - 09:00 results in terms of recovery time objective and Recovery uh Point objective RTO and RTO so we have three availability zones with one worker node dedicated for Posas in each availability zone so we position the primary for Posas in one worker node and it comes with its PG Dasa persistent volume PG data is where by default all posg files are located and if we want to we can add another volume to separate transactional
• 09:00 - 09:30 logs uh in another volume and then also use table spaces which are a way to and we'll see it to to to uh um add more more space for POS we can use uh Native streaming replication for Posas with synchronous standby synchronous replication so you have a synchronous standby and a potentially synchronous one then we provide a read bright service and the read only service to access the standby so this is by default the architecture
• 09:30 - 10:00 you get out of a very basic U cluster in in clpg in any case today we will focus on on this so what we'll try to do is that uh we we're trying to adopt a scientific approach here today and uh you have to understand that your organization is unique so you have your unique people your unique uh um systems your unique
• 10:00 - 10:30 data and the idea is that you can only choose through a scien scientific approach and let your basically let data dri drive your decisions so the idea here is to Benchmark Benchmark and Benchmark not only the database but the storage so at the end we will provide um some results so now it's your turn Gary back back to me um
• 10:30 - 11:00 yeah so now let's talk a little bit about how many people here are familiar with Cloud native PG man this's a good there's a good audience all right well cool you got the next one oh yeah so just for those who may not be um Cloud native PG it's a level five production ready kubernetes operator uh which is fantastic um it's already in use in a number of places obviously from edb's big animal uh at IBM's Cloud pack we have uh in a Google marketplace Tempo uh you can read you can read all the chart but you know fully open source V neutral uh created
• 11:00 - 11:30 by EDB it's great um uh for working on that multiple deployment options straight from a manifest for those who love to use whatever your G Ops tool might be is this working again we'll see we try yeah we'll try again and uh you know as well as uh oh from from operator Hub and you already saw the kind of the results on this um super popularity in 2023 how many stars we have 3,000 stars on this already so um this is great you can check out the uh the link there down at the bottom if you want to capture that
• 11:30 - 12:00 it's not working not working we tried the uh the really nice thing um being the fact that I am a love kubernetes right this is like the simplest version of the cluster resource um I'm sure you've all seen kubernetes manifest but um this is super simple to get you know up and running right I mean how many people have seen you know tried to do stateful set stuff themselves and configure everything themselves um and then obviously we can move to the sort of an operator model um this is pretty nice right right we basically just say I
• 12:00 - 12:30 want a cluster want to name it how many replicas do I want and storage obviously we'll talk about some more configuration parameters um but that's you know about as easy as it gets right to get up and running uh on the next chart guess we'll have to move that over um I guess let me ask this question how many people are if I had a beard I would be a Gray beard that's why I shave it um how many folks actually used to work with database in the days before we had kubernetes and VMS yeah do you guys remember creating
• 12:30 - 13:00 you know raid disc arrays mounting raw volumes trying to figure out how to map everything for optimal rights and everything like that and that's a lot what you might see from your dbas so how do we make that easier you know Gabriel said we can do we can mount whatever we want on kubernetes nodes right um but can we make it much easier for you right so obviously I'm sure everybody's familiar with Dynamic provisioning um you know this is great um with storage classes we can have separate volumes right um and with this operator we're
• 13:00 - 13:30 actually doing direct management of the storage itself not having to deal in the instances themselves not having to deal necessarily with the stateful sets obviously there's going to be some mandatory volume that must be created um and you'll see when we get to some of the performance testing just like you used to you know have to optimize for where where you want your logs written where you want you know different uh storage spaces um you can do this for the right ahead logs as Gabriel mentioned um and you can of course divide things up into numbers of table spaces uh the beauty of this is too
• 13:30 - 14:00 obviously under the covers we're leveraging all the I guess you call I call kubernetes Magic um but again you can more easily configure this without having to do it yourself through just a manifest definition from the from through the operator manifest here um as we cover there I think the other main thing to to look at is you know learn a little bit the only thing you have to know is a little bit about what your CSI provider does and what your actual storage you know your backing storage is right is it an SSD is it fast whatever whatever may be going to have to use those characteristics and you'll see
• 14:00 - 14:30 that that's why why testing becomes important on those because you may need to look you know trade-offs between performance cost and efficiency um and of course we have the uh some of the great work that's been done I think in kuber indes lately volume snapshots you get a lot of stuff for I'll call it for free um that's sort of in there which makes it easy right because now we're just using native kubernetes volumes and we can just use snapshotting capabilities and and then there's obviously there's other backup and Recovery Technologies as well that work in there uh it makes it super simple yeah that's actually a very good point
• 14:30 - 15:00 Gary it's about leveraging what kubernetes already provides this is kind of one of the pillars of of clpg so thanks for yeah I think the um I mean we we've seen you know I think just we won't go too much into it but there's many times where people try to figure out how to how do you map I guess you know normal constructs to kubernetes constructs right I think kubernetes is evolved enough to actually support these workloads and we have that sort of great mapping um and before Gabriella goes into uh some details on these things we
• 15:00 - 15:30 thought we just introduced some Concepts in case some of you weren't familiar with them uh the concept of table spaces right these are sort of these Global objects that you can have in postgress um they're typically going to be used to how you might want to divide up volumes typically like on on the simplest version they'll map to either you know a directory or it could be an actual you know raw disc or whatever you know on there um the typical use cases for these obviously store temporary files divide up uh logs do things for separate discs and as we said because basically dynamically provision class
• 15:30 - 16:00 storage provider classes right are typically going to create another dedicated disc in kubernetes hopefully that's the way you set things up you know on Prem in the cloud that's typically how it works you create a new storage volume um it's typically going to map to whatever a new solid state disc or persistent disc or whatever it may be but those are going to be dedicated to you with multiple things mounted to your actual underlying nodes um and this is beautiful um and we'll talk about how to configure this uh pretty simply with just a table space to stands right just add as many of those
• 16:00 - 16:30 as you want and I think you're next yeah so thanks Gary so now we'll start exploring some techniques so if you've been using pus outside kubernetes this is all stuff you know this is stuff we've been doing for many many years and I love the fact that now we kind there's a new wave of of us explaining this stuff you know so uh this section bywise the Cornerstone of of of this pre presentation so the previous slide uh
• 16:30 - 17:00 Gary told you about how Cloud native PG um offers a simp simless approach to scaling at the storage level so we can create additional volumes for walls and also table spaces so you've got flexibility here you can customize storage classes and also optimize cost efficiency and iio bandwidth uh for specific volume purposes so also the fact that kubernetes is working through annotation to control bandwidth and and optim
• 17:00 - 17:30 optimize the specific volumes is great because for us it's just adding one one annotation in the configuration so volumes can be added to uh live clusters and they can also be resized if the storage class supports it and uh this this basically gives us U tremendous adaptability and and scalability in in case you know we need we need to grow so
• 17:30 - 18:00 um just to recap the primary advantages of scaling with volumes it's not just performance isolation but also predictability of performance this is very important to know what's what you can expect from from from your uh storage and also distribute uh uh queries across multiple volumes and also simplify and make more more efficient database operations like vacuum or index indexing or
• 18:00 - 18:30 reindexing so the the lovely way of doing this in clpg is that you just need to add two lines and basically here we say to to to clown PG create a new volume for walls and using the default storage class and here is how you add basically a temporary table space so a temporary table space called TM ptbs and basically we are telling posg to add
• 18:30 - 19:00 this volume this this table space in the temp table spaces uh uh configuration option of POS so the the operator does that transparently for for you you can add more so it's really interesting and uh a widely used technique uh which is particularly effective if your database is simple is uh to involve separating I/O for uh
• 19:00 - 19:30 operations for tables and indexes so in this uh very simple example we create two table spaces one called data and one called idx and uh with the with the um SQL statements on the right side we can create for example a table and say this table needs to be in the table space uh data and we can also create indexes or constraints in general through the use using index table space uh um statement so all of
• 19:30 - 20:00 these really if you have large databases can can can sorry the same Technique we we'll see it in larger databases but for simple databases this is already um performance Improvement so let's try uh with a very simple example I probably you have uh all dealt with uh uh web access logs and this is an example of access log I will
• 20:00 - 20:30 use the time stamp as uh you know our our um most important uh uh um Dimension here and this is this isn't just Theory this is actually something that we my colleague Jonathan Gonzalez we've done in the past and uh is a fluent bit maintainer as well and we we actually uh use fluent bit to parts and store the this this table in pgus
• 20:30 - 21:00 so uh as time passes the fact table that I showed before expands it accumulates every uh every month new data and so think about think about how frequently you access um old or versus new data uh so think about this access pattern is new a data typically accessed more often so these are kind of questions we we need to ask yourselves also consider the
• 21:00 - 21:30 scenario where the poest planner decides that uh to retrieve a specific month it's actually faster to do a sequential scan sequential Full Table scan and uh also what happens every time you update or delete uh a record what happens to the to the index you are sharing the same index with all the records of the table or when you need to remove an entire month so this is pretty M pretty much the main cause of
• 21:30 - 22:00 bloat uh of your pogus database when you remove uh a lot of data like that so the database over time becomes less and less effic efficient so this cannot scale so the solution uh to this common problem is uh known in the database um industry as horizontal table partitioning so this is very common in data warehousing come from the data warehousing world and in general very
• 22:00 - 22:30 large database environments essentially this technique um involves slicing uh table records horizontally and spreading them across different tables these tables are known as partitions so basically what we do is we create this kind of abstract table called uh partition table from which we derive the the the concrete tables that
• 22:30 - 23:00 are the the partitions so basically each month resides in its own table and uh with with its own indexes so over time these tables become pretty much uh uh read only and maybe they're less frequently accessed so the the indexes don't need any more updates so the cool thing is that uh partitioning can also be comp combined with table spaces allowing all the data
• 23:00 - 23:30 to be moved to to to cheaper storage so essentially the partition uh partitions are kind of a first level index so that routing of of of inserts and queries is more more efficient and uh um retrieving for example the data of a whole month is much faster than than before uh if you want to remove a whole d a whole month of data you see simply drop drop the table so you don't have to
• 23:30 - 24:00 update any more indexes uh the cool thing is that out of the box POS Open Source comes with all this stuff and that um you can actually achieve partitioning by range list hash and also have sub partitioning anyway declarative uh partitioning is a complex topic you can study more by yourself I'll give you an example here on how to Partition by rank range using the time stamp this is the
• 24:00 - 24:30 partition table and this is how you create partitions this all through SQL and this is how you can for example put the current data in uh fast uh table space fast volume and progressively move all data in cheaper storage and and uh and basically achieve uh optimization of cost and and performance this is how you can set the table space by the ways I
• 24:30 - 25:00 shown before don't worry you can alter uh the table at the later time and move the the data in in another uh uh table space now the cool stuff yeah this is pretty cool the uh maybe you won't uh so you know we mentioned that well Gabriel mentioned that uh we really have to you know think about benchmarking things for you know your specific workloads and things like that um before we start talking about obviously doing these repeatable benchmarks text tests I'll just go back and highlight and again I
• 25:00 - 25:30 said I mean I love kubernetes so I always will throw throw the value back in there um I think it's super simple is my my technical term to iterate on this stuff right because as we showed to change how you configure things it's simply simple stands those are Fields right within the within your um within your crd right so if you want to try if I want to do a right am I going to have put the wall separately right am I going to do table spaces right you can continue to iterate on this stuff and especially in cloud cloud envir irirs uh it's much easier right you don't have to
• 25:30 - 26:00 worry about where that storage is mounted so it makes it very easy to itely test and try out these different scenarios so obviously you know the key things here start small right uh start with kind of a single instance of a cluster uh which makes sense right um this is pretty cool there's a tool PG bench uh I think the link down there talks more about running this yourself and how to use this Tool uh but this makes this easy to reproduce even the results that we'll show in the next one um the other key thing in your test so you don't skew your results uh
• 26:00 - 26:30 recommendation was 4X the size of memory so that you're actually forcing checking your actual disc performance and not checking your memory and caching performance um and the beauty of this is with this link down below again to rerun this anybody can run set up and run your test on a kubernetes environment your own anywhere in the cloud uh so on the next slide I think we'll talk about the uh simple base specifications here we're going small um note you know these aren't the this is nothing small I just one of call that out I know Gabrielle will get mad at me
• 26:30 - 27:00 1.5 is n yeah I mean postgress can do whatever needs to do um we're going to do a p this bench oltp processing here um again here was the sort of size right I won't read it all out but you know 4500 66 gigs I'll do 16 clients uh and a simple sort of sort of round tripping uh and then on the next one I think we talk about what were the scenarios that we tested so we wanted to try out a few of the various like techniques that are in there do we just do a single volume
• 27:00 - 27:30 what's our sort of performance look like that probably call that your Baseline do we dedicated volume for the right ahead logs do we look at table spaces for data and then do we do even do uh for the for the indexes sorry and then do we actually do the the last one that was shown which was sort of uh partition the data and have table spaces there the results are um pretty interesting in this particular case um maybe they're not as you know surprising obviously uh scenario 2 which you can see sort of
• 27:30 - 28:00 highlighted um worked well in three you know I guess three scenarios really um sort of a bare metal scenario uh there were two on uh on Google um and that's just because we have you know between us and Amazon and other ones everybody has different storage and different storage classes and different backing scenario two as you remember was separa in the right ahead log out which typically would make sense right but the performance is pretty significant right and then as a small thing we even ran two tests in Google um which was using just standard PD or
• 28:00 - 28:30 SSD uh maybe the difference here on SSD and PD at this scale wasn't like significant enough that maybe you'll decide hey it's good enough for me and I'm not going to pay the extra cost um but still a pretty good things yeah and we also have to remember that we just using 1.5 cores okay so this is really so if you if you scale with the CPU results could be could better you know but yeah yeah we're not going to have parallel rights to dis and things like like that right if you're you're still use you're still
• 28:30 - 29:00 Contex sharing the same CPU but then the other interesting result was that it turns out that in the eks case for example uh scenario 3 was actually better right in terms of it's improving it oh that was the biggest Improvement for it so I guess the the tldr is you know kind of test in your environment and where you are right but again it's fairly simple we just use the same test here to run this you know on these same cluster spin them up in this environments take your configs deploy it uh and you're ready to go then people that have done also tests on Raspberry
• 29:00 - 29:30 pies and you know 250 transaction per se I like that um the key outcome here I kind of I always get ahead of myself um but that's that's fine um in this particular case uh PG data and the right ahead logs you can see the improvements that we saw uh also depending on which sort of disc type we used um again we uh Gabriella highlighted that this is only 1.5 core so it's kind of uh maybe won't test as much if if we were separating out in partitioning by table spaces but
• 29:30 - 30:00 there was still improvement over the Baseline it's just that it wasn't as insignificant as the Improvement as wall was in this particular case um and again storage capabilities are important right um but I'll just leave it with it's really nice to be able to just run these tests kind of quickly right it doesn't take much to set this up kubernetes cluster is up and running you can pick your default storage classes you can specify your storage classes and then you can specify how you want to divide things up pass it over yeah and I want thank
• 30:00 - 30:30 people so the good story about this is that from now we can actually uh everyone can test this stuff and I want to test uh the people here for having helped me uh produce this this Benchmark and as I was saying we're just uh scrapping the surface now so this is an unexported territory for everyone this is just for example a slide that Sagi uh from light bits did using uh TCP nvme over TCP and this is just basic
• 30:30 - 31:00 performance okay this this is just the starting point we're talking about 15,000 transaction per second okay to start with okay um so conclusions um we cover this four um four primary uh sections and uh so lesson learned today is that storage I hope you understand it's the is the most critical part for a database in vertical
• 31:00 - 31:30 scalability but do your benchmarks know your goals so know your goals in terms of RTO RPO don't forget that you have to back up and restore and you have to ensure ey availability so all of this is included in this okay so pgus I hope you saw today can scale up uh through volumes my recommendation is to use shared nothing architectures so maybe consider uh placing Posas in nodes
• 31:30 - 32:00 separated from applications but running in the same kubernetes clusters and uh there's no one size fits all but that's also the good part that it's on you the work is on you because again your organization is unique so all all you have you you have an amazing set of Technologies in my opinion you've got kubernetes you've got pogress and I I like to say you've got also Cloud native p now and you can
• 32:00 - 32:30 you're free to run it everywhere uh private public hybrid multicloud bare M VMS and uh using local or network discs so uh last thing join uh our data on kubernetes community if you want to know more about stateful workloads and also the the cloud native PG uh community so thank you
• 32:30 - 33:00 and questions are there any questions think they I think they learned everything they needed to know today so and it's 6 o'clock hello I have a question about uh the backup and the fact that you split the data into several volumes yeah if you do snapshot of dis you don't have a
• 33:00 - 33:30 c cerence snapshot thank you for the question okay so we we are pretty much one of the first operators in the database space to support volume snapshot uh backups and Recovery I if you can go to uh Cube Con in Chicago uh you can watch the video that of the talk that I gave with Michelle from Google uh it's called just a recovery about very very large databases in pgus I showed
• 33:30 - 34:00 how to uh restore uh a 4.5 terabyte database in two minutes two minutes okay so the consistent is G is granted by the wall file so essentially when you take uh when you start the backup procedure uh you take a snapshot of all the volumes and we ensure that we copy also the the the the wall file at the start of the backup and at the end of the
• 34:00 - 34:30 backup okay the other way is that we also have a a way we call them cold backups where you can actually uh take a backup from a standby we shut it down temporarily and then you take basically cold snapshot so that's consistent by default and we we spin it up so it's done automatically by the operator okay what we're working on and I would like Leonardo to say stand up please Leonardo is actually working with TX storage to
• 34:30 - 35:00 implement the first uh operator supporting volume group snapshots in kubernetes so kubernetes is working on on ensuring consistency of of multiple volumes at the same time and we are the first kind of pioneers of of these technology so we're really happy it's actually there's already a patch for that so but this is it's achieved so it's it's pogress allows you to to to to
• 35:00 - 35:30 to um basically exploit all of that you know this is a technology that's been in pus for over almost 20 years so it's very stable thank you for the question no more questions okay thank you thanks everybody