Hacker News Reader

> 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.

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.

> 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.

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?

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.

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.

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.

>> 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.

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