How to select the next 100 rows from a large table?

Question

PostgreSQL

There are two related tables. I make a selection:

SELECT * FROM video INNER JOIN recording ON video.\"recordingId\" = recording.id LIMIT 100;

The number of rows in one table is over 10 million.
1. What is the best way to implement select in postgresql so that the next 100, 200, etc. can be selected?
2. Do I need to include order by in such huge tables?
3. Should three tables be linked? Will it be faster if I make three separate queries to the database?

Answer 1

1) SELECT ... LIMIT 100 OFFSET
300 the database will have to read all the million rows and sort them. If it's not critical, then it's not worth it.
3) How is it?

Answer 2

Presentation from one of the conferences (PGDay) https://pgday.ru/presentation/232/5964945ea4142.pdf , look, starting from page 14, there are more complicated methods, but they will provide good speed, unlike OFFSET.