Re: High Disk write and space taken by PostgreSQL

On Thu, Aug 16, 2012 at 1:30 AM, J Ramesh Kumar <rameshj1977(at)gmail(dot)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(at)gmail(dot)com>wrote:

> What are your indexes? Is the size in the indexes or the database tables?
>
>
> 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.

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).