Efficient pagination of a SQL table with 100M records
<p><pre><code>  > You need to walk thru the table,
  > extract each record,
  > transform it inside your application’s code
  > and insert to another place.
</code></pre>
The writer said his database died in the middle of the query when he selected the 100,000,000 rows without first breaking them into pages.<p>It looks like he's using MySQL. I've used PostgreSQL for over a decade, and I just don't see this happening, though I've tested only on a table with a few million rows. But anyway I doubt PostgreSQL loads the whole table into memory when you select it. It seems to load just parts at a time, as needed. And for what it's worth, an offset of a few million rows still took just a few seconds.<p>Actually what I would first try to is dump the table to a file, transform it if possible with Linux command-line tools like sed and awk, and then load the file into the new table.<p>Can anyone confirm if Postgres would die like MySQL did on too big a dataset --- selecting everything without paginating but fetching just one row at a time in the application?