0

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)
10
  • Which of the queries you show take hours? Show the EXPLAIN plans. If you ever wait long enough for them to finish, show the EXPLAIN (ANALYZE, BUFFERS).
    – jjanes
    Jul 26, 2022 at 17:26
  • Do the tables need to be modifiable by other processes/users during this operation, or can they be assumed to be read-only for the duration perhaps with a short cutover time? Are parallel inserts into a heap possible? Can primary and foreign keys be created in parallel, or serial only? a possible fast option is to avoid deletes altogether. Create 2 new heaps per original table: one for “keep”, one for “archive”. insert rows as appropriate into each heap. create PKs & FKs. rename tables. drop original 2 tables.
    – sqL_handLe
    Jul 27, 2022 at 3:21
  • @jjanes have updated the post with the explain, takes over 3.5 hours to run.....
    – rdbmsNoob
    Jul 27, 2022 at 13:04
  • @sqL_handLe I was initially going to have an outage for this as its more of a yearly thing that would be done. I have thought about creating a new table and inserting only the newest data and doing a rename on the tables but it would still take hours. In terms of creating heaps do you mean make 2 new tables with the exact same data and do the removals on those?
    – rdbmsNoob
    Jul 27, 2022 at 13:06
  • Which indexes do you have on the tables? The explain shows an index: ` idx_game_finished_on` that's not included in the ddl. How is this index defined, and are there other indexes? For the first query index: game( game_id, game_finished_on) may help (if it's not existing already). For the second query and index game(game_finished_on) may help Jul 27, 2022 at 13:22

1 Answer 1

0

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?

3
  • its something admittedly I have not considered. I will try increasing work_mem. The initial plan was to do this during an outage so no other connections will be affecting usage which means I could increase work_mem. My shared buffers is set at 1/4 of the server resource as its a dedicated PG 12 server. Will look at your view on doing it in years/months but this again would require multiple outages but ideally wanted it done once a year for example. With regards to the bash it was just a thought of doing during live system running. Is there a more efficient way? Thanks
    – rdbmsNoob
    Jul 28, 2022 at 8:28
  • Why does it need an outage? There is nothing there that inherently needs an outage as I understand it. Other things might be substantially slower while it is consuming resources of course. If other things lock the rows being moved, that would be a problem. But presumably no one is locking ancient rows anymore--otherwise you probably should not be trying to archive them in the first place.
    – jjanes
    Jul 28, 2022 at 14:52
  • off the back of your comments thanks for the heads up on setting the work_mem to a high value. Setting it to 20GB brings it down to 1.5 hours for the first chunk which is still a huge difference. Again the concern here is if I do it on live can set a particular session but as the system is heavily used it will likely be an outage task purely due to the amount of queries hitting the systems/tables. Thanks again! think just testing different methods will help me massively. Also as well is it better to have a where clause on a date or id?
    – rdbmsNoob
    Jul 28, 2022 at 18:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.