3 ms·

> You need to walk thru the table, > extract each record, > transform it inside your application’s code > and insert to another place. The writer said hi

> You need to walk thru the table, > extract each record, > transform it inside your application’s code > and insert to another place. 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. 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. 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. 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?

anarazel 9y ago
> 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? Depends on the client library. A lot of clientside SQL interfaces load all rows into arrays of hashes or such into memory.
- TheAceOfHearts 9y ago
  I'd seriously expect anyone working with such large datasets to be working with lower level libraries when interfacing with the database. Stuff like ActiveRecord is fine for simple operations and small datasets, but surely at the scale of hundreds of millions of records you can afford to hire an engineer capable of writing SQL and understanding the underlying database operations? Am I just being naïve?
  - barrkel 9y ago
    In my experience, if you're working with moderately large data sets and want to provide interesting UI with rich filtering operations, you'll want a lot more control over execution plans than straightforward declarative SQL gives you. You end up forcing the database's hand by nesting subtable queries; and since ORM tools don't generate queries in this style, you write your own SQL generation library.
  - userpass 9y ago
    That very same engineer capable of using ActiveRecord should also be capable of writing SQL.
shub 9y ago
The writer is incorrect. MySQL doesn't wait for query execution to finish to start sending rows, unless it has to. For a SELECT with no WHERE, it definitely doesn't have to (ORDER BY requiring a sort step is one case where it does). However, the default behavior of most (all I've ever run in to) MySQL clients is to buffer the entire result set before returning, to minimize the time the server spends executing the statement and holding locks.
- jbergens 9y ago
  When you show a paginated list to the users they usually wants some sort order. Often they also want to be able to sort on any of multiple columns. Don't remember what example the writer had but sorting is normal.
tkyjonathan 9y ago
He did not say that his database died. He said the "DB just kills it" as in, the query reached some sort of timeout.
no1youknowz 9y ago
> I've tested only on a table with a few million rows I've tested postgres with > 250m rows. It has problems. I've also tested to 1b rows, same issue. Now throw in some sum, count and group by functions, more issues. Add a join? Now you've major problems. Add several joins? Woah there! I've been able to bring a 5 node citusDB cluster to it's knees. Which the only solution was to scale out massively to double digits servers. But you can't do that on a limited budget. The caveat here, this was about 2 years ago now. I don't know if there have been any improvements since that time. I don't have a postgres database now, I've since migrated over to memsql as the majority of my work is OLAP.
- kuschku 9y ago
  I run a 270m rows database for my IRC bouncer, and run fulltext searches over it all the time, with many joins, expensive ranking, etc. On a single machine. I’ve never been able to bring PGSQL to its knees.
  - userpass 9y ago
    Did running the queries impact other users of that IRC bouncer?
developer2 9y ago
>> selecting everything without paginating but fetching just one row at a time in the application This is called an "unbuffered query". The problem usually isn't with the number of rows you're trying to read; it's about what the database must do on its end before it can know which rows to send first. ORDER BY being one example wherein, unless you have perfect indexes including the sorted column(s), the database has to generate the entire resultset before even sending the first row. That means writing potentially millions of rows' worth of information to memory or - more commonly at large sizes - to a temporary table on disk. Unbuffered queries are fairly rare in the "real world". While in theory it means you're parsing results faster and using less memory on the application side, it introduces difficulties like the fact you can't run a subsequent query until you've finished reading all rows from the original query. When an application is reading this many rows with a single query, it's usually an indication that the app is poorly written. Of course there are exceptions, though typically reserved for maintenance scripts, reporting, data migrations, etc.

anarazel 9y ago

> 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? Depends on the client library. A lot of clientside SQL interfaces load all rows into arrays of hashes or such into memory.

TheAceOfHearts 9y ago
I'd seriously expect anyone working with such large datasets to be working with lower level libraries when interfacing with the database. Stuff like ActiveRecord is fine for simple operations and small datasets, but surely at the scale of hundreds of millions of records you can afford to hire an engineer capable of writing SQL and understanding the underlying database operations? Am I just being naïve?
- barrkel 9y ago
  In my experience, if you're working with moderately large data sets and want to provide interesting UI with rich filtering operations, you'll want a lot more control over execution plans than straightforward declarative SQL gives you. You end up forcing the database's hand by nesting subtable queries; and since ORM tools don't generate queries in this style, you write your own SQL generation library.
- userpass 9y ago
  That very same engineer capable of using ActiveRecord should also be capable of writing SQL.

shub 9y ago

The writer is incorrect. MySQL doesn't wait for query execution to finish to start sending rows, unless it has to. For a SELECT with no WHERE, it definitely doesn't have to (ORDER BY requiring a sort step is one case where it does). However, the default behavior of most (all I've ever run in to) MySQL clients is to buffer the entire result set before returning, to minimize the time the server spends executing the statement and holding locks.

jbergens 9y ago
When you show a paginated list to the users they usually wants some sort order. Often they also want to be able to sort on any of multiple columns. Don't remember what example the writer had but sorting is normal.

tkyjonathan 9y ago

He did not say that his database died. He said the "DB just kills it" as in, the query reached some sort of timeout.

no1youknowz 9y ago

> I've tested only on a table with a few million rows I've tested postgres with > 250m rows. It has problems. I've also tested to 1b rows, same issue. Now throw in some sum, count and group by functions, more issues. Add a join? Now you've major problems. Add several joins? Woah there! I've been able to bring a 5 node citusDB cluster to it's knees. Which the only solution was to scale out massively to double digits servers. But you can't do that on a limited budget. The caveat here, this was about 2 years ago now. I don't know if there have been any improvements since that time. I don't have a postgres database now, I've since migrated over to memsql as the majority of my work is OLAP.

kuschku 9y ago
I run a 270m rows database for my IRC bouncer, and run fulltext searches over it all the time, with many joins, expensive ranking, etc. On a single machine. I’ve never been able to bring PGSQL to its knees.
- userpass 9y ago
  Did running the queries impact other users of that IRC bouncer?

developer2 9y ago

>> selecting everything without paginating but fetching just one row at a time in the application This is called an "unbuffered query". The problem usually isn't with the number of rows you're trying to read; it's about what the database must do on its end before it can know which rows to send first. ORDER BY being one example wherein, unless you have perfect indexes including the sorted column(s), the database has to generate the entire resultset before even sending the first row. That means writing potentially millions of rows' worth of information to memory or - more commonly at large sizes - to a temporary table on disk. Unbuffered queries are fairly rare in the "real world". While in theory it means you're parsing results faster and using less memory on the application side, it introduces difficulties like the fact you can't run a subsequent query until you've finished reading all rows from the original query. When an application is reading this many rows with a single query, it's usually an indication that the app is poorly written. Of course there are exceptions, though typically reserved for maintenance scripts, reporting, data migrations, etc.