4 ms·
So, the problem that I have with PostgreSQL isn't that it doesn't have every datastore under the sun, but rather the lack of automated distribution, and fault-t
by sargun 14y ago
So, the problem that I have with PostgreSQL isn't that it doesn't have every datastore under the sun, but rather the lack of automated distribution, and fault-tolerance.
This is not a hard thing to build though (continue reading please). I don't mean that it's an easy task, but that the semantics by which you may be able to build a fault-tolerant, distributed database on top of Postgresql are pretty straightforward.
1. Partitioning:
So, Postgresql has multiple notions of partitions. Ideally, this would be done at the database level in Postgres, but the idea is that instead of having your entire database fail at once, a transparent layer in which you SET PARTITION_KEY=X before querying would make this pretty straightforward. It would be nice if there was a semi-transparent proxy layer that this.
2. Replication / Fault-tolerance:
Postgresql today has built-in synchronous master/slave replication. It would be nice if someone built a automated failover system on top this. Alternatively, it would be really interesting if someone had multiple postgresql instances, and then build a WAL log per partition, and performed 3PC between N replicas, and you could have a fully peer-to-peer postgres. I imagine these transactions would either need to be PL/SQL, or fully written-out, staged serializable SQL queries.
3. Co-processors:
One of the biggest benefits a distributed, fault-tolerant Postgresql setup would give you is the ability to collocate your application with the data. Instead of having to do read, modify, write, you can write complex business logic that can run on whichever Postgres partition's master node, and the data doesn't have to go over the network.
I feel by introducing some basic wrappers around postgres to support partitioned, master-slave topologies, and perhaps a slightly different query interface to support this transparently, PostgreSQL could effectively replace most modern NoSQL databases.
- karavelov 14y agoThen you may be interested in PostgreSQL-XC: http://postgres-xc.sourceforge.net/ http://postgres-xc.sourceforge.net/ It is transparent, consistent, load-balancing and fault-tolerant cluster. Though some of the features of PostgreSQL are not supported yet, eg. triggers.
- bbromhead 14y agoHot Standby in postgres is pretty damn good for replication (like you said making failover occur properly takes a little bit of effort but its not too hard). Also pgpool2 helps a lot with partitioning and parallel queries. For a relational db, postgres does a pretty good job at distribution and fault tolerance.
- drdaeman 14y agoPostgres won't let you perform any sufficiently long (read-only) queries on slaves. For example, I was unable to run pg_dumpall on a slave to have DB snapshots. This is [well-explained](http://www.postgresql.org/message-id/201102272005.00234.jens@wilke.org http://www.postgresql.org/message-id/201102272005.00234.jens...), but still inconvenient. This way, slaves are only useful for failover and possibly offloading very short read-only transactions, which is pretty limited.
- joevandyk 14y agoThis used to be the case, but it's not anymore. You can run long-living queries, run pg_dump, etc on slaves.
- moe 14y agoDo you have a reference in which pg version this got fixed? We still pg_xlog_replay_pause() our slaves, it would be nice to drop that.
- bbromhead 14y agohttp://www.postgresql.org/docs/9.2/static/runtime-config-replication.html#GUC-MAX-STANDBY-ARCHIVE-DELAY http://www.postgresql.org/docs/9.2/static/runtime-config-rep...
- drdaeman 14y agoAh, that's really good news to hear. Seems it's time to upgrade. :) Could you, please, provide a reference to a documentation or a changelog? Maybe I'm looking for a wrong keywords, but I can't find any mentions of such changes by myself.
- atsaloli 14y agoPostgreSQL today has synchronous and asynchronous master/slave replication built in. The dev team is working on master/master replication (or peer-to-peer, as you called it).