Shane Borden: “Row Movement” in PostgreSQL… Is it bad?

In Oracle, right or wrong, I was always taught to try to avoid “row movement” between partitions due to the general thought that the extra workload of a “delete” + “insert” (rewrite of the row) should be avoided due to the extra I/O, index fragmentation and the associated risks of a migrating ROWID in the cases where the app developers might have used it in their code (now that’s a whole other problem). Oracle didn’t even let you do it by default. Table by table, you had to explicitly set: alter table [table name] enable row movement; Now, you also had to set this to do table reorganizations such as “alter table…. shrink space / shrink space compact” so it wasn’t something unheard of. However, when a customer recently explained to me that they were going to partition a PostgreSQL table and update the partition key column from null to the date when the row got processed, my mind immediately went to the space of that’s probably bad……. RIGHT?? Well, once I thought about it, maybe it’s not all that bad due to the way MVCC and the subsequent VACUUM operations occur in PostgreSQL. The only thing I could think of that might be a factor is that you would lose any potential benefit of HOT (Heap-Only-Tuple) updates since the row will no longer be part of the original partition, seeing that partitions in PostgreSQL are just another table. The benefit though is that I could limit my vacuum operations to one single partition and SMALLER table. A plus for this customer. **** Note: An implementation like this does not necessarily follow best practices with regards to partitioning. That being said, I was attempting to validate the idea with regards to how PostgreSQL MVCC behaves. That being said, I wanted to at least be able to prove / disprove my thoughts with a demonstration, so off to PostgreSQL we go. First let’s create a simple partitioned table and use pg_partman to help: CREATE TABLE partman_test.partman_partitioned ( id integer not null, val varchar(20) not null, [...]