Does this trick preclude the ability to sort your data within a partition? You wouldn’t be able to rely on the row IDs being sequential anymore to be able to just refer to a prefix of them within a newly created file.
This is a well-known class of optimization and the literature term is “late materialization”. It is a large set of strategies including this one. Late materialization is about as old as column stores themselves.
We're still drafting our next post in this series, but the answer is actually very simple: two tiers of object storage do not have the same drawbacks as a combination of object storage and local disk. We wanted to explain that in this post too, but it would've been unreasonably long.
We've designed WarpStream to work extremely well on the slower, harder-to-use one first, and that is how 95+% of our workloads run in production. The tiered storage solutions from other streaming vendors do the opposite, where they were first designed for local SSDs and then bolted on object storage later.
The equivalent would be if we were pitching our support for an even slower, cheaper tier of object storage like AWS S3 Glacier.
This strategy will not work well for Apache Kafka because it is extremely IOPS hungry if you have more than a few partitions, and a replay of a large topic will require lots of IO bandwidth. It would work well e.g. a columnar database where a query targeting old data may only require reading a small fraction of the size of the volume, but Kafka is effectively a row-oriented storage system, so the IO pattern is different.
We're not talking about no disks as in no storage, just nothing other than object storage. This does have a latency trade-off, but with the advent of S3 Express One Zone and Azure's equivalent high-performance tier (with GCP surely not far behind), a system designed purely around object storage can now trade cost for latency where it makes sense. WarpStream already has support for writing to a quorum of S3 Express One Zone buckets to provide regional availability, so there's not an availability trade-off here either.
Storage layout is not the primary issue here because IO throughput on commodity hardware has increased significantly in the last 10 years.
DuckDB is significantly faster than SQLite because it has a vectorized execution engine which is more CPU efficient and uses algorithms which are better suited for analytical queries. If you implemented a scan operator for DuckDB to read SQLite files, it would still have better performance.