Happy to answer any questions about deduplication. One thing that's not included in the write-up is that we also address out-of-order indexing alongside deduplication.
https://mega.nz/folder/A1BjnSYQ#NQe5qhYLVBqiRwhWRmcVtg
Article is updating too.
I basically executed literally what Clickhouse recommends at their guides for deduplication https://clickhouse.com/docs/en/guides/developer/deduplicatio....
Of course you can also materialize with aggregations or just use a group by, or even force optimize of the table. But my point is that you don't really get exactly once guarantees. Whoever is querying that table needs to be aware than a `SELECT * FROM tb` might contain duplicates and needs to adapt their queries accordingly.
Also i have not recently seen anyone not recommending it. It might have been the case a few years ago, but performance of final has improved and it’s faster than alternatives. People suggest to use MergeTrees obviously but if no alternative, replacing is the way to go.
I'm not sure what is about the database world where people are happy to discuss their competitors and include either mistakes or misinformation. It doesn't seem to happen in other parts of the industry.
Here is the command to input the files:
CREATE TABLE ecommerce_sample AS SELECT * from read_csv_auto('ecommerce_*.csv');
DuckDB is awesome. A couple of comments here. First of all, this is totally my fault, as I didn't explain it properly.
I am trying to simulate performance for streaming ingestion, which is the typical use case for QuestDB, Clickhouse, and Timescale. The three of them can do batch processing as well, but they shine when data is coming at high throughput in real time. So, while the data is presented on CSV (for compatibility reasons), I am reading line after line, and sending the data from a python script using the streaming-ingestion API exposed by those databases.
I guess the equivalent in DuckDB would be writing data via INSERTS in batches of 10K records, which I am sure would still be very performant!
The three databases on the article have more efficient methods for ingesting batch data (in QuestDB's case, the COPY keyword, or even importing directly from a Pandas dataframe using our Python client, would be faster than ingesting streaming data). I know Clickhouse and Timescale can also efficiently ingest CSV data way faster than sending streaming inserts.
But that's not the typical use case, or the point of the article, as removing duplicates on batch is way easier than on streaming. I should have made that clearer and will probably update it, so thank you for your feedback.
Other than that, I ran the batch experiment you mention on the box I used from the article (had already done it in the past actually) and the performance I am getting is 37 seconds, which is slower than your numbers. The reason here is that we are using a cloud instance using an EBS drive, and those are slower than a local SSD on your laptop.
You can use local drives on AWS and other cloud providers, but those are way more expensive and have no persistence or snapshots, so not ideal for a database (we use them sometimes for large one-off imports to store the original CSV and speeding up reads while writing to the EBS drive).
Actually one of the claims in DuckDB's entourage is that "big data is dead". With the power in a developer's laptop today you can do things faster than with cloud at unprecedented scale. DuckDB is designed to run locally on a data scientist machine, rather than on a remote server (of course you have Motherduck if you want to do remote, but you are now adding latency and a proprietary layer on top).
Once again thank you for your feedback!
Currently using BigQuery for a lot of this (ingesting ~5-10TB monthly) but would like to begin exploring in-house tooling.
On the flip side, we still use PSQL/RDS a lot and I enjoy it for the low operations burden - but we're doing some time series stuff with it now that is starting to fall over. TimescaleDB is nice because it is postgres, but afaik cannot work inside RDS. Clickhouse is next on my list for a test deployment, but QuestDB looks pretty neat too.
Reference count the hashes.
Clickhouse was deployed to replace a home grown distributed storage system that at one point in time a long time ago was much cheaper and faster than the results the team was getting with BQ.
We evaluated clickhouse and druid for a few data stores for doing interactive queries on a fairly high throughput clickstream data pipeline.
Clickhouse won in terms of performance. We did some chaos testing on it in the form of simulating node outages and network partitions and we were happy with the results. My only complaint is that there are some other database options which don't require me to run a database and that's nice if I can get away with it.
One of the things you might try is dumping your data into parquet files on gcs. There are quite a few databases you can query that with and get good results depending on your indexing and partitioning needs. It's tough to get lower operational burden than "stick it in cloud storage and sometimes spin up some stateless compute to query it".
I think duckdb is super cool but for me at the moment it's a solution that I'm still in search of a problem for.
Haven’t tried using it to read parquet direct from cloud storage but that’s on our todo list. We currently use it to generate a massive table once a day and then throughout the day that’s queried to serve prod requests.
No single query directly corrupted any persistent data, but "complicated" queries (any more nesting than the classic "select from join where" SQL backbone) were prone to returning no results, wrong results, error conditions, or slowly. For a bit I worked around that by wrapping right-sized queries (big enough to do something meaningful and avoid comms overhead, small/simple enough to work as expected) with a normal turing-complete language, but it was painful. Plus the defaults were quirky (broken telemetry opt-out, connections time out, RAM limits being per-arcane-questdb-subcomponent rather than per-questdb, ...) IMO, not that I would have minded enough to look elsewhere if the queries were correct.
Colored by that experience, I'd caution to thoroughly go through the docs and get it configured exactly as you want before deploying to prod, testing that the features you need behave correctly (best practice for most software, but IME not strictly necessary for a lot of products if your application code is sane), and also do something to probe reliability like spending a day hammering it with a toy project (maybe a few 10M row tables so that it's a fast experiment but anything fishy still stinks appropriately) and checking the query results. The current GitHub issues look fairly tame, so maybe it's better by now.
A couple of things the engineering team did to address this as it grew from the early days at Ycombinator:
QuestDB introduced fuzz tests for all major database components [1] which helped find and fix lots of bugs. Besides that, the SQLancer team [2] was kind enough to add QuestDB support and find bugs around tricky SQL edge cases. We do our best to squash all critical bugs and constantly grow our integration tests suite.
On the SQL side specifically, we brought many SQL-related fixes over the last few years, introduced EXPLAIN and query plan testing, and finally improved memory management of cached queries. We're prioritising new bugs should they arise on GitHub or elsewhere.
Large companies such as Airtel [3], Mizuho Bank, Airbus, NetApp, Yahoo [4], Central Group [5] (largest retailer in Asia), Cloudera use QuestDB in production today.
There is still a lot of work to be done to bring better functionality. If you're keen to try QuestDB for one of your projects, we'd love to hear your feedback and see how it works for you.
[1] https://questdb.io/blog/fuzz-testing-questdb/ [2] https://github.com/sqlancer/sqlancer/ [3] https://questdb.io/case-study/airtel-xstream-play/ [4] https://questdb.io/case-study/yahoo/ [5] https://questdb.io/case-study/central-group/
Though it works amazingly well, it is certainly slower than ingesting the data to be stored and manipulated in native formats.