[mgkimsal]

#2 - even if you don't store binaries, just very long posts, it's a problem.

#4 - whether it's a misconception or not, the majority of tutorials and even the default utilities make that assumption. Working around that is not something as widely documented as the defaults.

Also, forum software is a big arena. Not being able to get accurate count() values back hurt(s) postgresql. It's hurting people who now rely on MySQL's innodb as well, but you always have the option of MyISAM for certain tables that you want accurate count()s on for pagination. I've heard for years that "it's so easy to do this in PostgreSQL, just write some triggers and stored procedures... " yet... if it's "so easy", why not just bundle that in as default functionality (or perhaps even just a script that would create triggers for you) in postgresql?

I don't ever recall hearing about the "embedded linkable and serverless variant" of MySQL, and have been doing PHP since 1996. Perhaps it was a licensing issue? MySQL seemed to take off largely due to a rise in PHP, but sqlite seems to have taken off more because of embeddability and the public domain aspect of it first, well, before I ever saw PHP adoption of sqlite.

[pilif]

> I don't ever recall hearing about the "embedded linkable and serverless variant" of MySQL, and have been doing PHP since 1996. Perhaps it was a licensing issue?

http://dev.mysql.com/doc/refman/5.1/en/libmysqld.html

pretty much a licensing issue as all of MySQL is released under the GPL or a proprietary license. This also includes the mysql client by the way, but they made a license exception there that allowed linking against PHP (extensions mysql and mysqli) and later there was a reimplementation of the on-the-wire protocol inside a PHP extension (mysqlnd) released under the PHP license.

> Not being able to get accurate count() values back hurt(s) postgresql.

count() is totally accurate within the limits of MVCC. If you need it to be 100% accurate across statements, make your transaction SERIALIZABLE.

There's one thing about count(): count(*) under MySQL, if using MyISAM tables, is optimized so it doesn't have to actually count and thus is much faster than, say count(row), count(whatever) with a where clause or count([asterisk as to not confuse the HN parser.]) in any other database.

But the moment you use InnoDB or any other database that supports transactions, count(whatever) unfortunately requires counting in all cases.

[mgkimsal]

re: count() - I guess I should have also said "reasonably fast". Waiting 8 seconds for a count() isn't practical for many apps.

[jacques_chester]

> why not just bundle that in as default functionality (or perhaps even just a script that would create triggers for you) in postgresql?

Multi-version concurrency control means that a single universal definition of count() is basically insensible.

[bretthoerner]

Under what circumstances is count() inaccurate in Postgres?

[mgkimsal]

See earlier comment - I misstated my point - it's not so much that it's inaccurate as slow. Dog slow. InnoDB in MySQL 'fixes' the speed with estimates, but that's generally not very useful either in most cases (sometimes, it is).

I was confusing my earlier innodb count() experiences (inaccurate estimates) with postgresql count() experiences (dog slow).