Live data from Hacker News

Can PostgreSQL pickup where MySQL left off?

news.cnet.com

31–40 of 46 posts

Re: Can PostgreSQL pickup where MySQL left off?

#31

When you’re choosing a database, you’re making a long-term decision, because changing your mind later is difficult and expensive. You want to get it right the first time Here's a comparison in a tabluar format http://database-management-systems.findthebest.com/compare/1...

that's a good comparison. We use MySQL and PostGre both in our organization and the deciding factor is mainly cost.

> PostGre

It has never been named "PostGre".

Its original name was Postgres, the current one is PostgreSQL with Postgres the accepted nickname.

Re: Can PostgreSQL pickup where MySQL left off?

#32
When I started with PHP3 development in 1998, PostgreSQL was still called Postgres95 and it had some severe limitations which were directly adverse to the features needed for web development:

1) performance was very slow

2) There was an effective row size limit of 8K over the whole row which worked against people who wrote forum software (big posts) or were (ugh) storing binaries in the database.

3) Even today, PostgreSQL requires a bit more maintenance and configuration until it works right, whereas MySQL ran out of the box. Back then, VACUUM wasn't at all optional and still required an exclusive lock over the tables it was working on.

4) There was a misconception that each Postgres user needed to be a Unix system user (this might or might not be true. Probably isn't, but I and others were certainly thinking that), so it was working against the usual cheap virtual hosting.

5) The MySQL extension for PHP was much more advanced than the postgres one, leading to people preferring MySQL which in the end lead to the virtual hosts only installing MySQL at which point Postgres' fate was sealed.

6) MySQL was coined as a spiritual successor to mSQL (just look at the names), which was very popular at the time as it was the first SQL-database for "normal people". You either did flat-files or mSQL.

7) MySQL had Windows support from the beginning. Postgres95 was still using cygwin (if you could get it to work). Back then, many people were using Windows as their development environment and being able to run the whole stack on your development machine does have some advantages.

By now, aside of the maintenance thing, Postgres has lost all these drawbacks, but now it's too late as people are using what they know is working for them, so they are just going with MySQL (or SQLite if they need more simplicity, which is interesting in itself as MySQL for ages provided an embedded linkable and serverless variant which also never took on).

Re: Can PostgreSQL pickup where MySQL left off?

#33
post #32

When I started with PHP3 development in 1998, PostgreSQL was still called Postgres95 and it had some severe limitations which were directly adverse to the features needed for web development: 1) performance was very slow 2) There was an effective row size limit of 8K over the whole row which worked against people who wrote forum software (big posts) or were (ugh) storing binaries in the database. 3) Even today, Postg…

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

Re: Can PostgreSQL pickup where MySQL left off?

#34
post #32

When I started with PHP3 development in 1998, PostgreSQL was still called Postgres95 and it had some severe limitations which were directly adverse to the features needed for web development: 1) performance was very slow 2) There was an effective row size limit of 8K over the whole row which worked against people who wrote forum software (big posts) or were (ugh) storing binaries in the database. 3) Even today, Postg…

> 2) There was an effective row size limit of 8K over the whole row which worked against people who wrote forum software and were (ugh) storing binaries in the database.

Could you not just use the dedicated TEXT or BLOB type for that kind of stuff, and store them out-of-row? (yes, would probably have been even slower)

Re: Can PostgreSQL pickup where MySQL left off?

#35
post #32

When I started with PHP3 development in 1998, PostgreSQL was still called Postgres95 and it had some severe limitations which were directly adverse to the features needed for web development: 1) performance was very slow 2) There was an effective row size limit of 8K over the whole row which worked against people who wrote forum software (big posts) or were (ugh) storing binaries in the database. 3) Even today, Postg…

#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 MyS…

Under what circumstances is count() inaccurate in Postgres?

Re: Can PostgreSQL pickup where MySQL left off?

#36
post #32

When I started with PHP3 development in 1998, PostgreSQL was still called Postgres95 and it had some severe limitations which were directly adverse to the features needed for web development: 1) performance was very slow 2) There was an effective row size limit of 8K over the whole row which worked against people who wrote forum software (big posts) or were (ugh) storing binaries in the database. 3) Even today, Postg…

#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 MyS…

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

Re: Can PostgreSQL pickup where MySQL left off?

#37
post #32

When I started with PHP3 development in 1998, PostgreSQL was still called Postgres95 and it had some severe limitations which were directly adverse to the features needed for web development: 1) performance was very slow 2) There was an effective row size limit of 8K over the whole row which worked against people who wrote forum software (big posts) or were (ugh) storing binaries in the database. 3) Even today, Postg…

> 2) There was an effective row size limit of 8K over the whole row which worked against people who wrote forum software and were (ugh) storing binaries in the database. Could you not just use the dedicated TEXT or BLOB type for that kind of stuff, and store them out-of-row? (yes, would probably have been even slower)

Nope. There was blob-support, but that relied on special library functions to extract the data to a file which you'd then have to manually read.

Of course this also means that you couldn't search in these fields or do anything else you'd do directly on the database.

TOAST tables were added in 7.1 AFAIK which lifted that limit and allowed for arbitrary sized rows by moving the big fields away into their own storage.

The old functionality is still there (http://www.postgresql.org/docs/9.0/interactive/largeobjects....) though I totally failed and still fail to see any practical use.

Re: Can PostgreSQL pickup where MySQL left off?

#38
post #4

I wonder why MySQL became super-popular and Postgres didn't. I remember reading for years about how Postgres had a bunch of stuff (e.g. transactions) that MySQL didn't (until recently) and that anybody who knew anything about databases would choose Postgres, yet people still chose MySQL. What did MySQL have during the last decade that made it better for certain applications than Postgres?

Right now - the biggest problem is Wordpress which has ZERO postgres support. A PG-supported version of Wordpress will go a long, long way in popularizing Postgres.

It'll never happen. Wordpress core might be ported, but there's thousands of plugins and themes that assume MySQL.

Re: Can PostgreSQL pickup where MySQL left off?

#39
post #4

I wonder why MySQL became super-popular and Postgres didn't. I remember reading for years about how Postgres had a bunch of stuff (e.g. transactions) that MySQL didn't (until recently) and that anybody who knew anything about databases would choose Postgres, yet people still chose MySQL. What did MySQL have during the last decade that made it better for certain applications than Postgres?

Well, MySQL has been easier to set up, that cannot be underestimated; I've basically saved off a pair of configuration files I've just reused a dozen times to set up PostgreSQL. MySQL also works pretty fast right out of the box, if you data is smaller than physical memory, it might be nearly as fast as you can make it by default. PostgreSQL has fairly conservative performance settings when first installed, you can make it really sing but it takes some tuning and in some cases that tuning requires a fairly deep understanding of your data. Think most MySQL users are not DBAs, all the data specific settings for a PostgreSQL like database might frighten them some; it's a really odd juxtaposition when you think about it, stereotypically, geeks are drawn to the knobs and dials you can play with but I honestly think in this case it turned them away.

Then a little bit of support from ISPs and corporations and it's a no-brainer for most folks. All the oss CMS and blogs and everything support MySQL really really well. ALl the ISPs pretty much use MySQL as the "database" solution.

Re: Can PostgreSQL pickup where MySQL left off?

#40
post #32

When I started with PHP3 development in 1998, PostgreSQL was still called Postgres95 and it had some severe limitations which were directly adverse to the features needed for web development: 1) performance was very slow 2) There was an effective row size limit of 8K over the whole row which worked against people who wrote forum software (big posts) or were (ugh) storing binaries in the database. 3) Even today, Postg…

#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 MyS…

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

Post reply on HN