Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

API pagination design

solovyov.net

Post reply on HN
Print view View original

121–130 of 150 posts

Re: API pagination design

#121

Post by lkbm »

I tried running their exact example queries, since I conveniently have a >100k row PostgreSQL table available of the same name. (> 100k rows) The first (offset) was indeed on the order of 5x slower, but I suspect what the author saw with a 40ms to a 0.1ms time difference was that it wasn't pre-warmed first run.

For me, with offset, the FIRST run was 25ms, but subsequet runs are around 2-3ms. For `id > 10000`, it was under 0.05ms.

It's also very odd that they don't do an `order by`. This seems important. Default ordering is semi-stable. It'll tend not to change, but it can at any point. Benchmarking without that may be misleading.

Re: API pagination design

#122

Post by lkbm »

djeiasbsbo wrote: post #67
altdatathrow wrote: post #51

Earlier quoted context omitted.

fyi that mysql multiple column answer is incorrect, mysql does indeed support row/tuple comparisons [1]: > For row comparisons, (a, b) > (x, y) is equivalent to: > (a > x) OR ((a = x) AND (b > y)) [1] https://dev.mysql.com/doc/refman/8.0/en/comparison-operators...

Just in case you might be able to help me out.. is there any SQL flavor that supports specifying any except certain columns? For example something like: SELECT * EXCEPT FOO_ID FROM FOO; I have always wanted this but have never vome across it...

I've also wanted this on Postgres for years.

If there's subset of rows you frequently want, you may just be able to define a view and use that. (At one point, I defined a text macro in my terminal to list the fields I usually wanted on our "orders" table.)

Re: API pagination design

#123

Post by callesgg »

I think is better to not use pagination at all.

Design your api or page so that you receive all or more entries than you could possibly need.

If the user wants them all pagination is just in the way. If the user does not want them all give them as many as they are likely to be able to handle.

Re: API pagination design

#124

Post by hellcow »

callesgg wrote: post #123

I think is better to not use pagination at all. Design your api or page so that you receive all or more entries than you could possibly need. If the user wants them all pagination is just in the way. If the user does not want them all give them as many as they are likely to be able to handle.

"Woah, our database is slowing to a crawl, requests are deadlocked everywhere. Yeah, some asshole is hitting GET /messages and pulling 2TB in a single request again."

"Surely that's what he wanted."

Re: API pagination design

#125

Post by contravariant »

ajitid wrote: post #41
contravariant wrote: post #22

Earlier quoted context omitted.

Though you can't avoid seeing an item just before it is deleted and skipping an item just before it is created. This isn't a huge problem but does mean that the list you end up with may not be a coherent view of the database. But if you require guarantees like that then you might be better of just using SQL directly. Or just include everything in a single response, HTTPS was designed to handle arbitrary length payloa…

> use a more streamable format than JSON Did you mean we should stream data over WebSocket or use HTTP/2 or do we need to do something different altogether?

I don't think you need to do anything difficult you can just treat it as one big file, pretty sure online radio streams have been using this technique for ages. Though I'm not too sure about the particulars.

This is annoying to do with JSON though because you need to remember to close all your brackets, something like CSV is a lot easier because you can just write the header once and then just stream the data.

Edit: Looks like Python supports this using chunked transfer by simply providing the HTTP request data as an iterator.

Re: API pagination design

#126

Post by bmm6o »

anamexis wrote: post #120
knorker wrote: post #83

Earlier quoted context omitted.

A B C D 2 items per page first page is A&B. Next query is "WHERE ID>'B'". What's the problem?

Aa and Ab are inserted after loading the first page.

Sure, and then B comes by again. Which just means there are different use cases with different definitions of correct, and you need to specify yours.

Re: API pagination design

#127

Post by nareshb513 »

I have implented such a work in my initial career days like 15 yrs back there i have implemented first, previous,next, last no goto page number. It works for multiple columns in the order by. The trick is to swith the order by in reverse and limit the results to page size, this way we get results in reverse orde but while returning just send them.in the reverse order....

The assumption is to include the primary key as mandatory order by at the end implicitly.

This makes a lot easy in other situations like if mulitple pages of records found with same value.. etc...

Re: API pagination design

#130

Post by abhishekjha »

Can somebody elaborate on what the last paragraph means? If I have a limit of count per page and I know which page I am based on URL I can always generate a prev/next page link. I guess it is pointing out that limit/offset needs be used in such a case. How did reddit do it in terms or pagination? Such a large website wouldn’t survive on limit/offset.
Post reply on HN