4 ms·

"Want to add a column to your "orders" table and populate it without blocking concurrent writes for as long as it will take to rewrite a multi-million row table

"Want to add a column to your "orders" table and populate it without blocking concurrent writes for as long as it will take to rewrite a multi-million row table?" PostgreSQL ALTER TABLE ... ADD COLUMN is an O(1) operation, and requires no data rewrite (as long as you are OK with NULLs). You can even add an attribute to a composite type, and existing tables using that type will see the extra attribute. Again, O(1), no data rewrite.

rosser 12y ago
Yeah, it's not about adding the column; I know that's instantaneous. I'm talking about the UPDATE you'll have to issue to populate the column once it's been added — unless you're okay with it staying NULL (or doing some sort of "lazy loading" is viable in your specific scenario). If you try to do that for the entire table in one go, you're contending with concurrent writes, incurring a lot of IO at once, and as much as doubling the disk size of your table. This way, you're hitting a limited number of rows per iteration, significantly softening the IO impact (granted, at the expense of wall-clock time); the NOWAIT fails fast on rows that have write locks out against them when you try to grab them; and (if the column you're populating is indexed, obviating HOT updates) leaving a radically smaller number of dead tuples — particularly if you up-tune autovacuum on the table in question while doing this (though that can mitigate some of the reduction in disk IO).
- joshma 12y ago
  Could you elaborate on why there'd be a smaller number of dead tuples? Are you just taking into account the autovacuuming that can take place as you continue through the iterations?
  - rosser 12y ago
    Primarily, yes. Once autovacuum is marking dead tuple space as reusable (dead tuple space that was largely created by earlier iterations of the loop issuing your query), subsequent iterations can use that space for their new tuples, and the table should reach steady-state size, or at the very least, grow much more slowly. This is even more likely when you make autovacuum more aggressive on the table in question while you're doing this. Additionally, however, if you're bulk rewriting the table in one query, autovacuum has no (or very limited) opportunity to mark dead tuples as truly dead — especially if there's any lock contention going on, so other, older xids are waiting for your bulk update to complete — so in the worst case your table is half dead tuples, and twice the size it needs to be.
ddorian43 12y ago
Really? I had the impression that if you added a column to a composite-type, the rows that have that composite-type as a column will be rewriten (tested in 9.3 couple of months ago)?

rosser 12y ago

Yeah, it's not about adding the column; I know that's instantaneous. I'm talking about the UPDATE you'll have to issue to populate the column once it's been added — unless you're okay with it staying NULL (or doing some sort of "lazy loading" is viable in your specific scenario). If you try to do that for the entire table in one go, you're contending with concurrent writes, incurring a lot of IO at once, and as much as doubling the disk size of your table. This way, you're hitting a limited number of rows per iteration, significantly softening the IO impact (granted, at the expense of wall-clock time); the NOWAIT fails fast on rows that have write locks out against them when you try to grab them; and (if the column you're populating is indexed, obviating HOT updates) leaving a radically smaller number of dead tuples — particularly if you up-tune autovacuum on the table in question while doing this (though that can mitigate some of the reduction in disk IO).

joshma 12y ago
Could you elaborate on why there'd be a smaller number of dead tuples? Are you just taking into account the autovacuuming that can take place as you continue through the iterations?
- rosser 12y ago
  Primarily, yes. Once autovacuum is marking dead tuple space as reusable (dead tuple space that was largely created by earlier iterations of the loop issuing your query), subsequent iterations can use that space for their new tuples, and the table should reach steady-state size, or at the very least, grow much more slowly. This is even more likely when you make autovacuum more aggressive on the table in question while you're doing this. Additionally, however, if you're bulk rewriting the table in one query, autovacuum has no (or very limited) opportunity to mark dead tuples as truly dead — especially if there's any lock contention going on, so other, older xids are waiting for your bulk update to complete — so in the worst case your table is half dead tuples, and twice the size it needs to be.

ddorian43 12y ago

Really? I had the impression that if you added a column to a composite-type, the rows that have that composite-type as a column will be rewriten (tested in 9.3 couple of months ago)?