On Wed, Mar 14, 2012 at 9:57 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> Are you sure you're checking the toast table that goes with whatever
> parent table?
Yep. I find out the relation id of the TOAST table:
"select reltoastrelid from pg_class where relname = 'parent_table_name';"
Find out the relation name of the TOAST table:
"select X::regclass;" (where X is what I got from step 1 above)
> Easy way to tell. du -s /var/lib/data/base dir, then update a few
> thousand rows, roll it back, and run du -s again. Compare. If the du
> numbers stay the same then you're updating pre-allocated space and
> should be ok.
I don't think I can run this test while the system is in production...
we do 250-300 database transactions per second under low load... high
load takes us above 1500 tps. my numbers for "du -sh data/base" would
be affected by the production workload.
Let me ask you this: I've been looking at "select ctid from
big_table" on the master and slave and I notice that pages can have
"holes" in them. Some pages have rows that go sequentially from 0 to
26 or 27 or so, and some pages have rows that go:
(431665,2)
(431665,5)
(431665,8)
(431665,11)
(431665,14)
(431665,17)
(431665,20)
(431665,23)
That's the last page on the slave. It has only 8 rows in it. It's
composed mostly of holes. That aligns with my hypothesis that pages
on the slave have less data in them than pages on the master. (Which
would explain why the slave has 4x the number of pages compared to the
master.)
Is there any way to consolidate the pages on the slave without taking
replication offline?
Best,
Aleksey
