Paging Through Parquet in DuckDB: file_row_number vs OFFSET
Paging Through a Parquet File in DuckDB: File_row_number or Offset?
I tested paging through large Parquet files in DuckDB to see if OFFSET causes quadratic slowdowns. Surprisingly, DuckDB rewrites OFFSET efficiently, but using file_row_number remains faster and more reliable. Crucially, I discovered that OFFSET without ORDER BY can silently drop or duplicate rows, making simple row counts useless for validating data exports.
So don't validate an export by counting rows. A count can't see this, because the drops and the duplicates cancel each other out.
- matharmin
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.
- RobinL
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...