background

pgBouncer, Postgres 11 Gems, DocumentDB, JSON | Scaling Postgres 47

Join Over 1,000 Engineers & Get New Episodes Weekly!

In this episode of Scaling Postgres, we review articles covering pgBouncer, Postgres 11 gems, DocumentDB similarities and JSON capabilities.

Content Discussed

YouTube Video

Podcast Audio

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.comblog. And in it they're talking about Pgbouncer. Now, Pgbouncer normally how I think about it, it'sa connection pooler. However, this post is talking about its importance with setting up a proxylayer before you connect to your database, because PG Bouncer allows you to do someinteresting things like pause traffic, resume traffic, kill traffic to the database. And one of thethings that this post discussed that makes it important, particularly when you're doing failovermanagement, is it actually understands the postgres protocol and it can actually use someintelligence 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 therecent release of PG Bouncer 1.9. But overall they're advocating using it in your kind of failoverinfrastructure for some of its capabilities. Now, I want to use this opportunity to kind of look at 1.9and.

[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 monitorcontrolled switchover in high availability architectures. So that's kind of what this post is goinginto, some of these changes. So looking at the change log, they introduced a reconnectcommand which basically closes connections and starts up new ones on the server side ofthings, a wait close command, a fast close, which disconnects a server in the session poolimmediately if it's in a closed needed mode. So looking at the commands, you can see thisprocess controlling commands list and pause, which pauses connections from contacting thedatabase server. You can disable, you can enable, you can reconnect. And that's one of the newfeatures and basically doesn't really do anything in transaction pooling mode for PG bouncer, butin session pooling mode, as soon as basically a session is not being used, it's idle. It will goahead 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'rethinking about this, I definitely advocate reading the documentation, but there's a quote therecould be an extended period where some server connections go to an old destination and someserver connections go to a new destination.This is likely only sensible when switching readonly traffic between read only replicas. Thatmakes 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 replicationsetup. So this post was done by a second Quadrant, which has their BDR product, their bidirectional replication product. So I can imagine it's useful in that scenario. But if you have amaster you're failing over to a backup, this may not make sense because you may have.mprove performance of index additions for like serial ID columns, 30 50% boost filling theunused portion of for switched wall segment files with zeros for improved compressibilityreplication enhancements partitions and sharding and auxiliary tools. So we've covered a lot ofPostgres Eleven's feature set, but if you're interested in another summary of what they foundinteresting 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 ofcourse 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 mostpopular on down, but I didn't see that anywhere. But it's interesting that create the Postgres Tenfeature, create statistics is number one, postgres rocks except when it blocks understandinglocks. 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 bestcontent 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 isAmazon's fancy new MongoDB compatible database management system. Really PostgreSQLunder the covers. So I thought this was very interesting. So this is a new service from Amazonand 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 PostgreSQLthey mentioned. Amazon Document DB automatically maintains six copies of your data acrossthree availability zones. Again, just like Aura PostgreSQL, the Identifiers are limited to 63characters, same as PostgreSQL, the collection limit is limited to 32 terabytes, whichcoincidentally or not, they say the maximum size of a table in PostgreSQL. So pretty interestingsupposition 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 Bcolumns.

[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 isfrom the Several nines.com blog and it starts off pretty basic. What is JSON JavaScript objectnotation of course. And PostgreSQL has had some JSON support and more recently they'veadded the JSON B as a data type as well and they have pretty good use case example here. Sofor pure JSON, the JSON data type, it's pretty much like a text data type which stores only avalid JSON document, stores the documents as is including white space, does not support fulltext indexing, and does not support a wide range of JSON functions and operators. The JSON Bdata type however, stores the JSON documents in binary format, trims the white space andstores it in a format conducive for fast and efficient searches. It does support full text searchindexing and supports all the JSON functions and operators. General use case that I've seenthis for is that if you're storing a JSON payload, you don't necessarily want to search for it, butmaybe you're consuming an API and you want to store that. That's where you would reach forJSON 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.peaker C: The next post is one security system.

[00:09:01] Speaker B: For application connection pooling and PostgreSQL the case for LDAP orthe lightweight Directory Access Protocol. And this is from the Several nines.com blog. So this isa blog post about using LDAP within your application database environment and they'readvocating here. Once you start off initially you probably have no use case for LDAP, but as youstart adding more applications, more services, then you may want to introduce this as a commonway to connect up your applications to your databases and whatnot. And even PG bouncer. Sothey 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 topostgres. And this blog post talks about how you could do that. It talks about the different mailinglists that exist for postgres if you want to join them to see what development is like, familiarizeyourself with the process of going through it, potentially how to contribute in terms of reviewingpatches and even maybe eventually contributing code. So if you've been interested in that, thisis definitely a blog post to check out. Now, related to that, there was another blog post calledMaintaining Feature Branches and Submitting Patches with Git that was posted to the SecondQuadrant.com blog that talks about this individual's flow for managing branches and submittingpatches 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 tohead over to Scalingpostgres.com, where you can sign up to receive weekly notifications of eachepisode, or you.

[00:11:05] Speaker C: Could subscribe via YouTube or itunes. Thanks. --

episode_image