0:00Hello everybody and welcome to another show. We're going to talk about Postgres and specifically if your Postgres starts to be slow at answering analytics questions. I'm curious, I was just
0:18asking in chat where you're joining from. We have people from Canada, Greece, Italy, so all over the world. I hope you have a great breakfast, great lunch, or a great dinner, whatever is your time zone right now. And I'm curious, who is kind of joining? So this presentation of the webinar is
0:40mostly targeted to people doing data work but necessarily have data background and by that I mean often there is work that falls into the laps of product people or software engineers and they've been using Postgres for products. I'm also curious like who is using Postgres here
1:01versus other OLTP database? I'm not talking about analytical DB yet but just like which database are you using to ship your products? Is it MySQL? Is it Postgres? Postgres is the most popular but let me know in the comments. So this talk is targeted towards Postgres users but of course
1:24any other database if you use MySQL or any others manage Postgres behind the scene or around AWS and so on. All these tips and advice are working for you. And so the reason for the webinar initially is that we realized when we were talking to many data people is that
1:47or many team that needs to insert analytics they started with Postgres but have no knowledge on what is an analytical database, what is OLAP. And in this webinar in a few minutes, I think we'll
2:05run everything in 30 minutes probably, we'll learn what's the limitation of Postgres and what analytical database bring to you. We'll see some quick benchmark. I'll do a live demo if everything fails. I can blame the Wi-Fi as always. I'll do a live demo. You'll see the performance gains
2:26and basically everything hands-on for you to kind of like start your analytics journey out of Postgres but Postgres day. We'll talk about that later. I see a lot of people on Postgres. Yes, Postgres on GCP Cloud SQL. That's true. GCP call it Cloud SQL. PostGIS, so some people doing some
2:51geoanalytics, Postgres and Wasm. I'm curious in which case are you using specifically Wasm and Postgres? Yeah, so let's go forward. So as I said in the intro, you ship the product on Postgres is there and then the analytics move in. That's natural and there is nothing wrong with that.
3:14So that means you start with the tools you have which is Postgres and you need to insert typically some dashboards, questions or just analytics reports. And so you start over there on Postgres at the beginning is totally fine and then you start to have some issue. We'll see what's the
3:37sign you can you should see when you start to struggling with analytics and Postgres. But so hear me out. There is nothing wrong starting on analytics with Postgres. That's the best place to start, okay? But there is a graduation. So that's how the graduation is looking like. At the end
4:00every people it's the same world. Hopefully if you're successful. If you're not and you don't have that much data, then that's fine. But basically you have here on the world of query latency and the data volume. And what's happening is that as your data grows on Postgres and you
4:21start to do more typical analytics query where you do aggregation, you're going to start to add index. We'll see later why index is a temporary safe. Then you're going to add a read replica, then materialize view and then you start really into heavy optimization and charting. And that's
4:44where basically you kind of like done the wrong tour of all the optimization you can do and you just realize that Postgres is not fit for analytics. And so that's really that's why I was saying in the beginning again there is nothing wrong to start your analytics journey on Postgres depending
5:02on where you are on the data volume. If you start your analytics and you are already on that volume yeah sure you may not have even time to start doing analytics on Postgres but typically people start doing you know typical analytics and report really early on in their journey. So what's the difference? Some basics if you know that already you know stay with me I think it's
5:25always important to put people everybody on the same baseline knowledge. The difference is that Postgres is a row store database so it stores the data as a row and analytics database that you know like DuckDB, MotherDuck, BigQuery, Snowflake and others they store as column storage.
5:50And the difference is that Postgres is really good at small transactions where you're going to need to update certain fields or delete specific rows and that's good because it's stored by rows. So if you need to update for example a user is connected and you need to update the last connected
6:09field well it's pretty fast because you just look at the user id and then you have directly access to the data there. But on an analytics journey on the OLAP database so that's what we call the typical OLTP database online transaction database but for OLAP we store the data as column
6:30and the reason for that is again for based on the query pattern. I just give you an example of a query pattern for OLTP or Postgres database and for MotherDuck or any other analytics engine we start in column because we're going to do typically operation on a column. So here for example we do
6:49an average on amounts and so we can select directly a column and do the operation. Now you start to understand if you need to do this operation on Postgres it's going to fetch every single row for the column amounts if you want to have a total information on that which is typically
7:11this kind of like aggregation average that's what analytics query is typically all about. So as you said like we've talked to many engineers on different size of start of companies from Solent to Meteor Enterprise and it's always the same you know honeymoon period with the graph I showed you at the beginning.
7:33It can take minutes maybe up to 10 minutes sometimes and we try to aggregate a lot of data in Postgres and the latency spike. So let's see now a bit what are the signs that you've outgrown your Postgres because again I'm repeating myself there is nothing wrong to
7:53start your analytics journey on Postgres. There will be side that you should feel now it's time to move the analytics offload this from Postgres. So first aggregates takes minutes not seconds. I think usually people on the analytics world is used to seconds in terms of query performance
8:17so that if it takes minutes that that's not normal. If you put a replica so typically we have I think I have the slides right after but Postgres you have your Postgres in production and you put a replica to not impact your Postgres production is still slow so you just move you know the compute around but it's still Postgres on another
8:39compute. The materialized view are also a workaround so you can create those materialized view but they are quite expensive to create and you start to have timeouts on customers facing page. So that means typically you have let's say analytics dashboards you are e-commerce and you
8:57have analytics on what the seller has sell over the months and those page when it's typically analytics query patterns start to be really slow and the dashboard that is all your top your top complaint. So a read replica is still Postgres as I mentioned so you have some lags
9:19and you copy that to another database and then basically you have your dashboard that's on that that's just moving the problem separately but it's you know easy to to realize usually a lot of managed Postgres offer a replica service directly with just a few clicks.
9:39Materialized view again there are also an option but you have to put a scheduling pipeline or you know pg_cron to refresh this materialized view and that's really expensive so the the query gets faster on the read side but to create those they are pretty expensive again.
10:01So that's the different sign that you see basically you start to add on your Postgres and the whole idea is that Postgres stays like it is a means to serve online transaction for your products and that's great I'm still also using heavily Postgres for any web application
10:24but the analytics move and so now you have basically two to three questions I would say is first where is the analytics engine is going to be and probably the first question also is how do I move my data so here is a simple decision tree let me actually zoom let me zoom a bit here
10:55there you go if you need to so how your data gets into MotherDuck or another analytics database so first the question is can you move your data out of Postgres at all and the reason I'm saying this is that sometimes for whatever security or compliance reason you cannot and but you still
11:17need to do some analytics over there one option is pg-duckdb I'm curious in the audience if you heard about pg-duckdb pg-duckdb is basically DuckDB running inside your Postgres as an extension so you have the DuckDB engine inside Postgres so now you might be thinking okay I'm using an
11:40OLAP analytics engine but the problem with that is that I'm still fighting over the same resource right the resource is on Postgres again mitigation is you have this replica and you have pg-duckdb running on the replica so that works kind of but again that's a temporary solution but it also
12:02works depending on how you scale your Postgres if you don't if you can move your data out of Postgres which is most of the people you can do and if you need a second fresh dashboard I don't think anyone needs that a dip based on my years decade of experience as data engineers
12:27business wants real-time dashboards but they mostly cannot make a decision based on that now we have AI agents and that's another story and there is mission critical environments I work with like air traffic control or sometimes call center where they do need real-time dashboards
12:46but it's really really a small niche I would say of use case most people are fine within a day of refresh because human are not going to take decision that's a little bit parenthesis but I would really advise you to push back business needs on why do they need a real time if they
13:05are going to make any decision real time based on that because the infrastructure for streaming so typically what we call CDC change data capture is just a bit more overhead compared to simple batch and for that you have Debezium we have estuary partners for MotherDuck specifically that will
13:26help you you know to do that roughly plug and play but it's still I would say an additional complexity on your data infrastructure so if you don't need sub seconds and you you know you're fine to the I would say five minutes because you can do a lot of small queries to refresh five minutes is totally fine to have small queries and the question is if your data is
13:50already in object storage for some reason you have also objects to offload from Postgres now there are many I would say self fully managed Postgres that offer that I know Supabase for example of the offer that and others where they suggest you to move some data out directly in
14:11S3 and object storage for example and typically in parquet and the side note is that parquet as we saw here is also a columnar format so once you have parquet file sitting on S3 then basically the gap to load that into a lab is really really small
14:32and I saw there was a couple of questions I'll take some time to answer right after the demo right before the demo so please if you have any questions so far it in the comments I'll make sure to reply to them so you can move your data or you don't you have it on object storage and if you
14:51don't then basically the default is just a scheduled batch and the beauty of DuckDB is that it can integrate with many databases and including Postgres and MySQL so today I'll show you how to get your data out with DuckDB and load it to MotherDuck it's really really simple with the
15:10Postgres extension. So we talked about multiple products surface and there is one engine here in the case of MotherDuck so first if you offload to MotherDuck you have a DuckDB client
15:29DuckDB can live in any client so if python java node. js is your job you can have DuckDB load in the client and load data from postgres to MotherDuck as a regular batch pipeline you have the pg DuckDB that we talked where DuckDB run on your as an extension on the postgres server and the two
15:53other we're going to talk is more on the consumer side so once your data is in postgres and you have dashboard eating that the beauty of it is that we also talked to the postgres as a towards the postgres protocol which means you can talk to MotherDuck using the same postgres code you just
16:13change the postgres connection but you will be using MotherDuck engine behind the scene the all app engine so it's just basically a way for developer experience to switch to MotherDuck but don't have to add any DuckDB client on your existing codes web app or any other tools
16:35bi tools would it be um embedded Dives so i'm talking i'll talk i think i have a slide about dice but Dives are basically self-hosted uh fully hosted data apps that MotherDuck hosts and you can create any data utilization apps or that i can uh a bit show you quickly uh and that's
16:55also my quick plug that we are currently running a live hackathon for dives and basically you can see you can create beautiful visualization and so this for example on this website this is a realization that's connect to MotherDuck data here we are basically inspecting uh stars you see you
17:17can really create custom visualization and the data is all set on MotherDuck and we serve it here as an iframe on the MotherDuck dive gallery so coming back to this um sorry so that's unbated dive you create the visualization you all sit on MotherDuck it's just javascript so you can
17:41literally create anything and once it's hosted on MotherDuck you can serve it in your app to an iframe um all right let's talk uh demos i just want to quickly uh go over uh the question we have uh so
18:01far before jumping in the demo i've used the DuckDB postgres scanner so yeah so once on postgres scanner i ask uh many people what kind of uh postgres they are using i see postgres on planet
18:19scales yeah we have a native integration also with PlanetScale um all right so let's dive into uh the demo and for that basically i have a database postgres in PlanetScale that is a good
18:37side sideway and um we gonna do uh i put the pipelines uh to run those and ingest those to MotherDuck i'll show you the snippets of code on how to use it i'm not going to run it because the data is pretty heavy and then i have a web application it's typically an Next.js application
18:58that i run locally and it's connected to MotherDuck and here basically we're gonna see on the web app i have one version of the shards that's connected to postgres and one to MotherDuck i'm not cheating i had it indexed and this is where i want to talk about index for a minute
19:20on postgres because first you shouldn't you should not trust any benchmark you see you should do your own homework and experimentation but i want to call out that you know postgres can uh query can simply be faster if you had an index but the challenge is that you design the index based
19:39on your transaction workload so coming back to initial load i need to update uh you know the last connected field from a user maybe you have an index around that but the thing is that now you need to add also index for analytics query and adding more index you pay it at write time which
20:00means that you're going to slow your general performance on Postgres because adding index for analytics workload. So here I added a simple index on orders just to make it sure that at least it's quite fair for the benchmark. So this is how it looks like. I'll show you
20:23directly, but the snippets, this is a Python snippet, but it looks exactly the same if you use Node. js or if you use Java, it's SQL, DuckDB as its core use at least SQL. So once you have installed, sorry, the DuckDB package, you install and load the Postgres
20:43extension, then you create your secrets. So which is the Postgres secrets where you have basically store the, sorry, the hostname, user passwords. Then what you do is you attach, if you're familiar with DuckDB, the attach command here, just attach the Postgres as
21:08it is another database, DuckDB database, or here it's Postgres database. And now you can query it from DuckDB, whichever client you're using. So typically this scripts is going to run, you know, server side so that you can leverage the cloud network. Here, I'll show you a quick demo locally, but I'm not going to do the whole load from Postgres
21:32to MotherDuck using my local machine, because then I have the data translating to my laptop and then up, it doesn't make sense. But typically that's those pipeline and you're going to run into your GitHub action or any runtime that you want for that. Even a Lambda function, if you partition for small data, you know, specific data set that you want to load Postgres,
21:55it also works. And for MotherDuck, basically the only change is that you do an attach, you connect to an MD and MotherDuck act as a DuckDB extension. And that's it, you're connected to the cloud. So you see there is basically not that much difference between the left script, which is pure DuckDB and the right, which is MotherDuck. All right, time for some
22:19demo. So I want to probably show you, let me get out of the full screen there. First, just a bit, I've put a bunch of scripts that I use actually for the benchmark. But if you
22:38go on, for example, the hello Postgres, that's what I'm doing here. So you recognize attach the PGRL. I'm just loading from my . onv the Postgres URL for my database that's connected to that. I put the dimension and the fact that there is. And here I'm just doing a select
22:59count on the database to show you how it works. So let's do actually, let me do here and we go to pipelines and we're going to do a run
23:23hello Postgres scanner. So this is just another word. And you see all those queries are done from DuckDB locally in Python. And I'm doing just a select count on Postgres. And you see that even a select count for the first dimension, it went pretty fast. And then the others Postgres start to be a bit slower. You see order, I have 20 millions of rows
23:45and then I have order items, which is even bigger. So we have to wait a couple of seconds. So here, just to be clear, DuckDB is running locally. It's using the extension and it's a federated queries over Postgres. But it's just to show you how easy it is to quickly, basically play
24:07around Postgres data using an analytical engine locally with DuckDB. So now the next step, for example, is of course to load the data. And if we look at the script on loading the data, that basically look exactly as we did. I just do create table as. And as you see, I do
24:30a query of select from the table from Postgres. So the first thing is that I'm attaching Postgres database. I'm connecting to MotherDuck. And then basically I copy the table there. And of course, you can do incremental load to insert into basically with some condition here as I did.
24:53So that's basically a bit how you could just start ELT workloads to Postgres using DuckDB. Everything that I'm showing you is in Python, but you can do everything in Java or Node. js. I'm curious, what's the main language that you are currently using in your work? Tell me in the chat. I'm
25:15curious to know if you are a Node. js user, a Python user. I can also show you a quick example on how it looks like, but basically all the query syntax I've showed around here. So if you forget the Python dialect, but all the SQL syntax, install Postgres and so on, that's exactly
25:36the same thing in other clients. All right. Last demo. So we show a bit how a query we're already slow with just some sample queries. We explored a Postgres scanner and we saw how to
25:55package it as a Python pipeline. And now we have a Next. js application here. And this app is really a simple app where we're going to run this query. And you see, it's already a query where we do some joins. So if you remember on my demo here, this is the table I have. I have four dimension
26:19and two facts and a couple of rows. And here I'm doing, you see a join on orders and order items and shops. So on the biggest table to basically get the revenues that we generate. And so I'm
26:39going to run it right now because actually Postgres is going to take quite some time and hopefully it's a live demo. So bear with me. Normally it should be already done on Postgres. There is already something wrong. It's a live demo. So I'm not sure what is happening here.
26:57I may have to restart the web server. Let me look into that. What is running? But it's not normal that it's taking already there. Let me check quickly. And so while I check this,
27:22I wanted to talk to you about quickly how basically the connection works. And so I mentioned basically the pool here, you can use the Postgres endpoint.
27:40And so if you go to the Postgres UI, let me get you one minute.
28:01Okay. So if you go to the Postgres UI and you go to settings, you're going to see that you have a Postgres endpoint and you can just copy the value there. And you just need your MotherDuck token. So if you use the native DuckDB token, you can also connect it directly. Or if
28:24you use your current Postgres connection, then you just use this endpoint. And basically here, this is what is changing. It's exactly the same here. I provide a connection string. Actually, I could have split it in all support and so forth. And this is how it looks on Postgres. So
28:45exactly the same thing. So for some reason, it might be my network connection, but Postgres even time out here in this case and MotherDuck there, but that's not the benchmark I had later earlier. So I'll show you actually. I'm going to restart and we're going to try again.
29:10Okay. So my network connection seems okay. And it's not because it's cache. 1. 5 seconds for that much analytical query should be okay. And normally Postgres should come along around 100 seconds.
29:30So that's what I tested before. I think there was just an issue with my local web servers, which was kind of dead. Let me come back to the slides and we'll see where this one is going.
29:49So yeah, I had those numbers basically. So Postgres on the specific query I show you there. And this demo is going to be live so you can test it by yourself and inspect the data. But basically the thing here is that again, there is nothing magic is because we are storing the data as
30:09column and MotherDuck is an analytical engine. And so it will be drastically faster here. It's 81 times faster, but actually if you do other queries on the same data set here, a typical analytical query, you can go to 200 or 300 times faster. So that's a huge reduction.
30:33So on MotherDuck, you don't have to manage any clusters. You just basically pay for active CPU and storage. There's no always on a cluster. It's serverless. And so if you want to operate and handle also change on Postgres, I recommend also getting two data contracts because that's,
30:58when you create those pipeline from Postgres to MotherDuck, you need to be sure that the producer, if you're not the one controlling the Postgres schema, is not changing anything that would break those pipelines. But that's the classic issue. You can run logs and see in the MotherDuck UI what's
31:17happening. One thing I want to show you is embedded dive in the Postgres endpoint. So I already talked about it with the dive gallery. So the dive gallery here is using basically WASM here. So if you see the filter here, when I'm changing something here, it's actually doing a query
31:40locally on my browser, not on the service side. It's using DuckDB Wasm. And when there is the initial load, it's loading the data from MotherDuck. So we have a dedicated SDK for that. And how is that related to Postgres? It's because basically what's happening is that you can
32:00use either one or the other if you serve currently, I would say dashboard, either to an iframe or either the root of 3T architecture, which I did here. I just had a custom dashboards and
32:15basically run Node. js docdb or Node. js with the Postgres particle and you connect to MotherDuck. So again, I ran the demo. Now it's working as expected. So Postgres talks 115 times,
32:32which is about the numbers I write it down here, which is 107 and MotherDuck 1. 3 and here it did, I think, 1. 5. Yes, that's roughly, that's a better demo. All right, that's it for today.
32:51It's Postgres days and the analytics move. I hope I gave you quite some insight on how to think about basically how to manage your Postgres analytics. We have a few minutes left, so I'm going to answer a couple of questions. I see that we have people, Python Rust, Python,
33:15Node. js, R. Oh, Node. js and R, that's interesting. Is DuckDB supported on Postgres on AWS RDS? If you're talking about the Postgres extension, no, I don't think so because it's a custom
33:32extension and typically on a fully managed Postgres, the provider will have a restricted list of DuckDB extensions that you can install. As far as I know, we were in discussion with the AWS team, but I don't think it's there yet. Why is the analytical query so much faster?
33:57Because it's stored in columns. That's the way that the data is stored and therefore, the analytics engine is optimized for that. MotherDuck would be slow against other specific and any other OLAP database will be slower against a classic transaction, I would say query,
34:18because they are not designed for this. It's really about, if you remember on the first slides, this column and the row storage, which is completely different and the query pattern. Basically, the storage of each database is optimized depending on the query pattern. Postgres is not designed for analytics workload. When using MotherDuck with the Postgres endpoint,
34:43you have documentation about how write performance is affected, especially with hundreds of small write per second, also any limitation. I don't have this on top of my head, but I would recommend you to go to our docs if there is any limitation, so motherduck.com slash docs, and you can reach out
35:05to our Slack channel. If you go to slack. motherduck.com, I'll put it right here, you can you can join our Slack and support team will be happy to answer that specific question, because I don't know if you have that on top of my mind.
35:29Isn't just not allow reading the entire road? I find it surprising. Yeah, that's because it's depending on the query pattern and what you selected, you can select only a specific column rather than reading the entire row.
35:46Can pg. DuckDB be installed on a hosted Supabase? So, hosted Supabase or only local. When I think you mean hosted, the Supabase is fully managed Supabase, not you self-hosted. If you're self-hosted, you have control on the, I would say, extension. Again, as far as I know, I don't think
36:09Supabase is supporting a custom extension. They have pre-configured over 50 extensions. That being said, Supabase is invested into pg. DuckDB because they have one, they have people working over there, so they may have something
36:29cooking. If not already, I'm not up to date there yet, but again, I would recommend to look into that. It's not the best option, to be honest, pg. DuckDB is great if you remember the decision tree, if you cannot move your data out. But as we see, you can start to do small pipeline
36:51with any client in any language using DuckDB and the Postgres scanner. Say we cannot move our data for the same query, what is improved? The improvement using pg. DuckDB. So you're going to load the data into an OLAP query engine, but you're also kind of tied with the storage of Postgres, so you're not getting the same performance as
37:16an OLAP database with its own storage. But you can store data into DuckDB or offload to Parquet, I guess maybe that's one option because sometimes you cannot move out within a restricted perimeter to another, I would say, OLAP services, but you can offload it to an object storage
37:38within your VPC and others. So I would recommend to look into that. It's really easy actually to offload data to Parquet using pg. DuckDB because pg. DuckDB can read your Postgres table and export it to Parquet, Icebergs and others. We don't have a specific benchmark on that already. We do have
37:58some blogs. If you go on motherduck.com and search for pg. DuckDB, we have some reference and some tests that we've done. So a light benchmark, I would not call it a true fault benchmark. What about the impact on source Postgres when running the script? That's a really great question.
38:20So whenever we do federated queries to load the data, we are impacting Postgres, right? But the reason here is that you will only do that for loading the data, right? And the best practice for that is that you get a replica of your Postgres database and you do incremental
38:40loads. So the first load will be really slow on the replica. But if you have a replica for analytics at the moment, it's just, okay, transitioning that this one is going to be slow. And then when you've loaded most of your data to motherduck.com or any INTEL engine, then basically you just do incremental loads. And that on the replica will be really small because you're just
39:04loading daily the delta data. But indeed, I would not recommend you to basically do a full batch pipeline if your data is large on your Postgres database directly. You can do LLWord and kind of prototype on sample of data. If you do, you know, specific filter and so on that you're sure
39:25not eating a lot, you kind of mimic the prototype end to end. But the full batch will be, I would recommend on a replica at least. Another big helper is column stats in the row group. DuckDB will skip a lot of data or just use pre-aggregate from start. Yeah, sort of like
39:47DB index. That's very true. Thanks Nick for adding that. So there is column stats. And so basically we use pre-aggregate from stats like an index to. . .
39:59size query on the go. Yeah, so that's Parquet features, also internals. I've got Manjaro speed up exporting legacy DB to Parquet, now I load to MotherDuck for Claude. Yeah, so
40:20that's, again, that's a really good pattern because you offload to a columnar format already. So whatever you do after, even if you don't load to MotherDuck or another OLAP engine, I would recommend that's the first step. S3 storage is really cheap, so it's not going
40:38to cost you, you know, a lot on that storage. All right, we cover all, most of the questions. If you have more questions, please don't be a stranger and you can come to the MotherDuck Slack. We also going to have a Postgres guide launched this week, later this week, and you're
41:01probably going to receive it, one in your mail as soon as it arrives, which is basically a 24 page guide on everything we just talked as a summary and with some example of snippets and others. On that, I'll wish you a great end of day or beginning of day, and I'll see you in the next one. Cheers.
