Skip to main content

DuckLake Performance Optimization: Faster Queries From Your Lakehouse

Oct 27, 2026Featuring: ,

Alex Monahan of MotherDuck and Matt Martin, co-authors of O'Reilly's DuckLake: The Definitive Guide, walk through the book's performance optimization chapter. They cover how to find the bottleneck, how to partition and sort so queries skip files and row groups, which Parquet settings to tune together, and how to keep the catalog lean. The session uses slides and diagrams, with audience questions throughout.

Why DuckLake reads are fast

DuckLake keeps its catalog and metadata in a SQL database instead of on object storage. Alex contrasts that with Iceberg, where a read goes through four steps in series (the catalog, a metadata file, a manifest list, and manifest files), each taking 100 milliseconds or more on object storage, before any Parquet is read. DuckLake does one lookup against the catalog database, which he puts at around five milliseconds, and then reads Parquet. Matt adds that a relational catalog benefits from decades of indexing and tuning experience, and that code written against a local DuckLake moves to a cloud runner unchanged. In DuckDB 2.0, Alex says DuckLake gets about three times faster on large reads because async I/O lets each core run several background requests to object storage at once.

Find the bottleneck first

Measure before you tune. Matt's test is whether the juice is worth the squeeze, and Alex suggests focusing on workloads where people are waiting on a spinner. Turn on DuckDB's profiling and logging, which record HTTP times and time spent scanning, joining, and aggregating as JSON, log slow queries on the catalog database, and point an agent at the logs. Alex's rules of thumb: big reads point to file layout, small reads to the catalog, big writes to compute, and many small writes to the catalog and inlining.

Partitioning and sorting to skip data

Partition on a column most queries filter on, such as a date, so DuckDB skips whole files. Then sort so Parquet row group statistics let it skip row groups inside the files it does read. DuckDB 2.0 lets you declare partitioning and sorting inside CREATE TABLE and CTAS, which Alex notes suits dbt. DuckLake reasons about partition transforms, so filtering on a timestamp still prunes year and month partitions (hidden partitioning), and DuckDB pushes filters across joins into the scan. Changes only apply to newly written files, so recreate the table with a CTAS to rewrite existing data.

When queries filter on different columns, sorting by one column and then another scrambles the second. Alex recommends space-filling curves such as Z-order or Hilbert, which sort a little by both, and walks through a "restaurants near me" geospatial example. He says sorting by an arbitrary expression is unique to DuckLake, and calls this his favorite optimization.

Parquet settings, tuned together

Alex's three settings to try: Parquet version 2, Zstandard compression instead of Snappy, and larger row groups (about 4 MB per column, two to four times the default). Aim for roughly ten row groups per file, and move file size and row group size together because they affect each other. He suggests asking an agent to run a design of experiments instead of tuning one setting at a time. In his own testing about six months ago, tuning these together made queries around 40% faster, and he did not have per-setting benchmarks. For very wide tables he suggests trying uncompressed.

Inlining, catalog tuning, and maintenance

Inlining writes small inserts (ten rows or fewer by default) into the catalog instead of creating tiny Parquet files, which keeps latency low and avoids the small-file problem. Reads combine inlined and Parquet data transactionally. Flushing applies your partitioning and sorting, so Alex suggests flushing at about your target file size, every million to ten million rows, not every hundred.

For the catalog, he suggests adding an index on the column statistics table, giving the database enough RAM to hold the whole catalog, and expiring and deleting old snapshots, which Matt says you can do progressively (daily recent snapshots, then weekly, then monthly). Alex's maintenance advice is to compact deletions and compact files toward 1 GB or more, and to keep a ten-day deletion window so you never have to recover files from object storage. The first five chapters of the DuckLake book are available as a PDF, and Matt expects the finished book in early 2027. See also the DuckLake docs.

1:03Hello and welcome everybody. Pleasure to have you here with us. This webinar today, we are talking all about DuckLake,

1:13our absolute favorite data lake house technology and how to push it to the max. So we're going into the deep end of the DuckLake today. Um, faster, cheaper pick two. That's, that's our plan. And I am joined today by a special guest. Matt Martin. Welcome Matt. Thank you, Alex. Um, pleasure to be here to talk to you all and give you all some updates.

1:37I know we've been talking about this book now for several months. Uh, rest assured we are getting to the final end of, uh, this journey and we're looking forward to releasing this thing, uh, hopefully early 2027, I believe is the target date. So, uh, it's been a great, um, experience. And we're happy to share with you guys today, uh,

2:00some talks on performance optimization and how to get the most out of your DuckLake. And like Alex said, you get to pick both. You don't have to sacrifice one over the other. You bet. I appreciate that. And you will get to see the book long before the official, you know, physical books get printed. That takes more time. Uh, we launch about a chapter a month or so we've released five chapters out so far.

2:23You can head to the link that just went in the chat and that, uh, we'll let you download the first five chapters in PDF form right now. And we'll email you in your email inbox, uh, every single chapter as it comes out and you'll get that, you know, about one a month. The other thing I wanted to call out is next week, MotherDuck and about 10 other folks are hosting a conference in San Francisco

2:47called data outpost. It's all about being at the frontier of data and AI. And, uh, we have a coupon code for you. It's going to be DuckLake one 50. Thank you for being here. You can join us and get a discount on those tickets. And Matt and I get to meet for the first time in real life out there in San Francisco, which would be great. And we're going to do a signing of our early release book.

3:10So you'll get a physical copy with those first five chapters. If you join us. Yep. That's great. I'll actually finally be able to prove to Alex. I'm a human and not a robot. Hey, that's for rating is one. You'd be pretty good AI man. Uh, you, you would have put me pretty well. At that point. Uh, but yes, that'd be great. And, uh, there's also a workshop. If you join us on the fourth, our workshop day, Matt and I are leading a workshop as well, all about iceberg and DuckLake.

3:34So you'll get a taste of both. So have a jump there. Data outpost. ai. So our plan for today, I'll give you a quick rundown of, of what is DuckLake, if this is a new term for you, most folks, probably not. And I'll do a little bit of review of the architecture because we'll be able to fit our tuning advice into those buckets of the architecture, like little puzzle pieces. Um,

3:58so we'll go through it pretty quick. What I wanted to do is since I'm going to go fast. And since we're talking about something so specific, like tuning your questions are super, super helpful and valuable. So please put as many questions as you can into the chat and we will answer them as we go. And we'll have time at the end too. Uh, the most important thing is that you get something out of this and you learn how

4:21to take your DuckLake to the max and you've got Matt and I here. So, uh, give us your best shot briefly. What is DuckLake? Well, DuckLake is an open table lake house format, and it's created by the folks over at DuckDB Labs, but it is truly open. Uh, it uses parquet as its data format, which is very open. It has, uh,

4:43readers in Trino and DataFusion and DuckDB of course also is very portable and has a engine for it as well. The key goal for DuckLake is to be simple. And that's not something you hear from most data products or data platforms, but a lot of things flow from that simplicity here. You can see there's three commands.

5:07That's all it takes to set up duck. Like if you have one command before this to install DuckDB, that's really all it takes. Um, and then with that simplicity, you get a lot of speed because there's a lot less happening. And when there's a lot less happening, that elegance actually gets your speed too. We do have plenty of scalability. DuckLake is designed to be in that kind of petabyte range of data.

5:31The key way we do this. So it's like, Hey, Alex, you know, icebergs been around a long time. How are you able to, to, to do this so differently? The key is we store our data or our catalog and metadata data in a SQL database instead of on object storage. And we'll see that in a diagram here in a moment. Um, but Matt, I'm curious, what was kind of your first experience, uh, using DuckLake? Uh, what did it feel like to use it when you first checked it out? Well,

5:55I remember when I saw the initial press release on it and, um, I said, all right, might as well go give it a shot. I'm pretty well versed in iceberg at this point, been living and breathing it for a few years. I've dabbled in Delta Lake some, and then when I tried it for the first time on my workstation to create a DuckLake, uh, I was completely shocked at how easy it was.

6:20I was like, wow, it's not like it is with iceberg where you have to configure spark and make sure you have the right version of the JVM, the right iceberg run times. If you're connecting to a data store such as AWS or GCS, make sure you have their correct jars installed and all the authentication stuff and proxy redirects. And, you know,

6:45basically I was shocked at just how easy it was to get a DuckLake connected. And, you know, here he's showing, in my opinion, two lines. Sure. You can run the use statement at the end, um, to get it wired to, um, to S3, for instance, you just have to add, uh, in a secrets, uh, create secrets command ahead of time.

7:07And then you can specify for the data path at being an S3 bucket. Um, it's, it's just, it's nuts how easy it is. I could go on and on about it, but yes, that's, that's what my reaction was. I was like, wow, there's definitely something here. They have solved a big point of friction on lake houses. You bet. And I think that's a key point as well, is that it's easy to start with.

7:30This example is to set up a DuckLake on your laptop, but it's just as easy to run it on the cloud as well. And we'll talk about that part too, but that's the goal is that it's easy to start and also easy to run with. And we'll see it's, it's not too hard to tune either, just to have to have a couple ideas. And that's what we're here for. So why are we super excited about it? Um, we really believe DuckLake is the future. If you look at what iceberg is working on there,

7:54they're trying to trend in the same direction of fewer metadata files and, and more focused on the catalog. So really we think that that DuckLakes out in the front here. Well, we love it because it's very, very scalable. You know, this, this technology can handle whatever you throw at it. Petabyte, no problem. Uh, if you want to crunch your data in there super fast and then compacted in the background,

8:18it's got great ingestion speed. So it's really great for logs, observability, data metrics, agentic traces, all that you can bring your own blob storage. So this is an open lake house format. That means you have this independence where you get to keep the data right where it's, it's yours. You can grab it as much as you can grab, you know, a set of bits on S3, but it's yours to your bucket.

8:41And a key differentiator as we'll see is that you actually get low latency out of this. Most houses feel like an archive. It feels like you're going in the library. You're having to relearn the Dewey decimal system. You're walking down the dusty books. You're, you're, you know, looking for it, finding it and grabbing it out. Right? Um, DuckLake is super quick to, to go get and retrieve data that you need. Right. And one thing I want to add on to this folks, um,

9:06from a developer experience, um, developing locally versus the cloud for DuckLake is as easy as it gets. The portability is fantastic. Literally the code you write on your workstation can be copy pasted into a runner on EC2 or Fargate or any other compute service in the cloud. And it would just work. Um,

9:30you can't really say the same about local development of Delta and iceberg. And that's why you see those managed platforms that Google has for spark and Databricks and all of them exist because having all their tooling installed with the correct jars, the correct Java runtime, all that stuff takes a lot of careful planning and architecture. Whereas DuckLake,

9:54I can run it locally. If I like what I see and I want to schedule it, I can just promote it up into a cloud runner and that's it. Yes, I agree. That consistency of local to prod is underrated, especially if your agent wants to move fast. You want it to do some work locally where it can't break anything. You don't want prod to break as soon as you deploy. It's got to be exactly the same. Right. Um, well,

10:18thank you. Uh, the, the next piece we're almost done with talking about the kind of overview. This is the contrast with iceberg. So icebergs on the left, DuckLakes on the right, where if you want to read any data off of iceberg, there are four steps. They have to run in series. They have to wait for each other. First, you talk to the catalog, which is a rest service that has a database hidden behind the scenes.

10:42And then you have to go ask for a metadata file, get a manifest list, and then read any number of manifest files. And only then can you go read your parquet files sitting out there in object storage as well. And you'd think, okay, a couple of requests. Networks are fast. No big deal. Well, unfortunately that metadata layers on object storage, and it is really good at high throughput and not good at high at low latency.

11:07So it can take a hundred milliseconds or more on each of those requests. And if you add that up, you're a third or a half of a second behind out of every query in the absolute best possible case with iceberg. Whereas with DuckLake, you talk to Postgres once, maybe that's five milliseconds. Uh, and then you can read your data on parquet. Or if you're on MotherDuck, you read a MotherDuck catalog, five milliseconds,

11:31read your data on parquet. So cuts out a lot of the stuff in the middle. And, um, that gives you performance along with simplicity. Uh, it's just that, you know, it all lives in the database. This diagram, I think is also helpful to understand it. You know, in some ways DuckLake is a revolution in other ways. It's, I think a common sense, you know, way it should have always been type of solution. That's part of why I like it. Um, you know, I've got my, you know,

11:57uh, I don't know, like the, you know, old man, you look at the sky, like, why do we have to have that so complicated? You know, I feel like we, we get to avoid that with this. So iceberg and Delta, in my opinion, and I think the data is with me, they put their metadata in the wrong spot, put it on object storage. They should have put it the same place they put the catalog, which is a database. And so if you do that, uh, you get a lot of benefits.

12:21And so the reason I bring this slide up in particular is we talk about these three buckets a lot, uh, in how we tune things, the relational DB bucket, we call that either the catalog or metadata interchangeably with duckling sort of the same spot. So we'll, if we talk about tuning your catalog, that's what we're talking about. Uh, tuning your storage is, you know, object storage S3, Google cloud storage, what have you. And then the compute nodes, that's where your actual query is running.

12:45That's usually going to be DuckDB running somewhere, either on your laptop or in the cloud, but it could be Trino could be DataFusion, whatever you're using. You know, another thing too, to mention Alex on the catalog for duck bake, duck, duck, bake, DuckLake being in a relational database. If you think about it, folks, we've been not me personally,

13:06but the industry has been doing relational databases for over four decades at this point. And they've had a lot of learnings, a lot of walls they've hit and a lot of optimizations on how to get around that object store, you know, cloud object store hasn't been around nearly as long.

13:25And so when you kind of put things back where you think they can best thrive and shine, you get a huge speed boost with the relational databases, because we can index them, we can fine tune the metadata lookups. We've got a lot of bells and whistles in those systems that can really help us accelerate the performance of queries. Yes.

13:49There's a few things you can tune on a database. The good news is that they do tend to work pretty well out of the box, and they're very well understood. Those agents know a lot about them. So you can tune it up pretty fast with agents. I want to say thanks for the questions, folks, and keep them coming. We'll jump into one or two here, and then I see some others, and we'll talk about them in a bit. So someone is asking about DuckDB 2. 0. We have that on the list. We'll talk about that.

14:13The short answer is DuckLake gets about three times faster with DuckDB 2. 0. Not bad. So if, and that's especially in the bulk reads. So like your big, beefy reads where I really need to crunch a lot of data, that's where you see those biggest wins, which is really phenomenal. We had a good question on, hey, I'm new to DuckDB and just learning.

14:38How can I learn about DuckLake? We're going to plug the book. Definitely check out the book. There's also some really great YouTube content by Hoyt Emerson. He's on the chat here as well. If you want to see him there, you can ping him as well on his socials. And he's got a lot of good DuckLake content. Matt has been known to blog on Substack as well, and he's got a bit of content there as well.

14:59Yeah, and then the documents that they maintain, just the public documents for DuckLake are pretty good as well. Yes, DuckDB docs are impressive. They put a lot of time into it. Fun fact, the DuckDB docs are one of the most popular websites in all of the Netherlands. Wow. Pretty cool. Yeah, there's a lot of emphasis that they put on those.

15:24That was not on my bingo card. Hey, man, there's a lot of stuff that's pretty shocking about the scale of DuckDB, actually. Pretty, pretty cool stuff. All right. So someone is asking about Predicate Pushdown, which I love to see. We will talk all about that. So, Chris, your answers are coming. OK, we got a question, Lakehouse versus Warehouse.

15:48I think this is one we could go at length, but we can give a brief answer here. So I'll turn it to you first, Matt. How would you think about Warehouse and Lakehouse and kind of the tradeoffs there? Sure. So if you look at your traditional data warehouse, usually it's a package deal. It's appliance of some sort or a fully enclosed service, meaning the compute and the storage are closely knit. They're closely coupled together.

16:13And that's fine for a lot of traditional on-prem workloads. However, a Lakehouse is going to be a lot more scalable and a lot more cost efficient, especially if you're going to be re-platforming to the cloud. So if you're going from something like a traditional data warehouse, Teradata, for instance, where it's the massively parallel, share nothing architecture,

16:37again, that is a complete appliance on a server where the storage and compute are tightly coupled. When you transition to a Lakehouse, they decouple that. And so as your data continues to grow, you don't have to go buy more compute. And that's one of the tradeoffs. With warehouses, traditional data warehousing, you would have to go buy more compute.

17:00With Lakehouses, you can let that storage grow on object store. It's, you know, they say infinitely scalable. I know that there's physical brick and mortar data centers where this stuff does reside. So if we ever hit a gazillion Yotabytes, I'm pretty sure we will finally hit that limit. But again, just to kind of circle back, the big bang for your buck

17:21and the efficiency on Lakehouses is the storage and compute scale independently of one another. And they are purpose built for the cloud to thrive. I agree. I think DuckLake in particular has an even extra lever you can scale, which is you can scale your storage, your compute and your catalog and size up that catalog database, too.

17:44So it's even more tunable from that perspective. A big benefit is that you get to own your data in this in this world. And that does have cost advantages. It has longevity advantages. You know, you want to put it somewhere. You want to keep it there for a long time. And Lakehouses are really, really scalable. So if you have 10 gigs of data, 100 gigs of data, you can use a DuckLake because it's just so much easier than than any

18:09of the other Lakehouses. It tends to have its biggest bang for the buck when you get in that terabyte plus range up to that petabyte. Be another way I think about it. Well, thank you for the question. And let me see if there's anything else we want to jump to before we do a quick bit on MotherDuck and then keep going. I see the Hive Metastore question. We may we probably save that to the end. We can we get a pine on the Hive Metastore.

18:33Thank you for your question, Brian. All right, and Scott has a good question there. We will look at that briefly. So you can run your DuckLake workloads on MotherDuck. MotherDuck is the infrastructure for answers. So we have a managed serverless DuckDB compute platform. But it's a lot more than that. If you've blinked, things change around here in AI land. And we're firmly in AI land here at MotherDuck.

18:56So we have a Python runtime now as well. So you can ingest your data with a prompt. We have a full data app capability called Dives where you can build a full fully interactive data app with a prompt as well. We have a context layer to make sure your agents give you the right answers. And you can bring your own agent with our MCP. So that way you can use the token subscription

19:18you already have, but then get really nice, accurate answers out of your data platform with MotherDuck. And with MotherDuck, you can store it in a MotherDuck database like a data warehouse, or you can store it in a DuckLake. So in a little bit, in a certain way, you don't even have to decide, really. You could go back and forth. You can have one table be in one, one table be in the other, and mix and match. And they all just look like a table to MotherDuck.

19:43So you can use both. All right, let's jump into performance tuning. I will invite you guys again to ask questions here. If you want to help tune in your specific environment, if you have questions about the techniques we're talking about, or DuckLake in general, we'll have time for that. So really the first step, as with anything, is make sure you have a problem before you go fixing a problem.

20:07It's just good advice in general. I think we can get into trouble with agents now where we can burn a lot of tokens and a lot of time and attention and then go back and look, oh wait, I just saved, you know, one millisecond. You know, what was I up to there? So make sure that we measure the scale of the difference of what's going on. Right. I always try to tell people, is the juice worth the squeeze?

20:28Are you going to spend another six hours shaving off another two seconds on a batch job that runs once a day? It's not worth it in most cases, especially if you're meeting your SLA for your stakeholders. Yeah, focus on those workloads where people are staring at a spinner and they're waiting on things.

20:48I think DuckLake is probably the first lake house where you could actually have interactive workloads on it and not be uncomfortable. So it is a new thing to optimize for a bit. So I'll put this into the kind of the three buckets we talked about your storage, metadata and compute and kind of how I would think about things.

21:07On the storage layer, I probably wouldn't start there from like measuring the actual hardware of your storage layer. If you're using object storage, object storage is probably working pretty well. It's a commodity at this point. That would not be the first place I would look. But what I would think about is how you're laying out your data on object storage.

21:26Are there tons of tiny files? Are you keeping around lots of history that you don't need? And we'll talk about those things. But the actual storage itself is not as critical. That being said, to be thorough, I would try and measure your S3 latencies. How is that going? If you hammer S3 harder, they give you more speed automatically.

21:48So that's something to keep in mind. And I would measure your total storage size. That's a little bit in the weeds of the DuckLake architecture. But if you're writing data and then someone beat you to it and they changed the table while you were you're writing, there are cases where by design you will write extra files to storage that you will not use in your DuckLake.

22:09And so you do want to monitor your total storage use on your object store. That tends to not be a cost driver, but it's worth monitoring. The metadata side, this is the same as your catalog. Same, same, same. You can log those queries that are run either through DuckDB, which is really nice, or through your catalog DB itself.

22:31So if you're using Postgres, for example, which is a very good solution for your metadata catalog, that has options where you can say any query over X time, log out both the query text and the explain plan. So I know what the database was up to. DuckDB has the same thing. I can flip to and show you guys where you can find information about how to set that up.

22:55The compute side, by and large, is going to be DuckDB and most production deployments of DuckLake today. So you'd want to profile your DuckDB queries by turning on DuckDB's profiling settings and turning on logging as well. It can do things like log the HTTP times, log the amount of time it spends scanning, the amount of time it spends doing joins or aggregations. So you get a lot of detail by turning that on.

23:19And then my favorite thing is to point my agent at that directory and say, hey, agent, what's the bottleneck? And usually it does a pretty good job parsing it because it logs as JSON and agents can shred through JSON like nobody's business. So that is kind of first thought of like where I would look for the issue is make sure you're logging everything.

23:40Make sure you're looking at those logs. And I would start with either your catalog or your compute. High level rules of thumb. If you're doing big reads, look at your file layout. And that'll be partitioning and sorting, which we're talking about next. If you're doing small reads, pay more attention to your catalog because you're going to be hitting that catalog more often relative to the big reads.

24:04If you're doing big writes, I would look at your compute resources because it might be that it's taking some extra time to actually compress the data to write it into Parquet and send it to storage. So look at your compute if your writes are big and you're slow part. If you're doing lots of small writes, I would look at your catalog and consider using the inlining feature, which we'll talk about.

24:27It allows you to kind of use your catalog as a staging area or your writes, which is pretty slick and unique to DuckLake. So that's the first step. I will probably skip the docs. What I'd recommend is making sure you look at both the DuckLake docs and the DuckDB docs because it's actually split in both places for the logging information.

24:49Now we get to into what I think is the fun stuff where we get to be a bit creative and do some experimentation about partitioning and sorting your data. I could read less, so I'll pull up a diagram. I wanted to turn it over to you, Matt, and just hear about your experience in the past doing partitioning and clustering and things like that. What have you seen success with? Sure.

25:09And so the thought of partitioning and clustering, it's to just kind of level set, folks, it's not a new concept. It's been here for a very long time. It definitely got more popularized when Hadoop was in the scenes for object store and stuff. But it was even around before that. You can partition and cluster your data on, you know, Teradata, SQL Server.

25:33You can do it in a lot of places. And so one of the big advantages of partitioning, and this is especially true on large tables. And when I say large, everybody's like, well, what's the minimum size? Let's just let's call it a gigabyte or larger.

25:50If you partition your data based on a very common filter, such as like a date stamp or something, that is one of the cheapest ways to get performance boost because you're not having to throw extra hardware at the problem. You're basically just reorganizing the files to where once the query is executed,

26:11the optimization engine in DuckDB is smart enough to realize, hey, this query is filtering on the order date. And I know that this table is partitioned on the order date. So I'm going to just go to that one date and skip all those other files. Usually with big data sets, IO is your biggest limiting factor. You know, we can throw a lot of RAM. We can throw a lot of compute.

26:34At the end of the day, it still has to read the data. And so IO is going to typically be a big bottleneck. Now, that's kind of the first tier of what we call data pruning. And then you can go a level below that into clustering. And this is where it gets much more interesting, especially with Parquet files.

26:57So Parquet files, they naturally organize rows into what they call row groups. And so you can set sort keys on your DuckLake tables that align to like a common filter you would apply, such as, again, you can use a date if you're looking between min and max values,

27:20or you can do something like if it's like a sales table, like a region name or something. And if you physically sort it that way, then when the DuckDB query engine goes and creates the query plan, it's going to actually look at those Parquet files and look at the statistics of each of the row groups and say, hey, is this a candidate now to where I can skip various row groups?

27:41So that's your second level of data pruning. Alex can dig a lot further on that since I know this is a subject near and dear to his heart. I will say that that animation that he put up there is fantastic. I think that is a great visual on how to kind of think about it. So, Alex, what are your thoughts?

27:59You bet. I think DuckLake is pretty neat in that because we have a database as our catalog, we can do some pruning there instead of reading a whole bunch of files on object storage and then having to prune there. So we can very quickly prune at a kind of a high level file by file.

28:19And then the big benefits you get with file by file are in that partitioning idea is how do you how do you skip as many files as possible? And then the clustering comes in where if you had a Parquet file that was randomly ordered on the column that you're trying to filter on, you're filtering will not be able to skip anything. You can see these date ranges here in this example of the dates were scrambled.

28:41Maybe I ordered by something else. I wouldn't get any benefit from skipping. So that's why it's important to align your partitioning and align your sorting with your actual query patterns. And how do you know your query patterns? Log them out. So you'll see in that way. I would love to go into the weeds on the clustering side.

29:03This is kind of my favorite optimization. This is my my kind of. Initial contribution to DuckLake. Before I do that, I did want to show the syntax for this. There's new syntax in DuckDB 2. 0 where you can actually define your partitioning strategy when you create the table, either as a create table statement or even as a CTAS, create table as.

29:26And you can define it both your sorting and your partitioning all in that same statement, which is notice. Notice on the example, folks, what Alex is showing is that the partition does not have to be a solid field. It can be an actual scalar function. Yes, it can be any function you can think of.

29:45And this is actually something where. Well, actually, the partitioning has a couple of set functions it can do. It has some date math that it can do or some epoch dates now to align with iceberg. The sorting can be absolutely anything in the universe. So you can sort by, as we'll see, some really creative stuff. Yeah, I got my wires crossed. You're right. That's all good. That's all good.

30:07This. But you could do things like partition by year, month, day. So we have those functions built in, which is super handy. The sorting by expression is actually unique to DuckLake. In all other formats, to my knowledge, when you sort, you either have to use something that's built into the library, like built into Spark, not built into iceberg itself,

30:29or you have to choose from that those date kind of functions as your only functions that are available. So what would I do, given whatever flexibility I would want for that kind of sorting? So the first thing I would say is that it's very rare that all your queries filter on the same column.

30:51If they do partition it and and, you know, go grab a cup of coffee. You know, I think that's going to be going to be all you need in the real world. Things are a mixture. And so what I'm looking for is ways to make my data that I want to store just once. Robust to multiple different ways, I want to query it.

31:10And with that, I don't want to sort by one column and then sort by another column, because what that means is that it's going to mostly be sorted by the first column. And then if I wanted to filter in the second column, it's going to be basically scrambled. You can imagine if you were to sort by customer ID and then sort by date. You actually just wanted a date. You're going to check every day and customer ID.

31:34So there's there's a significant tradeoff if you sort by things sequentially. The way around that is to use space filling curves and that they're commonly known as Z order, Z ordering. And Hilbert is another algorithm here. But the way I like to think about it is the example from geospatial land where you can imagine that your your X axis here is your.

31:56Oh, gosh, your longitude and your Y axis here is your latitude. And if I want to filter down geographic data, usually what I want to do is say, like, find something near me. I sort by latitude. My longitudes are going to be totally scrambled. Likewise, if I did the reverse, it'd be totally scrambled. But what I want to do is I want to sort a little bit by both.

32:20And that's what Z ordering and Hilbert do. So I can say, for example, can you see my cursor? Yeah, you can see my cursor. Let's see. I'm I'm sitting right here. And I want to say, show me restaurants near me. I only need to look at data right around me. And if you follow this green line, that's the actual order of the data in the table. That's going to be all in order. Everything around me is is just about in order.

32:43So I'm going to be able to just grab that in one quick chunk instead of having to look through all of my data. So it could be really magical for those workloads where you'd need that variety.

32:58That's my favorite, favorite kind of hot tip is definitely be. Definitely go for that on the advanced cluster. So let me do a quick check here. See if there's some questions that have come in. Troy said, I didn't know they had it in the create statement. I know I'm so excited about that. I made the PR for it and then couldn't debug it.

33:20And so Pedro picked up the PR and improved it and made it battle ready, battle tested and production ready. And, you know, really did a lot more on top of it. It also makes it more convenient for dbt as well. dbt likes to do everything as a CTAS. Now you can do dbt and DuckLake will be peanut butter and jelly.

33:42And while you're looking, Alex, I think something important to call out to people. You know, we demonstrated that you can define your partition and your sort up front. You can create your table at first, load some data in there, and then later you can define the partition and the sort. However, that only applies to new files as you go forward.

34:03All of the existing files are not going to benefit from that partition and sort key. If you want them to, you basically have to overwrite and recreate the table. You can do that cheaply. I don't want to say cheaply. You can do it quickly with a CTAS in terms of how many lines of code you got to write. Just create a replace table partition by sort by as and then select star from the table.

34:26But just keep that in mind as you're planning. If all of a sudden you realize, oh, I can benefit from a partition here or a sort key. Anything that's already been written, it's not going to apply to it unless you go and recast the data. Great call out. I will say if you're doing that on your laptop, you might want to leave it up all night.

34:47But what I would say more so is that if you're on a platform in the cloud like MotherDuck, we have the ability to change your instance size with a click. So if you know you're going to do a repartition, you're going to want some more horsepower to chug through that heavy data work. You know, when you say that when you say the compute size on MotherDuck, I will tell I've said this before to people listening in.

35:08If you have a moment, I would go look at the various compute size graphics that they show. They have like a galaxy giga size where it shows a duck holding an entire galaxy in its belly. Those graphics are hilarious. Isn't isn't many the the artist behind all this stuff? He is an artist behind many things. I think those diagrams we had some help with.

35:29OK, I was just going to say that is some clever looking stuff. So anyways, it's it'll give you a laugh. Yes. Yeah, we have our mega ducklings and our giga ducklings. You know, that's the that's kind of giant galactic scale. What? Yeah. What Matt's talking about. That is over a terabyte of RAM. So really not for the faint of heart. It's one of those like if you know, if the nail's not going in,

35:53you get a big hammer and you knock it in and then you put that hammer down and you stop. I mean, that's that's that's the nuke at that point. That's not a bigger hammer. That is a nuke. I think that there's good analogies over there as well. Fantastic. Well, let's see. I think there's a couple good questions around this topic and then we will keep going on other tuning.

36:14But it's good to spend a little time here because this is actually the first thing you want to look at. So John asks, when you're partitioning by scalar expressions like year and month, how does DuckLake's optimizer handle filtering on it when you filter on the original column, but not the expression? And so the way this actually works is it does actually reason about the transforms.

36:37And that is the reason that you have a fixed set of transformations in the partitioning step so that it can reason about them. So it knows if I filter on a timestamp of, you know, March to now. Right. It knows that all that fits within this year. And if you partitioned by year, month, day, it's going to start in the year partition and jump to month and jump today.

36:59And so it absolutely is smart enough to handle that. So the other technical term for that, we talk about it in the book is called hidden partitioning. And that's what that terminology really means is that I can filter on one column, but rely on my partitioning having been on a different column or transform of it and still get all the benefits.

37:19But I, as an analyst, I, as an agent, wouldn't have to memorize exactly what I'm partitioned on. I just have to know, OK, this is kind of arranged by date or kind of arranged by customer. And then I could go from there. So that's an excellent question. Let's see.

37:39John had another question. Would that let us change something like region categorical and then latitude and longitude for geography? Yes, absolutely. And this is where the creativity comes in. Or if you want to just get your agent to just try a whole bunch of combinations, that's also a great plan in the modern era here. You can mix and match. You can do a high level partition or a high level sort.

38:01You could sort after that. You can partition by as many things as you want. You could sort by as many things as you want or totally ignore one or the other. And all of those are options. And Alex, what you correct me on earlier, it's the sorting where we can apply a custom scalar function.

38:19I believe we touch on that in the book when we go into bucket based partitioning at some point. Or if I recall, it's been a minute. This was chapter five, by the way, folks. So we're almost done with the book at this point. But yeah, there's there's definitely there's a lot of nuggets in chapter five.

38:37It's one of the longer chapters of the book, but it's got a it's got a ton of great nuggets and knowledge built into it. You bet. And Chris is helping out, John, as well, answering in the comments. Thank you for that. I do think so. He's mentioning if you do some complex expressions on multiple columns, that doesn't always get pushed down.

38:59There are more of those that get pushed down in DuckDB 2. 0 and DuckLake 1. 1, which have been released. So I would I would re benchmark in case that they have solved some of those. One of the key ones are or conditions. So now or expressions can be can be pushed down in a way where they couldn't before.

39:16So definitely give that a retest if you haven't already. And we had another question around this as well from John, does a hidden partitioning stay effective when there are predicates with joins and stuff?

39:32And the answer to that is yes. So when DuckDB does joins, it will do automatic push down across the joins, depending on which side is bigger. So let's say you're joining a big table to a tiny table. It's like my tiny table is like these are my active customer lists or my my top 10 customers. Go pull all the data for those. It will actually be able to diagnose those 10 filters.

39:56You know, that 10 customers that you're looking at. when you join and push that filter down into the scan of DuckLake, which is really, really helpful that allows even complex analytics to be scanning as little data as possible. Because as Matt said, it's usually not the join that's the slow part in Lakehouse Land. It's usually going and grabbing

40:20the data and doing as much push down as possible is in many ways the name of the game. Yep. All right. And if you had a question in there, but I didn't get to it, I think we're going to talk about it more at the end. So you are not forgotten and please keep them coming. All right. And we did look at the diagram there and we already looked at our syntax

40:42and we talked about space filling curves, which my favorite topic. Next one here, this is one where we can use agents and we can spend a couple of tokens and an hour and just get a faster DuckLake. So this is not likely to be the biggest lever that you have, but it's a quick way. And I recommend it because it's a lot easier now than even in the past.

41:04And the mental model here is when you're reading data from object storage, you want to read as big of a chunk as you can. That'll be a lot more performant. So here are my top three settings. You should always set with a nice little asterisk and always, because this is a database. It depends on your workload. The first one's a bit of a no-brainer upgrade to Parquet version two.

41:25Parquet version two has been around for about a decade. Yeah. And, you know, people might ask the question, well, why is that not the default? You know, they leave it at one just to ensure maximum backwards compatibility, but 99% of the time, like Alex has said, this has been, this is not the new kid on the block. It's been around for a while. Just go ahead and bump it to two out of the gate and you should be fine. Unless you're hitting some very old, I don't even know what it'd be like

41:49a pig system or something. Maybe. Oh, I don't even remember the system, but even if I did, I wouldn't throw them under the bus like that. It's a, yeah, it's a highly likely to be compatible now. It's version two. The next thing is the compression algorithm by default is Snappy. Again, for compatibility with the most engines. Snappy has been shown to be not Pareto optimal

42:14for most of that Pareto frontier of compression time and read speed. ZStandard tends to be Pareto optimal for a lot more of that range. So I would definitely give that a try. The next thing I think is probably more impactful, but requires a little more tuning,

42:31which is your row group size. And so when DuckDB reads data from storage, it reads things by row group. It doesn't read things one column at a time. It will, it will automatically combine requests for columns that are next to each other. Or if they're next to each other with a small enough gap between them, I think it's about a one megabyte gap. So it tries to do, you know,

42:53request multiple columns at a time, but what it will not do is request multiple row groups at a time in the same HTTP request. With DuckDB 2. 0, async IO means that you will have those in parallel, but there'll be more small requests, not smaller or not fewer big requests, which is what you really want. So to get those fewer bigger requests, you really want to have somewhere around four

43:17megabytes per column. Assume you're going to smush together two columns on average. And that's what you want to aim for, for your row group size. So I would multiply your row group size by two to four x, what the default is, right out of the gate. But my obligatory, we should use an agent for this.

43:37It's 2026. I wouldn't even test Parquet v2. I would just do it, as long as your other systems can read it. And then the keywords I would ask your agent are to use a design of experiments. And so why is that? If the, if the agent tries to tune these sequentially, you will end up in a suboptimal, local optima, not a global optima. What you want is a global optima. Because these

44:00things are not independent, they're related to each other. Your file size, for example, and your row group size are both related and impactful. If you have a smaller file and a bigger row group size, you're basically not going to get any benefit from sorting. Because if you have one row group per file, I can't skip any row groups. So you want to make

44:23sure, my rule of thumb is somewhere around 10 row groups per file. And so that ratio is what's important. And so you want to move file size and row group size in tandem, typically, somewhere in the ballpark of a 10 row groups per file type of ratio. All right. And I remember when we were authoring this part of the book, Alex and I had a, I would call it a lukewarm,

44:47back and forth argument on this, because I come from an old school mindset, tweak one thing at a time, benchmark it, see how it does. And Alex had to kind of walk me back on that one and say, hey, Matt, this isn't 2009, where you were just, you know, writing VBA code with SQL servers and

45:08Excel. This is the modern times. And we're going to, you know, run a few settings together, and then we'll benchmark. I don't think I said those exact words, to be fair, but you were gracious about it as well. And I think a lot of things are like this, where it used to be, we had to tune it one at a time, because that's what could fit in our brain. But now we can assume things are more interrelated rather than assume independence. And so that's another like,

45:32classic statistical assumption we don't have to assume anymore, because we can brute force the heck out of it in the modern times. All right, that's what I had for those couple recommendations. I think we had a couple questions come in. Let's see. I don't have, so Brandon,

45:53good question about real time benchmarks. I don't have them offhand. In my testing, six months ago, I saw some around a 40% performance improvement by tuning all those settings together. So I went from like a half gig file size up to more like a two to four gig file size. And then I bumped my row group size by 4x or 8x or so. And those were similar, but could get

46:17you somewhere in that 40% level. So it's not going to be a 10x type of improvement, but this is, you know, an hour with an agent could get you 40%. That's, you know, that's better than Geico. You made an insurance joke. I'm not allowed to comment on that since I work for an insurance

46:33organization. All right. You're picking up what I'm putting down. Awesome. I didn't even cross my mind on that. We had a great question from Chris S. Do these parquet settings recommendations

46:51change for very wide tables, 1,000 or 10,000 columns for sensor data? I do think that some of them change a little bit. Compression tends to happen at the whole row group level at a time in parquet if you're using a compression level like ZStandard. So if you have a really wide table,

47:12it might actually be worth going with uncompressed as opposed to compressed with ZStandard because you probably don't want to be decompressing big blocks or big pages for the data. So I would explore the uncompressed option more. And I think that the ratio is what I would go after is your row group size ratio to your file size. It might just mean that you have fewer rows in a row group,

47:36but that 10x ratio is still probably what I would go for. Any thoughts there, Matt? Yeah, no. I mean, usually when I'm thinking about things like IoT, I'm thinking more semi-structured data at that point, such as JSON. I know that, you know, there are definitely use cases to flatten it out into a tabular format for things like machine learning models, feature

47:57stores, things like that. But, you know, with JSON at that point, JSON definitely takes a hit when you have to deserialize it as well. So there's performance implications there. Basically, what this all boils down to is it depends. And that's why, you know, we kind of hammer this point in the book that you've got to, you know, make some adjustments, benchmark it. And as Alex has

48:21said several times now, leverage the LLMs, the AI agents. They're very good at running these design of experiments, trying different methods to see, like, do I uncompress it? Do I compress it? At what point do I hit the tipping scale, you know, or the fulcrum where I have diminished returns if I keep trying to tweak this thing that much more? So you've got a lot of different ways you can go

48:43with it. And I'm not trying to avoid answering the question. I'm just trying to tell you all that it fundamentally does and will pretty much always depend on the shape of the data and the type of performance you are after. Yes. And I think that's, to me, in the AI era, that shifts where we spend our focus. Our focus is understanding the architecture to know what options we have for

49:07tuning. And then thinking about the techniques for tuning, like this design of experiments concept and assuming, you know, interdependence of things. Those are really big. And then the details of the workload are workable. I think John does have an interesting strategic question around that same idea of kind of that algorithm approach. So with the design of experiments, would you recommend

49:29optimizing a weight of objectives over multiple workloads? Or would you focus on looking at the latency, throughput, and storage cost? And then how do you control for caching and object storage variability? Those last two, I think the caching can be disabled in some cases. If nothing else,

49:49if you disconnect your DuckDB connection and reconnect with a new instance, the caching will be turned off as well. So you have some control of the DuckDB layer to kind of do a non-cached read there. In terms of how I would focus on it, I think in general, one workload tends to be your cost driver, just because the Pareto rule comes into play. So if cost is your focus, you can usually focus on that.

50:14Otherwise, I would focus on which workload is more, you know, time sensitive. You know, is it, you know, focus on your BI dashboards, and then your overnight job takes a little bit longer. That's probably okay. You know, just to add on that, Alex, some other cheap optimizations that

50:30you can put in. Let's say you have a gargantuan table, and I'm talking, you know, orders of magnitude, 100 gigabytes higher. But let's say that most of the time, most people are just, let's assume it's like a time series sales table. Let's assume most of the time people are just asking for the last few weeks' worth of data. What you could do is you could divide that into

50:54two tables. You could have one table be the historical table, and you could have another table be the current and just direct most of your users to hit the current table. And you'll get very good performance, quick response, and then only you would direct the power users that are doing, you know, large swaths of time analytics to the historical table. That's another cheap

51:17option that you can do. Yep, you could definitely do that. Partitioning also will get you similar there, a little more workload on your catalog to do that, whereas you're taking all that burden off the catalog with exactly what Matt said of just point to a different table, which definitely would work. The nice benefit of DuckLake is that it has transactions across multiple tables,

51:39which means your transition from my active table to my archive table will be atomic. There's no chance for duplication. There's no chance for it being wrong for any point in time. You can just side note, I was going to say side note too, I think I saw this on 2. 0. The CTE statement

51:57now allows you to actually delete data and then direct it somewhere else. That is a clever update they had put in there. I haven't explored that one yet, but just kind of a side note, if you're having to archive data off, you can have a CTE saying with, you know, something as delete stuff from, I can't remember the exact syntax, but delete from, you know, current where time stamp

52:21less than equal to current date minus 10 days. And then you could select that or insert it into another table. It's pretty clever. I never would have thought to have a CTE run an actual operation that modifies data. Yeah. So it turns out that's actually something that Postgres had for a while that also was new to me, where you can have deletes and updates in a CTE and you can have

52:44a returning clause in there. So you could say something like delete these rows from here, returning all my rows, and the very next step could be insert into my archive when it was deleted. And it's all in one statement, happens in one shot, it's transactional, and it's pretty elegant from a syntax perspective. So definitely have a look at that. Thanks for that shout out. Well, we have a couple minutes left, so we might do a bit of a speed round and then we'll get to a

53:08couple more questions. Thank you for those questions. They've been really fun. This one is a topic we've talked about a little bit before, but it's unique to DuckLake. It's worth mentioning again. And for a certain workload, it's just killer. So good. So we're talking about inlining because we have this database in our Lakehouse format and we're going to use it to our advantage.

53:29So I want to turn that to you, Matt, a little bit and walk us through what is inlining and when does it help us? Right. So inlining is a strategy that DuckLake uses when you're doing small atomic inserts to a table. So let's say you're appending five rows at a time. What's going to happen is instead of those five rows getting written out to a parquet file,

53:54it's going to actually get written inside to the SQL catalog and stored there until you flush it to parquet. The default for inlining for DuckLake is 10 rows. So if I run an insert statement that's 10 rows or less, it's going to inline it and just store it inside the catalog. You can flush it later to parquet. But if you run it for 11 rows, then it's going to immediately

54:17write the parquet file. I remember the first time I was seeing this in action, I was writing some scripts for the book and I'm like, Alex, is my eyeballs playing tricks on me? I don't see any parquet files. And he's like, oh, inlining, Matt, you should look at that. And I'm like, oh, what's this inlining thing? And then I start reading on. I'm like, wow. To your point on where this can be

54:36useful, think about, again, IoT devices that are just inserting rapid fire one log at a time. You don't want to create thousands of parquet files just holding one row, do you? You probably don't. Instead, you're going to want to inline very quickly to the database. It's lower latency and it also combats the small file problem that we talk about in the book. It keeps you from

54:58having all these little tiny files. And then later, you can just run periodic inline flushing to where it'll flush all the buffered data sitting in your database into the actual persistent parquet file in the object store. So it's a great feature. It's a way to speed up, you know, to your point

55:17on there, hundreds of transactions per second, just rapid fire. It's a great feature with no tooling required on the user. It's built-in batteries included up front. Yes, absolutely. The other big benefit, too, is on the cost side. You don't have to do compactions as much. You just

55:41do these flushes so you get to skip a whole step that would take compute. And this is totally safe to do. When you do a read out of DuckLake, it's going to look at any data you have in parquet and combine it with any data you have inlined seamlessly. So you don't ever have to worry about it. It's fully transactional, fully instantly readable. So that has some advantages over

56:02something like Kafka where it's in a Kafka buffer and it's very hard to read out of a Kafka buffer fast enough. Whereas here you get transactional reads straight out of the gate, which is really nice. We did have a question from John around this. When you flush, what does this do with

56:20your sorting and partitioning? So it does actually flush two partitions and sort when you flush as well. So both of those will take place when you do the flushing. What that does mean is you probably don't want to flush every 100 rows. You probably want to flush every million rows or every 10 million rows. Approximately your desired parquet file size is the level at which

56:44the kind of frequency at which you should be flushing. You can always compact later. When you compact, you will also resort as well and keep those sorting benefits. Partitioning will still be there when you compact. You'll keep that too. And I'm pretty sure some people's minds are thinking now like, well, how do I know when I'm at a million rows or something? You can run

57:04periodic jobs to pull the SQL catalog and see how much data is stored in the inline tables. And then once it hits that threshold, you can then say, okay, if you know greater than equal to 1 million, call DuckLake inline flush. I forget the exact procedure, but we document it in the book and you can flush it out to disk or to parquet. Yes, absolutely. And someone had another great

57:27question about predicate pushdown on the catalog. So Che, I would need to double check. I think DuckDB 2. 0 and DuckLake 1. 1 have improved this area, so I need to double check for you. There's more predicate pushdown into the catalog than before. In the past, we did not want Postgres to have to do a lot of filtering because it's not always very efficient at it. We want to just grab

57:48the data out of Postgres and filter it in DuckDB. But we're doing a little bit more predicate pushdown now than in the past. That's part of the trade-off. You don't want to store 100 million rows in Postgres, so you don't want to store 100 million rows in inlining before you flush. There is a balance point where you want to flush a little bit more often. All right, well, we are

58:09in full rapid fire mode, so we'll jump through a little bit. Your questions are great. So the next piece of the architecture I would tune would be your catalog. And we don't have any extra detail on this other than to kind of show you the lay of the land of what's possible. So when you're tuning a catalog, you're tuning a relational database. Agents are good at this. There's a lot

58:29of literature about this. The first thing I would look at is, should I put an index on the biggest table inside of my DuckLake metadata catalog? And that's my statistics on my columns, my column file level statistics. That's your biggest table. Look at that and see if it's big enough where you're going to want an index on it. There are systems that will recommend indexes for you, and

58:52agents can do that as well. So indexes are the first thing I would look at. The next thing is, workloads on a DuckLake catalog are pretty repeatable, so make sure you've got a big enough database. Just make sure you've got enough RAM to hold the whole catalog in memory. You don't want to be paging back and forth to disk for this because this is not going to be big. It's going to be, you know, a couple gigs would be like a petabyte scale, you know, lake house. It's going to be, you

59:15know, measuring gigabytes for the catalog. So just make sure you've got enough RAM. Don't use the, you know, the $1 Postgres. Use the $25 or something a little bit beefier if you're getting slow performance there. The next thing you could do is, the catalog is going to store your whole history of operations until you expire them. And so if your catalog is having to scan a lot of old,

59:38you know, versions of your data, let's say you replace your data every night, you don't need to store all those old versions. And those old versions, not only do they take up room on object storage, they take up rows in your catalog. So you can expire those snapshots and then delete those snapshots. There's just two SQL commands that'll do that for you. Right. And real quick on that, I think Alex touches on this in the book.

60:01You can have a progressive snapshot expiration with StairStep. So let's say, you know, for current weeks of data people might want to filter by every day. So you leave all seven days. And then once you're back a week, you, then you expire snapshots and combine them at the weekly level. And then once you're a few months back combine them at the monthly level. Yes, absolutely. So you, it's not all or nothing

60:24and you can do that all with, with a two SQL queries pass a variable around. It's pretty slick. I think we should at least mention maintenance a little bit. I think that the default is that you should have maintenance that is another key difference between a warehouse and a lake house. Warehouses tend to kind of curate upfront when you load the data, whereas a lake house will load it in as fast as possible and expect you to kind of come around

60:48sort of like a Postgres vacuum, you know come back and clean it up a little bit later on. So that's another thing we didn't mention. So you always want to have maintenance on at some level in your DuckLake. And the key things you want to do there are compact your deletions. If you do a lot of update statements and then you want to compact your files together. If you don't have those, you know, half a half a gig files are bigger. You want to make sure you have somewhere

61:12in that one gigabyte or so range of file usually especially further back in time. If you're using it more like archival data at that point, you can go even bigger for that. Anything you would add on kind of the maintenance side of how folks should think about that. So, yeah, you know, and we touch on this, I think again, in the book, as you're compacting files what's going to happen is, and correct me

61:35if I'm wrong, Alex, I think on certain operations the actual expired files are not physically deleted. So you have to run an additional maintenance step to actually get them out of your object store so that we're not paying for that additional storage. You know, they don't hurt your performance being there because they're marked as logically deleted. So the DuckLake catalog won't even honor looking at them. They won't think that they're no longer there but they are still sitting in object store

62:00and still paying, you're paying a storage fee for them. So you're going to want to make sure you keep an eye on that as well. What I would recommend is set your deletion window to 10 days because you might want to take a week of vacation and you don't want to have to go through the retrieval process of object storage to go claw that back from AWS. So the organizational side is actually more important there

62:23because if you're going to store several years worth of data an extra 10 days is nothing, but operationally it's just so much easier to manage if you keep, you know 10 days of files lying around just in case. So that would be my recommendation. Yeah. Someone had a question about DuckDB 2. 0. We need to at least give it a shout out. It is launched. Give it a try. Async IO is one of the headline features.

62:46This means that for one CPU core on your DuckDB compute it can launch multiple background threads. There's a pool of background threads. It can grab several out of that to go have multiple requests out to object storage at the same time. And the default ratio is somewhere around like four of those threads. Whereas with DuckDB 1. x was one and synchronous.

63:09So you can get 3x faster because it really is 4x and there's a tiny bit of overhead. So it's highly possible and frequent that you'll get a 3x benefit. Someone in the chat commented that they saw that in their production workload. So I didn't even know that before I did that on the slide but we totally lined up. So definitely try DuckDB 2. 0. You'll be able to read more in parallel. And as Matt was saying, storage reads are the bottleneck

63:34in the modern cloud in many cases. And so async IO is a huge win in getting rid of that bottleneck. All right, last thought as we wrap up, join us again next week at Data Outpost. Matt and I will both be there. We will be signing our early release copy of the DuckLake Definitive Guide book. And you can get that as a PDF as well

63:57but you can get a real physical piece of paper with us next week. But Data Outpost is all about the intersection of data and AI. We've got folks there from OpenAI, from DoorDash, from Perplexity and also Tristan from dbt Labs. Huge variety of folks, hundreds of people there that you'll get to really meet and get to know. We have a DuckLake workshop. So bring your questions there. We'll be talking about Iceberg,

64:21talking about DuckLake and going into the weeds. So with that, join us. It's DuckLake 150 if you wanna join us for 150 less. And thank you so much for being here, Matt. Pleasure having you. Sure, thank you, Alex. And thank you everybody for joining us on this live chat today. We really appreciate you all making time to listen in.

64:46Absolutely. Pleasure, everybody. Take care. All right.

FAQS

Measure first. Turn on DuckDB's profiling and logging, which record HTTP times and time spent scanning, joining, and aggregating as JSON, and log slow queries on the catalog database too. Then hand the logs to an agent and ask what the bottleneck is. Alex's rule of thumb: big reads usually point to file layout, small reads to the catalog, big writes to compute, and many small writes to the catalog and inlining.

Partition on a column most queries filter on, such as a date, so queries skip whole files, and sort on the columns you filter by so queries can skip row groups inside the files they read. DuckLake handles hidden partitioning, so filtering on a timestamp still prunes year and month partitions. If your queries filter on different columns, consider space-filling curves such as Z-order or Hilbert instead of sorting one column after another.

Move to Parquet version 2, try Zstandard instead of Snappy, and raise the row group size to roughly 4 MB per column, two to four times the default. Aim for about ten row groups per file and tune file size and row group size together. Alex suggests having an agent run a design of experiments. In his own testing, tuning these together made queries around 40% faster.

Yes, but it only applies to files written afterward. To apply it to existing data, recreate the table with a CTAS that replaces it and includes the partition and sort definitions. On MotherDuck you can size up the instance for the rewrite.

Inlining stores small inserts (ten rows or fewer by default) in the catalog database instead of writing a tiny Parquet file for each, which avoids the small-file problem and keeps latency low. Reads combine inlined and Parquet data transactionally. Flushing applies your partitioning and sorting, so flush at about your target file size, every million to ten million rows rather than every hundred.

Related Videos

"Using SQL in Your Data Lake with DuckDB, Iceberg, dbt, and MotherDuck" video thumbnail

24:42

Using SQL in Your Data Lake with DuckDB, Iceberg, dbt, and MotherDuck

Jan 17, 2025 • Jacob Matson

In this talk at our MotherDuck Seattle meetup, Jacob, developer advocate at MotherDuck, talked about why we should use more SQL and go hands-on with some practical examples of how to read from Iceberg using dbt, DuckDB, and MotherDuck.

dbt

Meetup

SQL

"LLMs Meet Data Warehouses: Reliable AI Agents for Business Analytics" video thumbnail

LLMs Meet Data Warehouses: Reliable AI Agents for Business Analytics

Nov 19, 2025 • Ryan Boyd

LLMs excel at natural language understanding but struggle with factual accuracy when aggregating business data. Ryan Boyd explores the architectural patterns needed to make LLMs work effectively alongside analytics databases.

AI, ML and LLMs

MotherDuck Features

SQL

Talk

Python

BI & Visualization