I need to partition a table with ~400M records. It had several pitfalls and setbacks due to DB resources, WAL sizes etc. After different approaches the solutions seems to be this:
- create child tables for every month in the scope, representing the future partitions
- insert related data from parent to child, and delete from parent in the same transaction
- stop inheritance, attach former child tables as partitions to a new table partitioned by default
In such a way there is almost zero downtime, since querying the original (parent) table will return data even if it is already in a child. The insert/delete part is done via a script which loops through the table in batches. All of this worked locally (with much less data), but there are problems with the actual table.
Somehow in some batches there are much less rows deleted, even though the common table expression I'm using to collect the batch is the same for both commands. Due to this, and the fact that I ran the script multiple times for reasons, there are duplicated rows in the child (unfortunately there are no constraints in it, so this could happen).
Now I need to deduplicate it and then delete the already migrated rows from the parent. Even after that, I will have to fix the deleting issue, which is my actual question: What could be the problem? Are these two commands interfering with each other somehow? Is it a problem that
First I thought only the logs are deceptive, but seeing all the duplicated data shows that there were indeed less rows deleted.
Thanks in advance!
The script: (a bit simplified, the actual script loops through the months as well, but the essentials are included)
DO $$
DECLARE
batch_size INTEGER := 10000;
off_set INTEGER := 0;
rows_inserted INTEGER;
rows_deleted INTEGER;
BEGIN
SELECT MIN(id) INTO off_set FROM ONLY parent_table WHERE inserttime >= ('2023-01-01') AND inserttime < ('2023-02-01');
LOOP
WITH parent_table_cte AS (
SELECT * FROM parent_table
WHERE inserttime >= ('2023-01-01')
AND inserttime < ('2023-02-12')
AND id >= off_set
ORDER BY id ASC
LIMIT batch_size
)
INSERT INTO child_table SELECT * FROM parent_table_cte;
GET DIAGNOSTICS rows_inserted = row_count;
RAISE info 'Inserted [%] rows into [child_table]!', rows_inserted;
WITH parent_table_cte AS (
SELECT * FROM pod.parent_table
WHERE inserttime >= ('2023-01-01')
AND inserttime < ('2023-02-12')
AND id >= off_set
ORDER BY id ASC
LIMIT batch_size
)
DELETE FROM ONLY pod.parent_table USING parent_table_cte
WHERE parent_table.id = parent_table_cte.id;
GET DIAGNOSTICS rows_deleted = row_count;
RAISE info 'Deleted [%] rows from [parent_table] in the range of (2023-01-01) - (2023-02-01)!', rows_deleted;
IF rows_inserted = 0 THEN
RAISE info e'No more rows to insert from range (2023-01-01) - (2023-02-01) into table: [child_table]\n';
EXIT;
END IF;
RAISE info e'[%] rows were inserted and [%] were deleted in the batch ending at offset [%]\n', rows_inserted, rows_deleted, off_set + rows_inserted;
COMMIT;
off_set := off_set + batch_size;
-- PERFORM pg_sleep(5);
END LOOP;
END;
$$;
UPDATE
Now as I ran through my question I noticed that the CTEs aren't selecting from only the parent, which means the child is also in the query. This could be the problem, I'll check it ASAP.
