Hacker News Reader

Comment by stephen_g | Hacker News Reader

For the most part, that happened long ago... What features are you using that aren't in Postgres?

Speed :) On one project we were able to get MySQL onto an embedded system where Postgres kept choking. Also replication is dead-simple with MySQL.

It's amazing how fast you can go when you don't care about safety ;)

That said, for all non trivial work loads, the latest postgres is quite the work horse, but of course requires some tuning for performance. We ended up switch to it after MySQL consistently sucked on smaller joins.

Can't argue on the replication deal: it's a work in progress.

If it's an embedded system, why not use SQLite instead?

Honestly I wasn't part of that conversation on that project. I just heard rumblings from a distance :)

> Speed :)

At the cost of ACID. In my experience PostgreSQL is faster than MySQL-InnoDB.

Postgresql is generally faster than mysql. Fitting it onto an embedded system is either memory or storage constraints, but postgresql is equal or better in both those regards so I can't imagine what problem you had. Replication is dead simple with postgresql as well, with the added bonus that it doesn't randomly die and require manually intervention to get it working again.

For any reasonable RDBMS deployment resources PostgreSQL has been faster than MySQL for me. Write-heavy loads are a huge win for it, but even on large reads (formerly its weakness) PostgreSQL has the advantage unless you've got no writes at all on the table. I guess if you've got completely static data MVCC isn't a win, but at that point I'm not sure you need to be using an RDBMS in the first place...

Replication is dead simple now, but a few years ago it was not.

select count(*) speed.

Apparently it's decently fast in 9.4, but... that's been 20+ years where it's not been anywhere near as fast as MySQL for most common use cases (blogging, forums, etc where paginating records is common).

Postgresql has long been consistently faster than mysql. Count(*) is slow because it is doing what you ask. You solve this problem the same way you solve any aggregate performance problem, with a computed column.

Consistently faster?

In my experience over the years, on any moderate sized db (more than, say, 100k rows in a table) select count() has always been faster under mysql - both myisam and innodb (although innodb doesn't claim to be 100% accurate all the time).

Why should I* have to keep a computed column when the core engine has all the data all the time? And it's something pretty fundamental to the data - how much of it is actually in there.

I think we're misunderstanding each other. I thought you were claiming mysql is faster for "most common uses cases", which is why I pointed out that it is actually the opposite. Yes, mysql is faster at count(), and yes it is because it doesn't actually do a count() and just returns a guess. If your argument that postgresql is bad is that it doesn't return fake data, then it says more about you than about postgresql.

>Why should I have to keep a computed column when the core engine has all the data all the time?

The core engine does not have that information, keeping that information for no reason would be foolish. You should keep a computed column for performance, obviously. That is your complaint remember? How is this any different than "select users.id, users.name, count(photos.id) from users left join photos on photos.user_id = users.id where users.id = ?" being slow? How do you solve that being slow? You use a computed column. The fact that you complain about a complete non-issue because you inexplicably refuse to use the standard solution to the problem in this one particular instance of the general pattern is neither logical nor reasonable.

I may be wrong here, but I think the difference is largely around how things are viewed differently between experts and non-experts.

Experts understand that "estimate the number of rows" and "count the number of rows" are two different operations. They know that "count()" is supposed to count, not estimate and that in MySQL it does an estimate instead. They even know a simple way to provide quick estimates.

Non-experts know that "count()" is faster in one tool than another.

Certainly, mysql's existence has always been based on "we're the db for people who don't know anything about dbs". But in this particular case, the person I responded to does know that mysql isn't doing a count, he pointed it out himself.

This select count in PostgreSQL is comparable to MySQL's

select n_live_tup from pg_stat_user_tables where relname = 'mytable';

Reply on news.ycombinator.com