Why is a Move and Delete Data (approx 1/3 of the table) using (CTEs) WITH statements taking hours on a 600m row table taking 3+ hours - best approach?

Question

Am not sure if this is the best method of moving/deleting data. The play table has 700m+ rows and game table has over 600m+ rows.

I have 2 tables play and game, information from the play table is referenced from the game table.

I am trying to find all games that are greater than the finished on date and get a list of game_ids and then delete from the play table using those game_ids. The Game_id column is on both game and play table.

The plan is to firstly insert the deleted values from the play table into a playArchive table (same table structure), then delete the game_ids from game but also move the deleted rows from game into a gameArchive table.

Table structures of both game and play

public.play (
    play_id serial4 NOT NULL,
    game_id int4 NOT NULL,
    session_id int4 NULL,
    play_created_on timestamptz NOT NULL

    CONSTRAINT play_pkey PRIMARY KEY (play_id),
    CONSTRAINT ref_play_to_game FOREIGN KEY (game_id) REFERENCES public.game(game_id),
);


Indexes on play:
"play_pkey" PRIMARY KEY, btree (play_id)
"play_idx_game_id" btree (game_id)
"idx_play_play_created_on" btree (play_created_on)
"play_idx_game_id" btree (game_id)

public.game (
    game_id serial4 NOT NULL,
    customer_id int4 NOT NULL,
    game_started_on timestamptz NOT NULL,
    game_finished_on timestamptz NULL,
    
    CONSTRAINT game_pkey PRIMARY KEY (game_id),
    CONSTRAINT ref_game_to_customer FOREIGN KEY (customer_id) 

Referenced by:
    TABLE "play" CONSTRAINT "ref_play_to_game" FOREIGN KEY (game_id) REFERENCES game(game_id)
);

Indexes on game:
"game_pkey" PRIMARY KEY, btree (game_id)
"game_idx_01" btree (customer_id)
"idx_game_game_started_on" btree (game_started_on)
"idx_game_finished_on" btree (game_finished_on)

Am doing this by using WITH statements see below:

begin;

create table IF NOT EXISTS public.playArchive (like public.play);
explain analyze
WITH delplay AS (
delete FROM public.play p
--where p.game_id in (select g.game_id from game g where g.game_finished_on < '2020-12-31 23:59:59'  limit 100)
--WHERE p.game_id IN (SELECT g.game_id id FROM game g where g.game_finished_on < '2020-12-31 23:59:59' ORDER BY g.game_finished_on LIMIT 10)
where exists (SELECT g.game_id id FROM game g where p.game_id = g.game_id and g.game_finished_on < '2020-12-31 23:59:59' ORDER BY g.game_finished_on)
--order by p.game_id 
--limit 100

--WHERE p.game_id = g.game_id
--and g.game_finished_on < '2020-12-31 23:59:59' 
RETURNING
p.play_id,
p.game_id,
p.session_id,
p.play_created_on,
) 
, insPlay AS (
INSERT INTO public.playArchive
SELECT * FROM delplay --order by game_id

)

SELECT count(*) FROM delplay;
commit;

The -- are other alternatives I have tried within the CTE's

Once data is removed from Play I can then remove from Game (due to the constraints)

Second Query:

begin;
create table public.gameArchive (like public.game);

with delGame as (
    delete from public.game gme
    where gme.game_finished_on < '2020-12-31 23:59:59'
    returning 
gme.game_id,
gme.customer_id,
gme.game_started_on,
gme.game_finished_on
)
    , insGame as (
    insert into public.gameArchive
    select * from delGame
    )
    
SELECT count(*) FROM delGame --order by play_created_on desc;
    
commit;

Am trying to look for the best way of removing the game data and inserting into a gameArchive table but also remove the play table data using the game_ids from game table and also storing the removed play data into a playArchive table.

If I run my current queries it would take hours to complete. The play table itself is approx 700m rows and I would be removing approx a 3rd of it into another table.

Is there a better approach? I have tried CTE's, Temp tables but all give very slow times.

The archived data must be accessible so would have to have it in a separate table, the only other alternative I can think of is to create a duplicate table(s) of what exists and do the removal of newer data. For example create the archived tables with all the current data in and then remove the newer data and then the older data is retained.

Have also looked at a bash script for batch deleting data in chunks, see below:

#!/bin/bash
DATABASE=testDb
USERNAME=test
HOSTNAME=data.dev.company.com
export PGPASSWORD=XXXXXX
while true; do

  res=$(psql -h $HOSTNAME -U $USERNAME -d $DATABASE -c "
WITH rows AS (
  SELECT
 game_id
  FROM
    play where game_id in (select game_id from game where game_finished_on < '2016-12-31'::date order by game_id) 
  LIMIT 1000
)
DELETE FROM play
WHERE game_id IN (SELECT game_id FROM rows);


" \
    | grep DELETE | awk '{print $2}' )
  if [[ $res = '0' ]]; then break; fi;
  sleep 1; 
done

Not sure the best approach trying numerous alternatives all take in excess of 3+ hours.

Any help is much appreciated.

UPDATE: explain from the first query alone takes 3+ hours.

                                                                                    QUERY PLAN                                                                                     
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=85827720.09..85827720.10 rows=1 width=8) (actual time=12813114.116..12813114.136 rows=1 loops=1)
   Buffers: shared hit=873215825 read=317100961 dirtied=295396748 written=16373773, temp read=6049056 written=12962209
   CTE delplay
     ->  Delete on play p  (cost=12076249.26..66149217.05 rows=463023601 width=12) (actual time=203017.329..12118558.384 rows=479712879 loops=1)
           Buffers: shared hit=873215825 read=317100961 dirtied=295396748 written=16373773, temp read=6049056 written=6049056
           ->  Hash Join  (cost=12076249.26..66149217.05 rows=463023601 width=12) (actual time=203017.198..1672227.670 rows=479712879 loops=1)
                 Hash Cond: (p.game_id = g.game_id)
                 Buffers: shared hit=204388696 read=26502332 dirtied=429993 written=1003300, temp read=6049056 written=6049056
                 ->  Seq Scan on play p  (cost=0.00..36394500.44 rows=1333439744 width=10) (actual time=0.251..520248.843 rows=1333427979 loops=1)
                       Buffers: shared hit=11 read=23060092 dirtied=429993 written=846734
                 ->  Hash  (cost=7959773.81..7959773.81 rows=236812916 width=10) (actual time=202969.632..202969.634 rows=239705711 loops=1)
                       Buckets: 8388608  Batches: 64  Memory Usage: 226518kB
                       Buffers: shared hit=204388682 read=3442240 written=156566, temp written=921684
                       ->  Index Scan using idx_game_finished_on on game g  (cost=0.57..7959773.81 rows=236812916 width=10) (actual time=0.072..156692.273 rows=239705711 loops=1)
                             Index Cond: (game_finished_on < '2020-12-31 23:59:59+00'::timestamp with time zone)
                             Buffers: shared hit=204388682 read=3442240 written=156566
   CTE insplay
     ->  Insert on playarchive  (cost=0.00..9260472.02 rows=463023601 width=1149) (actual time=996425.401..996425.401 rows=0 loops=1)
           Buffers: shared hit=497412893 read=2029 dirtied=8251627 written=8249599, temp read=6913154 written=1
           ->  CTE Scan on delplay  (cost=0.00..9260472.02 rows=463023601 width=1149) (actual time=0.537..216003.520 rows=479712879 loops=1)
                 Buffers: temp read=6913154 written=1
   ->  CTE Scan on delplay rollback  (cost=0.00..9260472.02 rows=463023601 width=0) (actual time=203017.339..12753461.808 rows=479712879 loops=1)
         Buffers: shared hit=873215825 read=317100961 dirtied=295396748 written=16373773, temp read=6049056 written=12962209
 Planning Time: 5.891 ms
 Execution Time: 13812651.450 ms
(25 rows)

Updated: Applied an index on the play table: create index game_id_game_finished_on_idx on game (game_id, game_finished_on

Explain results after took in excess of 4 hours:

                                                                                    QUERY PLAN                                                                                     
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=86271708.76..86271708.77 rows=1 width=8) (actual time=13663636.033..13663636.321 rows=1 loops=1)
   Buffers: shared hit=872942765 read=317374021 dirtied=298868075 written=19880752, temp read=6049056 written=12962209
   CTE delplay
     ->  Delete on play p  (cost=12233733.37..66322063.16 rows=469403426 width=12) (actual time=178884.058..12977477.519 rows=479712879 loops=1)
           Buffers: shared hit=872942765 read=317374021 dirtied=298868075 written=19880752, temp read=6049056 written=6049056
           ->  Hash Join  (cost=12233733.37..66322063.16 rows=469403426 width=12) (actual time=178883.924..1752734.480 rows=479712879 loops=1)
                 Hash Cond: (p.game_id = g.game_id)
                 Buffers: shared hit=204115636 read=26775392 dirtied=8269446 written=5100921, temp read=6049056 written=6049056
                 ->  Seq Scan on play p  (cost=0.00..36394500.44 rows=1333439744 width=10) (actual time=0.706..623344.354 rows=1333427979 loops=1)
                       Buffers: shared read=23060103 dirtied=8269446 written=5100921
                 ->  Hash  (cost=8062566.93..8062566.93 rows=239959235 width=10) (actual time=178838.069..178838.072 rows=239705711 loops=1)
                       Buckets: 8388608  Batches: 64  Memory Usage: 226518kB
                       Buffers: shared hit=204115633 read=3715289, temp written=921684
                       ->  Index Scan using idx_game_finished_on on game g  (cost=0.57..8062566.93 rows=239959235 width=10) (actual time=0.793..134050.142 rows=239705711 loops=1)
                             Index Cond: (game_finished_on < '2020-12-31 23:59:59+00'::timestamp with time zone)
                             Buffers: shared hit=204115633 read=3715289
   CTE insplay
     ->  Insert on playarchive  (cost=0.00..9388068.52 rows=469403426 width=1149) (actual time=897808.433..897808.433 rows=0 loops=1)
           Buffers: shared hit=497412893 read=2029 dirtied=8251627 written=8249599, temp read=6913154 written=1
           ->  CTE Scan on delplay  (cost=0.00..9388068.52 rows=469403426 width=1149) (actual time=0.406..176368.753 rows=479712879 loops=1)
                 Buffers: temp read=6913154 written=1
   ->  CTE Scan on delplay rollback  (cost=0.00..9388068.52 rows=469403426 width=0) (actual time=178884.067..13602854.675 rows=479712879 loops=1)
         Buffers: shared hit=872942765 read=317374021 dirtied=298868075 written=19880752, temp read=6049056 written=12962209
 Planning Time: 16.193 ms
 Execution Time: 14564776.179 ms
(25 rows)

Answer 1

Notice that the vast majority of the time is exclusively in the Delete.

The data to be tested to be deleted is divided up into 64 batches, and batch membership is essentially random (based on the first 6 bits of the hash code):

Buckets: 8388608  Batches: 64  Memory Usage: 226518kB

Then the data that does need to be deleted is passed from the Hash Join to the Delete in the sequence of those batches. This means the Delete has to make 64 passes over the table to delete the rows. (Ideally within each batch, they are in physical order of the rows in the table, so each pass at least is sequential, but in practice it seems even worse than 64 full table scans but I don't understand why).

I think PostgreSQL should be able to inject a Sort node (ordering by ctid) between the Hash Join and the Delete to fix this problem, but no one has written the code to do that. I can't even figure out how to do this in some crude way as a proof of concept that it works, and without a POC it is hard to motivate people to put in the time to implement it in high quality code that could be adopted.

If you increase the work_mem by a lot, you can reduce the number of batches needed, and so improve the problem. Obviously you need enough RAM to support the higher work_mem, or you will run out of memory or swap to death. This isn't quite as bad as it seems as work_mem can be adjusted per session. In situations like this and if the database is the only demanding service on the server, I would set work_mem in that one session to the bulk of RAM, say, 80% of (RAM minus shared_buffers), but only if I a willing to closely monitor the situation with top while it runs, and preferably also test it ahead of time on a non-production clone.

An alternative is just to do this in chunks, by lowering your timestamp cutoff and then raising it by a few months (or whatever) at a time until you reach the desired cutoff. This might be slower overall (on the other hand, it might not be, if it trades slow random IO for higher amount of faster sequential IO), but if you can do it while still in production, does that matter?

You also show a bash script that does--something. It doesn't seem to be doing the same thing as your one-off queries do just in smaller chunks, as it doesn't have the insert component. Assuming it does the right thing in the first place, what is wrong with it? Is there a problem or do you just want something more elegant?