0:00Hello, hello. Thanks, everybody, for joining us for this session on the Postgres endpoint, which is a new feature that we released recently here at MotherDuck. If you are joining us from somewhere cool and exciting, please let us know in the chat. I see some folks filtering in. Hershey, PA in the house.
0:20That's not far from where I grew up. Glad to see you here. As folks are filtering in, I thought we would just do some quick introductions, and then we're going to spend most of our time on demos today. I have just a couple quick slides just to kind of orient us, but I have also Louisa here
0:40joining as a special guest. Louisa, do you want to introduce yourself? Yes. Hi, I'm Louisa Huang. I'm an engineer at MotherDuck. I'm one of the engineers who built the Postgres endpoint. Very cool. And I'm Garrett. I'm on the Marketing Endeavor All team here at MotherDuck. Cairo, Egypt
1:01in the house. That's got to be the furthest that I've seen live on a live stream in the last few weeks, at least. Yes, thank you, everybody, so much for joining us. Glad that you could make the time. I know it's been a busy season for a lot of folks, so thanks for taking the time out of your day. Today, we're going to, as Louisa said, we're going to talk about the Postgres endpoint,
1:24which is a new feature in MotherDuck that Louisa and some of the folks on our ecosystems team have been working on for a long time. And the coolest thing that we've seen it enable is folks who have been able to add MotherDuck's analytical power to their applications without changing their stack. So that's what we're going to talk about today. Please put your questions in the chat as we work
1:48through the session. We'll take them live as they come up, and then anything that we don't get to, we'll make sure to cover at the end. Again, thanks so much for joining us. So I'm going to go ahead and present just a couple of slides to kind of get us oriented, especially for the folks that maybe aren't familiar with MotherDuck. And then we will back out of these and spend the most of
2:13our time on a couple quick demos. So what are we even talking about when we talk about Postgres endpoint? I think it's actually really helpful to kind of like orient on the way that MotherDuck works currently or has worked in the past before the Postgres endpoint. And that is with basically
2:35a client-server DuckDB architecture. And so this is just one example of how that can be implemented. But if you log into MotherDuck today and you work around in our SQL IDE or build a notebook, what you'll be experiencing is a client-server DuckDB architecture where there is a DuckDB
2:56embedded in the browser and also a DuckDB instance on the server in MotherDuck's Cloud. And that's because DuckDB, unlike Postgres itself or MySQL or many other database systems, is an in-process database. It's a single binary. There's not a client and a server
3:18for DuckDB. It's just DuckDB. And so the way that we make this work really well in MotherDuck is through this client and server DuckDB architecture. One of the ways that this enables some pretty cool experiences is through a feature that we call dual execution, which I'm
3:38actually going to demo. And that enables basically server-side aggregation of query results with client-side interactions. And so you get a lot of really cool implementations of this that look like super-fast filtering and scrubbing for data UIs. There's a MotherDuck feature that I'm going to show called Instant SQL, which is a really great implementation of this. And that's because that
4:01DuckDB instance runs in the browser via the DuckDB WebAssembly extension. This also extends into our Dives product, which are embedded data applications that you can use internally or embedded in your own application that implements this same type of architecture. And so that is the way that
4:22DuckDB works in a client-server relationship in MotherDuck today. Now, if you're building your own application, there are some advantages and trade-offs of having that DuckDB in the client, DuckDB on the server architecture. As I mentioned, some of those advantages are that you can build
4:41really fast, millisecond-latency interactions for customer-facing analytics. You also get to use your end-user's client compute with DuckDB Wasm, right? It all runs on a local machine, and so those are not network requests that are being taken just to do simple interactions. And of course, there's a cost-benefit there as well. And then it also enables what we call
5:06hypertenancy on MotherDuck. So that is a dedicated compute instance for each user, which allows you to scale them independently and manage costs more effectively. It also adds isolation to your architecture, which is a really nice benefit. There are some real trade-offs, though, to using the DuckDB client, which is that not every runtime can load the DuckDB library.
5:32These are cases like Cloudflare workers versus all serverless functions, where there are restrictions either on the size of the libraries that you can load or security restrictions where maybe DuckDB is just not allowed to persist as a client. There's also cases where you don't want to use a database per connection as part of your backend, or perhaps
5:54you're using a BI tool or a no-code platform that just doesn't support DuckDB natively yet. So there are some real trade-offs here when you're thinking about the DuckDB client for your own application. And so what did we build, right? When I say we, I mean Louisa and team. The Postgres endpoint is essentially a translation layer that allows MotherDuck
6:18to speak the Postgres wire protocol. It's, as the name suggests, a Postgres wire protocol in front of MotherDuck that allows basically just about any tool that can speak Postgres to connect without installing that client-side DuckDB library. We'll talk a little bit about the details behind this and some of the trade-offs here as well,
6:40but one thing to know is that you do still write DuckDB SQL, which is a very friendly and similar dialect to Postgres SQL, and execution still happens server-side on MotherDuck. And so, you know, while you may be interfacing with your application via the Postgres wire protocol, it is very much MotherDuck powering those analytical queries behind the scenes.
7:01So you can imagine a case in which you are using Postgres in an existing application, and you want to port over some of those analytical queries that might be slowing down or impeding some of your transactional workloads. You want to port some of those into MotherDuck so that you can give those power up. So just architecturally, this is just a, you know, kind of a concept
7:24in the DuckDB client version of the world. Now, this is the way the MotherDuck UI works. You have a DuckDB client that connects directly to a Duckling, which is our term for a compute instance of DuckDB that we run on the server. And so, as I mentioned, that's kind of like the example is that dual execution architecture that I showed you earlier. The Postgres client
7:48adds this endpoint, or sorry, the Postgres endpoint example adds this translation layer where you can connect via a Postgres client like psycopg. Other languages and drivers are pretty well supported, as well as we're adding support for BI tools.
8:07And the Postgres metadata catalog is translated into DuckDB catalog functions. Postgres types are translated to DuckDB types. And as you might imagine, Postgres wire protocol then is translated into the MotherDuck secret protocol, which connects to that Duckling instance. And so it's pretty simple, like, you know, framework here. Just think about like MotherDuck speaking Postgres.
8:35So let's talk a little bit about the use cases and when you would use Postgres endpoint versus when you'd reach for that DuckDB client. So the DuckDB clients really excel in exploratory data analysis. It's super easy to spin up, you know, an instance of the local DuckDB CLI, connect to MotherDuck or do analysis locally. We have a ton of Python users
8:56who are using the Python client for DuckDB to do exploratory, really fast data analysis. You're also going to want to use the DuckDB client for loading data into MotherDuck, as well as those interactive browser apps with dual execution via DuckDB Wasm. We'll show it. We'll see an example here in just a minute. If you are building an application with serverless
9:20functions or lambdas, things like that, you will definitely want to reach for the Postgres endpoint as DuckDB clients aren't quite widely supported yet. Example that Louise is going to show as well is an existing Postgres backend where you're adding analytics. Plenty of people run their applications on Postgres. They start to build
9:42and scale their analytical workloads on their transactional cluster and then things start to gum up or fall over or competing with transactional workloads. That's a great use case for adding the Postgres endpoint with MotherDuck into an existing application. And then the last is if you are using a BI tool or no-code platform. We're mostly talking about
10:05this thin client use case today, but this is a big unlock for the folks using a long tail of BI tools or even some of the core BI tools that just have improved support for MotherDuck via the Postgres endpoint. We just released Tableau support, Power BI support. This is also a great example for if you are using no-code platforms like Zapier or n8n
10:29that have well-supported Postgres integrations, but not DuckDB integrations. You can plug in the Postgres endpoint pretty easily there as well. Just a couple caveats to keep in mind. You write DuckDB SQL through the Postgres endpoint. I'll show an example here in a moment. You're not writing Postgres SQL, even though the dialects
10:54are very similar. In fact, DuckDB SQL was inspired heavily by Postgres SQL and borrows from it in the syntax. You also don't have quite all of the feature parity that you would expect just interacting with the Postgres database because MotherDuck is on the back end. The most important examples of these are stored procedures and materialized views, which are things you can't use through
11:17the Postgres endpoint. But of course, the trade-off is that you gain a really friendly SQL dialect and a ton of analytical power behind your queries. You'll also want to use a connection pool, especially in serverless. We have a great blog coming out on this using Cloudflare Workers as an example, and that's also included in our documentation. But you'll definitely want to
11:38use a connection pooler like HyperDrive or PgBouncer just to manage connections through the Postgres endpoint. And then SSL is required, and your token goes into the password field when you're making that connection, and we'll show that here in just a few minutes. Cool. And that is the end of Slideware. I'm going to stop sharing, and I'm going to switch over to
12:02the MotherDuck UI. And again, for the folks joining us, please put your questions in the chat as we go through. The demos here will kind of stop to take things as they come. I will just swap out. Presentation mode for a moment.
12:30Cool. So before we talk about examples of Postgres endpoint in action, I do want to show just like the first side of this, which is using DuckDB as both a client and a server. And that's actually what we've implemented in the MotherDuck UI. And so here I have a super simple notebook with a
12:52SQL cell that runs a very simple query with a filter on top of an orders table. I can run that query, and it pops up, and you get this nice interaction. The feature that I want to show you here briefly is just what's enabled by that DuckDB in the browser WebAssembly, which is
13:12instant SQL, which I will run. I think that keeps me in the mode. No, it pops me out. Instant SQL allows you to filter for results as you type for them, and it's pretty instantaneous, as the name suggests. Whoops.
13:46Just getting cursed by the demo gods, of course. There we go. So now the filter, now we are cached, and I can just delete
14:09and fix my syntax, and we get pretty instantaneous interaction. So that works the same way as I'm adding it back or ship region. I'll just flip back to instant SQL mode.
14:29I can filter on ship state or ship region, and that interaction is going to be pretty instantaneous. So all this is enabled by that DuckDB in the client architecture. Filtering and sorting is very fast, and you can implement this in your own applications as well.
14:53So that's an example of the DuckDB in the client and in the server version. I do also want to show you a quick example of the Postgres endpoint counterpoint to that, which I will flip over to in just a moment. Just a moment.
15:22Yeah, in case you missed it, Jerel just shared the documentation for the Postgres endpoint. Please check out that link as an example. We also have a cool example, Jerel, if you wouldn't mind sharing, just of the Cloudflare workers demo that Dumkey created earlier this week. I think that's a really nice example of an implementation as well.
15:45Cool, so I'm just going to flip back to. . . Okay, so here's something different, which this is a SQL IDE I really like called TablePlus. It's just a desktop application, and the thing I want to highlight for you here, it's the same data set, it's just that instead of connecting through the DuckDB client to
16:08MotherDuck servers, I have a Postgres endpoint server that I'm connecting to. All right, so I'm on US East 1. It's also supported for our EU Central 2 region, and you can see that I have a Postgres user rather than a DuckDB user.
16:29And so I'm actually not going to cancel, I'm not going to change this connection currently, but if I wanted to add a new one, for example, I could connect. You see I have a Postgres connection that I'm using right now. I also have DuckDB connections, but the Postgres connection is backed by MotherDuck. And so I've got like a very simple query that I can run, just like star,
16:53but you also get the affordances provided by DuckDB SQL. So these are really clever syntactical things like skipping selects and just going straight to from orders where it should region is California. And we can see that we're actually writing DuckDB SQL here rather than Postgres SQL.
17:16Just one thing that I discovered actually late last night that is really neat, if I can grab it, is in the console, we can actually see the PG catalog functions that are being accessed here. And so there's a little bit of like that translation that we can see from the console perspective. But again, like we were just accessing MotherDuck through a Postgres
17:39translation layer rather than, you know, working through DuckDB directly. So just a quick couple of examples of the difference between that DuckDB and the client and server architecture versus using the Postgres endpoint to connect, which we're doing here.
17:59I'm just going to stop sharing here, and then we will take a look at any questions we have, and then we might actually go ahead and move on to a very cool demo that Louisa has built. But just before we do, I'm just going to take a quick look. MotherDuck connection. We'll actually tackle that here in a minute. Roger.
18:24There's one good question that you might know the answer to, Louisa, and sorry to put you on the spot, which is having a layer in between, will we have any performance hits from using the endpoint? Um, yeah, that's a good question. So theoretically, yes, there's definitely the cost of translating
18:48the DuckDB results back into the results that format the Postgres protocol expects. But I think what we've seen in practice, when you're especially building an application, which I'm going to show in a moment, you get really fast
19:11spin-up time or connection time, and you get these really lightweight connections. So if you're in a serverless environment, that's actually beneficial. Excellent. Thank you. I think there's one very similar question that I might just put up here from Roger, who asks, is the MotherDuck connection in AWS Lambda unsupported?
19:35We have DuckDB itself working in Lambda currently, but are thinking of more direct integration with MotherDuck, specifically streaming some data into a DuckLake. Would you suggest using the PG endpoint for this? Yeah, so I think this question is about kind of how you ingest data into MotherDuck. And so for Lambdas, both the DuckDB client and the Postgres.
20:00the Postgres client would work. And for Ingest, it's probably better to use the DuckDB client just so you can kind of batch a lot of the, batch the rows and write them quickly into MotherDuck, say using the Appender API. Excellent, thank you.
20:24Okay, I have one more that just came in and thanks for taking all these. Nick asks, the ODBC Postgres driver connected to MotherDuck. Will ODBC built-ins work correctly, like get primary keys? This is a pretty specific question. Maybe you know.
20:46That I am not too sure. If you try it and it doesn't work, you can report it to us and we'll get it fixed pretty quickly. Yeah, great question, Nick. There's a lot of testing that Louisa and the team have done that goes into this. So please try it out and either join us in community Slack
21:10or let us know at support at motherduck.com. Definitely curious to see what you find. Okay, I think it's a great point to switch to a little, to your Vercel demo, if you don't mind. Oh yeah. Okay, so yeah, so this is like a quick dashboard application
21:37I've built with the Postgres endpoint connecting to MotherDuck. It's using Next. js and running on Vercel. Yeah, so this is a dashboard on the NYC taxi data and it shows a bunch of stats and trips on trips per day, pickup zones, kind of a Sankey diagram that maps the payment method to the fair type.
22:03You can also click on these, the dates to get kind of a breakdown of the like analytics per day, all built using kind of standard recharts components. And every chart is powered by a query that's run through the Postgres endpoint to MotherDuck.
22:27And it's using the node-postgres driver. So I guess that's why using, it's using the Postgres client but it's still like a query is still powered by MotherDuck. So, which is why you can see like this dashboard is blazing fast and really responsive to my clicks even though every time I click on it
22:51it's actually rerunning the query. Which is like computing some aggregates over millions of data points. So yeah, so I guess this is kind of showing how like with the Postgres endpoint you get to use any standard Postgres driver,
23:13which, and that's like a very mature ecosystem and you can pick any connection pools that works with Postgres and on the server side you can get the DuckDB performance for your analytical queries. And are you using, sorry to interrupt you, are you using a Pulse Duckling
23:36or what is the size of your computer? Yeah, so this is running on a free MotherDuck account and it is using a Pulse instance. Okay, so super lightweight and you still get like a really fast, nice interaction. Yeah, so I'm gonna switch over to the code
23:58which is, there's like a public repo available. Okay. It's taking a while to find. And just as folks are checking that out or thinking about building their own demos,
24:21Jerel added a repo link to the comments. So feel free to check that out as well as the Vercel application link for what Louisa built as an example. Seems like it came together pretty quickly. So that's really cool. Yeah. Yeah, so this file is just kind of everything
24:44you need to connect to MotherDuck through the Postgres endpoint in like a node application. Yeah, so here we construct the connection string. The username is like, you can put anything here. The token is your MotherDuck token
25:08and the host name is based on your MotherDuck region. So for the default would be US East one. And the database here, I put sample data because that's the data based on grant query. You can also put, so if you have multiple databases in MotherDuck, you can do like MD colon
25:30and that will kind of give you connection to the entire, your entire MotherDuck workspace and you'll be able to query all the databases through the same connection. SSL is required. Also, we are using a connection pooler here with max 10 connections.
25:52So that lets you run 10 queries in parallel and the connections. So, and then because I'm in, I'm building this on Vercel and Next. js, which is like a serverless environment. I'm reaping the connections pretty quickly after they go idle.
26:13And for Vercel specifically, after creating the pool, I also called this helper function to make sure it keeps working. It works in the Vercel serverless environment. And here are some helper functions to kind of use the pool to run queries.
26:35So this one lets you check out a connection and run a single query to MotherDuck. And this one lets you kind of group a bunch of queries and run them sequentially. On the same connection. Yeah, so let's check out the backend,
26:56that the API that's endpoint that's serving the dashboard. So basically what this does is whenever the front end kind of requests, kind of, so like it will request data for a specific time range.
27:19It will send this request to the backend and we'll start this function. And all it does is run a bunch of queries and promise all, so they all run concurrently. Yeah, and then collect the results and return it back to the front end.
27:42And are you, like your query syntax, it's all just kind of vanilla Postgres? Yeah, I think these are pretty like standard SQL. Yeah, they're like SQL standard. Yeah. Informant. Okay, so no DuckDB syntax tricks here, at least.
28:06No. Yeah. Yeah, so let's talk a bit more about kind of the connection pooling side of things. Oh yeah, okay, back to this. So if you've worked with a Postgres database,
28:29conceptually the reason why we use connection pool here is not very different. Because creating the initial connections will take some time. It will have to, the application will have to reach out to the Postgres endpoint. We'll have to do the TCP and TLS handshakes and authenticate to MotherDuck using token.
28:51So that can take say a few hundred milliseconds. But the MotherDuck query can actually run much faster than that. So by opening a connection pool at the beginning of the session or at the start of your server, if you have a long running server, you can just keep reusing the connection
29:14so that every subsequent user interaction and query goes through the already established connection. So you can get the raw MotherDuck query performance without the overhead of creating these connections. So we recommend always using a connection pool when you're building an application through the Postgres endpoint.
29:39And I guess the configuration here. So I think the pool size and idle timeout really depends on the needs of your application. Like for example, how many queries you want to run concurrently. And also your server environment. I think serverless is slightly different
30:03from long running servers. And, but a rule of thumb is anything that sort of the Postgres, like the real Postgres connection pooling best practices generally also apply to using the MotherDuck Postgres endpoint.
30:29There's actually a question about that specifically. I wonder if we could just tackle, you shared this briefly, you had a limit in your code for connections. What are, are there connection limits in using the endpoint? Yeah, so there is no hard limit,
30:52but I think the same, I guess what I was saying earlier, that connecting the best practices of connecting to a real Postgres database sort of applies. So like, I think you're working with a real Postgres database, I think a few thousand connections is a lot.
31:12So yeah, so I think for example, 10 here is very reasonable. And yeah, so maybe like a hundred, you could go to a hundred for like a pretty high scale application and it should already work pretty well. Very cool. There's one other question floating around
31:36that I want to get to, but it's kind of orthogonal. Was there anything else that you wanted to, I interrupted you with a question here. Oh, so I guess another neat thing, I guess, with the Postgres endpoint for those of you who are more familiar with MotherDuck
32:00is that there's this feature in MotherDuck called read scaling. And actually what this feature does is in MotherDuck, you can acquire a read scaling token instead of a regular token. And using that token to connect to MotherDuck
32:22will route you to a fleet of identical Ducklings, which are like isolated compute environments to run queries. So, and you can adjust the fleet size kind of to scale out. So the neat thing is if you did use a read scaling token
32:45here, the pool would just be initialized with the different connections being like routed to different Ducklings. And so kind of what happened, if I were to use a read scaling token here, what would happen in the application is kind of here.
33:09It would be tracking out these different, six different connections and each connection would be potentially routed to a different duck link. And that kind of gives you a horizontal scaling to really handle a lot of concurrent queries.
33:40Yeah. So is a use case where you would swap in the read scaling token, you know, that same example that you showed, but just with a high user demand? Yeah. Yeah. And would you, just for the folks that don't, aren't as familiar with read scaling, you know, it sounds like your first choice would kind of be to scale that pulse instance
34:04across like a fleet of read replicas. Is that fair? Yeah, that's what happens if you use read scaling. I think you can also like create bump your instance size without going read scaling. There's actually a relevant question here. I might just ask. Roger asks, are the connection limits different
34:28depending on the chosen duckling size and do different users have separate connection limits or is it more of a service level limit? Yeah, great question. So I think right now, there's not a lot of use cases where you can do that. Great question. So I think right now there's no like, yeah, it doesn't,
34:51it's the connection limit is independent of the user or the duckling size. Cool, thank you. Cool, well, thank you for sharing.
35:15Yeah, thanks for sharing. There's one more that I wanna get back to if you don't mind, but yeah, folks have other questions about Louise's demo or about Postgres endpoint generally, please throw them in the chat. We've got five or 10 minutes left if we wanna use it just for questions while we have sort of the expert here.
35:37One from Denal a few minutes ago asks a dbt question. Does the integration support running dbt queries via Postgres? Yeah, so I've tried it and I think the answer today is no, but we are actively working on making that supported. Excellent.
36:04Okay, then I'll swap this one out. There's another question down here about Postgres extensions. About the PG extensions, are third-party extensions compatible and are there any DuckDB specific extensions for Postgres? Yeah, so when it comes to extensions,
36:25it would, I think I would think about it as kind of what extensions does MotherDuck support on the server side. So I think we have Docs on a list of DuckDB extensions that MotherDuck already supports. And so that should be, yeah,
36:49so the Postgres endpoint should support the same. Excellent. This is a thing that always like messes with my head a little bit that it's still a DuckDB server. Like at the end, right, we're kind of just translating. It's, you know, there are other implementations like pgDuckDB, which are different interactions
37:11of how DuckDB and Postgres sort of meet up, but very much a DuckDB server behind the scenes. Yeah, thanks everybody for asking. I think that actually brings us to the end of our sessions.
37:36If folks have any more, please check them in the chat in the last minute here. Otherwise we will wrap up, I think. Unless Louisa really wanted to chat about our show. Oh, getting a little bit of internet lag. I think you're back now.
38:01Yeah, no, there's nothing more for me. Cool, well, yeah, thanks again for joining us. Thanks for the demo and sharing your examples. I appreciate everybody who is in the chat asking questions. Check out those documentation links that Jerel shared in the chat, as well as the Vercel link for Louisa's demo
38:25and the repo for all the code that she shared. So you can see all the details about the connection limits that she chose and how it was implemented in this Vercel dashboarding demo. Thanks everyone for joining and we will see you next time.
