Slashdot Mirror
Why To Choose PostgreSQL Over MySQL, MariaDB (dice.com)
Nerval's Lobster writes: PostgreSQL, MySQL, and MariaDB are the three "main" open-source relational databases available today (there are four if you count FireBird, but for brevity we're excluding it). For years, MySQL had a reputation of being faster than PostgreSQL, but much of that was due to the MyISAM database engine, which didn't support transactions. On the flip side of things, PostgreSQL had a reputation for being slower but more reliable. But with the recent versions of both platforms, things have started to change; for example, speed has been less of a problem for PostgreSQL, while MySQL now defaults to the InnoDB engine, which does handle transactions. According to developer David Bolton, here's why PostgreSQL is worth a second look for your database-management needs (Dice link).
At least for me, the killer feature of PostgreSQL is its procedure language PL/pgSQL. By a fortunate accident, I had the opportunity to write some complicated features (read "calculation heavy") for a web app using PL/pgSQL. Once coupled with triggers, you can just leave everything to the DB to the point, controller has to do nothing except query and return JSON objects to the front. It is so expressive, powerful, efficient and reliable.
I have worked with MySQL, Oracle, MSSQL and as of late MongoDB.
Given a choice, I will always settle for PostgreSQL... it is just so natural to work with.
PostgreSQL has it. Makes development faster by an order of magnitude with a big database schema.
and PostgreSQL has pgFouine http://pgfoundry.org/projects/...
I would expect the issue to be MVCC, not FKs. Both Postgres and Firebird do MVCC, which incurs overhead when writing data (never overwrite, always add delta records, then fix pointers so readers can follow the chain, and also cleanup deltas no longer needed by any active transactions) and when reading data (follow pointer chains, verify a given record should be visible to the current transaction despite it being listed in the slightly-larger index), etc.
The switch from myISAM to InnoDB brings MVCC with it (in addition to, as you point out, actual constraints) so the cause/effect may be unclear.
Everyone I know just calls it "Postgres".
This was its original name. It was the successor (Get it? "Post"?) to the RDBMS known as Ingres. There was some IP issue associated with the Postgres name when they went to open source it, so they gave it a new name: PostgreSQL. Yes, I agree it was a very poor choice, something only a true nerd would come up with.
That's because the PostgreSQL developers actually try to follow the ANSI SQL standards. MySQL and MSSQL? Not so much.
Not the same. JetProfiler shows you, in realtime and with pretty pictures, what fucked up stuff you're doing to your database. pgFouine is:
"pgFouine is a PostgreSQL log analyzer written in PHP. It is based on PQA, the Practical Query Analyzer written in Ruby. pgFouine aims to be able to parse huge logs and to have a nice and useful HTML output."
Yeah, welcome to 1995.
Depends on what kind of replication you need.
It does pretty decent asynchronous master->slave replication.
You can also have a mirror with synchronous writes.
Multi-master replication- there are some 3rd party tools to do that, still pretty young and immature AFAIK.
Clustering- there are some 3rd party projects to do that, some commercial.
More info here: https://wiki.postgresql.org/wi...
--Coder
I recommend either PostgreSQL or SQLite. PostgreSQL is so easy to install and set up, though, that I would recommend SQLite only if you don't control the server.
It's pronounced Postgres Q L, if you want to say it all the way. But it's okay if you just want to say Postgres. Even the database's default superuser is still called postgres.
As a programmer my favorite features are all nonstandard SQL stuff. For instance: arrays, array operators, and literal array notation.
No more whiny devs complaining about how they can't prepare foo IN ($1) so fuck prepared statements and security:
(This works way better from a program than from the psql console - only problem is that if you're doing arbitrary strings, the escaping challenge moves from apostrophes to commas, but messing up a comma doesn't let a malicious user drop a table)
Second most favorite is DISTINCT ON () which makes an awesome poor man's window function for a very specific case. You want to see every customer's most recent invoice?
Which is about half as much work to write as
It might even be half as much work for the database to process since there's no subquery, but I've never benchmarked it to see.
If I have been able to see further than others, it is because I bought a pair of binoculars.