DuckDB Doesn't Need Data to Be a Database
nikolasgoebel.com
nikolasgoebel.com
Our team is currently building a form builder SaaS. Most forms have responses under 1,000, but some of them would have more than 50,000 responses.
So, when user tries to explore through all responses in our “response sheet” feature, usually they could be loaded via infinite scrolling (load as they scroll).
This uses up to 100MB of network in total if they had to get object arrays of 50,000 rows of data with 50 columns.
That was where duckdb kicked in : just store the responses into S3 as parquet file(in our case Cloudflare R2).
Then, load the whole file into duckdb-wasm into client. So when you scroll through sheet, instead of getting rows from server, you query rows from local db.
This made our sheet feature very efficient and consistent in terms of their speed and memory usage.
If network speed and memory is your bottle neck when loading “medium” data into your client, you definitely should give it a try.
PS. If you have any questions, feel free to ask!
PS. Our service is called Walla, check it out at https://home.walla.my/en
But the data is still remote (in object storage) right? If I understand correctly, this works then the first solution because parquet is a much more efficient format to query?
The advantages of loading “parquet” in “client side” are that 1) you only have to load data once from server and 2) the parquet files are surprisingly well zipped.
1) If you load once from server, no more small network requests while you are scrolling a table. Moreover, you could use the same duckdb table to visualize data or show raw data.
2) Sending whole data as a parquet file is faster through network than receiving data as json in response.
I'm not sure what the optimal response size is for an http response, but probably there are diminishing efficiency returns above more than a MB or two, and more of a latency hit for reading the whole file. So if you used row groups of a couple of MB and then streamed them in you'd probably get the best of both worlds.
On the other hand, let’s say we have to draw a chart from a column. The type chart could be changed by user - they could be Pie charts, means, time series chart, median, table or even dot products. To achieve this goal, we would bring just a column from s3 using duckdb, and apply sql queries from client side, rendering adequate ui.
We have also tried arrow js or parquet wasm, and they were much lighter than duckdb wasm worker.
DuckDb however was useful in our case, considering our nature as form builder service, we had to provide features for statistics. It was cool to have OLAPS inside a webworker that could handle (as far as we checked) more than 100,000 rows at ease.
A regular JavaScript array can also handle 100k object rows very fast.
An improvement could be having pre-calculated DuckDB database files that are directly attached from the DuckDB WASM frontend, see https://duckdb.org/docs/guides/network_cloud_storage/duckdb_...
I'll probably try again later because it really does look cool and as much as I absolutely love my intellij/datagrip I'm always looking for alternatives and have thought several times about building a tool with these automatic analysis tools built in.
>Build self-updating desktop app packages in minutes. Deploy to every OS from any OS. Platform native formats, fully signed and notarized. Electron, Flutter, JVM and native.
And free:
> Conveyor is free for open source projects.
s3://robotaxi-inc/daily-ride-data/*.parquet
SELECT pickup_at, dropoff_at, trip_distance, total_amount
FROM 's3://robotaxi-inc/daily-ride-data/*.parquet'
WHERE fare_amount > 100 AND trip_distance < 10
Thinking more, I feel this boils down to ownership. Does it make sense for you to own this abstraction layer? Or does it make sense to shift the ownership towards the receiving end, and just provide the raw data.
To me maintaining this sort of views to data sounds more like responsibility of data analysts that random devs. They are the experts in wrangling data afterall. But of course there is no single right or wrong answer either
- Managed storage with zero-copy clone (and upcoming time travel)
- Secure Sharing
- Hybrid Mode that allows folks to combine client (WASM, UI, CLI, etc) with cloud data
- Approved and supported ecosystem of third party vendors
- Since we're running DuckDB in production, we're working very closely with the DuckDB team to improve both our service and open source DDB in terms of reliability, semantics, and capabilities
(co-founder at MotherDuck)
That's a pretty outlandish statement.
With this approach, next time they attach the DB, they automatically see the latest version
Still, I would more readily send people a link to a Gist or playground than a binary DB on S3.
What I'm talking about is snowflake vs a $50 EC2 instance running DuckDB reading data from S3.
Try it out sometime — the results might surprise you
To be clear I have used Duckdb in some serverless ideas and it has worked well, but nothing has bet hyper optimized engines like snowflake and redshift.
What's going on here that wouldn't warrant building a processing pipeline that places the data in a more permanent data warehouse and create all the necessary views there?
I haven’t decided where I land on this. In some ways, stacking SQL views looks like it simplifies a bunch of ETL jobs, but I also fear a few things:
* It either breaks catastrophically when there’s a change in the source data
* Fails silently and just passes the incorrect data along
* More challenging to debug than an ETL pipeline where we have a clear point of error, can see the input and output of each stage, etc
* Source control of SQL views seems less great than code. Often when we have too many views, you can’t update one without dropping all of the dependencies and recreating them all
But I also wonder if I feel this way because I know programming better than SQL
edit: now I read your question more carefully. I think the s3 data is meant to be managed by other orchestration. This is a quick easy way to share a data source with an analyst, PM or end consumer.
I do not expect poster is advocating this as any intermediate stage in a data pipeline.
IIRC, SQLite can do similar things with virtual tables (with a more limited set of data file types).
I always liked this way of working, but I also wonder why it never really took off. Data discovery can be an issue, and I can see the lack of indexing as being a problem.
I guess that’s a long winded way to ask: as interesting as this is, what are the use cases where one would really want (or need) to use it?
It’s actually a chapter in the ISO/ANSI SQL specification.
As a result, a specification was added, as part of ISO/ANSI SQL specification process.
The MED term is an acronym and not a shortening of "medical".
I wouldn't be surprised if the term SQL/MED had been spoken in healthcare/medicine circles (I worked in that industry before). That said, it's the first time I've ever seen anyone connect the term/concept specifically to medicine as the driver for the term.
I'm actually someone who has gone deep down the SQL rabbit hole (I worked for a company that built a data federation platform previously), I'd be interested to read more if you can share.
My apologies, I thought the confusion of the acronym simple came from the "MED" part, which as I mentioned, stands for "management of external data".
In that respect we are in complete agreement :)
EDIT: Perhaps you might be referring to something like this paper about data federation in general? https://citeseerx.ist.psu.edu/document?repid=rep1&type=pdf&d...
However, I like to limit my toolset to three things, these days thats duckdb, julia for analysis side, and... OK two things.
Oh yeah Trino for our distributed compute.
In today's new fangled world, a lot of developers don't use a lot of the great stuff that RDBMS can provide - stored procedures, SQL constraints, even indexes. The modern mindset seems to be that that stuff belongs in the code layer, above the database. Sometimes it's justified as keeping the database vanilla, so that it can be swapped out.
In the old days you aimed to keep your database consistent as far as possible, no matter what client was using it. So of course you would use SQL constraints, otherwise people could accidentally corrupt the database using SQL tools, or just with badly written application code.
So it's not hard to see why more esoteric functions are not widely used.
It's honestly the dumbest situation, where people eagerly use extremely complex databases as if they were indexed KV stores. Completely ignoring about 97% of the features in the process.
What's especially funny is that half the time a basic KV store would perform better given all the nonsense layered on top.
And then there's this whole mentality of "we can't interface with this database unless we insert a translation layer which converts between relationships between sets of tuples and a graph structure".
It's like people have unearthed an ancient technology and have no idea how it's intended to be used.
The “consistency must be enforced at all costs” just turned out not to be true. Worked at many places at moderate scale since my old dba days that don’t use fks. It just doesn’t actually matter, I can’t recall any serious bugs or outages related to dangling rows. Plus, you end up having multiple databases anyway for scale and organizational reasons so fks are useless in that environment anyway.
On the other hand, I’m all for indexes and complex querying. not just k-v.
I’m not sure why it’s so hard to accept that we stopped using them because they were bad, not for some other gotcha reason (social, ignorance, fads, etc)
Also, oracle plsql is way better than ms t-sql, it mostly feels like pascal/modula2 with embedded sql statements.
Also i hope you're not suggesting anyone pick up oracle for a new project these days :)
Wouldn't advise anyone to use Oracle, but neither would i advise them to use sql-server. Usually Postgres is good enough, although oracle plsql is still nicer than postgres plsql (e.g, packages).
> You can source control them, but then you have a deployment coordination problem. Often changes to business logic will be half in application code and half in the sproc, and you have to coordinate the deployment of that change. You have to somehow reason about what version of the sprocs are on your server. Add on that usually in enviroments that use sprocs, changes to them are gatekept by DBAs. Just write your business logic in application code.
But I like foreign keys (ON DELETE RESTRICT), they are the last layer of defense against application bugs messing up the database.
YMMV with this one, i could see how it might pan out differently in other environments
Exception is true banking ledgers maintained at banks, but even then you’d be surprised. Entirely different systems handle loans and checking accounts
That's what I meant when I said it's weird people keep picking these particular databases for projects which don't end up using any of the features.
Stored procedures, triggers and all the other features people seem to refuse to use work mostly fine if you actually design the rest of the product around them and put a modicum of effort into them.
"It doesn't scale" - that can be said of the fundamental database design itself. If you need to scale hard then you need to pick a distributed database.
You can source control them, but then you have a deployment coordination problem. Often changes to business logic will be half in application code and half in the sproc, and you have to coordinate the deployment of that change. You have to somehow reason about what version of the sprocs are on your server. Add on that usually in enviroments that use sprocs, changes to them are gatekept by DBAs. Just write your business logic in application code.
Transpile? why on earth would anyone bother with that, it just makes it even more complicated and impossible to debug. without that, TSQL/PSQL/PGSQL are objectively very awkward languages for imperative business logic. People are not familiar with them, it makes it hard to jump from regular code to *SQL code to read the logic flow, nevermind debugging them with breakpoints and suchlike. Splitting your buis logic between database and app makes it much more awkward to test too. Just write your business logic in application code.
Scale - making the hardest to scale piece of your architecture , the traditional rdmbs, an execution environment for arbitrarily complex business logic is going to add a lot of extra load. Meaning you're going to have scaling problems sooner than you otherwise would. Just write your business logic in application code, you can scale those pods/servers horizontally easy peasy, they're cheap.
Look you can make any technology work if you try hard enough, but sprocs are almost all downsides these days. The one minor upside is some atomicness, but you can get that with a client side transaction and a few more round trips. There's just IMO no reason to pick them anymore and pay these costs, given what we've learned.
BTW I could have made this clearer in the original comment but i love RDBMSs - i love schemas, indexes, complex querying, and transactions. It is not weird to keep picking these databases to get those things.
I just will never again use them as a programming environment.
I think you put too much weight on what people currently do. Every year it seems the previous years trendy ideas are "terrible" and something new is "the way it should have always been done from the start".
>[stored procedures, business logic splits, and DBA gatekeeping]
You probably shouldn't be using stored procedures to be implementing more than you need to keep things internally consistent (i.e., almost never). I actually don't understand why you're so hung up on stored procedures. I've seen people try to implement whole applications on top of stored procedures, certainly not anything I'm recommending.
Yes if you split your business logic like that then you're going to have trouble, so don't. If instead you treat your database as an isolated unit and keep it internally consistent, then it's not really that difficult to keep things always working even when migrations happen.
As for the DBAs, nobody says you have to have DBAs, but if you're going to use a technology, it's worth having someone who understands it. (or, you know, just pick a different database).
>[transpile?]
I mean, lots of people use query generators which let you write e.g. native python and generate equivalent SQL (SQLAlchemy Core).
Anyway, your complaint seems to be that people don't understand SQL databases well enough to use them properly. Sure, but this is like complaining that git is bad because nobody knows how to use it. People who don't know or want to learn git, SQL, or a tool they're using, should pick a different tool.
>[it scales worse if you try to implement an application in it]
well yes
>[more complaining about abuse of stored procedures]
sure
...
I think at some point you read something I wrote as: "And you should attempt to find out a way to shoehorn your entire application into the RDBMS such that your application is just a frontend which calls stored procedures on the RDBMS" but that's certainly not anything close to what I said.
Designing your database such that it stays internally consistent when you perform operations on it is a good goal, and doesn't require filling it with stored procedures and making it your backend.
Implementing constraints in application code is certainly a lot easier (and easier to test) than in the database. What I want is a database which far stronger constraint capabilities than eg MySQL or Postgres provide, so that my application-level constraints can live in the database where they belong, without compromising ease of development and maintenance.
I can’t imagine doing this. You’re basically saying that you will only ever have one program writing to your database.
The idea being that a single service/codebase is controlling interaction with the database. Any read/writes should go through that service's APIs. Basically a "CreateFoo(a, b, c)" API is a better way to enforce how data is written/read vs every service writing their own queries.
1. Costs of API maintenance - rigid APIs need constant change. To mitigate that people create APIs like GraphQL which is a reimplementation of SQL (ie. a query language).
2. Costs of data integration - (micro)systems owning databases need data synchronisation and duplication. What's worse - most of the times data synchronisation bypasses service APIs (using tools like CDC etc.) so all efforts to decouple services via APIs are moot.
A single data platform (a database) with well governed structure enforced by a DBMS is a compelling alternative to microservices in this context.
Rigidity is the point...
If you have a "CRUD a Foo" set of APIs, and how you create/read a "foo" is defined in that API. Sometimes change is necessary, sometimes the API contract changes, but sometimes just the internal implementation changes (eg new or refactored tables/columns). The rigidity of the API ensures that every downstream user of a foo creates/reads in the exact same way. It centralizes permissions, overrides (eg. a/b testing), rate limiting, transactions, etc. to be homogeneous for everyone. If you want to create/read a foo via database queries alone, and the database changes or the business logic changes, then the same issue occurs where all clients need to change, but now it needs to be coordinated everywhere, and you can't benefit from a centralized change hiding implementation details.
Many people prefer to keep all the logic around enforcing consistency and object lifecycle (application behavior) in the application layer. This allows a single codebase to manage it, and it can be uniformly guarded with tests. Exposing the database itself is really just an example of a leaking implementation details.
> To mitigate that people create APIs like GraphQL
If you need raw flexible queries, then this is probably the wrong sort of solution. Ideally, the developer of a service already knows what queries will be made, and clients don't typically need detailed custom queries. Analytics (typically read-only) should already occur in an offline or read-replica version of the database to not impact production traffic.
> (micro)systems owning databases need data synchronisation and duplication.
What do you mean? Foo service exposes "CRUD-Foo" apis and is the only service that calls the Foo-storing database. If Foo service is horizontally scaled, then it's all the same code, and can safely call foo-db in parallel. If the database needs horizontal scaling, you can just use whatever primitives exist for the database you picked, and foo-service will call it as expected, and the transaction governs the lifecycle of the records.
> ...all efforts to decouple services via APIs are moot.
To be clear, different services wouldn't operate on their own duplicated version of the same shared data, in their own database. They'd call a defined API to get that data from another service. The whole point of this is to allow each service to define the interface for their data.
This is a mistake that a lot of people make - treating database as an implementation detail. Relational model and relational calculus/algebra were invented for _sharing_ data (not storing). The relational model _is_ the interface. Access to data is abstracted away - swapping storage layer or introducing different indexes or even using foreign data wrappers is transparent to applications.
Security and access policies are defined not against API operations but against _data_ - because it is data that you want to protect regardless of what API is used to access it.
> To be clear, different services wouldn't operate on their own duplicated version of the same shared data, in their own database. They'd call a defined API to get that data from another service.
You mean synchronous calls? This actually leads to what industry calls "distributed spaghetti" and is the worst of both worlds.
> The whole point of this is to allow each service to define the interface for their data.
The point is that well defined relational database schema and SQL _is_ a very good interface for data. Wrapping it in JSON over HTTP is a step backwards.
People don't hate stored-procedures (or other custom SQL constraints/triggers) per-se, but more the amount of headache they bring when it comes to debugging and keeping in sync with version control.
That's way easier to debug than if Jimmy also had his query living as a string somewhere in your backend code.
Honestly, if this stuff is hard, it's because you've made it hard. I can only assume most people griping about stored procedures don't have a /sql folder living next to their /python or /java folder in source control.
What build system has good integration for doing these kinds of updates out of the box or does it require customization?
Don’t worry, just wait another 5-10 years, they’ll be back in vogue again.
Not necessarily. All depends on particulars. Very complex processing of reasonably sized chunk of data especially when can be done in multiple threads / distributed can be way more efficient.
I've head very real case: consulting company was hired by TELCO to write the code that will take content of the database and create one huge file containing invoices to clients. Said file would then be sent to a print house that can print the actual invoices, and mail those to clients.
They tried to do it with stored procedures and had failed miserably - it would run for a couple of days give or take and then crash.
I was called for help. Created 2 executables. On is a manager and the other is multithreaded calculator that did all the math. Calculator was put on 300 client care workstations as a service. Dispatcher would do initial query from accounts table for current bill cycle, split account id's into few arrays and send those arrays to calculators. Calculators would suck needed data in bulk from a database, do all the calculations and send the results back to dispatcher for merging into final file. TELCO people were shocked when they realized that they can have print file in less than 2 hours.
- Audit tables are often implemented thanks to triggers
- Soft deletes can be easily implemented thanks to triggers
Its storing transform code in an inscrutible way, hiding pipeline components from downstream users. The rest of your alarms and dq has to be extendend to look into these...
Oh an PL/SQL requires a huge context switch from whatever you were doing.
On the other hand, yes, going back to the days of poorly written and documented oracle pl/sql stored procs, yes, I shudder too, but then again, that can also be said of a number of public APIs that have been hacked/had side effects exposed.
I understand if it's a prototype, I have seen developers of even mature products follow this mentality that they want the DB to be portable. It's insane. The reality is that rarely happens and when it does its mostly cause your business is growing. But not knowing (or caring to know) the features your DB offers you and not exploiting it is just laziness.
But whereas Presto/Trino/Bigquery/etc are server-based where queries execute on a cluster of compute nodes, duckdb is something you run locally, in-process.
- Deploy to your own account (BYOC)
- "S3 first": Partitioned and compressed Parquet on S3
- Secure sharing of write access to the Data Tap URL
- 50x more cost efficient than e.g. "Burnhose" whilst also having unmatched scalability of Lambda (1000 instances in 10s).
[1] https://www.taps.boilingdata.com/ (founder)
SELECT *
FROM read_parquet([
's3://bucket/file1.parquet',
's3://bucket/file2.parquet'
]);
You're just showing a query that dynamically fetches data.
It's a cool example, but really an antipattern. Nowadays everyone gets analysts want access to raw data, since they know which aggregations they need best, whereas data engineers stay away from pre-aggregating and focus on building self-service data access tooling. Win-win this way.
How about building a duckdb accessible catalog on top of s3? Like instead of read_parquet, you would select from tables, which themselves would be mapped to s3 paths aka external tables.
When you start running aggregations/windows over large amounts of data, you'll soon see the difference in performance.
I guess you could run into something similar with this solution?
https://duckdb.org/2024/02/13/announcing-duckdb-0100#backwar...
I don't understand this - if I start saving those files in a different format how will it continue to work? Why would the view remain the same if I just rename columns, even?
Is it like dumping a SQLite database somewhere with a view in it, and connecting over that as well? Or does DuckDB have more magic to transfer less data in the query work?
Where DuckDB will have an advantage is in the processing speed for OLAP type queries. I don't know what the current state of SQLite Virtual Tables for parquet files on S3 is, but DuckDB has a number of optimisations for it like only reading the required columns and row groups through range queries. SQLite has a row oriented processing model so I suspect that it wouldn't do that in the same way unless there is a specific vtable extension for it.
You can get a comparable benefit for data in a sqlite db itself with the project from the following blogpost but that wouldn't apply to collections of parquet files: https://phiresky.github.io/blog/2021/hosting-sqlite-database...
I'm quite intrigued about DuckDBs ability to read parquet files off of buckets. How good is at at simply ignoring files (e.g, filtering based on info in datafiles paths)?
It will even use range requests in S3 to avoid fetching the entire blob.
See here for the details https://duckdb.org/2021/06/25/querying-parquet.html#automati...
Parquet pushdowns combined with Hive structuring is a pretty good combination.
There are some HTTP and Metadata caching options in DuckDB, but I haven't really figured out how and when they really making a difference.
Duckdb over vanilla S3 has latency issues because S3 is optimized for bulk transfers, not random reads. The new AWS S3 Express Zone supports low-latency but there's a cost.
Caching Parquet reads from vanilla S3 sounds like a good intermediate solution. Most of the time, Parquet files are Hive-partitioned, so it would only entail caching several smaller Parquet files on-demand and not the entire dataset.
Running a simple analytics query on ~4B rows across 6.6K parquet files in S3 on an m6a.xl takes around 7 minutes. And you can "index" these queries somewhat by adding dimensions in the path (s3://my-data/category=transactions/month=2024-05/rows1.parquet) which DuckDB will happily query on. So yeah, fairly expensive in time (but cheap for storage!). If you're just firehosing data into S3 and can add somewhat descriptive dimensions to your paths, you can optimize it a bit.
Parquet is kind of winning the OSS columnar format race right now.
It's technically not the very best format (ORC has some advantages), but it's so ubiquitous and good enough -- still far better than than CSV or the next best competing format. I have not heard of Carbon -- it sounds like an interesting niche format, hopefully it's gaining ground.
It's the VHS, not the betamax.
[1] https://parquet.apache.org/docs/file-format/data-pages/encod...
I’m sure there are more advanced formats.
..and blah blah about the sharing from a developer perspective.
In reality the analyst is some higher up that only knows how to import/view CSV in Excel so that's exactly what will ask for ("hey zzz can you send me those daily parcels in a .csv file? thank you")
Extension available (read only, apparently)
InvalidInputException: Invalid Input Error: Attempting to execute an unsuccessful or closed pending query result Error: IO Error: Hit DeltaKernel FFI error (from: kernel_scan_data_next in DeltaSnapshot GetFile): Hit error: 2 (ArrowError) with message (Invalid argument error: Incorrect datatype for StructArray field "partitionValues", expected Map(Field { name: "entries", data_type: Struct([Field { name: "keys", data_type: Utf8, nullable: false, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: "values", data_type: Utf8, nullable: false, dict_id: 0, dict_is_ordered: false, metadata: {} }]), nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, false) got Map(Field { name: "key_value", data_type: Struct([Field { name: "key", data_type: Utf8, nullable: false, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: "value", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }]), nullable: false, dict_id: 0, dict_is_ordered: false, metadata: {} }, false))
I'm guessing you were reading from a table with a checkpoint :)
DuckDB unfortunately hasn't pulled in the latest changes just yet, but as soon as they update to include that fix, I expect your queries will work.
If the table is public, I'd be happy to check that kernel now supports reading it.