5 ms·

PostgreSQL: What happened to Hot Standby?

29 points by jawngee 18y ago

aditya 18y ago
Hmm. PgSQL is rock solid as an RDBMS, but the lack of dependable replication options is quite annoying. I'm curious to know if anyone here has actually used {londiste, pgpool2, slony} to do master/slave replication and made it work well?
- olefoo 18y ago
  I have set up and run both slony and mammoth-replicator in production. It is not a trivial task. The big problem I've encountered is that the database to be replicated must have defined primary keys. Far too few developers understand what is meant by referential integrity.
- mdasen 18y ago
  It is a great database, but replication is lacking. Slony-I is the best of the bunch currently, but it works by using triggers, requires you to set it up for every table and key, scales quadratically, and just generally has a lot of overhead (when compared to MySQL's replication). Mammoth Replicator is the most interesting project to me since it operates a lot more like MySQL's replication in that it works off log shipping. Currently they only have a beta for download off their site (https://projects.commandprompt.com/public/replicator https://projects.commandprompt.com/public/replicator), but I'm hopeful for it. Replication is a really hard problem to solve given all the little inconsistencies that can creep in, but it's one of those areas that's holding PostgreSQL back most. Hot failover and scalable read-only replicants are a big selling point for MySQL. I really hope that PostgreSQL nails this area in 2010 because it's query planner is much better at handling complex queries (really, even something as simple as a sub-query). But you're right, it it annoying that PostgreSQL doesn't have a nicer replication option. EDIT: If performance isn't your biggest concern and you don't mind a highly tedious setup, Slony-I is reliable. However, it's not suitable for larger clusters of machines since communication costs grow quadratically.
  - neilc 18y ago
    it's not suitable for larger clusters of machines since communication costs grow quadratically. Quadratically? Why?
    - mdasen 18y ago
      http://www.pgadmin.org/docs/1.4/slony/slonylistenercosts.html http://www.pgadmin.org/docs/1.4/slony/slonylistenercosts.htm...
- Andys 18y ago
  I've used pgpool2 in (lightly loaded) production. It is quite amazing and works as advertised. True multi-master for updates, and load balancing for reads. I don't know why it doesn't receive more attention. It is, however, very fiddly to set up and maintain, and you have to provide and roll your own "initial synchronization" which you can base on WAL archiving or pg_dump, or whatever. It has also been a work in progress, with its design and features changing over time as it matures. This has made it a bit hard to wrap in easy to use scripts.
  - mdasen 18y ago
    The issue is that pgpool is that it doesn't keep data consistent necessarily. For example, MySQL's default replication is statement based. That means that if you execute "UPDATE table SET var=57 WHERE id=10" on the master, it will run that same statement on the mirror. BUT, MySQL's replication is smart. If you say, "UPDATE table set updated_at=now()" on the master, it won't run that on the mirror. Rather, it will run "UPDATE table set updated_at=<the time that now evaluated to on the master>". pgpool also requires table locking when dealing with SERIALs and that is just unacceptable for performance. It just has limitations that don't make it a good replication tool for production environments. pgpool is an awesome tool, but not for replication. Use it for load balancing and connection pooling which it is wonderful for.
    - Andys 18y ago
      I believe pgpool also deals with timestamps properly. Secondly, no one is forcing you to use SERIAL datatypes - the table locking is there for backwards compatibility. SERIAL is not the best performance choice for replicated servers, and there's nothing to stop you using another style of ID field.
patrickg-zill 18y ago
For all the noise about replication, the reality is that buying a server with a) lots of RAM b) dual power supplies, each going to a separate UPS or power feed c) dual or quad CPUs will get you 95% of the way there. If you really care, get an external storage solution and buy two identical servers; then you can switch the storage over to the working machine if the first one fails. So many people I know have sites that do not require fancy-pants replication - if they just shelled out the $20K for a big 128GB RAM system and learned how to do decent backups they would save on hosting charges and have a site that is just as reliable.
- tobi 18y ago
  Maybe for some use cases but most people on this site are concerned about scaling web sites where scaling up (which is what you suggest) isn't feasible because of the obvious cap to the approach and the fact that you can't even add your own hardware to cloud/virtual hosting which is quickly becoming the norm. In general, every problem that can be solved in software should be solved in software. Postgres can be the second coming of jesus but it's still utterly infeasible for high traffic web applications because of the replication issue. Even the current hacks that add replication will not work because they cannot deal with schema changes without downtime. At Shopify we add an average of 12 columns and 2 tables to the database every month and we had a grand total of 58 minutes of downtime in 2008.
  - aditya 18y ago
    Tobi, that's good to know... I'm assuming you're running MySQL then?
    - tobi 18y ago
      We actually launched Shopify beta on Postgres. One of the last things we setup before launch (literally 2 days before) was replication. This was foolish by us but there was a lot on our plates back in the day. We spent the entire weekend setting up every different replication solution for postgres and in the end I just had to make a judgement call and went to mysql. Luckily we used rails which is pretty agnostic and we have a huge unit test coverage so we could quickly get it ported. We converted the data and launched on MySQL and have been very happy with it ever since. In fact this was a good decision for other reasons as well: the MySQL query cache saved us from having to seriously look at caching for a crucial 3-4 months which we could spend on more important things. On a personal note, i like Postgres much better if it weren't for those issues.
chiffonade 18y ago
> As expected, the NTT Open Source team working on Synch Standby ran into some unexpected issues Is that sort of like a known unknown? Or an unknown known?
- patio11 18y ago
  I think people who mock those phrases mercilessly probably never heard them used in context. They're government contractor-speak that is probably popular around the DoD, but they're not meaningless. Here, let me try. Known known: There is a bug open in the tracker which reports that there is a race condition under certain circumstances. You have someone assigned to look into it. Unknown known: One of the programmers noticed a race condition under certain circumstances, but he did not put it in the tracker, and as a result the information is compartmentalized and the issue is not being dealt with. Known unknown: In preparation for your release you've got a load balancer, four application servers, and memcached set up, but you're not sure whether it will be able to handle a Slashdot on release day. You resolve to test this, when the schedule permits. Unknown unknown: Your password recovery form permits a SQL injection attack and nobody in your organization has a clue. The manager take away point: it is far better to have known unknowns than unknown unknowns because you can take steps to mitigate the risk, and unknown knowns are almost useless to you as a manager because they prevent you from mitigating the risk even if you're aware of a solution at some level of the organization.
  - chiffonade 18y ago
    I think anyone who can figure out what an adjective put in front of a noun means understands what these phrases mean, the point is that they're just so fucking ridiculous as to be laughable.
discojesus 18y ago
"Hot Standby"? Sounds like a porno movie set in an airport terminal.
gfodor 18y ago
As frustrating as it is, PostgreSQL users have always benefited from the lack of defects in the software due to hard choices made like this.

29 points by jawngee 18y ago

Hmm. PgSQL is rock solid as an RDBMS, but the lack of dependable replication options is quite annoying. I'm curious to know if anyone here has actually used {londiste, pgpool2, slony} to do master/slave replication and made it work well?

olefoo 18y ago
I have set up and run both slony and mammoth-replicator in production. It is not a trivial task. The big problem I've encountered is that the database to be replicated must have defined primary keys. Far too few developers understand what is meant by referential integrity.
mdasen 18y ago
It is a great database, but replication is lacking. Slony-I is the best of the bunch currently, but it works by using triggers, requires you to set it up for every table and key, scales quadratically, and just generally has a lot of overhead (when compared to MySQL's replication). Mammoth Replicator is the most interesting project to me since it operates a lot more like MySQL's replication in that it works off log shipping. Currently they only have a beta for download off their site (https://projects.commandprompt.com/public/replicator https://projects.commandprompt.com/public/replicator), but I'm hopeful for it. Replication is a really hard problem to solve given all the little inconsistencies that can creep in, but it's one of those areas that's holding PostgreSQL back most. Hot failover and scalable read-only replicants are a big selling point for MySQL. I really hope that PostgreSQL nails this area in 2010 because it's query planner is much better at handling complex queries (really, even something as simple as a sub-query). But you're right, it it annoying that PostgreSQL doesn't have a nicer replication option. EDIT: If performance isn't your biggest concern and you don't mind a highly tedious setup, Slony-I is reliable. However, it's not suitable for larger clusters of machines since communication costs grow quadratically.
- neilc 18y ago
  it's not suitable for larger clusters of machines since communication costs grow quadratically. Quadratically? Why?
  - mdasen 18y ago
    http://www.pgadmin.org/docs/1.4/slony/slonylistenercosts.html http://www.pgadmin.org/docs/1.4/slony/slonylistenercosts.htm...
Andys 18y ago
I've used pgpool2 in (lightly loaded) production. It is quite amazing and works as advertised. True multi-master for updates, and load balancing for reads. I don't know why it doesn't receive more attention. It is, however, very fiddly to set up and maintain, and you have to provide and roll your own "initial synchronization" which you can base on WAL archiving or pg_dump, or whatever. It has also been a work in progress, with its design and features changing over time as it matures. This has made it a bit hard to wrap in easy to use scripts.
- mdasen 18y ago
  The issue is that pgpool is that it doesn't keep data consistent necessarily. For example, MySQL's default replication is statement based. That means that if you execute "UPDATE table SET var=57 WHERE id=10" on the master, it will run that same statement on the mirror. BUT, MySQL's replication is smart. If you say, "UPDATE table set updated_at=now()" on the master, it won't run that on the mirror. Rather, it will run "UPDATE table set updated_at=<the time that now evaluated to on the master>". pgpool also requires table locking when dealing with SERIALs and that is just unacceptable for performance. It just has limitations that don't make it a good replication tool for production environments. pgpool is an awesome tool, but not for replication. Use it for load balancing and connection pooling which it is wonderful for.
  - Andys 18y ago
    I believe pgpool also deals with timestamps properly. Secondly, no one is forcing you to use SERIAL datatypes - the table locking is there for backwards compatibility. SERIAL is not the best performance choice for replicated servers, and there's nothing to stop you using another style of ID field.

patrickg-zill 18y ago

For all the noise about replication, the reality is that buying a server with a) lots of RAM b) dual power supplies, each going to a separate UPS or power feed c) dual or quad CPUs will get you 95% of the way there. If you really care, get an external storage solution and buy two identical servers; then you can switch the storage over to the working machine if the first one fails. So many people I know have sites that do not require fancy-pants replication - if they just shelled out the $20K for a big 128GB RAM system and learned how to do decent backups they would save on hosting charges and have a site that is just as reliable.

tobi 18y ago
Maybe for some use cases but most people on this site are concerned about scaling web sites where scaling up (which is what you suggest) isn't feasible because of the obvious cap to the approach and the fact that you can't even add your own hardware to cloud/virtual hosting which is quickly becoming the norm. In general, every problem that can be solved in software should be solved in software. Postgres can be the second coming of jesus but it's still utterly infeasible for high traffic web applications because of the replication issue. Even the current hacks that add replication will not work because they cannot deal with schema changes without downtime. At Shopify we add an average of 12 columns and 2 tables to the database every month and we had a grand total of 58 minutes of downtime in 2008.
- aditya 18y ago
  Tobi, that's good to know... I'm assuming you're running MySQL then?
  - tobi 18y ago
    We actually launched Shopify beta on Postgres. One of the last things we setup before launch (literally 2 days before) was replication. This was foolish by us but there was a lot on our plates back in the day. We spent the entire weekend setting up every different replication solution for postgres and in the end I just had to make a judgement call and went to mysql. Luckily we used rails which is pretty agnostic and we have a huge unit test coverage so we could quickly get it ported. We converted the data and launched on MySQL and have been very happy with it ever since. In fact this was a good decision for other reasons as well: the MySQL query cache saved us from having to seriously look at caching for a crucial 3-4 months which we could spend on more important things. On a personal note, i like Postgres much better if it weren't for those issues.

chiffonade 18y ago

> As expected, the NTT Open Source team working on Synch Standby ran into some unexpected issues Is that sort of like a known unknown? Or an unknown known?

patio11 18y ago
I think people who mock those phrases mercilessly probably never heard them used in context. They're government contractor-speak that is probably popular around the DoD, but they're not meaningless. Here, let me try. Known known: There is a bug open in the tracker which reports that there is a race condition under certain circumstances. You have someone assigned to look into it. Unknown known: One of the programmers noticed a race condition under certain circumstances, but he did not put it in the tracker, and as a result the information is compartmentalized and the issue is not being dealt with. Known unknown: In preparation for your release you've got a load balancer, four application servers, and memcached set up, but you're not sure whether it will be able to handle a Slashdot on release day. You resolve to test this, when the schedule permits. Unknown unknown: Your password recovery form permits a SQL injection attack and nobody in your organization has a clue. The manager take away point: it is far better to have known unknowns than unknown unknowns because you can take steps to mitigate the risk, and unknown knowns are almost useless to you as a manager because they prevent you from mitigating the risk even if you're aware of a solution at some level of the organization.
- chiffonade 18y ago
  I think anyone who can figure out what an adjective put in front of a noun means understands what these phrases mean, the point is that they're just so fucking ridiculous as to be laughable.

discojesus 18y ago

"Hot Standby"? Sounds like a porno movie set in an airport terminal.

gfodor 18y ago

As frustrating as it is, PostgreSQL users have always benefited from the lack of defects in the software due to hard choices made like this.