Why PostgreSQL Instead of MySQL: Comparing Reliability and Speed in 2007
postgresql.org
postgresql.org
We've seen the pain that mysql rebuilds can cause, with companies being down for hours. I've seen this in companies I've worked at, and on public companies such as wikipedia.
We've had good luck with psql internally. The speed isn't as much of an issue as some people had predicted, once we eliminated row counts. We're doing most of our processing on the server side, rather than using stored procedures, so we may have a different use case than most.
Slony should be fine for replication for most people. There's also the heftier Mammoth replication, but that's for-pay.
We've talked with Command Prompt (commandprompt.com) a few times, and they've been great at supporting pgsql- They have top notch pepole on staff, who know the details of slony setup, or tweaking db files. I believe they employ two slony devs on staff.
The only real annoyance I've found with postgresql is capitalization. they're being faithful to the SQL standard, but they require that all column names are lower-case, unless you put each part in quotes.
select * from "Product"."Table", where "Field", etc.
It'd be great if there were a way to allow more permissive names throughout.
In all though, I'd highly recommend it.
-Colin
1. unquoted identifiers are folded to lower-case
2. double-quoted identifiers have their case preserved
This applies to all SQL queries, whether DDL or otherwise. Therefore, you can freely use mixed-case or upper-case identifiers, as long as you consistently either don't double-quote them, or always use the same case when you double-quote them.So it is perfectly feasible to use non-lower-case identifier names, you just need to be aware of the rules. In practice, most Postgres users just use lower case identifiers all the time, but that's mostly a matter of style I think.
(FWIW, Postgres' case-folding behavior is actually one of the few areas where it is not conformant with the SQL standard.)
When we tried using mixed case without quotes it always converted it to lowercase internally- It reminds me a bit of OS X's file system handling, in how it worked- It accepted Mixed case, it just didn't care ;)
I'll look more into it, thanks. -Colin
Consider many different things when choosing DB. Don't just consider raw performance.
MySQL, even with its problems, is good enough. Moreover it accounts for future growth with MySQL Cluster. While MySQL Cluster is not yet mature (though this may change soon), it's there, it's open, and it's why I chose MySQL for my startup, even though I'm a Postgres veteran.
The problem with Postgres (and most databases without clustering) is when you need more resources your only option is to buy a bigger box to replace your current one. Eventually you'll have to buy specialized and super pricey machines...
With clustering you can add x-number of cheap commodity boxes to the existing set to meet your current needs.
" We use postgresql + slony, and are fairly pleased. Most of the time reddit slows down, it's db related, but so far there has always been a way to work through it. That being said, I feel we have to spend far too much time worrying about the database, and that's probably the situation with any relational db.
Presently, we have a db master that replicated to one machine, and that second machine replicates to a couple others. Most of the reddit queries are handled by the master and the first slave. The other slaves are used for backups and slow queries (e.g. stats and recommendations).
Replication and query load-balancing helped out a lot with the database load, but it introduced a new host of problems. Replication lag, in particular. This occurs when a db write is committed to the master, but the next read hits a slave before the write has propagated. "
Is there an open source sharding solution for Postgres?
An Unorthodox Approach to Database Design : The Coming of the Shard [http://news.ycombinator.com/item?id=38407]
Partitioning vs. Federation vs. Sharding [http://www.lethargy.org/~jesus/archives/95-Partitioning-vs.-...]
Sharding is where you split the data across multiple servers, based on some identifying category like users, functional area, etc.
Hot spares?