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:


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 (
   WHERE  game_created_on < NOW() - interval '1 year' -- older than 1 year
 , ins AS (
   INSERT INTO public.gamearchived
   SELECT * FROM del
   ORDER  BY game_id 
SELECT count(*) FROM del; --426m  -- get your count 


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.

  • Wouldn't it be much faster creating two new tables? Commented Nov 11, 2021 at 16:58
  • I need to keep the old data somewhere else for now so made sense to just store in a separate table. What do you mean by creating 2 new tables?
    – rdbmsNoob
    Commented Nov 11, 2021 at 18:52
  • Deleting is quite costly, doesn't save room. It may be more efficient to create new tables for the old and the current data. Commented Nov 11, 2021 at 19:21
  • 1
    I know how it goes with documentation, but thanks to the rules of the RDBMS, you have the data dictionary where all that information is available. Once the new table has been renamed to the old name, no external program will notice the difference. Commented Nov 12, 2021 at 10:15
  • 1
    Not easier, but normally a lot faster. Commented Nov 12, 2021 at 13:09

1 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:


  • 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.

  • would that then mean I would have to setup my slave again? Also when you say crash recovery is that in the event of an issue during this specific transaction?
    – rdbmsNoob
    Commented Nov 11, 2021 at 18:54
  • If you need replication, none of these ideas is feasible. But your question didn't state that, did it? Commented Nov 12, 2021 at 5:01
  • Sorry no it did not, we have a master and slave setup (I am testing against on a non master/slave setup at the moment). I could just setup the slave again after completing the task and that would work I suppose.
    – rdbmsNoob
    Commented Nov 12, 2021 at 8:51
  • UNLOGGED tables won't be replicated. I guess there is nothing much you can do except raise max_wal_size. Commented Nov 12, 2021 at 8:53
  • Thanks Laurenz, I did raise max_wal_size to 20GB from 4GB originally, as much as the messages didnt appear in the log the time was almost the same in terms of duration. I may look into selecting only the most recent data into a new table and then rename the old table. This may be faster but will have to test. If i set the wal_level and the archive_mode being minimal and off, I could see how long that takes and then revert the changes once done?
    – rdbmsNoob
    Commented Nov 12, 2021 at 9:08

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.