background

Handle One Million Connections? | Scaling Postgres 360

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

In this episode of Scaling Postgres, we discuss how a new tool pgDog might allow one million client connections to Postgres, novel use cases for pgvector other than semantic search, don't expose port 5432 and Postgres on Kubernetes.

Content Discussed

YouTube Video

Podcast Audio

Transcript

In an ideal world, our applications would scale effortlessly, and every application process or thread would have a dedicated database connection, so it could immediately connect to the database and retrieve or store any data we wanted. And maybe that's true for some key value stores or document stores. But if you're looking for the consistency of a relational database, that can be hard to come by. So as a solution to this problem, people have started using poolers like pg_bouncer, and application frameworks have their own poolers as well. It basically lets you multiplex your connections and have a lot of connections on the client side and very few database connections, because you really want to keep those minimized. Because each database connection, at least in Postgres, is a process, and all those processes are also accessing shared memory and other shared resources. So the fewer connections you have, the more efficiently the system actually operates. You can run into contention issues if you push your level of connections too high. But we're going to talk about a pooler this week that could potentially have 1 million client connections down to a reasonable number of server connections, but I hope you, your friends, family and coworkers continue to do well.

Our first piece of content is actually an episode of Postgres FM called “PgDog”. And I have never heard of PgDog before, and apparently it was made by the same person who started PgCat. So clearly you can tell he has a cat and likes it, and has a dog and he likes it. And this is Lev Kokotov, I think. Forgive me for the pronunciation, but if you don't normally listen to or watch postgres FM if you're interested in scaling, I highly suggest you listen to this episode. They talked a lot about pushing scaling, particularly right, scaling, scaling in this episode because Lev used to work at Instacart and he talked about the scaling challenges that they experienced. He talked about writing PgCat that eventually Instacart adopted, and now he's working on PgDog at his new company. But in the episode, they talked about the scaling challenges they saw at Instacart, predominantly with writes. So it seems like they maybe had double the writes that a normal application would typically have with Postgres. And the write pressure was so high, they eventually decided to shard their application basically into multiple databases functionally. So they put a certain number of tables in one database, a certain number of tables in another database to be able to handle the write traffic that they were seeing. And he said they were testing how fast they could do writes and with using local ephemeral disks on EC2 in a RAID 0 configuration, they were getting up to 200 megabytes per second of wall writes, which constitutes over a day, about 17 terabytes. So a lot of writing, but they were writing even more than that, so they needed to basically do a scale out. And this actually seems to be where Lev got the idea for PgDog. So he did start and write PgCat, which is a pooler, which is designed to spread the load across multiple read replicas, and it can automatically detect failures and resend the queries to another postgres reader node, I believe. But PgDog basically does sharding of it, so that's its big emphasis. Yes, it is a pooler, but it is a sharding pooler. So not only can you distribute reads to different databases, but now you can distribute the writes as well by sharding your data, of course. And how this works, which I think PgCat kind of does too, it has a parser that auto routes queries to either the writers or the readers based upon what the queries are. So essentially PgDog is a scale out solution. And probably the solution you're thinking about is Citus, because Citus does scale out postgres. And the thing that Lev mentioned is that he feels maybe Citus is constrained to a certain extent because it has to be an extension within postgres. So it has some limitations built into it, whereas being an entirely separate connection pooler that you don't need to get an extension approved for, but you can just put it in a container and it can talk to any databases you want to. There are implementation benefits from that as well as performance benefits. So if we take a look at the GitHub code here, it is open source, it is using an AGPL 3.0 license, so you should be aware of that. And you can see it is a load balancer. So it accepts postgres connections and it sends select queries to replicas and all others to the primary. Although they did have a discussion in the show on what do you do about function execution? What do you do about select for update? And PgDog maintains a list of healthy hosts, so it does health checks and failovers. It of course, does transaction pooling like PGBouncer does, but it's stated it allows hundreds of thousands of connections to use just a few PostgreSQL server connections. Sure, it's more than just a few, but again, with PgDog, sharding is the big thing, so it can shard data and send it to multiple writers and the actual site for pgdog is here and they go over pgdog again and mention the different features. And then finally there have been some blog posts listed, so you can feel free to check out the different blog articles they've written about it as well. So I definitely encourage you to watch or listen to the episode of postgres FM because I actually found this very interesting.

Next piece of content “Using pgvector for timeseries data”. This is from cybertec-postgresql.com and we've all heard about using pgvector for semantic search at this point, probably, and also different AI use cases. But this is using vectors in a different way, and it's basically finding similarity between two different vectors or how close two different vectors are from one another. In this example, he got some historical time series data from the German stock market, and he used some window functions similar to this to get the differences in the close for each day. But he wanted to get a little bit more information. So he actually wrote a query as shown here. He wrote it in terms of a view to get the last five changes of a row of data. So, for example, if this is the current close, it shows the difference between the six previous days and those are stored in an array, which is basically a vector. And now he wanted to do some analysis on it. So he got the average vector from the entire view that he created and it outputs this here, which it's pretty much close to one. So things balance out, but they rise incrementally over time a little bit. And he compared it to the current vector and sorted by it to see what are significant times when things happened in the market. So you could see there's a fair amount of activity that happened in 2008 and one here as well, and then a fair amount of activity in 1989. So 1999 was actually the Friday 13th mini crash or Black Friday. And then in 2008, the Dow Jones Industrial Average crash closed lower in all five sessions. So by using this type of vector analysis, he was able to see what are the largest variances of stock moves over time. So just wanted to bring this to your attention because I thought it was a very interesting use case of vectors. That's not what you typically see.

The next post actually came earlier than that one, and it's about “Semantic Search in PostgreSQL: An Overview”. So this is the more classic way that we've seen people doing semantic search using pgvector. And here it looks like they're generating the embeddings using Python. So if you Want to learn more about this? You can check out this blog post.

Next piece of Content “Do not expose port 5432 to the public Internet”. This is from thebuild.com now I know hosted database providers do typically expose this port, which from a security perspective is a concern because if there is a zero day vulnerability all of those could be vulnerable. If you have to expose it, some ways to mitigate it are use a random port, use certificate authentication that will be much harder to try to break into, or some kind of VPN or encrypted tunnel to actually allow your application to talk to a server even though it happens to be on the Internet. But this post mentioned some other things to be concerned about is that you're basically opening yourself up to a distributed denial of service attack. So even if no one can get in, they could bring down your database from so many connections. But of course as a really good policy, you know, have postgres behind a firewall or a separate internal network that's not routable on the Internet. That's the ideal. Also never allow remote logins by super users and make sure you lock down your access control in terms of AWS security groups if you're using AWS and also of course the pghba.com file. But if you want to learn more you can check out this blog post.

Next piece of content “CNPG Recipe 17 - PostgreSQL In-Place Major Upgrades”, this is from www.gabrielebartolini.it and Cloud Native PG version 1.26 now offers declarative in place major upgrades. So basically with this they basically rounded out the upgrade possibilities. So the first approach they had was an import capability so you could do a PG dump and a pg_restore of a cluster into cloud native pg. The second method does logical replication and a zero downtime upgrade of two systems and you can even do from Amazon RDS to cloud native PG for example. But this release offers the third method actually using PG upgrade and you may be thinking like I was. It's like well if you have the blue green deployment with logical replication, why would you ever want to use PG upgrade? Because for my clients I actually haven't used PG upgrade in about a year or two. I've only been doing logical replication upgrades. But they say, you know the case for in place major upgrades is maybe you have a hundred database clusters so there maybe you can endure a little bit of downtime to just do the PGUpgrade method and you don't want to invest all the time required to do a logical replication upgrade that takes time to set up an entire duplication of another environment. But they did show it in place here how you can get it up and working. And they did test a 2 terabyte database upgrade. So they went from Postgres 13 to Postgres 17 I believe, and it only took 33 seconds because clearly they're probably using the link method of Postgres PGUpgrade and we say quote here, the upgrade time primarily depends on the number of tables rather than the database size and like I mentioned, it makes it particularly well suited for environments managing a large fleet of small to medium sized PostgreSQL instances. So if you want to learn more, definitely check out this blog post.

Next piece of content so as you've seen the cloud native PG different posts, maybe you're thinking, well gee, maybe I are to learn more about Kubernetes. Well, next piece of content is a blog post converted from a presentation called “Postgres on Kubernetes for the Reluctant DBA”. I'll probably have to mention myself amongst the Reluctant DBAs with regard to this. I am getting my feet wet with it, but in my opinion the longer I wait, the more robust and foolproof the operators become, so I don't have to figure out some catastrophic issue. But I thought that was a particularly interesting presentation. It is quite long as you can see here, but it covers concerns, worries and fears of DBAs, the challenges of databases on kubernetes, but then also the strengths of database on kubernetes. Basically the operators take care of a lot of the heavy lifting and some suggestions on how to get started and build confidence with it. So if you want to learn more about that, you can check out this presentation.

Next piece of content “PostgreSQL 18: “swap” mode for pg_upgrade”, so PGUpgrade already does these different ways of performing the upgrade, doing a whole copy, doing a clone of the data as opposed to a copy copy file range. But also LING and LINQ is the one that I typically use because it's generally been the fastest, but now they're offering swap. So basically it moves the files from one old cluster to the new cluster and then replaces the catalog files with the new ones from the new cluster. And this is actually faster than the link method. So he did a speed test with this and the link mode of doing a 17 to 18 upgrade I think took a little over 13 seconds, whereas with the swap mode it took 11.4 seconds. So it saved about 2 seconds or maybe 15 to 18% of the time. So hopefully this new option will be coming in Postgres18.

Next piece of content “pgstream v0.4.0: Postgres-to-Postgres replication, snapshots & transformations”, so I thought this was pretty interesting. This is basically a extension called PGstream that is an open source CDC, so a change data capture tool for Postgres. So it enables you to do postgres to postgres replication. So it generates SQL that you can just send to another instance. It also lets you do snapshots of the data as well as certain transformations as well. So if you're looking for a robust CDC solution, you may want to check out this extension.

And the last piece of content “TIL - Starting in read-only mode the easy way”, this is from kmoppel.github.io and basically he says a lot of times when he starts a PSQL session, particularly at a client side, he doesn't want to make any changes to anything. So generally he does something to make a session read only and he has been setting the session characteristics as transaction read only. But then if he ever needs to do any changes he does begin transaction, read write, does whatever change and then commits it. But sometimes that can get hard to write in. So generally he might make a .psqlrc file. It's also possible to make a dedicated read only user, but then he discovered default transaction read only. So he says oh my gosh, why don't I just set the PG options when I start psql to have the default transaction read only on so that way no changes are possible. You can only do selects, he doesn't have to have a psqlrc file and you can even do it in different types of connections like here's the JDBC connection and you can pass in this option as well as you can see here. But check this out if you want to learn more.

And now for the consulting corner. I actually don't have some great insight this week. I'll just share kind of what I'm working on. I am doing some logical replication upgrades for some clients, but I'm having to find ways to transfer rather large database 10 terabytes that's fairly active. So I'm breaking it into multiple publishers and subscribers so the data can be transferred faster as well as keep up with the volume of activity. And if you do have to break up logical replication between multiple publishers and subscribers, be sure to try to not cross any foreign key bounds. Meaning if you have foreign key relationships between tables, make sure those are in the same publisher and subscriber. Otherwise, you may run into conflict issues when applying them on the subscriber, but I'll see what more comes up next week. 
 

episode_image