A database in essence does three things: write, store and read data. A text file can do the same thing, so why do we need 20 different types of databases? Graph, document, vector, OLAP, OLTP, real-time, they all serve a purpose once you reach a certain scale and your requirements change. Some people will tell you to always use Postgres and to be fair, that’s solid advice. Until you hit a wall that is and your boss wants you to increase performance or cut costs. Read along to see when you finally need to act on that // TODO: find a better database in your code.

The name is Lite, SQLite

SQLite is nothing more than a single file solving a very common problem: atomic, durable transactions and a schema that enforces constraints with no server to run. Fancy words so let’s make sure we’re on the same page. Let’s say your database is like a CSV file: each line is a row and by splitting the row on a , we get a value for each column in that row.

id,bike_type,brand,price,stock
1,racing bike,Duckingdale,1500,5
2,mountain bike,Duckalized,1600,5
3,cargo bike,Urban Duck,3000,2

Adding a new bike is as simple as adding a new row at the end of the file.

id,bike_type,brand,price,stock,color
1,racing bike,Duckingdale,1500,5,orange
2,mountain bike,Duckalized,1600,5,black
3,cargo bike,Urban Duck,3000,2,teal
4,city bike,VanDuck,1000,1,silver

Now let’s say one function is adding a new color of mountain bike conveniently placed directly after the first mountain bike. At the same time another function is updating the stock of the cargo bike to 0. If these happen at the same time the program might accidentally update the wrong row. Atomicity means all of these transactions in one batch happen at the same time or not at all. Something a CSV file can’t enforce, but SQLite can, even when your hard drive fails or your neighbors WiFi cuts out.

Do not underestimate the power of SQLite (it ships on all iPhones for example), but with only one writer at a time you might quickly run into some of these limitations.

• What if multiple people want to write to the same file at the same time?
• What if 1000 people want to see the current stock at the same time on your website?
• What if you have 10 million bikes in your file?
• What if some people are allowed to see the bikes, but not the stock?
• What if you want to have the same bike in multiple colors or with different configurations?

Some of the most popular databases that solve this exact problem are Postgres, Microsoft SQL Server and MySQL. They all use Structured Query Language (SQL), where the convention is to shout everything in ALL-CAPS even though that’s totally unnecessary.

Why do we need another ducking database then?

Some people say you should use Postgres for everything. To some extent that’s true, because you can even run DuckDB inside Postgres. But it’s like saying you can put cheese on everything. You can, and cheese is great, but the experience won’t always be the best. Postgres is a great and popular database and probably your best bet for any project that needs data, but there is a point where Postgres just doesn’t cut it. That point is usually when you try to use it for something it wasn’t optimized for. A few examples.

• Finding all database engineers on LinkedIn that know someone you worked with
• Seeing votes in real-time for the best ducking coffee in the world
• Comparing your energy usage today to your average energy usage on the same weekday over the last 5 years

It’s not like you cannot solve these problems with Postgres, you can even solve them with a CSV file for that matter. It’s that you can not get the speed and performance you likely require from Postgres. It’s like the cheese topping arriving after you’ve finished your pasta. If you have a need for speed, keep reading.

The problem that DuckDB, and other analytical databases solve is a different type of reading data. A database that does a lot of writing and updating, like processing orders for a new bike, needs a different disk layout then a database that does analytics. By its nature an analysis uses aggregation: the sum of all revenue, the average order value, these are read operations for a column while updating an order is a write operation on the row. This translates to the actual storage on disk to minimize latency when processing millions of rows.

You’re here because you want fast and cheap analytics

You’ve come to the right place. To understand what makes DuckDB fast and cheap we have to dive into a few of the optimizations. Performance and cost are mostly an optimization problem. You can solve a lot of speed problems with a bigger machine, by storing the same data in multiple layouts (for example by creating an index on a column in Postgres), or storing it on a faster hard drive. But all those improvements come at a cost. Let’s start with why DuckDB is fast and cheap, because a big part of what makes MotherDuck great is the fact that we run DuckDB in the cloud to begin with.

Columnar, vectorized execution and predicate pushdown

A big chunk of performance is using as little data as you need to accomplish the task. Every time you need to read data from the disk, that adds time to your query. DuckDB, like many columnar databases, allows you to read only the columns you need, let’s say date and order_value to get the average order value per month. If you have a lot of orders the data for each column can be further split up into row groups to allow batch processing using multiple CPU threads.

select
  month(date),
  avg(order_value)
from orders_table
group by month

Predicate pushdown is the method of pushing your filter further down the query plan, as close to the scan as possible. It allows the scan to decide if rows should be sent back. That of course is extra convenient when those rows live on a slower system like a Parquet file on an object storage like S3 or R2. With predicate pushdown it becomes possible to do pruning. Pruning usually comes in one of the following forms.

• Partition pruning: Think of partitions as files in folders. With Hive-style partitioning this could look something like /year=2026/month=9/region=EMEA/part-1.parquet. Having a filter on a year or a region allows skipping over entire folders of files instead of having to read each file individually.
• Zone maps / Row groups: DuckDB and Parquet files split files into groups of rows based on either the number of rows or the size on disk (for example when rows are wide and carry a lot of data). Each row group contains metadata (per column) about the minimum and maximum values in that column. So a filter like price >= 1500 can skip decompressing the full row group when the max value is 1200. Note that zone maps only ever exclude, they either say “definitely not here” or “maybe here”. They don’t point at exact rows, which is what keeps them cheap to have around.

Compression

Pruning and predicate pushdown can limit the rows and columns you need to read, but at some point you will need to process data. Data will almost always be compressed to make it more effective to move around. You’ll notice the benefit of that mostly when moving data over the network. The smaller the file size the faster the transfer. You can always add more threads to decompress data faster, but you can’t just add another wire to your network bandwidth. That’s why zstd is the default compression codec. It strikes the right balance between minimizing file size and speed of decompression. And remember kids, friends don’t let friends use gzip.

Embedded, in-process execution

Embedded and in-process sound like very fancy words to say something simple. The traditional way of running a database is to have a computer always on stuck somewhere in a datacenter. From your machine you then connect to this database machine. If too many people connect at the same time, the database server crashes. To prevent it from crashing you either have to make sure it’s always oversized or add an additional “management” server that scales the “worker” servers with the required load.

DuckDB is embedded, which means that instead of running on a separate server it can run directly with the data in the program or application that processes it: a Python script, a Java application, a browser. It’s also in-process meaning it uses the same address space, same heap, same threads as your application. In other words: it’s a library not a server. That doesn’t mean you can’t run it somewhere other than your own machine, that’s of course exactly what MotherDuck does. It does mean that you can often run it closer to the data and give every user their own compute without the overhead instead of sharing a database server.

The upside of course is that there is no 4 minute cluster startup time bricking your data while you wait, no shared database squeaking under load, no “hot pool” of always-on servers that someone needs to pay for in case a unique snowflake requires an immediate answer to their query.

Query optimization

Everything so far was about reading less data. DuckDB’s query optimizer tries to do as little work as possible with the data you must read. Before a single row is touched, DuckDB pushes your query through ~26 rewrite rules, and the nice part is that you don’t need to know any of them. Write SQL “naturally” and your query comes out fast on the other side. Four patterns are worth seeing, because they show the type of work the optimizer does.

• Reordering filters: cheap predicates run first. An equality check on an integer is a couple of instructions; a LIKE, a regex or a function call is orders of magnitude more. Same WHERE clause, evaluated cheapest-first, so the expensive predicate only ever sees rows that survived.
• The IN clause rewriter: Normally you’re told to use EXISTS instead of IN, because that was what the old folks used to read as the first StackOverflow answer on an ancient technology called Google Search. DuckDB turns it around: brand IN ('Duckingdale', 'Duckalized', ...) evaluated per row is O(rows × list), but DuckDB rewrites a long list into a set of values joined against your table, which turns it into a hash join at O(rows + list).
• Statistics propagation: min/max values travel up the plan, not just down. If the bikes you’re joining to only have ids 25 to 50, that becomes a between 25 and 50 filter on the scan of the orders table — where the zone maps from earlier do the actual skipping. A filter you never wrote, on a table you never filtered. Like a colleague getting you that coffee you didn’t know you needed until you had it.
• Join order optimization: you write your joins in whatever order reads well. The optimizer estimates how many rows each join produces and reorders them to keep intermediate results small, which mostly means getting rid of accidental cross products.

What’s left for you is the same theme as before: keep predicates in a shape the engine can use. date >= '2026-01-01' prunes, where year(date) = 2026 often can’t, because the statistics are on date and not on the output of a function. And when something is slow, EXPLAIN ANALYZE (no not the other EXPLAIN ANALYZE) tells you which operator eats CPU cycles for breakfast.

When your problem is neither analytical nor transactional

There are other use cases that require again different types of database layouts or storage on disk. Let’s go through a few more examples.

• Graph databases like Neo4j store the relations between entities as edges and nodes. For the LinkedIn example above it could be that each user is stored as a node with properties like their age or location. You’ll then get relationships between those nodes, which are called edges. These edges can also have properties. If there’s an edge me —> worked_at —> bike shop that edge can have the properties from: 2010, to: 2012.
• Vector databases like LanceDB, Qdrant or Postgres with pgvector store rows with one weird column: a list of a few hundred to a few thousand floats, called an embedding. A model looked at your product photo, or the description “orange racing bike, feather-light frame”, and wrote down 512 numbers. Nobody, including the people who trained the model, can tell you what number 67 means. The only property that matters is that two similar things get two lists of numbers that sit close together. So “find bikes like this one” isn’t a new kind of query at all, it’s order by distance(embedding, :query_vector) limit 12. What the database adds is an index (usually HNSW or IVF-PQ) so that ordering doesn’t have to compare your query against all 10 million bikes. The trade-off is a new one though: the vector index is approximate. Unlike a zone map, which only ever says “definitely not here”, a vector index might quietly skip the best match to answer 50x faster.
• Document databases like MongoDB store a record as a single self-contained blob, usually JSON. Instead of a bikes table joined to a configurations table joined to a colors table, you have one document per bike with the colors and configurations nested inside it. That’s a genuinely different trade: reading one bike is a single lookup with no joins, and every bike can have a different shape, so the cargo bike can carry a max_load_kg field that the racing bike has never heard of. You pay for it on the other end. There’s no schema to stop you from writing both price: 1500 and bike_price: 1500, and the question “what is the average price per brand?” now means reading every document in full to get at two fields — which is exactly the column-vs-row story from earlier, at document scale.
• Key/value stores like Redis, DynamoDB or Cloudflare KV take that idea to its minimum: you hand over a key, you get back a value, and the database refuses to be curious about what’s inside. That constraint is the feature, because a lookup that can only ever be a hash of a key is super fast and predictable at basically any scale. It’s what sits in front of your website holding stock:bike:4 -> 1, so 1000 people can see the stock count without 1000 queries hitting your transactional database. The moment you want “all bikes under €2000” you’re out of luck, unless you thought of that question in advance and wrote a second key to answer it.
• Time-series databases like InfluxDB, Prometheus or TimescaleDB assume time is the primary key and that data arrives in append-only order and is queried in windows. That lets them lean hard on time-based partitioning, delta encoding on the timestamps, and automatic downsampling of anything older than a week. Great for logs and traces.
• Geospatial is less a separate database than a set of types and indexes bolted onto an existing one, PostGIS being the famous example. “Which bike shops are within 5km of this point” needs an R-tree rather than a B-tree, because the thing you’re sorting on has two dimensions and no natural order.

So which database to pick?

All of them write, store and read data.

• A CSV file is bad at all three the moment a second person shows up.
• SQLite is good at all three until a second person shows up.
• Postgres is good at all three when sharing, but great at none, which is why it’s the right answer roughly 80% of the time and you should genuinely just use it when starting.
• Redis is fantastic at reading exactly the one thing you told it about in advance and useless at anything you didn’t.
• Mongo will store whatever shape you throw at it and then make you read all of it back to answer “average price per brand”.
• Neo4j is brilliant at “who knows whom”
• Prometheus is brilliant at “what happened at 14:32:12.345”
• A vector database is the one that looks across images, audio and text and says: these likely belong together
• The analytical database, like DuckDB or MotherDuck, will effortlessly take your Terabytes of data for the last 5 years and give you any aggregation you ask for.

If you’re looking to play around with some databases on your own machine DBngin is a great tool to play with Postgres, MySQL and Redis. DuckDB of course also is a quick install, and if you want to skip the install altogether give MotherDuck a try.

In the end the question is not “which database is best?”. It’s “which question am I going to ask ten thousand times a day?”. If it has where user_id = you want a transactional database. If it starts with sum, avg or group by and ends with someone in finance asking why the bill is that high, well, welcome to the flock.

This post was originally published on the MotherDuck blog.