UPDATE Using Space from Deleted Rows

Summary: In this article, we will explore how to use the space of deleted rows for HOT updates in Redrock Postgres.

Table of Contents

Redrock Postgres Internal Update Behavior

Databases based on Undo Logs achieve transaction rollback and Multi-Version Concurrency Control (MVCC) by recording old versions of data before modification. However, when dealing with changes in row length, there are some differences in the internal behavior of update operations.

Scenario 1: New Row Length Remains Unchanged or Becomes Shorter

This is the most ideal update scenario with the highest performance.

Internal Behavior:

• Complete In-Place Update: The row data is modified directly in its original position within the original data block.
• Undo Recording: Only the old values of the updated columns are recorded (incremental recording), rather than the entire row.
• Row Structure: The physical location of the row and its CTID remain unchanged.
• Data Block Space: Freed space will be utilized by other rows within the data block.
• Index Updates: The corresponding index entries only need to be updated if the updated column is an indexed column.

Example:

-- 将varchar(10)列从"Johnson"更新为"Smith"（长度变短）
UPDATE employees SET last_name = 'Smith' WHERE employee_id = 100;

-- 将integer列从10000更新为20000（长度不变）
UPDATE employees SET salary = 20000 WHERE employee_id = 100;

Scenario 2: New Row Length Becomes Longer

When the new row length becomes longer—for example, when a NULL column is assigned a non-null value, or the value of a varchar or text column becomes longer—the system will first attempt an in-place update. Row migration is triggered only when there is insufficient available space in the data block.

Sub-scenario 2.1: The Current Data Block Has Sufficient Remaining or Reclaimable Space

• Still Updates In-Place: Utilizes reserved or reclaimable space within the data block.
• Undo Recording: Similarly, only the old values of the updated columns are recorded.
• Row Structure: The physical location of the row and its CTID remain unchanged.
• Data Block Space: Consumes reserved space, or reclaims the space of deleted rows for reuse.

Sub-scenario 2.2: The Current Data Block Has Insufficient Remaining or Reclaimable Space

• Triggers Row Migration: The old row is deleted, and the new row is inserted into another data block that has sufficient space.
• Undo Recording: Records the deletion operation of the old row and the insertion operation of the new row.
• Row Structure: The physical location of the row and its CTID will change.
• Data Block Space: The space from the deleted old row will be utilized by other rows within the data block.

Table Data Update Scenarios: Use Cases

Below, we will create a table in a Redrock Postgres database, insert some data, and perform cleanup operations on the table data.

CREATE TABLE t_large (id integer, data text);
INSERT INTO t_large (id, data)
  SELECT i, repeat('Pg', 64)
    FROM generate_series(1, 1000000) AS s(i);

CREATE INDEX large_idx ON t_large (id);
ANALYZE t_large;

Modify the table’s storage parameters to disable autovacuum:

ALTER TABLE t_large SET (autovacuum_enabled = off);

The INSERT operation above inserts 1,000,000 records. Then, let’s delete one row of data from each table page:

DO $$
DECLARE
  i integer := 1;
  tuples_per_page integer;
BEGIN
  SELECT ceil(reltuples / relpages) INTO tuples_per_page
    FROM pg_class WHERE relname = 't_large';

  WHILE i < 1000000 LOOP
    DELETE FROM t_large WHERE id = i;
    COMMIT;
    i := i + tuples_per_page;
  END LOOP;
END $$;

We use the statistical information in the system catalog pg_class to calculate the number of tuples in each table page. Then, we loop through each page to perform the deletion operation. Next, using the same method, we loop through each page to perform update operations.

DO $$
DECLARE
  i integer := 2;
  tuples_per_page integer;
BEGIN
  SELECT ceil(reltuples / relpages) INTO tuples_per_page
    FROM pg_class WHERE relname = 't_large';

  WHILE i < 1000000 LOOP
    UPDATE t_large SET data = repeat('Pg', 70) WHERE id = i;
    COMMIT;
    i := i + tuples_per_page;
  END LOOP;
END $$;

Here, we intentionally change the data field of the table rows to a longer string value to observe whether the update operation can utilize the space of deleted rows to perform the update. Finally, we verify the HOT update metrics through the system view pg_stat_user_tables.

SELECT relname, n_tup_upd, n_tup_del, n_tup_hot_upd, n_tup_newpage_upd,
       vacuum_count, autovacuum_count
  FROM pg_stat_user_tables 
  WHERE relname = 't_large';
 relname | n_tup_upd | n_tup_del | n_tup_hot_upd | n_tup_newpage_upd | vacuum_count | autovacuum_count
---------+-----------+-----------+---------------+-------------------+--------------+------------------
 t_large |     18519 |     18519 |         18519 |                 0 |            0 |                0
(1 row)

As can be seen from the output results, the update operations above are all HOT updates, meaning the space of deleted rows was reclaimed for intra-page updates. In addition, the vacuum_count and autovacuum_count metrics in the output are both 0, indicating that the table has not undergone a VACUUM.

Test Conclusions

Previously, we used a “delete + insert” test model to verify that table bloat in Redrock Postgres is controllable and the space occupied by the table remains stable.

Redrock Postgres introduces undo logs, marks index records as deleted when modifying tuples, and achieves in-place updates. Today, we have further verified that it can use the space of deleted rows to perform HOT updates. Combined with the 64-bit transaction ID’s ability to eliminate the need for data freezing, we can conclude that in Redrock Postgres, the background autovacuum process only needs to periodically ANALYZE the tables, without needing to perform heavyweight VACUUM and FREEZE operations.