Replacing Absolute Paging and Related Properties

Im my professional career I've never worked with dataset large enough where pagination would become difficult to solve. I wonder where are the limits of SQL, Postgres. How do I learn to extract most of rdbms?