Episode 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 Kristen Jameson, and this is scaling postgres episode 49.
[00:00:22] Alright, the first article we have this week is PostgreSQL at scale database Schema changes without Downtime. And this is from the braintree product technology blog. So this post is super useful. So I'm about to say stop what you're doing and go read it, kind of keep track, or at least bookmark this for reference because this is pure gold right here. So basically it goes over 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 to transactional, DDL, locking table operations, different column operations. So this would be changing 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 of consolidates all these best practices and puts them in place for your Rails projects. So that your migrations, your database changes basically adhere to the advice given here. Now, what I'm super surprised about is how low the claps are for this particular article. Because if you have a super 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, so I'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 to watch out for. Definitely a great blog post and I suggest you definitely check it out and keep it as a 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 records per 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 over 2000 claps, as it were, on Medium. And what it is, is a story of how they've built up their PostgreSQL database as they've scaled and the different things that they encountered along the way. 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 go ahead and choose a PostgreSQL. And some of their goals is that they wanted to keep all of their data in one database and try to avoid where they say, here we don't, and presumably didn't want to have was part of their goals a standalone message queue service, a cache service or replicas for data warehousing. So they wanted to avoid that. So they were doing things within the database, the single database to be able to handle everything. And they're from the company Applaudience 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 older version 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 they decided to move to a hosted solution that would host it on their behalf. But eventually they just started self hosting it their own. Now, they mentioned here this is the first time they were using PostgreSQL, and one of the reasons they primarily wanted to use it is to use materialized views because they wanted to try and update everything in the database and not have to worry about a data mart or data warehouse, et cetera. So what's interesting here is that so they went off on this journey, but here's the first attempt at materialized views and then they weren't performant enough or they were taking up to hours once they got to millions and billions of records. So they said, all right, second attempt, divide and conquer. So they split it up to smaller materialized views, but even that was running into problems. So they had a third attempt, moving to a materialized views based upon an abstract data domain. Now, this worked for some, but not for others. So then they actually started materializing table columns. Now, they didn't use materialized 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'm understanding correctly. So you see all through the iteration that they're trying to go to, trying to avoid setting up like a data warehouse or setting up logical replication for doing real time queries from the database. So this is one path that you can go. And I think they took this path because they were initially using materialized view and kept traveling down that path. But at some point you have to wonder, well, would it have been more efficient to go logical replication route to go ahead and have a separate reporting database or other solutions like Citus where you can scale out to multiple servers potentially? Now, that was part of their goals. They wanted to avoid multiple servers, they wanted to have one central database, but it's just something to keep in mind as you're reading this post.
[00:05:33] Then they talked about some of their issues using the database as a job queue. So again, they wanted to have everything in one database and they didn't want to have a separate job queue for handling things like that. But they ran into some issues with performance based upon some of what they were initially doing, like trying to use a four update skip lock. And that worked, but it looked like it wasn't scaling. So then they actually created a dedicated table with the list of outstanding tasks to be done. But that queue of course requires some management and after a task is deleted, the reference to the task is deleted. So they found a solution but it's definitely an interesting read on some of the issues that they ran into. They mentioned some other miscellaneous things, talking about latency between because actually at one point they had their database, it looks like in AWS and then a Kubernetes cluster in Google, I believe, a Google data center. So of course they were encountering performance issues because the data centers 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 around some column order and they got a savings of some storage space. And this was actually a post that was referenced in a previous episode of Scaling Postgres. And they gave some other suggestions here some issues that they ran into and some suggestions to resolve it. Now, it's also important if you're going to look at this post, also look at the comments because some of the comments had some very interesting information as well, particularly this top one here. And they go into some of their issues with using PostgreSQL and scaling and some practices that they adopted to address some issues that they were encountering. So a lot of interesting content and particularly the comments to learn more about how people are using PostgreSQL and potentially avoiding issues with it.
[00:07:22] The next post is Life of an SQL Query and this is from Numeracy Co blog and it basically covers exactly what says here what happens when you run an SQL statement. And they did this for PostgreSQL. So they have this great diagram here of when you're going to select all from users, what actually happens in terms of sending the request, going through the parser, transforming it to the planner, to the executor and then the server response being delivered 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 where the data is on the disk by looking at various errors in PostgreSQL to identify where the data is actually located and then how the response is returned to the client. So if you're interested in learning more about that, definitely a blog post to check out. Now, interestingly, about the same time 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 believe this is a postgres process, how it parses, the statement goes to the traffic pop, goes through the rewrite query, generate paths, generate plan, execute plan and then each of these, this is actually clickable. So if you click on the rewrite query, it actually tells you some more information about what it does. So if you're wanting to learn more about how statements get processed in PostgreSQL, definitely a two piece of content to check out. The next 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 of wall being generated from a master to a or a primary to a replica database node. And you can look at the LSN difference between what the current LSN is and then some different areas to measure 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 difference and 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 a given LSN to determine how much throughput has happened on a PostgreSQL instance in terms of how many wall files have been generated. So in this example, they initialized a medium 100 scale 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. So basically anything producing wall traffic will make this throughput tick higher. So it's not just rights to the instance like inserts and updates, but anything that modifies the contents of the wall. So they're saying this might be another metric that you want to potentially track if you're interested, because maybe you could detect something doing a large bulk insert or particular client activity or things of that nature. So, definitely an interesting blog post to check out. The next post is managing transaction. ID exhaustion or wraparound in PostgreSQL. Now this is of course something you need to monitor to make sure you're not going to run out of TX IDs because then you need to stop your database and basically do a full vacuum on that table. And they have a query here that they use to track percent towards wraparound and percent towards emergency auto vacuum. So basically 2 billion is the limit. You don't want to hit the 2 billion limit and 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 with high transactions rates, it could be beneficial to increase the automaticum freeze age to avoid that. Emergency vacuum period come around quite so often and they've seen cases where often set 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's just something to keep in mind if you're monitoring your database. So if you're noticing you're getting high TXID age for your database, or more specifically in a particular table, you can vacuum that table to resolve the issue. And they talk about the Per table fix here, but really you should just vacuum that table. And they also talk about there's a separate post called Pertable Auto Vacuum Tuning. So you can adjust that to potentially vacuum that table more to prevent transaction ID from coming around sooner. So definitely a blog post to check out to be able to better learn how to monitor your transaction IDs. The next post is a guide to building an active active PostgreSQL cluster. And this is from Crunchydata.com. So this is essentially talking about Master Master Replication. Now, this post is actually about symmetric DS, which is a third party software that uses Java to do the coordination. So this post essentially describes setting up this particular tool. But as I've seen in other content that I've mentioned on previous episodes of Scaling Postgres, a lot of times for using a Master Master Replication, you're going to need to alter your application to be able to handle that situation. In the case of conflicts, and I know in here 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're using sequencing, maybe you need to use UUIDs or start sequences at separate places to ensure no conflicts from that perspective. But if you're interested in using this third party tool to do this, definitely an option to check out. The last post is Webinar Banking on Postgres financial application considerations. Follow up. So this was a webinar that was set up by second quadrant and 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 if you'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 content mentioned in the show notes. Be sure to head over to Scalingpostgres.com where you can sign up to receive weekly notifications of each episode. Or you could subscribe via YouTube or itunes. Thanks.
