On Tue, 14 Oct 2008, Ivan Sergio Borgonovo wrote:
The fact that out of the box on common hardware PostgreSQL under-perform MySQL with default config would matter if few paragraph below you wouldn't say that integrity has a *big* performance cost even on read-only operation.
If you want a more detailed commentary on that subject, I'd suggest http://wiki.postgresql.org/wiki/Why_PostgreSQL_Instead_of_MySQL:_Comparing_Reliability_and_Speed_in_2007 which hits all the major sides of the speed vs. integrity trade-offs here.
You suggest putting together a "how much are you paying for integrity?" comparison. That's hard to do from the PostgreSQL side because most options don't allow an unsafe mode. What would be easier is benchmarking some application in MySQL with the optional strict mode toggled on and off; MyISAM+loose vs. InnoDB+strict would be instructive I think.
Those of us who prefer PostgreSQL don't spend too much time working on this area because the very concept of a non-strict mode is horrifying. Quantifying how much full data integrity costs is like seeing how much faster you can run if you're set on fire: while you might measure it, far better to just avoid the whole possibility.
Well horror stories about PostgreSQL being doggy slow are quite common among MySQL users.
In addition to the default PostgreSQL configuration being optimized for size rather speed, there are a few operations that just don't execute well at all in Postgres. The most common example is how counting things happens; that's slow in PostgreSQL for reasons that are deeply intertwined with the transaction implementation. I don't believe there any problems like that in the Drupal implementation, but there's enough of that in other web applications that some percentage of horror stories come from that sort of thing--just not using PostgreSQL for a job it's a good choice for. It's hard to distinguish those cases from those where it was appropriate, but just wasn't setup properly or compared fairly.
Anyway making easier to tune PostgreSQL even if not optimally would be a good target.
There were two commits to the core PostgreSQL server code last month aimed at making it easier to build tools that interact with the server configuration. The tool(s) that use those features are coming soon.
-- * Greg Smith gsmith@xxxxxxxxxxxxx http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
