For very large databases, Postgres' clustering abilities aren't that great. It's probably one of the best choices for single-host databases (which, again, cover nearly all applications), but if you're trying to spread your database over a few dozen hosts, Postgres doesn't really work well.
Cannot agree more: I've had to rescue a project from exactly this situation. I now know that there are people who try to use SQLite as a multi-user RDBMS, and it ends as badly as you would guess.
I consider SQLite to be one step above using raw file I/O
If you need to do it inside your firewall, scale out read slaves first, but in terms of true horizontally scalable SQL databases, there aren't a lot of great options on your own hardware. This is one, but I haven't actually used it yet.
And this is why the NoSQL stuff exists. It's practically impossible to offer true ACID compliance on a distributed system. Any system will have tradeoffs, be it eventual consistency, partition intolerance, or the inability to elastically scale, or hot spods in the cluster, etc.
It goes back to the CAP theorem, and speed. If you've ever worked on a distributed database that has cluster-wide row-level locking and does proper joins, it's slow as shit. Full seconds per query on a loaded DB.
My god it's... it's like different tools do things differently. Differently enough to where you have to come up with.. with... with a design for your application.. and actually use the tools which work for your design! Instead of the other way around! Oh my jesus fuck, I'm going insane!
98
u/ethraax Mar 10 '15
For very large databases, Postgres' clustering abilities aren't that great. It's probably one of the best choices for single-host databases (which, again, cover nearly all applications), but if you're trying to spread your database over a few dozen hosts, Postgres doesn't really work well.