Over One Billion, No Downtime, SQL Life, Throughput | Scaling Postgres 49

In this episode of Scaling Postgres, we review articles covering over one billion records per month, schema changes with no downtime, the life of a SQL query and throughput tracking.

Content Discussed

• PostgreSQL at Scale: Database Schema Changes Without Downtime
• Lessons learned scaling PostgreSQL database to 1.2bn records/month
• Life of a SQL query
• [PostgreSQL] Backend Flowchart
• PG Phriday: Terrific Throughput Tracking
• Managing Transaction ID Exhaustion (Wraparound) in PostgreSQL
• A Guide to Building an Active-Active PostgreSQL Cluster
• Webinar: Banking on Postgres – Financial Application Considerations [Follow up]

YouTube Video

Podcast Audio

Transcript

[00:00:00] In this episode of Scaling Postgres, we talk about over a billion records a month,schema changes without downtime, life of an SQL query and throughput tracking. I'm KristenJameson, and this is scaling postgres episode 49.

[00:00:22] Alright, the first article we have this week is PostgreSQL at scale database Schemachanges without Downtime. And this is from the braintree product technology blog. So this postis super useful. So I'm about to say stop what you're doing and go read it, kind of keep track, orat least bookmark this for reference because this is pure gold right here. So basically it goesover all the different things that you would need to do for a super large database at scale,making schema changes without downtime. So they talk about issues with regard totransactional, DDL, locking table operations, different column operations. So this would bechanging tables, altering them in some way, column operations, index operations, constraints,enum types, and then even a library that they built for the Ruby on Rails framework that kind ofconsolidates all these best practices and puts them in place for your Rails projects. So that yourmigrations, your database changes basically adhere to the advice given here. Now, what I'msuper surprised about is how low the claps are for this particular article. Because if you have asuper large database and you're wanting to make schema changes without downtime, like I said,this article is pure gold into explaining the best way to do that. Now, this is quite a long article, soI'm not going to go through it. Trust me that if you're looking to do something, rename an index,the best way to do it, how to handle nut null constraints, how to create the index, and things towatch out for. Definitely a great blog post and I suggest you definitely check it out and keep it asa bookmark for when you need to make schema changes to your larger database.

[00:02:11] The next post is lessons learned. Scaling PostgreSQL database to 1.2 billion recordsper month. And this is from, I don't know how to pronounce the name Kaiju.

[00:02:25] My apologies. I have no idea. Now, in contrast, this post has been super popular over2000 claps, as it were, on Medium. And what it is, is a story of how they've built up theirPostgreSQL database as they've scaled and the different things that they encountered along theway. So from a journey perspective, it's definitely insightful and useful.

[00:02:47] So they talked about some of their initial goals and kind of why they decided to goahead and choose a PostgreSQL. And some of their goals is that they wanted to keep all of theirdata in one database and try to avoid where they say, here we don't, and presumably didn't wantto have was part of their goals a standalone message queue service, a cache service or replicasfor data warehousing. So they wanted to avoid that. So they were doing things within thedatabase, the single database to be able to handle everything. And they're from the companyApplaudience to track Cinema data for Cinema Goers. Now, in terms of hosting the database,they talk about their journey. They tried Google Cloud, SQL, but that apparently is using an olderversion and they wanted some of the additional features. So they moved to Amazon RDS.However, it did not support the Timescale DB extension that they wanted to use. So theydecided to move to a hosted solution that would host it on their behalf. But eventually they juststarted self hosting it their own. Now, they mentioned here this is the first time they were usingPostgreSQL, and one of the reasons they primarily wanted to use it is to use materialized viewsbecause they wanted to try and update everything in the database and not have to worry about adata mart or data warehouse, et cetera. So what's interesting here is that so they went off on thisjourney, but here's the first attempt at materialized views and then they weren't performantenough or they were taking up to hours once they got to millions and billions of records. So theysaid, all right, second attempt, divide and conquer. So they split it up to smaller materializedviews, but even that was running into problems. So they had a third attempt, moving to amaterialized views based upon an abstract data domain. Now, this worked for some, but not forothers. So then they actually started materializing table columns. Now, they didn't usematerialized views for this, but they actually added --columns to the table and refreshed those. So it's refreshing data on the table per row, if I'munderstanding correctly. So you see all through the iteration that they're trying to go to, trying toavoid setting up like a data warehouse or setting up logical replication for doing real time queriesfrom the database. So this is one path that you can go. And I think they took this path becausethey were initially using materialized view and kept traveling down that path. But at some pointyou have to wonder, well, would it have been more efficient to go logical replication route to goahead and have a separate reporting database or other solutions like Citus where you can scaleout to multiple servers potentially? Now, that was part of their goals. They wanted to avoidmultiple servers, they wanted to have one central database, but it's just something to keep inmind as you're reading this post.

[00:05:33] Then they talked about some of their issues using the database as a job queue. Soagain, they wanted to have everything in one database and they didn't want to have a separatejob queue for handling things like that. But they ran into some issues with performance basedupon some of what they were initially doing, like trying to use a four update skip lock. And thatworked, but it looked like it wasn't scaling. So then they actually created a dedicated table withthe list of outstanding tasks to be done. But that queue of course requires some managementand after a task is deleted, the reference to the task is deleted. So they found a solution but it'sdefinitely an interesting read on some of the issues that they ran into. They mentioned someother miscellaneous things, talking about latency between because actually at one point theyhad their database, it looks like in AWS and then a Kubernetes cluster in Google, I believe, aGoogle data center. So of course they were encountering performance issues because the datacenters were trying to talk to each other and it's best to have everything in one data center.

[00:06:33] They talked about some column order issues and then when they switched aroundsome column order and they got a savings of some storage space. And this was actually a postthat was referenced in a previous episode of Scaling Postgres. And they gave some othersuggestions here some issues that they ran into and some suggestions to resolve it. Now, it'salso important if you're going to look at this post, also look at the comments because some ofthe comments had some very interesting information as well, particularly this top one here. Andthey go into some of their issues with using PostgreSQL and scaling and some practices thatthey adopted to address some issues that they were encountering. So a lot of interesting contentand particularly the comments to learn more about how people are using PostgreSQL andpotentially avoiding issues with it.

[00:07:22] The next post is Life of an SQL Query and this is from Numeracy Co blog and itbasically covers exactly what says here what happens when you run an SQL statement. Andthey did this for PostgreSQL. So they have this great diagram here of when you're going toselect all from users, what actually happens in terms of sending the request, going through theparser, transforming it to the planner, to the executor and then the server response beingdelivered to the client. So it talks a little bit about this in detail down here in terms of the request,the parse and transform, the planner, optimizer the execution stage and even determining wherethe data is on the disk by looking at various errors in PostgreSQL to identify where the data isactually located and then how the response is returned to the client. So if you're interested inlearning more about that, definitely a blog post to check out. Now, interestingly, about the sametime it looks like this backend flowchart was released on the actual PostgreSQL website.

[00:08:26] So again it goes through the phase of a request coming in and per request, I believethis is a postgres process, how it parses, the statement goes to the traffic pop, goes through therewrite query, generate paths, generate plan, execute plan and then each of these, this isactually clickable. So if you click on the rewrite query, it actually tells you some more informationabout what it does. So if you're wanting to learn more about how statements get processed inPostgreSQL, definitely a two piece of content to check out. The n --ext post is PG Friday. Terrific throughput tracking. This is from the second quadrant.com blog.Now it talks about here being able to track essentially wall production and what is the amount ofwall being generated from a master to a or a primary to a replica database node. And you canlook at the LSN difference between what the current LSN is and then some different areas tomeasure the sent lag, the write lag, the flush lag, and the replay lag. And if you're using slots,you can monitor restart lag and flush lag. But they're saying you can actually use the differenceand they make an argument here.

[00:09:46] You could use the start point and ask what the difference is from the start point to agiven LSN to determine how much throughput has happened on a PostgreSQL instance in termsof how many wall files have been generated. So in this example, they initialized a medium 100scale PG bench instance and that using this technique generated 1267 megabytes of data.Doing a 1 minute test, increased it to 1356, and then adding an index increased it to 1551. Sobasically anything producing wall traffic will make this throughput tick higher. So it's not justrights to the instance like inserts and updates, but anything that modifies the contents of thewall. So they're saying this might be another metric that you want to potentially track if you'reinterested, because maybe you could detect something doing a large bulk insert or particularclient activity or things of that nature. So, definitely an interesting blog post to check out. Thenext post is managing transaction. ID exhaustion or wraparound in PostgreSQL. Now this is ofcourse something you need to monitor to make sure you're not going to run out of TX IDsbecause then you need to stop your database and basically do a full vacuum on that table. Andthey have a query here that they use to track percent towards wraparound and percent towardsemergency auto vacuum. So basically 2 billion is the limit. You don't want to hit the 2 billion limitand by default 200 million is the point at which an emergency auto vacuum is kicked off.Although you can adjust that if you want. And they have a recommendation for databases withhigh transactions rates, it could be beneficial to increase the automaticum freeze age to avoidthat. Emergency vacuum period come around quite so often and they've seen cases where oftenset to a billion without too much of an issue, but it's just something to be aware of and monitor.Now this emergency auto vacuum just takes a bit more resources at a standard vacuum, but it'sjust something to keep in mind if you're monitoring your database. So if you're noticing you'regetting high TXID age for your database, or more specifically in a particular table, you canvacuum that table to resolve the issue. And they talk about the Per table fix here, but really youshould just vacuum that table. And they also talk about there's a separate post called PertableAuto Vacuum Tuning. So you can adjust that to potentially vacuum that table more to preventtransaction ID from coming around sooner. So definitely a blog post to check out to be able tobetter learn how to monitor your transaction IDs. The next post is a guide to building an activeactive PostgreSQL cluster. And this is from Crunchydata.com. So this is essentially talking aboutMaster Master Replication. Now, this post is actually about symmetric DS, which is a third partysoftware that uses Java to do the coordination. So this post essentially describes setting up thisparticular tool. But as I've seen in other content that I've mentioned on previous episodes ofScaling Postgres, a lot of times for using a Master Master Replication, you're going to need toalter your application to be able to handle that situation. In the case of conflicts, and I know inhere they're talking about managing conflicting rights and it gets stored in a particular table,apparently for you to address yourself because there's always potential conflicts, like if you'reusing sequencing, maybe you need to use UUIDs or start sequences at separate places toensure no conflicts from that perspective. But if you're interested in using this third party tool todo this, definitely an option to check out. The last post is Webinar Banking on Postgres financialapplication considerations. Follow up. So this was a webinar that was set up by second quadrantand you can go ahead and click the link here to get access to it. You should --just be able to fill out a form and get access to the webinar. I haven't viewed it myself, but ifyou're in the financial sector, potentially this is a blog post you want to check out.

[00:13:57] That does it. For this episode of Scaling Postgres, you can get links to all the contentmentioned in the show notes. Be sure to head over to Scalingpostgres.com where you can signup to receive weekly notifications of each episode. Or you could subscribe via YouTube oritunes. Thanks. --