On Thu, Aug 16, 2012 at 1:30 AM, J Ramesh Kumar <rameshj1977@xxxxxxxxx> wrote:
# To avoid freqent autovacuumautovacuum_freeze_max_age = 2000000000vacuum_freeze_min_age = 10000000vacuum_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@xxxxxxxxx> wrote:
The size I mentioned is the total folder size of the data directory. There is no difference in the database schema / index between MySQL and PostgreSQL.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).
