[profquail]

You can also use SQL:2011 "System-Version Tables" (a.k.a. temporal databases) supported in most of the major RDBMSs now. You'd just need to keep track of a timestamp when you started querying/paginating and include it as part of the SQL query, which'll give clients a consistent view even if the database is being concurrently modified.

[Scarbutt]

temporal databases) supported in most of the major RDBMSs now.

Which ones?

[mgkimsal]

I'm only aware of SQL Server (2016), Oracle and MariaDB supporting temporal tables, and... having dived in to them, they can certainly add some complexity to your data and queries. Would be happy to learn of more - had to do some digging on this in late 2019 for a PostgreSQL project, but there's nothing native or 'standard' for pg at that point in time.

[dragonwriter]

It's in progress:

https://commitfest.postgresql.org/32/2316/

[mgkimsal]

Thanks!