Episode Transcript
[00:00:00] In this episode of Scaling Postgres we talk about ten things audit triggers, schema design and high availability. I'm Creston Jameson. And this is scaling postgres episode 180.
[00:00:21] Alright, I hope you, your coworkers and family are still doing well giving the situation that's still with us but our first piece of content is ten things I hate about PostgreSQL. This is from R Branson at a Medium and I'm just going to run through the different things that he hates about PostgreSQL. Now ultimately he does like PostgreSQL and these are the pain points that he sees and hopefully they will fix eventually but number one is disastrous XID or the transaction ID wraparound. So basically this is you can't have more than 2 billion active transactions and Vacuum takes care of handling this but if you hit this 2 billion mark you basically have to take the table offline in order to repair it and that may mean that of course the database as well. So basically, definitely something you should be monitoring. And I agree it is an unfortunate thing having to monitor for this, particularly for larger databases. And hopefully at some point, as he mentions here, the Xids will be using say, something like a 64 bit integer but until then, we're pretty much stuck with it. So definitely have to agree with him with this.
[00:01:35] Two is failover will probably lose data. Now he's talking about you're using streaming replications so this is streaming the wall files from a primary to a Replica and normally when you set that up, the default way to set it up is asynchronously now there's a possibility that you're going to lose some data when say the primary goes down. Now, you can do synchronous replication and you can even do it with a quorum commit like it has to commit on so many replicas. But then you're going to be taking a performance hit and particularly if you're trying to spread your databases across, say, availability zones in the case of AWS, like multiple regions, you're going to further take a performance it if you try to do that using synchronous replication. So if you're going to use it, it probably makes sense to do it within the same availability zone or within the same region, but then synchronizing to other regions. Do it asynchronously if you're going to try to attempt that but basically saying there's no really a great solution for this. He mentions some other types of clustering solutions you may want to investigate if this is important to you. And we also cover something about bi directional replication later that has its own set of issues, but that would be another way you could investigate trying to really avoid losing data. But we'll cover that in an upcoming piece of content in this episode. So I do have to agree this is an issue that people will have, and there is a performance hit with synchronous replication. Not quite sure how to necessarily avoid that. Three is inefficient replication that spreads corruption. Now, I wasn't really quite sure what he was referring to here. He talks about the wall and then with a large database, they had something that led to hardware induced data corruption on the Master, much more easily propagating to Replicas. So I was a little confused. So some hardware caused the corruption, so I would blame the hardware as opposed to Postgres. And yes, if you are replicating data from a primary to a replica, any data corruption will probably be passed through to the Replica. So I don't really get necessarily where he's going with this. And this is with essentially wall based replication, doing streaming physical replication as opposed he says logical replication may avoid some of this, but I've never seen kind of what he's mentioning here if I'm interpreting correctly. Number four is MVCC garbage frequently painful? Now essentially he's talking about a vacuum. And when you're say doing a lot of updates on a certain table, it's actually creating additional rows and the dead rows essentially have to be vacuumed up. And I definitely have to agree with him on this because many developers engineer something with a lot of updates. And Postgres can run into vacuum issues when you set it up that way. But he does mention Zheep. So of course this is a storage solution that adopts a technique that MySQL and Oracle use using redo or undo logs. So essentially using this type of log allows you not to have to create a whole new row, but you can do an update in place and indicate that that row has been updated. So if you need to reference what that value was or do a rollback it's within the redo or the undo logs. So I do have to agree a vacuum can be painful, but there seems to be hope with Zheep. So we'll see what the future holds with regard to that. Next is process per connection equals pain at scale. And essentially this is when you get up to a couple of hundred connections. Because it's a process for every connection, it uses a lot of resources and basically people look to connection poolers to help mitigate that and to get more connections to the database. And for example, PG Bouncer is the connection pooler I have experience with. But it would be great if Postgres eventually had a threaded means of supporting more connections out of the box without having to rely on PG Bouncer. Because even though PG Bouncer is great, it had some of its own things you need to be aware. Like for example, it itself is single threaded. So if you have a multi CPU box and want to run PG Bouncer on it and utilize all the cores or the CPUs of the system, then you have to run multiple instances of PG Bouncer and funnel your clients through each of those PG Bouncers running on different ports. So I have to agree. Not great. It would be ideal to have a better solution for what exists currently. Number six is primary key index is a space hawk. So this is saying that an index exists separate from the physical rows on the table. And other database systems do indexed organized tables. So the primary key, it's organized that way on disk and that if you do it that way, you can save some space, particularly if there's not that many columns in a table. So for example, this is a primary key that covers three columns, but it's only a four column table that allows you to save a lot of space. But how often does that really happen? In my implementations, this is very rare, so I'm not really sure how much space this would save. So with this, I'm a little up in the air on agreeing with it or not. Number seven major version upgrades can require downtime. And he says I quote some major version upgrades require hours of downtime to convert data for a large database. Now, I know with PG upgrade I've been able to do it in less than a minute, or worst case, a few minutes. Now, what is an issue is upgrading any active replicas because you can just upgrade the primary and then you need to essentially do a whole restore to get replicas back online, unless you do a particular procedure to be able to upgrade those as well. So I can see that being an issue and if there was a resolution for that, that would be great. But actually upgrading the primary database in my experience, can be done really quickly. Number eight, somewhat cumbersome replication setup, and I don't know if I've had difficulty with it. You do need to have experience and kind of know what you're doing, but I don't find it particularly cumbersome to set up replication. Nine ridiculous. No planner Hints dogma so this is the case where you actually can't use planner hints within your SQL code. You always have to rely on whatever decision the planner makes. Now, I do have to agree with him that it would be nice to have this capability for this particular reason here. He says this philosophy seems brutally paternalistic when you're watching a production database spiral into a full meltdown under a sudden and unexpected query plan shift. So I've seen this where you have a production database running just fine. And then there's something that happens with the data. Something's changed, and the plan changes. And now suddenly you have poor performance for queries that were just fine before. But you have to do some investigation to figure out what's wrong and figure out what the problem is. If there was a way to do a hint as a temporary workaround, of course those probably always end up being longer term. I could definitely see the benefit of that. So I can see the benefits of being able to use planar hints in SQL queries in certain use cases. And lastly, ten is no block compression. So he says PostgreSQL does automatically compress some large values but it isn't useful for the most common ways data is stored in a relational database. And he says what's important is actually have block level compression. Now I've seen other people use things like he mentions here, ZFS or other file systems that do compression that enable them to boost their performance as well as significantly reduce the storage space required for the database. So I wouldn't say this is something I hate, but it would be beneficial to have this capability as an option. So something to look forward to, for example, but you can do file system compression for where your database files reside, running it on a different file system. So overall I would say I agree with more than half of his proposals of things that you need to be aware of or can be a pain and that hopefully the PostgreSQL community will be working toward it. So definitely a blog post I suggest you check out.
[00:10:19] Next post is performance differences between normal and generic audit triggers. This is from CyberTech Postgresql.com and they're referencing a previous post talking about row change auditing purposes for PostgreSQL and they proposed a couple of different ways. One way they proposed was being able to set up per table triggers and logs for auditing the data being changed. The other was using a generic table, a common table, and have triggers in each of the logged tables being able to write there. And he wanted to know what the performance differences were because his interpretation was that the table specific triggers and audit tables would lead to better performance. So he goes through his testing methodology. Here how he set it up, his hardware set up, and here are the TPS and the latencies. And you can see here that the generic solution, which is the one I tend to prefer, was actually 35% faster as opposed to explicit triggers for each table, which he's surprised and I'm surprised as well. Now he said the disk footprint of it is larger for the generic triggers, but he does mention the last paragraph. There's a way you could probably mitigate it to not write as much data, so definitely check that out. Now, one reason you may not want to do it is that it is more difficult to query the generic table versus the explicit tables. But performance wise he was very impressed by how efficient this generic auditing solution is. So if you're interested in learning more about that, definitely check out this blog post.
[00:12:00] The next post is PostgreSQL schema design. So this is from Graphile.org and they're setting up a schema design for a forum. Now, what's interesting about this is that a lot of application frameworks have their own way of setting up the schema and you use their DSL for setting it up. But this goes through and shows you a scenario where you can build more in on the database side in terms of data protections and schemas and unique keys and things of that nature, where sometimes the application framework wants to handle that. So I thought this is a good post to look at to get a sense of what's possible with postgres if you're not as familiar with how you can configure your table. So for example, they're showing a person here, it's in its own custom schema, and they have a check constraint set on the first name to limit the character length and on the last name as well. And the created at column actually has a default, which is the function now. So like in a lot of frameworks, they never apply this type of check constraint to the database. That's a check that they do within the application themselves as well as a lot of times the created at doesn't include this, the application framework handles doing it, but if you have it in the database, then any other application accessing the database doesn't have to build this into it because it's already a part of the database. So this is a good post to review to see what kind of things are possible using the capabilities that exist within your database. They cover, say you want to use a random UUIDs instead of serial. This is how you would set it up, how you can add a table documentation, how you can set up custom types, how you can set up database functions. For example, concatenate a person's first name and last name together. You could do this in the database as opposed to your application. So all sorts of different ways of building an application where you're using a lot more database features. So if this is of interest to you, definitely a blog post to check out.
[00:14:06] The next post is actually a YouTube video and it's the next generation of Postgres High Availability, PostgreSQL Ha, and this is on the Database Month YouTube channel and it's presented by Tom Kincaid of a second quadrant postgresql.com. Now actually what he's talking about is their BRD product or their bi directional replication. So he perceives this as the next generation of Postgres High Availability and he talks about what's kind of current. Now you have a primary that is streaming to one or more Replicas and you have some means of determining whether that primary is still active. And you set up automated processes to be able to promote the Replica once the master has gone down. So it goes through the process of setting that up. And some of the course disadvantages is that you can run into a split brain situation and it can take up to a minute or more in order to make that switch. And he argues that if your uptime is super important and you want to be able to switch to another active server, you may want to investigate going master to master. So bi directional replication. Now there are of course a number of disadvantages with that because when you have conflicts so if the same entity is updated on each database, say the last one wins. Now, you can't determine or alter the decision process for that, but it's still something you have to take into account. But what I found interesting is he had mentioned that some customers opt to keep a shadow master. So it's there, it's constantly replicating, but it's not actively being used. So it's on the sidelines, as it were. And within seconds you could choose to switch to that, one being the master and not the other. Now, the advantage of that is a faster switch over and second, because you're not actively using both databases, it means you avoid these conflict possibilities. Now of course, you still have to take into account if you're doing something like a master master, you need a centralized way of handling ID creation. Meaning you can't really use serial columns anymore that have sequences within each database. You need to use a Uuid or some other central ID creston method. But it's definitely an interesting perspective. I hadn't considered this situation of a shadow master. So if you're interested in learning more about this and their bi directional replication product, definitely a video to check out.
[00:16:40] The next post is useful queries for PostgreSQL index maintenance. And this basically runs through different queries that you could use to manage your indexes. Now it's interesting, they start off pretty basic here. We're just seeing how many indexes your table has. Now you can do this with the D command for a table or the D Plus to find out more information with regard to the table and the indexes. But they're showing a way to get just that information and the system views you would need to query in order to get it. But they also show how you can determine if an index is unique.
[00:17:16] What's the size of the index, what's the size of tables indexes and the total size of all of them together? What query built the indexes? Determine if there's limitations of what kind of indexes that can be built looking for unused indexes as well as looking for duplicate indexes. So if you wanted a few queries that can help you better manage your indexes, definitely a blog post to check out.
[00:17:41] The next post is PostgreSQL wall evolution, part one. Now there is part one here and part two here that is covering the evolution of the write ahead log. So going from 7.1 where it just protects against a crash, version eight adds point in Time Recovery, version nine adds Replication Capabilities. Version ten adds Logical Replication capabilities and then of course, what's going on in the future and they look at it from the perspective of the different configuration variables and what's possible to be set in each one. So they go through each version. The first post here goes through 83 and the second one continues on with version nine and continuing up through version twelve. So if you're interested in learning more about the history and all the different capabilities of configuring your write ahead log, definitely a pair of blog postgres to check out. Oh, and I should say this is from Heigo CA.
[00:18:42] The next post is can't connect to Postgres. This is from Mydbainotebook.org and this is a follow on blog post where he's talking about you've tested that postgres is running, you know what port to connect to, but you still can't connect to it. So he has a flowchart for you to try different things. One other thing he mentions is it could be your listen addresses. And the other thing is again checking out your logs to see what may be going wrong and if you see a particular error message, some solutions to hopefully figure out what's going on. So, very brief post, but meant to help you be able to connect to postgres when you're having difficulty.
[00:19:21] The next post is the PostgreSQL Person of the Week is Sarah Connor Schner. Forgive me if I mispronounce that. So if you want to review Sarah's work and experience and contributions to the PostgreSQL community, definitely check out this blog post.
[00:19:38] The next post is PostgreSQL GSS API authentication with Kerberos. Part Three the status of authentication, encryption and user principle. So this is the part three, the final part of setting up Kerberos with PostgreSQL. So if you're interested in doing that, definitely a blog post to check out.
[00:19:57] And lastly, last blog post is importing spatial data to postgres. So this shows you a couple of different ways that you can import mapping data as well as where to find a few data sets and get it into PostgreSQL to use with PostGIS. So if you have interest in doing that, definitely a blog post to check out.
[00:20:19] 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 Scalingposgriz.com where you can sign up to receive weekly notifications of each episode. Or you could subscribe via YouTube or itunes. Thanks.
