Re: Why does my DB size differ between Production and DR? (Postgres 8.4)

atsaloli(dot)tech(at)gmail(dot)com (Aleksey Tsalolikhin) writes:
> Situation: Disk usage on production server root filesystem is at 68%
> utilization (80 GB used), on DR is at 51% (56 GB used). We use
> SlonyII-1.2.x to keep the DR up to date. I would like to account for
> the 24 GB difference.

It's more than likely a result of transactions failing on the origin,
leaving dead space around, where replication doesn't bother trying to do
any work for the "failed stuff," with the consequence that there's no
corresponding "clutter" on the replica.

I'm talking here about cases of failures that are expected.

Look to what activities you have that tend to lead to tranactions that
ROLLBACK. Slony-I makes no attempt to replicate activity that is
terminated by ROLLBACK (explicit or implicit), so all that activity
won't be processed on replicas.

For instance, in our applications, operating domain registries,
intentionally failed database transactions occur heavily *common*
whenever customers are 'fighting' over domain names - one and only one
customer can win the name, while all others lose, and each losing
request leaves a certain amount of mess in its wake. Common patterns of
this sort include transactions that fail because:

- Customer has insufficient funds on account to pay for the transaction

- Inventory request fails because there are insufficient items in stock

- Attempt to insert a second instance of an object that is required to
be unique

- Rejection of partially processed transaction due to violation of some
business policy (which is mighty open-ended!)

It's likely, as well, that there is some set of tables that you are not
vacuuming heavily enough. Probably a table or three needs to have
CLUSTER run on it to bring them down to size, and you may need to fiddle
with autovacuum parameters to vacuum more frequently.
--
(reverse (concatenate 'string "moc.liamg" "@" "enworbbc"))
http://linuxdatabases.info/info/lisp.html
"Microsoft has world class quality control" -- Arthur Norman