5 ms·

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 dro

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).

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).