Limit / offset pagination is one the easiest Data pagination methods.
Using an ordered list of records, a LIMIT and an OFFSET we can easily achieve pagination.
Pages are created using a limit N and an offset Y. You would be able to calculate your offset, or page, by multiplying your limit N by an arbitrary page number, X.
A simple formula could look like: Offset = Limit * PageNumber.
Like everything, there are downsides to this
Volatile data When a record is inserted or deleted that comes before your offset, it is either duplicated or omitted.
Performance
The OFFSET clause is not as performant as a WHERE clause (as used in the …). The OFFSET needs to read all records from disk, regardless of indexes, before being able to ‘throw’ them away.
The performance really degrades over the OFFSET.
Is this only in PostgreSQL, or also in SQL Server?
This is not only in sequel databases but also NoSQL databases.
Reference
We need tool support for keyset pagination. (n.d.). Retrieved 2 November 2023, from https://use-the-index-luke.com/no-offset
dbdemon. (2020, March 11). Answer to ‘Offset vs Where performance for pagination with index’. Database Administrators Stack Exchange. https://dba.stackexchange.com/a/261718