Re: PostgreSQL Process memory architecture

Hi Stephen,

The case which I'm seeing is that I have an empty table without any rows,
Create table test (
Num Integer,
C1 character varying(512),
C2 character varying(512),
C3 character varying(512));

I create several partial indexes on this table:

Create index(index_1_c1) on test(c1) where Num=1;
Create index(index_2_c1) on test(c1) where Num=2;
Create index(index_1_c2) on test(c1) where Num=1;
Create index(index_2_c2) on test(c1) where Num=2;
...

This doesn't consume much memory on the PostgreSQL backend process,
But if I create 500 indexes It consume several MB of memory.

If I have 10 tables with 500 indexes each PostgreSql backend process consume 20MB,
If I have 100 tables with 500 indexes each PostgreSQL backend process consume 200MB

All tables are empty without data.

If have Connection pool of 100 connections then All this processes consume 100*200MB = 20GB of memory

What is the reason to consume so much memory for empty indexes?

Thanks,
Lior

-----Original Message-----
From: Stephen Frost [mailto:sfrost(at)snowman(dot)net]
Sent: Monday, May 27, 2013 15:16
To: Atri Sharma
Cc: Ben Zeev, Lior; Pg Hackers
Subject: Re: [HACKERS] 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