←back to thread

Everything I know about good API design

(www.seangoedecke.com)
428 points ahamez | 3 comments | 24 Aug 25 19:10 UTC | HN request time: 0s | source
Show context
Joker_vD ◴[25 Aug 25 16:22 UTC] No.45015564[source]▶
> Instead of using OFFSET, the query becomes WHERE id > cursor ORDER BY id LIMIT 10

Wait. Surely "ORDER BY id OFFSET 20 LIMIT 10" works about the same as "WHERE id > cursor ORDER BY id LIMIT 10", if "id" is indexed?

replies(1): >>45019544 #
1. asddubs ◴[25 Aug 25 21:55 UTC] No.45019544[source]▶
no, because it has to count the amount of matching rows preceding the offset rows to determine the offset, i.e. iterate over all preceding rows. The cursor provides a starting point for the offset so in this instance it's not necessary.

https://use-the-index-luke.com/sql/partial-results/fetch-nex...

replies(1): >>45021098 #
2. Joker_vD ◴[26 Aug 25 01:07 UTC] No.45021098[source]▶
It only has to count if the index doesn't store the amounts of records in the "chunks" it manages, does it? I'm pretty sure B-trees do actually store the sizes of the subtrees.
replies(1): >>45101592 #
3. VGHN7XDuOXPAzol ◴[02 Sep 25 11:25 UTC] No.45101592[source]▶
Afaik Postgres doesn't. In my exposure it'd be quite uncommon for a b-tree to store the size of a subtree; would cause more churn/writes when updating trees.

Perhaps some of the page-level Copy-on-Write databases (LMDB?) might do this, since they have to rewrite ancestor pages anyway.