5 ms·

I never got to test this, but I always wanted to explore in postgres using table partitions to store soft deleted items in a different drive as a kind of archiv

I never got to test this, but I always wanted to explore in postgres using table partitions to store soft deleted items in a different drive as a kind of archived storage. I'm pretty sure it is possible, and it might even yield some performance improvements. That way you wouldn't have to worry about deleted items impacting performance too much.

gleenn 8mo ago
It's definitely an interesting approach but the problem is now you have to change all your queries and undeleting get more complicated. There are strong trade-offs with almost all the approaches I've heard of.
- snuxoll 8mo ago
  With partitioning? No you don't. It gets a bit messy if you also want to partition a table by other values (like tenant id or something), since then you probably need to get into using table inheritance instead of the easier declarative partitioning - but either technique just gives you a single effective table to query.
  - edmundsauto 8mo ago
    Pg moves the data between positions on update?
    - bandrami 8mo 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 8mo 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 8mo 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 8mo 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 8mo 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.
paulddraper 8mo ago
IDK if the different drive is necessary, but yes partitioning on a deleted field would work. Memory >>>>> Disk in importance.

gleenn 8mo ago

It's definitely an interesting approach but the problem is now you have to change all your queries and undeleting get more complicated. There are strong trade-offs with almost all the approaches I've heard of.

snuxoll 8mo ago
With partitioning? No you don't. It gets a bit messy if you also want to partition a table by other values (like tenant id or something), since then you probably need to get into using table inheritance instead of the easier declarative partitioning - but either technique just gives you a single effective table to query.
- edmundsauto 8mo ago
  Pg moves the data between positions on update?
  - bandrami 8mo 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 8mo 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 8mo 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 8mo 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 8mo 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.

paulddraper 8mo ago

IDK if the different drive is necessary, but yes partitioning on a deleted field would work. Memory >>>>> Disk in importance.