Why did my PostgreSQL tables double in size?

From: Abdullah Ergin <abdullaherginwork(at)gmail(dot)com>
To: pgsql-admin(at)lists(dot)postgresql(dot)org
Subject: Why did my PostgreSQL tables double in size?
Date: 2023-11-19 22:24:03
Message-ID: CAJZxWEP2t4Fw+D=gwWMNboBZmCL3G58aZO+cyLn4jE-9n1qGEg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

I have a table in a partitioned structure, and the size of the table for
the 10th month has doubled compared to previous months. On October 7, 2023,
a failover was performed in this database, and the replica server became
the master server. Before October 7th, this database was a replica, and
after the failover, the auto vacuum parameter in the postgresql.conf file
of the database remained commented out. I noticed this on November 11,
2023, and enabled the parameter. During this period, I believe the
unchecked increase in table size was due to the auto vacuum parameter being
turned off. What should I do to prevent this from affecting the 12th month
and beyond?
[image: tables.png]
postgresql.conf;

max_connections = 4096
- Memory -
max_prepared_transactions = 4096
work_mem = 2MB
maintenance_work_mem = 3GB
- Asynchronous Behavior -
effective_io_concurrency = 200
max_parallel_workers = 2
- Checkpoints -
min_wal_size = 2GB
max_wal_size = 3GB
checkpoint_completion_target = 0.9
- Planner Cost Constants
effective_cache_size = 48GB
- Sending Servers -
max_standby_archive_delay = 300s
max_standby_streaming_delay = 300s
- Planner Method Configuration -
random_page_cost = 1.1
- Query and Index Statistics Collector -
track_activity_query_size = 8192

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message 舒兰) <sweety.soul7@gmail.com> 2023-11-20 02:18:55 Re: GSSAPI authentication on Redhat8 and PostgreSQL15/16
Previous Message Gurjeet Singh 2023-11-17 21:16:32 Re: bulk DMLs