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@depesz.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/