Re: Dramatic change in memory usage with version 9.1

On Mon, Dec 19, 2011 at 17:04, Rafael Martinez <r.m.guerrero@usit.uio.no> wrote:
> * Sudden decrease of swap when running backup/vacuum+analyze jobs

Do you know for certain that this memory use is attributed to
vacuum/analyze/backup, or are you just guessing? You should isolate
whether it's the vacuum or a backup process/backend that takes this
memory.

Do you launch vacuum/analyze manually or are you just relying on autovacuum?
How many parallel vacuum jobs are there?
What's your autovacuum_max_workers set to?
How large is your database?
How did you perform the upgrade -- via pg_upgrade or pg_dump?

> Any ideas about why this dramatic change in memory usage when the only
> thing apparently changed from our side is the postgres version?

Well, for one, there have been many planner changes that make it use
memory more aggressively, these probably being the most significant:
* Materialize for nested loop queries in 9.0:
http://rhaas.blogspot.com/2010/04/materialization-in-postgresql-90.html
* Hash join usage for RIGHT and FULL OUTER JOINs in 9.0

However, none of these would apply to vacuum, analyze or backups.

Regards,
Marti