Episode Transcript
[00:00:00] In this episode of Scaling Postgres, we talk about psql tips postgres 13 upgrade, roles, hierarchy and replica scaling. I'm Kristen Jameson and this is scaling postgres episode 161.
[00:00:22] Alright, I hope you, your friends, family and workers continue to do well. Our first piece of content is Psqltips and this is from Mydbainok.org. And at the top of that site they have a psql tips and when you click on it, you get a random tip out of 100. Now you can also go to the all tips link that I'll provide and it shows you all 100 psql tips. Now that's a lot of tips, however, each of them are slight variations. So for example, tips one through ten shows different ways you can execute SQL commands with psql. So you can use the Hyphen, C or the command flag to send a single command, or you can do multiple commands, or you can send in a file, or you could pipe it or do different types of variations of just executing SQL. Now of course that's not what all the tips are. There's tons of other tips about connecting and whatnot. But if you want to improve your psql knowledge, I highly suggest you check out this resource to see if there's more that you can learn about psql.
[00:01:32] Next piece of content is standing on the shoulders of a giant elephant. Upgrading discourse to PostgreSQL 13. This is from a blog, Discourse.org and they're talking about their upgrade to postgres 13 and the main area that they are highlighting is the Btree deduplication. So for example, they looked at their largest table and when they looked at a version twelve table with all the data in it, it was 114gb. Looking at version 13, it was 85gb. So that's a 25 reduction in size. Now I should mention this is total table size, so it's indexes plus the actual table. Now when they split it off, they can see for version twelve what the table size was versus the indexes, and the same for 13. And here you could see the big reduction is in the index size. And then more specifically, they looked down at which indexes showed the size difference. So for example, their unique index is identical because it's a deduplication, but everything is unique in there, so nothing's duplicated. However, their post timings on the user ID and the summary did have some duplication. And it goes from say, 21gb for each index down to about seven, or a little bit less than 7GB for each index. So for them, this was a great reason to upgrade to postgres 13. And if you're looking for these types of space savings, maybe you want to consider upgrading as well.
[00:03:00] The next piece of content PostgreSQL getmember roles and permissions. This is from CyberTech Postgresql.com and they're talking about roles and seeing who is a member of each role. So for example, in this post they created two users, user A and B and now, the difference between a user and a role is that a user is still just a role, but it can log in. But here they've defined essentially roles that can log in A and B or users and then CDEF or other roles. And then they granted roles to particular users or roles to other roles. So they ran these various grant statements. So basically, you have a hierarchy of roles. Certain roles belong to others and users belong to those roles and they want to see who belongs to what. So the first thing they're looking at is the tables that will be involved. And one is the PG Off ID, which shows the role name and some information about it, such as the role can log in, as well as the PG Off members that shows the members of a particular role ID. Now, with this information you can then build a recursive query using the with clause to be able to extract who are the members or what is the hierarchy of the role membership. So you can see the results of this query right here. So just a simple basic post about finding the hierarchy of roles. And if you're interested in learning more, definitely check out this blog post.
[00:04:25] The next piece of content is Replicas scaling by the numbers. This is from Momgm US and he's talking about read write workloads. And that the thing you have to keep in mind when you have a replica, is that whatever write load is on the primary will also be on the replica because it needs to keep up with everything that the primary is writing. So for example, he says, quote, suppose the write load on the primary is 30%. That leaves 70% of the I O for readonly queries. So each replicate can only use essentially 70% of its read performance because it's still having to do that 30% of writes. So this may be a scenario where having more replicas really helps your performance. But if you predominantly have a write workload, say 75, 80% of the primary is just dealing with writes, it's going to be hard to scale out using read replicas. And in this instance, you may want to go with Sharding, which he mentions here. So if you want to learn more about this, you can check out this post.
[00:05:24] The next piece of content PG Backrest, the best postgres backup tool with a very active community. This is from Migops.com. And they're talking about PG backrest. And it looks from their opinion that this is the best backup tool to use with postgres. And they list 15 advantages for using it. Some of the big ones they mention is that it's open source, it does parallel backups and streams those files to a remote repository or somewhere in the cloud, and it can do incremental and differential backups. But of course, they list ten more reasons why it's a very, very good backup tool. And then the second part of the post they cover how do you install it, how do you get set up to be able to backup some postgres database systems with it. So if you want to learn more about PG Backrest and get an implementation up and running, definitely check out this blog post, the next piece of content announcing Google Cloud Storage Support for PG Backrest. This is from Crunchydata.com and they have now added Google Cloud Storage to the number of repositories that you can send PG Backrest data to. The others that already exist are for Amazon s Three and for the Azure repository. So this adds a third. So if you want to learn more about this addition, you can check out this blog post.
[00:06:42] The next one, also from Crunchydata.com, is introducing PG Backgrounds multiple repository support. So this means you can send your data to more than one repository, not just one. And in this example, they are showing where they set up one repository as a locally mounted NFS volume and a second repository for Azure. So if you want to learn more about this feature in PG Backrest, definitely check out this blog post.
[00:07:08] The next piece of Content deploying PostgreSQL for High Availability with Petrone, etcd. And HAProxy. Part one. And this is from Digitalis IO and they're talking about setting up High Availability postgres using these tools. So they discuss a little bit about it and then they go into the process of starting to set it up to create a three node cluster where you're going to have one lead and two followers in postgres. And they're all communicating using Petroni, etcd. And HAProxy. So that's part one. And part two goes into more depth on setting up the final system. So if you want to learn more about this, definitely check out this blog post.
[00:07:49] The next piece of content how to run hierarchical Queries with PostgreSQL this is from Haigu, CA. And this post is a little bit different because it talks about how Oracle does it. And it shows a typical example where you have someone being managed by another employee in terms of the data set that they're looking at, and they show you how you would do this in Oracle and then they answer those questions using PostgreSQL, using CTEs or with clause queries. So if you want to learn how to do hierarchical queries like this, definitely check out this blog post.
[00:08:23] And the last piece of content is getting started with QGIS PostgreSQL and PostGIS. This is from CyberTech Postgresql.com and they're talking about how to very simply get started up with using geographical information systems and postgres. So if you want to learn more about how to do that and set it up, definitely check out this blog post 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 to sign up for weekly notifications of each episode. Or you can subscribe via YouTube or itunes. Thanks.
