If I could have a pet feature added to DuckDB, it would be some form of native ordered table. In a database like Clickhouse or any of the dedicated time series DBMSes or log stores, there’s a built-in concept that a table might have an order, and the database will optimize based on the order. But, for databases that are logically just bags of rows (traditional DBMSes and also DuckDB [0]), you either need an index or you need to rely on full table scans or at least scans of big blocks. DuckDB does the latter really well, but I think it would be quite nice for some workflows to have explicit ordering. Also, I bet compression could work a lot better with ordering hints.
All that being said, I’m quite excited about DuckDB 2.0. I want to give the improved VARIANT support a try.
[0] Documentation on DuckDB’s native format is rather sparse AFAICT. But the DDL has nothing resembling an ordered table.
You‘re not the only one interested in this. But seems its a big change that would have to change many parts within DuckdB:
https://github.com/duckdb/duckdb/discussions/8444
Neat!
I’m sort of amazing that nothing in that thread mentioned ClickHouse, even just as a product that has this capability and from which one might take syntactical inspiration.
Arc does pruning, and make that scan faster. Check it out: https://github.com/Basekick-Labs/arc