I finally tried to build the whole stack again (after a very long hiatus), and was pleasantly surprised by how smoothly it ran. The almost-out-of-memory-computer-slowed-to-a-crawl is no more, and eventually came to completion Thank you @drotheram!)

So, I finally googled for "LIMIT and OFFSET" to learn about this magic trick of reducing memory usage, and came across the following articles:

• Efficient Pagination in SQL and ElasticSearch
• database - Why is retrieving data from Elasticsearch so much faster than from PostgreSQL? - Stack Overflow
• Five ways to paginate in Postgres, from the basic to the exotic

So, it seems that as the OFFSET increases, each SQL query becomes more time-consuming. "Cursor" is suggested as an alternative, and indeed, it seems Psycopg has support for cursors:

• Python psycopg2 cursors - Stack Overflow
• The cursor class — Psycopg 2.9.1 documentation
• Basic module usage: Server side cursors — Psycopg 2.9.1 documentation

Hope the use of cursor is indeed faster!