What postgresql.conf values in Postgres 9.6 would help being altered for Large Data Deletion/Migration

Question

I have a query which is to remove approx 418 million rows from a table into a separate table until we decide what to do with the older data.

Query below being used:

BEGIN;

CREATE TABLE public.gamearchived (LIKE public.game);

DROP INDEX public.idx_game_game_created_on;
DROP INDEX public.idx_game_session_id;
DROP INDEX public.game_idx_01;
DROP INDEX public.game_idx_game_id;

WITH del AS (
   DELETE FROM game
   WHERE  game_created_on < NOW() - interval '1 year' -- older than 1 year
   RETURNING *
   )
 , ins AS (
   INSERT INTO public.gamearchived
   SELECT * FROM del
   ORDER  BY game_id 
    )
SELECT count(*) FROM del; --426m  -- get your count 

rollback;

The Postgres server has 128GB RAM and has the following postgresql configuration options to name a few:

listen_addresses = '*'
max_connections = 1000
hot_standby = on
shared_buffers = 25GB
temp_buffers = 32MB
max_prepared_transactions = 1000
work_mem = 256MB
maintenance_work_mem = 1GB
effective_io_concurrency = 4
wal_level = logical
wal_buffers = 1MB
checkpoint_timeout = 5min
checkpoint_completion_target = 0.9
max_wal_senders = 6
max_wal_size = 4GB
min_wal_size = 256MB
wal_keep_segments = 1000
random_page_cost = 2.0
effective_cache_size = 60GB
wal_sender_timeout = 5min
wal_receiver_timeout = 5min
archive_mode = on
archive_command = '/home/postgres/bin/pgWalSender.bsh %p %f'
max_replication_slots = 6 

When initially running the query it would take approx 1 hour to run (with no concurrent access), it mentions about checkpoints are occurring too frequently consider increasing max_wal_size. I increased the max_wal_size to 20GB (from 4GB). I also increased maintenance_work_mem to 4GB but on re-running it made no difference to the time.

ALTER SYSTEM SET max_wal_size to '20GB';
alter system set maintenance_work_mem = '4GB'
SELECT pg_reload_conf();

I read about populating the database https://www.postgresql.org/docs/9.6/populate.html but cannot bring down the time much more.

The query drops the indexes in the table which does indeed make it quicker. Running an explain of the query does highlight some areas with the biggest being the CTE scan on the delete.

Aggregate  (cost=419106605.41..419106605.42 rows=1 width=8) (actual time=1785764.863..1785764.864 rows=1 loops=1)
  Buffers: shared hit=860596986 read=15614507 dirtied=7410237 written=19916, temp written=6202971
  CTE del
    ->  Delete on game  (cost=0.00..31560181.92 rows=427582719 width=6) (actual time=0.704..1566072.507 rows=430298493 loops=1)
          Buffers: shared hit=860596986 read=15614507 dirtied=7410237 written=19916
          ->  Seq Scan on game  (cost=0.00..31560181.92 rows=427582719 width=6) (actual time=0.689..639193.714 rows=430298493 loops=1)
                Filter: (game_created_on < (now() - '1 year'::interval))
                Rows Removed by Filter: 481148349
                Buffers: shared read=15614507 dirtied=7410237 written=19916
  CTE ins
    ->  Insert on gamearchived  (cost=372581028.33..377925812.31 rows=427582719 width=1133) (actual time=1499482.885..1499482.885 rows=0 loops=1)
          Buffers: shared hit=446210882 read=7409133 dirtied=7409132, temp read=12412360 written=6209388
          ->  Sort  (cost=372581028.33..373649985.12 rows=427582719 width=1133) (actual time=461931.126..847702.190 rows=430298493 loops=1)
                Sort Key: del_1.game_id
                Sort Method: external merge  Disk: 49668936kB
                Buffers: temp read=12412360 written=6209388
                ->  CTE Scan on del del_1  (cost=0.00..8551654.38 rows=427582719 width=1133) (actual time=0.572..257803.017 rows=430298493 loops=1)
                      Buffers: temp read=6202973 written=1
  ->  CTE Scan on del  (cost=0.00..8551654.38 rows=427582719 width=0) (actual time=0.707..1742679.647 rows=430298493 loops=1)
        Buffers: shared hit=860596986 read=15614507 dirtied=7410237 written=19916, temp written=6202971
Planning time: 2.235 ms
Execution time: 3290505.140 ms

Understand partitioning could be an option but not something at the moment we want to look at implementing, also understand the postgres version is EOL and in the very near future postgres will be upgraded.

Is there any other changes to either the code or the postgres configuration I can do, not sure where else to go from here?

Any help is much appreciated.

Answer 1

There is not much you can do to speed that up:

• You could set wal_level = minimal and archive_mode = off and restart PostgreSQL.

  Then PostgreSQL doesn't have to write WAL for gamearchived, which will speed up at least the INSERT. As the documentation says:

  In minimal level, no information is logged for permanent relations for the remainder of a transaction that creates or rewrites them. This can make operations much faster (see Section 14.4.7). Operations that initiate this optimization include:

  [...]

  CREATE TABLE
  

• You could create gamearchived as an UNLOGGED table. That will also avoid writing WAL for that table, but the table will be empty after crash recovery.