On Thu, Aug 16, 2012 at 1:30 AM, J Ramesh Kumar
<rameshj1977@gmail.com> wrote:
# To avoid freqent autovacuum
autovacuum_freeze_max_age = 2000000000
vacuum_freeze_min_age = 10000000
vacuum_freeze_table_age = 150000000
In general, I'm no expert, but I've heard, increasing freeze_max_age isn't wise. It's there to be decreased, and the risk is data corruption.
You should check PG's docs to be sure, but I think the default is usually safe and fast enough.
And, if you have updates (anywhere), avoiding autovacuum may not be a good idea either. Autovacuum won't bother you on tables you don't update, so I think you're optimizing prematurely here. If you're worrying about it, just increase its naptime.
You'll most definitely need to vacuum pg's catalog with that many (and regular) schema changes, and autovacuum also takes care of that.
You may also want to set asynchronous_commits, to better match MyISAM's characteristics. Or even, just for benchmarking, fsync=off (I wouldn't do it in production though).
Anyway, seeing the schema of at least one of the biggest growing tables would probably help figuring out why the disk usage growth. Index bloat comes to mind.
On Thu, Aug 16, 2012 at 1:30 AM, J Ramesh Kumar
<rameshj1977@gmail.com> wrote:
What are your indexes? Is the size in the indexes or the database tables?
You have a problem right there. Postgres and Mysql are completely different beasts, you *will* need to tailor indices specifically for each of them. You'll find, probably, many indices you needed in MySQL are no longer needed with postgres (because it has a much more sophisticated planner).