4 ms·

Please try to alter a schema of a live multibillion rows table before pretending that it works. Unless they changed how is implemented recently then such an ope

by rixed 10y ago

Please try to alter a schema of a live multibillion rows table before pretending that it works. Unless they changed how is implemented recently then such an operation can take hours. I love postgres for many reasons so i would like to be proven wrong.

unixhero 10y ago
Hours is okay, if it works.
- sgift 10y ago
  Now, if you could just tell this to the people who depend on the software and tell me that hours of down time is not a possibility.
  - MarHoff 10y ago
    Hours of work <> Hours of downtime The bigger the database the more complex it will be to implement schema change while keeping track of transactions that happen while your are transitioning. However I see no reason why it shouldn't be feasible given proper amount of preparation. And if short downtime is a goal I don't see why a proper amount of time developing a solution wouldn't make it either. And on top of that PostgreSQL community is implementing new way to solve problem with each release. I'm not in a huge live-data use case so I might be wrong, but upcoming logical replication look promising for such use cases https://www.depesz.com/2017/02/07/waiting-for-postgresql-10-logical-replication/ https://www.depesz.com/2017/02/07/waiting-for-postgresql-10-....
grzm 10y ago
Depends on the schema change. If you have a multibillion row table there are plenty of other things you need to pay close attention to as well, as you're probably all-too aware.
luhn 10y ago
Some schema changes can take prohibitively long on large tables, so you do have to be careful. But there are ways of achieving the same results that are safe for production database. Braintree did a decent writeup on it. https://www.braintreepayments.com/blog/safe-operations-for-high-volume-postgresql/ https://www.braintreepayments.com/blog/safe-operations-for-h...
jeltz 10y ago
I have never worked with a database of that size but I have done major refactoring of tables with about half a billion rows. And while it can take hours for the schema changes to finish it is hours without any downtime (or just a couple of seconds of downtime during when the exclusive locks need to be acquired). It is tedious to have to wait that long (especially if one has 10 times the rows than what we did), but PostgreSQL can do almost any change online just fine. What kind of schema changes do you think of that would require that much downtime? Even in complex cases that can be avoided with triggers or even rules.

by rixed 10y ago

unixhero 10y ago

Hours is okay, if it works.

sgift 10y ago
Now, if you could just tell this to the people who depend on the software and tell me that hours of down time is not a possibility.
- MarHoff 10y ago
  Hours of work <> Hours of downtime The bigger the database the more complex it will be to implement schema change while keeping track of transactions that happen while your are transitioning. However I see no reason why it shouldn't be feasible given proper amount of preparation. And if short downtime is a goal I don't see why a proper amount of time developing a solution wouldn't make it either. And on top of that PostgreSQL community is implementing new way to solve problem with each release. I'm not in a huge live-data use case so I might be wrong, but upcoming logical replication look promising for such use cases https://www.depesz.com/2017/02/07/waiting-for-postgresql-10-logical-replication/ https://www.depesz.com/2017/02/07/waiting-for-postgresql-10-....

grzm 10y ago

Depends on the schema change. If you have a multibillion row table there are plenty of other things you need to pay close attention to as well, as you're probably all-too aware.

luhn 10y ago

Some schema changes can take prohibitively long on large tables, so you do have to be careful. But there are ways of achieving the same results that are safe for production database. Braintree did a decent writeup on it. https://www.braintreepayments.com/blog/safe-operations-for-high-volume-postgresql/ https://www.braintreepayments.com/blog/safe-operations-for-h...

jeltz 10y ago

I have never worked with a database of that size but I have done major refactoring of tables with about half a billion rows. And while it can take hours for the schema changes to finish it is hours without any downtime (or just a couple of seconds of downtime during when the exclusive locks need to be acquired). It is tedious to have to wait that long (especially if one has 10 times the rows than what we did), but PostgreSQL can do almost any change online just fine. What kind of schema changes do you think of that would require that much downtime? Even in complex cases that can be avoided with triggers or even rules.