3 ms·

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. Thes

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.

porker 3y ago
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?
- meitham 3y ago
  financial risk calculations with many sensitivities
hawk_ 3y ago
> results lose their original casing and returned all lowercased. That's a shame. Have you reported this bug on their repo?
- meitham 3y ago
  Someone else beat me to it https://github.com/duckdb/duckdb/issues/3821 https://github.com/duckdb/duckdb/issues/3821
boomskats 3y ago
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?
- meitham 3y ago
  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.
jason_wo 3y ago
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/ https://simonwillison.net/2022/Sep/1/sqlite-duckdb-paper/ [1] https://vldb.org/pvldb/volumes/15/paper/SQLite%3A%20Past%2C%20Present%2C%20and%20Future https://vldb.org/pvldb/volumes/15/paper/SQLite%3A%20Past%2C%...
- meitham 3y ago
  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.

porker 3y ago

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?

meitham 3y ago
financial risk calculations with many sensitivities

hawk_ 3y ago

> results lose their original casing and returned all lowercased. That's a shame. Have you reported this bug on their repo?

meitham 3y ago
Someone else beat me to it https://github.com/duckdb/duckdb/issues/3821 https://github.com/duckdb/duckdb/issues/3821

boomskats 3y ago

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?

meitham 3y ago
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.

jason_wo 3y ago

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/ https://simonwillison.net/2022/Sep/1/sqlite-duckdb-paper/ [1] https://vldb.org/pvldb/volumes/15/paper/SQLite%3A%20Past%2C%20Present%2C%20and%20Future https://vldb.org/pvldb/volumes/15/paper/SQLite%3A%20Past%2C%...

meitham 3y ago
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.