pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedrfc: DROP column and replication impact
2+ messages / 2 participants
[nested] [flat]
* rfc: DROP column and replication impact
@ 2017-08-03 16:06 gleeco <gleeco@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: gleeco @ 2017-08-03 16:06 UTC (permalink / raw)
To: pgsql-admin
hello - we're excited to be making a change to drop an insane wide column
that is pg_toast table > 10T. The question is: what kind of fallout/impact
to expect for auto-vacuum and replication after `DROP COLUMN xxx`.
This is a 9.3 cluster w/ multiple of read replicas; > 1k tx/sec; and 2
wal log/sec
thanks in advance!
-gleeco
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: rfc: DROP column and replication impact
@ 2017-08-03 22:04 Tom Lane <tgl@sss.pgh.pa.us>
parent: gleeco <gleeco@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2017-08-03 22:04 UTC (permalink / raw)
To: gleeco <gleeco@gmail.com>; +Cc: pgsql-admin
gleeco <gleeco@gmail.com> writes:
> hello - we're excited to be making a change to drop an insane wide column
> that is pg_toast table > 10T. The question is: what kind of fallout/impact
> to expect for auto-vacuum and replication after `DROP COLUMN xxx`.
I believe that exactly nothing will happen as a result of DROP COLUMN
per se. Rather, each entry in the toast table will be deleted when
the referencing row in the parent table is next updated or deleted.
So reclamation of the toast table space will be spread out over what
could be a really long interval. You could speed it up by issuing
dummy bulk updates against the parent table, or you could force the
issue by doing a VACUUM FULL, but it sounds like maybe you'd rather sit
tight and let it happen slowly.
[ thinks a bit... ] Or maybe not. The attraction of forcing the issue
with an immediate VACUUM FULL is that, for the price of rewriting the
parent table, you'd end up dropping the toast table without, I think,
incurring any update traffic against it first. If the toast table is
way bigger than the parent then this seems mighty tempting. You'd need
to be able to schedule some downtime because of VACUUM FULL's exclusive
lock, but it's likely worth trying to do that.
Unless the very-wide column is the *only* one in this table with any toast
entries, you'd likely eventually have to do a VACUUM FULL anyway to get
back the disk space eaten by the toast table --- Murphy's Law guarantees
that some of the surviving toast entries will be near the end, preventing
significant truncation of the toast table with anything less than VACUUM
FULL.
regards, tom lane
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2017-08-03 22:04 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-08-03 16:06 rfc: DROP column and replication impact gleeco <gleeco@gmail.com>
2017-08-03 22:04 ` Tom Lane <tgl@sss.pgh.pa.us>
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