pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedWhy did my PostgreSQL tables double in size?
4+ messages / 3 participants
[nested] [flat]
* Why did my PostgreSQL tables double in size?
@ 2023-11-19 22:24 Abdullah Ergin <abdullaherginwork@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Abdullah Ergin @ 2023-11-19 22:24 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
Attachments:
[image/png] tables.png (37.6K, ../../CAJZxWEP2t4Fw+D=gwWMNboBZmCL3G58aZO+cyLn4jE-9n1qGEg@mail.gmail.com/3-tables.png)
download | view image
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Why did my PostgreSQL tables double in size?
@ 2023-11-20 03:22 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Abdullah Ergin <abdullaherginwork@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Ron Johnson @ 2023-11-20 03:22 UTC (permalink / raw)
To: Abdullah Ergin <abdullaherginwork@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
On Sun, Nov 19, 2023 at 5:24 PM Abdullah Ergin <abdullaherginwork@gmail.com>
wrote:
> 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?
>
Make sure not to comment out "autovacuum = on"?
Create a cron job that regularly runs psql -Xtc "SHOW AUTOVACUUM", and
sends an email if it's set to off?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Why did my PostgreSQL tables double in size?
@ 2023-11-20 08:22 Achilleas Mantzios - cloud <a.mantzios@cloud.gatewaynet.com>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Achilleas Mantzios - cloud @ 2023-11-20 08:22 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 11/20/23 05:22, Ron Johnson wrote:
> On Sun, Nov 19, 2023 at 5:24 PM Abdullah Ergin
> <abdullaherginwork@gmail.com> wrote:
>
> 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?
>
> Make sure not to comment out "autovacuum = on"?
>
> Create a cron job that regularly runs psql -Xtc "SHOW AUTOVACUUM", and
> sends an email if it's set to off?
And be sure to check no table has unexpectedly individual autovacuum
turned off :
select pg_class.oid::regclass,relkind, opts.* from pg_class JOIN LATERAL
pg_options_to_table(reloptions) opts ON (true) where relkind not in
('i','v','S') AND option_name ~* '.*autovacuum_enabled' AND
option_value='false'
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Why did my PostgreSQL tables double in size?
@ 2023-11-20 09:29 Abdullah Ergin <abdullaherginwork@gmail.com>
parent: Abdullah Ergin <abdullaherginwork@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Abdullah Ergin @ 2023-11-20 09:29 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
This problem seems to have started from the 10th month, this parameter
value was changed in the 9th month and was done for all clusters. I
thought at first the vacuum didn't work but then I see the auto vacuum was
on. My case was, that there was a problem with the auto vacuum but it seems
like that's okay too. So the problem is because of the application? If the
update-delete progress is coming a lot from application, because of that
dead rows are going to loop. And when a new insert operation occurs, the
number of dead rows for a single insert increases because it tries too many
times.
Abdullah Ergin <abdullaherginwork@gmail.com>, 20 Kas 2023 Pzt, 01:24
tarihinde şunu yazdı:
> 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
>
>
Attachments:
[image/png] tables.png (37.6K, ../../CAJZxWEPrpeHQU2ZJ6ZSv96UPO9gSt-mvCGTj6LJB3m9Rg8vxVA@mail.gmail.com/3-tables.png)
download | view image
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2023-11-20 09:29 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-11-19 22:24 Why did my PostgreSQL tables double in size? Abdullah Ergin <abdullaherginwork@gmail.com>
2023-11-20 03:22 ` Ron Johnson <ronljohnsonjr@gmail.com>
2023-11-20 08:22 ` Achilleas Mantzios - cloud <a.mantzios@cloud.gatewaynet.com>
2023-11-20 09:29 ` Abdullah Ergin <abdullaherginwork@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox