Episode Transcript
[00:00:00] There's a lot of ways to get analytics from your postgres data. I mean, you couldn't use postgres at all. You could do change data capture or export the data into a separate data warehouse. Or you could run everything in postgres and use it to run all of your analytics. You could also use different extensions like pgmooncake or pgduckdb that might embed an analytic engine within postgres. Well, for the first post this week we're going to be talking about a novel technique I actually haven't seen before. Also be sure to stay till the end where my consulting corner. I'll just talk about some things I've been working on, but I hope you, your friends, family and co workers continue to do well. Our first piece of content is introducing PG Clickhouse, a Postgres extension for querying Clickhouse.
[00:00:53] This is from Clickhouse.com of course, and as you can tell they are moving the data into Clickhouse and they want to query it, but they're querying it from postgres and they make a comment.
[00:01:06] The time consuming part isn't moving data. That means moving data from postgres into Clickhouse back to the quote. Click Pipes has that bit nailed. It's rewriting months or years of analytics SQL baked into dashboards, orms and cron jobs. So basically they wanted to focus on this pain point.
[00:01:26] So they built the extension PG Clickhouse that allows you to query Clickhouse from PostgreSQL and to allow existing PostgreSQL queries to run unmodified. But yet it's talking to Clickhouse. So this is going to push down the query execution to Clickhouse to get its advanced analytical engine. And the other reason they're doing the extension is to create a foundation for continual querying pushdown evolution.
[00:01:53] So basically they'd like for you not to have to modify your postgres queries, but this extension just lets you run those queries but yet point to a Clickhouse data source to get those analytical questions answered. So they go into the history. They basically are utilizing foreign data wrapper technology. So there was an existing Clickhouse foreign data wrapper. So, so they started working with that and made the improvements to it. And they mentioned all the different enhancements they have added to that and particularly a lot of work on the push down. So that very minimal postgres resources have to be used to process the data. It basically can all get processed in Clickhouse. And they show an example query here where they actually rewrite for Clickhouse how the extension does this rewrite presumably on the fly to generate a clickhouse query that looks like this, but yet if you look at it from the postgres side all it basically says it's a foreign scan. So clearly all the processing is happening within Clickhouse. So they say, quote, this rewrite avoids shipping millions of rows back to postgres and keeps the heavy work inside Clickhouse. So once they got the basics working, then they started using tpch, which benchmarks decision support workloads, and they got I think three queries working, but then they started working more on this and especially on the semi join push down and they eventually got anything with the checkbox. Here is a full push down. So it looks like more than half of the queries of the TPC H benchmark are being pushed down fully to Clickhouse. And they showed an example of postgres runtime for this query as well as the BG Clickhouse results.
[00:03:40] And it looks like it gets as significant as 600 times faster where it's over 6 seconds in Postgres and 10 milliseconds in Clickhouse. Now there are cases where Postgres is faster, so here PG Clickhouse is at 1.6, Postgres is at 492 milliseconds, although not much is pushed down. But here's another case where it's 10 milliseconds in clickhouse and it's 6 milliseconds in postgres. So even with the push down, it's still faster in postgres. So it's definitely not universal. But overall the performance is far better for these analytical queries in Clickhouse compared to postgres. But they definitely have plans, they have a whole section on the future, basically getting the remaining unpushed down queries pushed down for the TCPH benchmarks and doing things like supporting all Clickhouse data types, support for lightweight deletes and updates, support for batch insertion via copy. So a lot of enhancements they plan to develop on this. So this looks like another analytics solution available for users of Postgres and check it out if you're interested.
[00:04:49] Next piece of content 14 times faster. Faceted search in PostgreSQL with paradedb this is from paradedb.com and faceted search is when you're looking for a particular item and some other column, typically some sort of type. It shows the breakdown and the counts of that type just by doing a simple search. So these are facets of what you're searching for and it gives you the counts now, paradedB is known to be an elasticsearch type search engine embedded directly into PostgreSQL and it supports the faceting. Now, faceting is really hard to get performant with a relational database and SQL. So the type of indexes that they've created for paradedb really help with the performance.
[00:05:35] Now looking at the results here for a small number of records, like looking at 187 results, actually Postgres is faster with its manual faceting compared to ParadedB, which we'll look at the purple here. But once you look at local larger result sets, say around 227,000, it's about twice as fast per ADB compared to just using Postgres manual faceting. At almost 2 million results, it's 5 times faster all the way to almost 15 times faster when you're looking at every result which is over 46 million. So the larger results that you're dealing with, the PARADE db, like faceted search, is much better than trying to use postgres for this solution.
[00:06:18] Now they also looked at MVCC off and we'll talk about that in a second here. But they show how to set up faceting and they're using this new function that their extension adds to postgres and when you're passing in the results you can pass in false and that basically disables mvcc and that basically avoids doing visibility checks. So normally it's going to have to check every row to make sure that it's still in the table. But if you are okay with not 100% accurate results, you can turn off MVCC checks to make it faster. And that's what the orange is represented here. So as opposed to being almost 15 times faster becomes 43 times faster if you don't want to have those visibility checks done. So they show a complete example and how to get it working, what the result sets look like and they compare the approaches from doing a manual faceting that looks like this in terms of postgres to how paradedb you can query the database to get the faceting results. And as a reminder, my understanding with paradedb, they do offer a community version, but that is not crash safe, meaning it doesn't persist the indexes in the wall log. I think the paid version is where you get that. So just keep that in mind if you're potentially evaluating this or double check me on it.
[00:07:41] But the next piece of content is also from ParadedB and it's deep dive into ParadedB's version 2 API, the future of SQL search. So they are mentioning all the enhancements they've recently made. And here's an example of the version 1 API you would have to use to create an index and now the version 2. So clearly shorter, better, but it can get to as simple as this, just creating the BM25 index on a few columns. But they talk about all the different features that they've added in terms of a new set of search operators supporting proximity search, doing fuzzy search, handling relevancy and scoring as well as search highlighting. So if you're interested in this, definitely check out this blog post Next piece of content There was another episode of postgres FM last week. This one was on max connections versus Migrations and I thought this was a bit of an odd naming, but what basically happened is someone posted, I believe on X or Twitter that there was a particular time of day where migrations to their database were failing and it was related to a data pipeline that was running at that time.
[00:08:52] And their solution that apparently seemed to work is bumping up Mac's connections. Now suddenly the migrations worked again. And my perspective from Nick, he was saying oh my gosh, what's going on that this would happen. And as was discussed, what's probably going on is that these migrations are causing some sort of locks that's probably causing waiting and using up the connections that are available and increasing max connections helps some work to get done, but clearly some waiting is probably going on. But they talked all about best practices with regard to doing migrations and as well as thoughts on max connection setting. And Nick mentioned I can't understand why AWS has their max connections at 5,000. I agree with that. I've actually dropped down some clients lower but he has a guide that he likes to use where basically about 10 times your VCPUs on the boxes is where you would want max connections at or at least no higher than that. So if you have a 32 CPU box then don't have more than 320 max connections. They also mentioned, you know pgbouncer, you definitely want to be implementing that and that should let you have a 10 to 1 connection ratio as well. They also discuss of course handling migrations. I mean the way I tend to recommend to do it is have your application users and they go through a proxy like pgbouncer. But then when you're running migrations potentially have that as a separate user with a different configuration or at least when you're running the migrations do things like set a more aggressive lock timeout. So if that migration that alter table statement locks other queries for too long, it cancels itself and then of course you need to implement retries. And I also wouldn't use a connection pooler with it, just make a direct connection to the database. But it was definitely an interesting discussion. I encourage you to listen to the episode here or watch the YouTube video down here.
[00:10:50] Next piece of content. Postgres scan types and explain plans. This is from CrunchyData.com and they are talking about all the different scan types you will see in explain. The first one is the sequential scan. This is just basically doing a sequential scan of the table and it looks like this sequential scan on accounts. The next is an index scan. So this is where it scans through the index, finds the values and then looks them up in the table and it looks like this index scan using a particular index. Next is a bitmap index scan and this is when a lot of values are probably going to be found by the index. It actually builds an in memory bitmap and then scans that. So you can see a bitmap index scan of a particular index and then a bitmap heap scan where it does a recheck of that index condition as well as other filtering that may be going on. The next one mentioned is a parallel sequential scan. So this is doing a scan in parallel with multiple processes as you can see here, different workers. And again if this is a larger query, this tends to happen because it does take more resources to actually do things in parallel and to combine the results at the end.
[00:11:59] So there needs to be enough work to justify that. And then you have the parallel index scan which scans in parallel and then of course does a gather operation to find the data.
[00:12:09] Then the index only scan that all of the data is retrieved from the index and that of course is generally the fastest way to do queries. But if you want to learn more, check this out. Next piece of content, PostgreSQL High Availability Architectures this is from cyber.postgresql.com and he's talking about architectures you can set up for postgres that help you achieve high availability and automatic failover. So number one on his list is using patrony. So setting up a patrony cluster. And you'll generally want at least three nodes to avoid a split brain problem at any one time. One of these nodes is the primary, the rest are replicas so you can read from them, but if there is a failure on one it will automatically fail over to one of the other clusters. So basically the advantages are you always have a primary server as the source of truth. Replicas allow read scalability. A short shared nothing infrastructure is highly resilient, it's easy to scale as needed and replicas can exist in various locations. Now in terms of connecting, he mentions a couple of different ways you can do it. You can use multi host connection strings in postgresql. So basically you define the different hosts it needs to collect to. And you can use target session attributes to define, you know this needs to go to the primary or any other type of rules you want to do to define the client that's connecting to the cluster. The other option is to use VIP manager. So this is a virtual IP manager. So basically you would assign a floating IP to one of the systems and when a failover happens that floating IP would move to whichever the primary is. So you always know which is the primary system. And the third option is using haproxy connection routing to be able to know which the primary is. Now we mentioned you could also do multi data center clusters. So basically you could still use patrony but have an entire duplicate data center set up. And you can do manual failovers from one cluster to another data center cluster. So that is a manual flip over. You can't do it automatic unless presumably you have three set up. But now you're talking about an awful lot of data duplication. And then lastly you could go multimaster and this could involve geo redundancy.
[00:14:29] So each country for example in this scenario would have its own server, each office is able to write. And when you're dealing with multimaster you got to be really careful of replication conflicts. So that's the main barrier to this. Although you can make it easier if you follow the rules he mentions here, whereby people local to a server, that's the only server they can write to, but the data gets transferred for the others to see, so that way you don't have to deal with conflicts. So London would only write to the London server, but it could still see data in Germany and Sweden and vice versa. The German location could only write to the German server, but it can still see the other data as it's synchronized. But if you want to learn more, definitely check out this blog post. Next piece of content. What you should know about constraints in PostgreSQL the this is from Zeta I.O. and they're talking about what is a constraint. Well, you can think of the data type you choose for a column you create as a constraint, but she's actually speaking more specifically about constraints you add to a particular column. So for example, you can add a check constraint that says make sure this price column is greater than $0. That is an additional constraint. And you can add all sorts of additional constraints to individual columns to ensure it's only receiving the data you're using. Expect she talks a little bit about constraint triggers as well as domains, because domains are stored in the constraint table as well. So basically they created an email address domain where it's like a custom data type that must match this constraint. But if you want to learn more, definitely check out this blog post Next piece of content postGIS performance simplification this is from CrunchyData.com and they're talking about getting better performance by simplifying something. And this is kind of similar to the previous post they did about sampling. Basically you're only choosing certain pieces of data to simplify something in order to get enough data points to get the amount of accuracy you're looking for, basically getting a result that is close enough in my interpretation, and it shows a variety of GIS functions you can apply to your dated to get good results performantly. So check this out if you're interested. Next Piece of content Building a rag server with PostgreSQL part 2 chunking and embeddings this is from pgedge.com and this is a follow on to a blog post where they're using their PGEdge vectorizer extension to process embeddings and even do chunking in postgres itself without having to use an external database solution. So check this out if you're interested.
[00:17:07] And the last piece of content the future of the PostgreSQL hacking workshop this is from arhas.plugspot.com and he said basically they're taking a break in December with these, but he's kind of questioning how much more content they have to go through for these because they're kind of running out of some of the talks. So he doesn't know if they'll continue on a monthly basis. Maybe it will be less frequently, but they do have a talk picked out for January and it is Andreund's what went wrong with AIO that he gave at 2025 pgconf.dev. so if you're interested in joining that, just click here to sign up using that form. And now it's time for my consulting corner. So I'll just be very brief this week. Just mention some things I'm working on. I am doing some performance optimization for a client, so taking a look at pgstats statements seeing what the top 10 to 20 queries are, optimizing those to make the efficiency of the system better. I'm also working on a partition creation script for a client. Normally these aren't too hard, but this particular client does define a number of indexes on only the children or the partition tables, and they also have some custom table settings as well. So those need to be applied to all the partition tables, as well as having just a lot of partition tables because of how much their data needs to turn over. But those are just some of the projects I've been working on.
[00:18:30] I hope you enjoyed this episode. Be sure to check out scalingpostgrads.com where you can find links to all the content discussed, as well as sign up to receive weekly notifications of each episode there. You can also find an audio version of the show, as well as a full transcript. Thanks and I'll see you next week.
