Episode Transcript
[00:00:00] Speaker A: In this episode of Scaling Postgres, we.
[00:00:02] Speaker B: Talk about PG bouncer Postgres eleven gems document DB and JSON. I'm Creston Jameson.
[00:00:10] Speaker A: And this is scaling postgres episode 47.
[00:00:21] Speaker C: Alright, our first post is PG.
[00:00:23] Speaker B: Friday. Pgbouncer or bust? And this is from the second Quadrant.com blog. And in it they're talking about Pgbouncer. Now, Pgbouncer normally how I think about it, it's a connection pooler. However, this post is talking about its importance with setting up a proxy layer before you connect to your database, because PG Bouncer allows you to do some interesting things like pause traffic, resume traffic, kill traffic to the database. And one of the things that this post discussed that makes it important, particularly when you're doing failover management, is it actually understands the postgres protocol and it can actually use some intelligence to handle connections, whereas a network based proxy has.
[00:01:09] Speaker D: No concept that a database connection is.
[00:01:12] Speaker B: Open or not, or session is active or not. So it's a little bit of a long post, but it's talking about those importance and particularly some changes that were made for the recent release of PG Bouncer 1.9. But overall they're advocating using it in your kind of failover infrastructure for some of its capabilities. Now, I want to use this opportunity to kind of look at 1.9 and.
[00:01:38] Speaker D: It was released back in August 2018.
[00:01:41] Speaker B: And it says it contains several new features to facilitate and monitor controlled switchover in high availability architectures. So that's kind of what this post is going into, some of these changes. So looking at the change log, they introduced a reconnect command which basically closes connections and starts up new ones on the server side of things, a wait close command, a fast close, which disconnects a server in the session pool immediately if it's in a closed needed mode. So looking at the commands, you can see this process controlling commands list and pause, which pauses connections from contacting the database server. You can disable, you can enable, you can reconnect. And that's one of the new features and basically doesn't really do anything in transaction pooling mode for PG bouncer, but in session pooling mode, as soon as basically a session is not being used, it's idle. It will go ahead and do a reconnect, close out that server connection and.
[00:02:43] Speaker D: Connect it to potentially a new connection.
[00:02:46] Speaker B: That you're redirecting it to. Now, one thing to keep in mind here, if you're thinking about this, I definitely advocate reading the documentation, but there's a quote there could be an extended period where some server connections go to an old destination and some server connections go to a new destination.
This is likely only sensible when switching readonly traffic between read only replicas. That makes sense because you're going to.
[00:03:10] Speaker D: Get the same answers and transactions can.
[00:03:12] Speaker B: Close out or when switching between nodes of a multimaster replication setup. So this post was done by a second Quadrant, which has their BDR product, their bi directional replication product. So I can imagine it's useful in that scenario. But if you have a master you're failing over to a backup, this may not make sense because you may have.
[00:03:37] Speaker D: Some transactions trying to hit one database versus the other.
[00:03:40] Speaker B: And they say if all connections need to be switched at the same time, pause is recommended instead. And also if to close server connections without waiting, consider kill.
[00:03:51] Speaker D: So that would be like an emergency failover situation.
[00:03:54] Speaker B: So definitely some new features have been added to PG bouncer and I encourage you to read the post and also some of the documentation with what some of these features do. To find out more.
[00:04:05] Speaker C: The next post is Unearthing some hidden.
[00:04:07] Speaker B: PostgreSQL Eleven Gems and this is from Cybertechn Postgresql.com and this is actually released at the end of September that I actually, I believe kind of missed. And this kind of goes over another.
[00:04:20] Speaker D: Summary of some of the Postgres Eleven.
[00:04:22] Speaker B: Features that they particularly liked. So like to improve performance of index additions for like serial ID columns, 30 50% boost filling the unused portion of for switched wall segment files with zeros for improved compressibility replication enhancements partitions and sharding and auxiliary tools. So we've covered a lot of Postgres Eleven's feature set, but if you're interested in another summary of what they found interesting about it, definitely a blog post to check out.
The next post is ten Most popular Citus Data blog posts in 2018 featuring postgres. And this is of course from Citusdata.com and they're listing their Ten Most Popular as of last year blog posts. Now, I don't know which was the most popular. I'm assuming that the first one was the most popular on down, but I didn't see that anywhere. But it's interesting that create the Postgres Ten feature, create statistics is number one, postgres rocks except when it blocks understanding locks. That was a good one. I remember. I understand that being pretty high up there.
[00:05:30] Speaker D: Database sharding explained in plain English if.
[00:05:33] Speaker B: People were looking into Sharding, that makes sense.
Again, another lock post. So if you kind of want to look over on what was some of the best content from this blog, definitely a blog post to check out.
[00:05:46] Speaker C: The next post is is DocumentDB really PostgreSQL.
[00:05:51] Speaker B: And this is from the enterprisedb.com blog. And the subtext is is Amazon's fancy new MongoDB compatible database management system. Really PostgreSQL under the covers. So I thought this was very interesting. So this is a new service from Amazon and they're talking about some technical details that make it sound an awful lot like PostgreSQL.
[00:06:14] Speaker D: So they say Document DB scales vertically.
[00:06:17] Speaker B: For writes and horizontally for reads via replication, just like PostgreSQL they mentioned. Amazon Document DB automatically maintains six copies of your data across three availability zones. Again, just like Aura PostgreSQL, the Identifiers are limited to 63 characters, same as PostgreSQL, the collection limit is limited to 32 terabytes, which coincidentally or not, they say the maximum size of a table in PostgreSQL. So pretty interesting supposition here. So I just found this post interesting if you want to take a look at it.
Now of course, how could they be using this a document store? Maybe they're using JSON B columns.
[00:07:00] Speaker D: And the next post we're taking a.
[00:07:01] Speaker B: Look at is an overview of JSON capabilities within PostgreSQL. This is from the Several nines.com blog and it starts off pretty basic. What is JSON JavaScript object notation of course. And PostgreSQL has had some JSON support and more recently they've added the JSON B as a data type as well and they have pretty good use case example here. So for pure JSON, the JSON data type, it's pretty much like a text data type which stores only a valid JSON document, stores the documents as is including white space, does not support full text indexing, and does not support a wide range of JSON functions and operators. The JSON B data type however, stores the JSON documents in binary format, trims the white space and stores it in a format conducive for fast and efficient searches. It does support full text search indexing and supports all the JSON functions and operators. General use case that I've seen this for is that if you're storing a JSON payload, you don't necessarily want to search for it, but maybe you're consuming an API and you want to store that. That's where you would reach for JSON just to maintain the format of it and you're not necessarily searching in it.
[00:08:19] Speaker D: I mean, maybe you're grabbing some values.
[00:08:21] Speaker B: If you need them, but it's not like you're using as a document store on your side. However, JSON B, that's enabling searching and very fast and efficient use of it. You probably want to use JSON B for that purpose. And they examine some of the data types. They show you how to query the JSON data using some different methods in addition in terms of indexing and if.
[00:08:45] Speaker D: You want to do indexings, basically you're going to want to use a gen.
[00:08:48] Speaker B: Index and they talk about some of the ways you can do that. So if you're wanting a refresher on.
[00:08:55] Speaker D: JSON and JSON B, definitely a blog.
[00:08:56] Speaker B: Post to check out.
[00:08:59] Speaker C: The next post is one security system.
[00:09:01] Speaker B: For application connection pooling and PostgreSQL the case for LDAP or the lightweight Directory Access Protocol. And this is from the Several nines.com blog. So this is a blog post about using LDAP within your application database environment and they're advocating here. Once you start off initially you probably have no use case for LDAP, but as you start adding more applications, more services, then you may want to introduce this as a common way to connect up your applications to your databases and whatnot. And even PG bouncer. So they cover ways you can use PG Bouncer with LDAP as well.
[00:09:43] Speaker C: So if this is interest to you.
[00:09:44] Speaker B: Definitely a blog post to check out.
[00:09:47] Speaker C: The next blog post is contributing to.
[00:09:49] Speaker B: Postgres, and this is from the Citusdata.com blog. So with the New Year, if you have some New Year resolutions you've planned, maybe it's contributing in some way to postgres. And this blog post talks about how you could do that. It talks about the different mailing lists that exist for postgres if you want to join them to see what development is like, familiarize yourself with the process of going through it, potentially how to contribute in terms of reviewing patches and even maybe eventually contributing code. So if you've been interested in that, this is definitely a blog post to check out. Now, related to that, there was another blog post called Maintaining Feature Branches and Submitting Patches with Git that was posted to the Second Quadrant.com blog that talks about this individual's flow for managing branches and submitting patches for postgres. So this is a good companion piece.
[00:10:46] Speaker C: To the previous post.
[00:10:47] Speaker B: If you're potentially wanting to get involved with postgres development, that does it.
[00:10:53] Speaker A: For this episode of Scaling Postgres, you.
[00:10:55] Speaker B: 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.
[00:11:05] Speaker C: Could subscribe via YouTube or itunes. Thanks.
