Hacker News Reader

Comment by meitham | Hacker News Reader

I used duckdb successfully in prod to replace SQL Server. We have a micro batch that generates around 5 billion rows of very wide tables every 3 minutes. These data used to go into SQL server, only to be replaced by the new batch of 5 billions and gets marked for deletion. SQL Server was struggling with all these purge activities. Replacing with DuckDB made things much lighter and faster. The only issue I faced is the case sensitivity, where in DuckDB if you ask for your queries to be case insensitive, your results lose their original casing and returned all lowercased.

View on HN

I'm curious, what kind of problem needs 5 billion rows every 3 minutes to replace the previous 5 billion rows?

By "micro batch" I understand batch processing of some kind?

financial risk calculations with many sensitivities

How do you insert into DuckDB fast and what settings ("Indices") do you use? As far as I understand DuckDB builds up statistics for each "block" of data (number of different values, ... ). So I assume inserting is slow. There is a paper [0] and a comment [1] that mentions that DuckDB is 10-500 times slower in a write-heavy workload.

[0] https://simonwillison.net/2022/Sep/1/sqlite-duckdb-paper/ [1] https://vldb.org/pvldb/volumes/15/paper/SQLite%3A%20Past%2C%...

I have a large number of small and frequent batches, think of it like discrete ETL, where each process operates on a pandas DataFrame. This frame ends up being written to disc as parquet and immediately followed by creating a DuckDB that imports the parquet. The duckdb file from then on will only be opened for read, no further writes.

I use a python odata library to convert user queries in rest to a SQL similar to Postgres and run it on these duckdb for applying any filters where needed.

I know this is irrelevant, and I'm really no a fan of SQL Server either, but I'm curious - did you try partitioning those tables on the batch ID, and then truncating partitions instead of deleting old rows?

It's a bit more complicated than I put it above. The schema is quite normalised in SQL Server and represents financial risk, with several views on it. The schema makes sense for EOD data but not for live data. The whole performance of EOD was impacted by these live insertions. Moving to another DB (on another server) would incur licence fees and costs. DuckDB simple files are cost free. Many of these files are deleted even before anyone can be bothered to read them.

> results lose their original casing and returned all lowercased.

That's a shame. Have you reported this bug on their repo?

Someone else beat me to it https://github.com/duckdb/duckdb/issues/3821

Reply on news.ycombinator.com