Re: PostgreSQL (9.3 and 9.6) eats all memory when using many tables

On Mon, Jun 13, 2016 at 09:27:40AM -0700, Jeff Janes wrote:
> On Mon, Jun 13, 2016 at 2:39 AM, hubert depesz lubaczewski
> <depesz(at)depesz(dot)com> wrote:
> > On Sun, Jun 12, 2016 at 01:50:48PM -0700, Jeff Janes wrote:
>
> >
> > Line #2 shows output of ps nh uww -p <backend_pid> before start of work.
> > There are, in total, 74002 tables, and then I iterate over list of them,
> > and for each, I do the select I mentioned.
> >
> > Every 1000 tables, I get stats - ps output, and (in parent) sum of
> > "Anonymous:" lines from /proc/<backend_pid>/smaps.
> >
> > As you can see - we're getting ~ 32kB of cache per table.
>
> What hardware and OS are you using? I only get a bit over 8kB per
> table, and that amount doesn't change much between 9.3 and 9.6.

These are 64bit aws virtual boxes (ec2, not rds) using ubuntu trusty.
Pg is from ubuntu repo.

> Can you share a representative table definition, including constraints?

There are ~ 180 different tables, each looking different, and they are
copied (schema, not data) across ~ 400 schemas.

some tables have 1 index, some have up to 17.

The 17 index table looks like this (sorry, had to redact it heavily):

Column | Type | Modifiers
--------------------------------------+-----------------------------+---------------------------------------------------------------------------
id | bigint | not null default nextval('...................................'::regclass)
.... | character varying(255) |
.......... | bigint | not null
...................... | character varying(255) |
.............. | character varying(255) | not null
.... | character varying(255) |
........ | timestamp without time zone |
........... | timestamp without time zone |
................... | bigint |
......... | boolean |
........................ | boolean |
.......... | timestamp without time zone |
.......... | timestamp without time zone |
............................ | boolean |
............. | text |
............................... | boolean | default false
.......................... | character varying(255) |
....... | bigint |
.............................. | boolean | default true
........... | character varying(255) |
............ | character varying(255) | default '....'::character varying
.................. | bigint |
............... | bigint | not null
.................. | bigint | not null
............. | character varying(255) |
............ | bigint |
............... | boolean |
............. | bigint |
................. | text |
................... | boolean |
................. | text |
............... | boolean |
....... | character varying(255) |
....... | boolean |
.................................... | boolean |
.................. | bigint |
...... | character varying(255) |
........ | text |
..................... | bigint |
................ | text |
.................. | text |
.................... | character varying(255) |
..................... | integer |
.............. | character varying(255) |
......... | character varying(255) |
.............. | character varying(255) |
........... | bigint |

and on this there are 17 indexes, 7 fkeys, and it is being referenced by 15 other tables.

Best regards,

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/