6 ms·

Transactional DDL changes is a huge one. Postgres allows me to modify the table schema in a transaction, and rollback changes as if it was a mere data transacti

Transactional DDL changes is a huge one. Postgres allows me to modify the table schema in a transaction, and rollback changes as if it was a mere data transaction, if something fails mid-migration. Makes maintaining production databases through upgrades and schema changes much less stressful.

stepbeek 5y ago
Transactional DDL changes cannot be understated - it’s the most outrageously useful database feature out there. I have to work with oracle for a client right now and I’m gobsmacked that they don’t offer it at the pricepoint.
WJW 5y ago
I've always wondered about this in the scope of REALLY big tables. Migrating a busy table with a few billion rows will take 10+ hours, doesn't the redo log fill up? The amount of work that needs to be undone could easily take another 10+ hours, is there enough storage space for that? Every busy production db I've ever seen has had big problems when dealing with 10-hour transactions, and I don't see how Postgres could deal with these kind of problems as they're fundamental in the data model.
- andruby 5y ago
  What kind of DDL migration are you thinking about that would take 10+ hours? Adding a column is instant, even if your table is multiple TB's large. Same for dropping and renaming columns.
  - funcDropShadow 5y ago
    Adding a column can be instant on multi TiB tables, but that depends on the details. If is a non-null column with a non-stable default value, like the current time, it will require a full rewrite of all rows of the table.
    - WJW 5y ago
      Indeed, i was talking about multi-TB tables. From the postgres v13 docs (https://www.postgresql.org/docs/current/sql-altertable.html https://www.postgresql.org/docs/current/sql-altertable.html): > Adding a column with a volatile DEFAULT or changing the type of an existing column will require the entire table and its indexes to be rewritten. As an exception, when changing the type of an existing column, if the USING clause does not change the column contents and the old type is either binary coercible to the new type or an unconstrained domain over the new type, a table rewrite is not needed; but any indexes on the affected columns must still be rebuilt. Table and/or index rebuilds may take a significant amount of time for a large table; and will temporarily require as much as double the disk space. Clearly there are workarounds where you make a new empty column that you then slowly backfill with the correct value and eventually transactionally rename the new column to the old one, but the magic "oh postgres does DDL in a transaction so it's magic and we never have to worry" is pretty much gone at that point.
    - bostonvaulter2 5y ago
      Yes, but generally that could be handled in a multi-stage process. First you would create the new column with no constraint, then periodically start updating the rows, once nearly all the rows are updated with the new default, in a transaction you update the rest of the rows, update the table with the constraint, and set the default. Not very much downtime is required with this approach (although of course it is more involved).
- jayknight 5y ago
  >doesn't the redo log fill up? It's not a redo/undo log. It's more like (simplifying things here) having multiple copies of things and different sessions/connections "see" different sets of data. That's why you need to vacuum the database periodically, to garbage collect old rows that are no long accessible to any sessions/transactions. See https://devcenter.heroku.com/articles/postgresql-concurrency https://devcenter.heroku.com/articles/postgresql-concurrency >is there enough storage space for that? You do need to make sure you aren't too close to filling your storage, postgres needs some breathing room for this reason.
WhyNotHugo 5y ago
Geez, I can't imagine running migrations without this. You'd just run your migration statements one after the other and pray it doesn't fail midway? Or have a contingency plan for every possible point of failure? Postgres has given me a sheltered life.

stepbeek 5y ago

Transactional DDL changes cannot be understated - it’s the most outrageously useful database feature out there. I have to work with oracle for a client right now and I’m gobsmacked that they don’t offer it at the pricepoint.

WJW 5y ago

I've always wondered about this in the scope of REALLY big tables. Migrating a busy table with a few billion rows will take 10+ hours, doesn't the redo log fill up? The amount of work that needs to be undone could easily take another 10+ hours, is there enough storage space for that? Every busy production db I've ever seen has had big problems when dealing with 10-hour transactions, and I don't see how Postgres could deal with these kind of problems as they're fundamental in the data model.

andruby 5y ago
What kind of DDL migration are you thinking about that would take 10+ hours? Adding a column is instant, even if your table is multiple TB's large. Same for dropping and renaming columns.
- funcDropShadow 5y ago
  Adding a column can be instant on multi TiB tables, but that depends on the details. If is a non-null column with a non-stable default value, like the current time, it will require a full rewrite of all rows of the table.
  - WJW 5y ago
    Indeed, i was talking about multi-TB tables. From the postgres v13 docs (https://www.postgresql.org/docs/current/sql-altertable.html https://www.postgresql.org/docs/current/sql-altertable.html): > Adding a column with a volatile DEFAULT or changing the type of an existing column will require the entire table and its indexes to be rewritten. As an exception, when changing the type of an existing column, if the USING clause does not change the column contents and the old type is either binary coercible to the new type or an unconstrained domain over the new type, a table rewrite is not needed; but any indexes on the affected columns must still be rebuilt. Table and/or index rebuilds may take a significant amount of time for a large table; and will temporarily require as much as double the disk space. Clearly there are workarounds where you make a new empty column that you then slowly backfill with the correct value and eventually transactionally rename the new column to the old one, but the magic "oh postgres does DDL in a transaction so it's magic and we never have to worry" is pretty much gone at that point.
  - bostonvaulter2 5y ago
    Yes, but generally that could be handled in a multi-stage process. First you would create the new column with no constraint, then periodically start updating the rows, once nearly all the rows are updated with the new default, in a transaction you update the rest of the rows, update the table with the constraint, and set the default. Not very much downtime is required with this approach (although of course it is more involved).
jayknight 5y ago
>doesn't the redo log fill up? It's not a redo/undo log. It's more like (simplifying things here) having multiple copies of things and different sessions/connections "see" different sets of data. That's why you need to vacuum the database periodically, to garbage collect old rows that are no long accessible to any sessions/transactions. See https://devcenter.heroku.com/articles/postgresql-concurrency https://devcenter.heroku.com/articles/postgresql-concurrency >is there enough storage space for that? You do need to make sure you aren't too close to filling your storage, postgres needs some breathing room for this reason.

WhyNotHugo 5y ago

Geez, I can't imagine running migrations without this. You'd just run your migration statements one after the other and pray it doesn't fail midway? Or have a contingency plan for every possible point of failure? Postgres has given me a sheltered life.