pg_duckdb: Ducking awesome analytics in Postgres

0:08Hi, I'm Jelte, and I work at MotherDuck, and I'll talk about queries in Postgres faster and easier to write.

0:20and how it makes ducking awesome analytics in Postgres possible. with what is pg_duckdb.

0:32And well, pg_duckdb is a Postgres extension

0:36that embeds DuckDB inside Postgres.

0:40what that means.

0:47So first, we'll take us a bit of a step back.

0:51is a pretty amazing database. It's open source, there's many contributors. and there's many other good things about it.

1:01One of those also being that every release gets extra functionality.

1:08then it's very extensible. that add that functionality.

1:16which means it can very easily

1:21find a specific user in the database, or show your current shopping basket.

1:30all that from the database,

1:34and could do that very fast, very efficiently. or sometimes called OLAP.

1:43What that means is, to answer a query, instead of finding the right data, or finding it in a shopping basket.

1:56all orders to find out

2:0010 most sold products from the last month,

2:04or from the last 10 months, and display those,

2:09nice graph in some dashboard.

2:14That Postgres is usually not great at that. yeah, like this elephant under the books.

2:22it doesn't like it.

2:27So why is it not great at OLAP? because there's no columnar storage in Postgres.

2:34Postgres stores rows of data on the actual disk.

2:39So every row is one specific item. all the data for that row, like if you want to show a user profile.

2:51that means that maybe you don't,

2:57columns, because you want to see all the rows.

3:04you maybe only care about like 3 rows, but for all the rows, because that's how many columns the table has.

3:18than it actually needs to be, that you don't care about, right when you read it.

3:28parallel processing.

3:32these days computers have lots of cores.

3:37is very important for making it go fast.

3:42is not really designed with that in mind. but it's sort of bolted on a bit.

3:49with parallel processing in mind.

3:54So not all the, like many of this Postgres

3:58internals, they don't work in like a parallel way.

4:03They're very sequential. Only specific things are made parallel in Postgres.

4:12can come to the rescue.

4:17that is underneath.

4:22in-process SQL Analytics engine.

4:28It does have disk columnar storage. It's built with multi-threading in mind. It's like there's one of its core design principles, like lots of things need to be parallel.

4:40And it tries to make stuff go fast in parallel. in-process stuff mean?

4:48like we have Postgres and SQLite.

4:53a transactional database, like I explained before.

4:58which means that a client

5:03usually over their network.

5:07And that makes it very nice for certain applications, all connected to the same database.

5:19But SQLite is a different database. but instead, it is something that you load inside

5:28a process. So, for instance, some things that the web browser needs to store.

5:35And then you can use that without going over the network. that is well-suited for transactional workloads,

5:46without having a dedicated server.

5:51So it's sort of two different use cases. And DuckDB is another type of database.

5:59It's similar to SQLite in that it's in-process, so you can load it in Python.

6:07or it runs inside your browser in Wasm.

6:12binary and execute it inside your browser.

6:20like I said, it's an analytical database.

6:26And it's very fast at that. So, Postgres and DuckDB,

6:34good at different things. DuckDB being good at analytical workloads.

6:40that's sort of the idea we're trying to make them one.

6:47maybe put it inside the Postgres process,

6:53so we can put DuckDB in there. And then and everything will be faster. that is both transactional and analytical.

7:09And that's sort of what pg_duckdb tries to be.

7:16So what does that look like? how do you put DuckDB inside Postgres?

7:23And what does that mean? Well, I kind of want it to be like this.

7:28next to a sunset, drinking a nice cold beer,

7:35and just being general friends and happy together.

7:41that's not entirely what it always looks like. this sort of monster picture

7:50that is like this Frankenstein-y mess of things.

7:57And now, after a lot of work, it's a lot better. this is sort of how it started for sure.

8:06And the reason is that Postgres and DuckDB, they're very different. It's not just like ducks and elephants, basically.

8:13It's not just that

8:17and the other is good at analytical workloads. in how they're actually designed

8:27to accommodate those different workloads. because Postgres is much older than DuckDB. Postgres is from the previous millennium.

8:35And DuckDB is from a few years ago.

8:39in how it's designed based on

8:46when it got started. in Postgres stemming from those early days.

8:55And one of those things is is written in C and DuckDB is written in C++.

9:03And so there's a lot of mismatch in how things are done to make development a bit easier.

9:16well with the Postgres code.

9:21You have to make some translations here and there,

9:26but it's doable...

9:31to work relatively well with plain C.

9:39It's at least sort of similar. main problems is the way exceptions work.

9:48just exist in C++. It's a built-in thing. You can throw an exception. where an unexpected error happens.

9:58For instance, a divide by zero. saying, oh, there's an error.

10:05And Postgres provide any way of doing that by default.

10:13So Postgres its own exceptions with its own try and catch.

10:22but Postgres is PG_TRY, PG_CATCH. basically the same, but also very different.

10:30is that the different try-catch blocks

10:36don't understand each other's exceptions. PG_TRY, PG_CATCH block around that,

10:44those handlers will not get executed. And that cleanup that might happen in the

10:53PG_CATCH handler is not executed. So you might leak some memory. And the same is true the other way around.

10:59If Postgres throws an exception, with its normal try-catch handler.

11:09cleanup in C++ relies on even locking cleanup.

11:16when an exception is thrown,

11:23and release a lock, stuff like that.

11:29but we manage.

11:34It is a very hard problem to

11:38make work well. and DuckDB uses threads.

11:45DuckDB uses a ton of threads, and Postgres doesn't use any of them, basically. and the code is also not thread-safe,

11:55oh, there's only one thread in this process. That's the one that's doing everything. So I can change global variables.

12:04I can do anything that I want.

12:09And so, yeah, that's

12:13problematic because there's all these threads but that Postgres function

12:23cannot be called in a thread-safe way. around how to handle that.

12:31And similarly, the other way around, for instance, from multiple processes.

12:39It needs to open it from only one process. makes that problematic,

12:472 DuckDB instances and 2 processes that maybe want to open the same file. as best as we could.

12:59And that paid off. in the sunset that you saw in the picture before.

13:10So how does it work? That's sort of an important question. how does it actually work?

13:21how Postgres works. a client or the network,

13:31the first thing it will do is give it to the parser, or this string of characters,

13:37it's called abstract syntax tree,

13:42of what this query actually means. and it selects these columns from this table.

13:50of objects and structures

13:54than with a plain string.

13:59And then, once it has that, and the planner will then of executing this. the planner will give that plan that it's

14:11and that will actually do the work. it will do the computations, it will do the GROUP BY, stuff like that.

14:22And that's how it works in Postgres. So, how does it work in pg_duckdb? is it basically steals the query.

14:29And it doesn't do this always. There's sort of is this a query that pg_duckdb should care about?

14:36And if it does, then it will just take over.

14:40sort of meta query structure.

14:45It will not let the Postgres planner do anything. It will just and give it to the DuckDB Planner, and then the DuckDB Executor will execute that.

15:00And then, is that it can still read Postgres data. So, pg_duckdb, it can still read the data that's in Postgres.

15:13So, that's sort of a small but important detail.

15:19What can this thing do? of using pg_duckdb.

15:29and go into a little bit more detail after. engine on Postgres tables.

15:37And that's sort of it. You don't have to change where your data lives. You can just use the pg_duckdb on the data that you already have.

15:48and write data in Blob Storage.

15:52for Azure Blob Storage and S3 and

15:57formats like JSON, Parquet, Iceberg.

16:02Lots of different ones. to Blob Storage.

16:08you can offload analytics to MotherDuck.

16:14So, let's go start with the first one. engine running on Postgres tables. So, how does that work?

16:24Well, it's extremely simple. duckdb.force_execution, set it to true.

16:30and the DuckDB plan.

16:35if it was a bit faster. It depends on your query.

16:43It depends on your data. It depends on a lot of things.

16:49But, sometimes, yes, it's much faster. For example, there's this ClickBench benchmark. That's a public benchmark created by ClickHouse.

16:57analytics queries on a single table. mainly aggregation. It's very aggregation-heavy.

17:05And there, for some of the queries,

17:10if you just set this new setting to true.

17:17And so, that's pretty impressive. Some queries also get slower. So, it's not like a silver bullet.

17:24But it is sort of worth trying out. while trying some benchmarks. which is also a query command benchmark.

17:34and no indexes. But here, I wanted to see

17:41how it would work without. And for analytics, that's fairly normal because

17:48for your analytics queries are usually for your transactional workloads.

17:58So, the first query in TPC-DS,

18:03and I just gave up.

18:08And then, I set this setting to true, and I ran it again. And now, it's done in 450 milliseconds.

18:15and pretty much it's finished. query optimizations I ever did. everything is much better. As I said, this is an extreme example.

18:28This is not something you can normally expect. because maybe you get 2,

18:38maybe you get a 10% performance increase from

18:43changing a single setting. And then we get to the second use case, which is using Blob Storage.

18:52we can just read from Blob Storage.

18:56and then read_parquet, and it will just return the results,

19:06like return the first 5 rows in this case.

19:10because you can just do like...

19:15and then you get a table full of the parquet data.

19:23ORDER BY on this parquet data.

19:29parquet is actually stored in columnar format.

19:35And so DuckDB knows that, in this parquet file to your Postgres server.

19:44It will just fetch the columns that you need. we just need title, days in top 10, and type.

19:49for those 3 columns and leave all the, maybe like a hundred different columns. and not touch it.

19:57the bandwidth used will be less.

20:02One thing to note is that like r['stuff'] that you see.

20:08That's because sort of arbitrary columns, what columns it's going to be based on. based on what's actually in the file.

20:19having this sort of dynamic column stuff.

20:25So instead, what read_parquet does, just like you could index like a JSON column.

20:33on the columns of the row, basically.

20:40before we go to the last thing, we have to talk a bit about resources.

20:46normally looks like.

20:51with some transactional queries, that take up a few resources. like pg_duckdb,

21:04well, the two examples I gave before of using it, a lot more resources because

21:12it's going to use more CPU but it will use a lot more resources of the machine.

21:18concurrently as you could previously.

21:23So that's where MotherDuck comes in. this is the company I work for,

21:29you can offload this computation to the MotherDuck cloud get the results on the Postgres server.

21:42queries on Postgres are much smaller. Postgres instance, or you can still combine it

21:50that you might have. is happening in the MotherDuck cloud so that makes it much easier to scale

22:01and stuff like that. that looks like, you copy data into MotherDuck.

22:10CREATE TABLE and then USING duckdb.

22:16And that's sort of the key part. some data from a Postgres table,

22:22and then you have the data in MotherDuck. with the same data as in the hacker_news table

22:29that was originally on Postgres. And then you can just query that like normal. you can do whatever you want

22:38sort of work transparently without you

22:46except that it's a lot faster, suddenly.

22:51with some data inside Postgres. So you can do... to store in Postgres because you update that.

23:02But the archive from before, that's still in MotherDuck. and sort of get a shared result coming from Postgres coming from MotherDuck.

23:18but is that actually fast? And, well, yes. For analytics, it is really fast. in the ClickBench benchmark

23:35and pg_duckdb compared to just regular Postgres,

23:40even with Postgres with indexes in this case.

23:45So yeah, that's it. It's MIT-licensed. It's open source.

23:50or install a Docker image.

23:55Feedback is very welcome. to hearing from any of you to try it out.