Re: PostgreSQL Process memory architecture

* Atri Sharma (atri(dot)jiit(at)gmail(dot)com) wrote:
> On Mon, May 27, 2013 at 3:41 PM, Ben Zeev, Lior <lior(dot)ben-zeev(at)hp(dot)com> wrote:
> > Do you have idea what may be the reason that PostgreSQL process consume more memory when there are more partial indexes on the DB table?

It might use a bit more, but it shouldn't be excessive.. What, exactly,
are you seeing and would it be possible for you to provide a repeatable
test case with a small-ish set of data?

> Well, I am not too sure, but indexes always take up more space, so if
> your backend has a lot of indexes, it will cause the process to
> consume more memory.

Indexes require additional disk space, certainly. Having a lot of
indexes, by itself, shouldn't seriously increase memory usage.

> Indexes should be used with care, as too many indexes can cause a
> memory overhead,which can cause performance degradations.

This is not generally a reason to avoid indexes. Indexes require more
disk space and must be kept up to date, making them expensive to
maintain due to increased disk i/o. Building an index uses as much
memory as it's allowed to- it uses maintenance_work_mem to limit itself.

Thanks,

Stephen