Paging Through a Parquet File in DuckDB: File_row_number or Offset?

Posted by rustyconover 3 days ago

Comments

Comment by matharmin 3 days ago

I'd assume it's common knowledge that you cannot paginate using OFFSET + LIMIT unless you use ORDER BY on an unique set of columns, no matter what database you're using. The only exception is when the database provides an explicit order guarantee without the ORDER BY, such as the case with `preserve_insertion_order = true` here.

Comment by sroussey 3 days ago

Even then, that does not work if new records are being inserted.

Comment by maest 3 days ago

You handle this by paginating inside a transaction

Comment by sroussey 3 days ago

That does not work for web apps, mobile, etc

Comment by ndr 3 days ago

Unless I grossly misunderstood your point, you're bound to see stale data. Such is the nature of distributed systems.

Comment by sroussey 2 days ago

Yes, there is always a failure mode. But if do a search or have some result set, it is convenient to have paging work as close to expected as possible.

Say you are processing/shipping orders. You do the first page and then advance to page 2. The very next order may no longer be the next order that would have shown depending on how many orders came in. In fact if order are coming in fast it will be impossible to page through a search result. But if you incorporate the last I’d seen you will get something closer to what the user expects. Naive offset won’t give you this.

Comment by RobinL 3 days ago

There's also a pseudocolumn called rowid that's worth knowing about for similar purposes but on duckdb tables:

https://duckdb.org/docs/lts/sql/statements/select#:~:text=Ro...