4 ms·

Pg moves the data between positions on update?

• bandrami 9mo ago
  If you are updating the parent table and the partition key is correctly defined, then an update that puts a row in a different partition is translated into a delete on the original child table and an insert on the new child table, since v11 IIRC. But this can lead to some weird results if you're using multiple inheritance so, well, don't.
  • tomnipotent 9mo ago
    I believe they were just pointing out that Postgres doesn't do in-place updates, so every update (with or without partitions) is a write followed by marking the previous tuple deleted so it can get vacuumed.
    • snuxoll 9mo ago
      That’s not at all what the child to me was saying in even a generous reading. But HOT updates are a thing, too.
      • tomnipotent 9mo ago
        What do you think they were saying? I don't see any other way to read it. HOT updates write to the same tuple page and can avoid updating indexes, but it's still a write followed by marking the old tuple for deletion.
        • snuxoll 9mo ago
          > Pg moves the data between positions on update? I assume they typo'd "partitions" as "positions", and thus the GP comment was the correct reply.