2 ms·
> The advances features of Postgres are not that needed in most web applications. If they were that needed, more people would have used Postgres. I am not so s
by davidw 17y ago
> The advances features of Postgres are not that needed in most web applications. If they were that needed, more people would have used Postgres.
I am not so sure. Many people just start with Mysql (with MyIsam) and realize that they have a pile of transactionless FAIL on their hands only at some later date, without really going through a selection process.
Regarding the performance, has anyone actually done an apples-to-apples benchmark lately? Say PG 8.3 vs Mysql with InnoDB?
In any case, this particular comparison is not really worthwhile... it's full of hyperbole: "blazing fast", "rocks", "aeons" and so on. Doesn't sound all that professional.
- jrockway 17y agoIndeed. I have seen many "transactional" web applications that lose data because of MySQL's policy of handling errors by ignoring them, among other problems. (0000-00-00 as a date is just the tip of the iceberg.) I even know of self-proclaimed "Postgres experts" whose applications lose data with Postgres due to its very "liberal" default isolation level. (MySQL has the same problem, of course, except you can't even fix MySQL with a config setting.)
- davidw 17y ago> "Postgres experts" whose applications lose data with Postgres due to its very "liberal" default isolation level. Explain further. I'm not a Postgres 'expert', just a happy user, and I'd love to actually learn something from a silly X vs Y thread.
- jrockway 17y agoPostgres defaults to "read committed" isolation, rather than repeatable read or serializable isolation. I have mostly noticed the difference when dealing with updating HTTP session objects in a transaction; with "read commited" isolation, concurrent requests can overwrite newly-written data. With serializable isolation, the transaction that tries to overwrite new data with older data (read at the beginning of the request) is aborted. (The fix for this is difficult, but I basically don't use the session for much transient data anymore, so this became less of an issue. Before setting up the database to be strict, I never even thought of this as a problem. But obviously it is.)
- neilc 17y agoI wouldn't say it is "obviously" a problem: READ COMMITTED are perfectly reasonable semantics, and are preferable to many applications (e.g. many apps aren't written to retry transactions that abort due to serialization conflicts). Which isolation level ought to be the default is a matter of debate, but I don't think using RC by default is "obviously" wrong -- if an application depends on a particular isolation level for correctness, it should set it explicitly in any case.
- jrockway 17y agoJust remember that with read committed isolation, certain access patterns can silently trash your data. People are afraid of transactions aborting for some reason, as they think that's an error. It's not; you just retry your transaction, which will now be using correct data. "Read committed" opens you up to race conditions in exchange for transactions rarely (never?) aborting themselves. I think the default should be "never trash my data without telling me".
- gchpaco 17y agoPostgres defaults to READ COMMITTED transaction isolation, where it is possible that if you perform the same SELECT twice you will get different answers due to a transaction committing in between. This can affect updates in some circumstances, although if you build your database so that everything is done with INSERTs then it's pretty harmless. Postgres permits you to specify the SERIALIZABLE transaction level which is complete isolation, if you care about this. MySQL does not.
- ams6110 17y agoThe important thing is to understand your database's concurrency model. They don't all work the same way. This causes problems e.g. for SQL Server developers who are new to Oracle, or vice versa, and naively assume that everything is the same.
- rimantas 17y agoSeems like not many know about sql_mode in MySQL :( http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html