Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dQDuG-000600-RT for pgsql-performance@arkaria.postgresql.org; Wed, 28 Jun 2017 14:25:25 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dQDuF-0000Uv-Si for pgsql-performance@arkaria.postgresql.org; Wed, 28 Jun 2017 14:25:23 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dQDuE-0000UE-Qp for pgsql-performance@postgresql.org; Wed, 28 Jun 2017 14:25:22 +0000 Received: from mail-vk0-x230.google.com ([2607:f8b0:400c:c05::230]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dQDu9-0006Xp-LF for pgsql-performance@postgresql.org; Wed, 28 Jun 2017 14:25:21 +0000 Received: by mail-vk0-x230.google.com with SMTP id r126so33925433vkg.0 for ; Wed, 28 Jun 2017 07:25:17 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:in-reply-to:references:from:date:message-id:subject:to; bh=Sm41XHC5xigAyBt3CbFYo72T1WI+D1IQfnZOspSZqfM=; b=tIZHt7OtvHYJOU2yjDy+j53qaADGCELRro5Nz6D3Ly42Cv5wURkkP6k119CosuRBJl x8FQIh+T2wVL3RD9fT9OK13JaAkwkSdxulvfdZcEUc/oUXbpO9MymsSabc2EqzSROjeZ TPMPaIZP5kf3yvklNSyJvMWnedwoR32WFrbmrtAlAYWvWuWifmh3b5AiWpebAePq3DsN wVYpLMw8vYGKJc118DlBdx2JUz155Cb47NcS+cyXXWGrrkk6hJTLD6H7IRTFFuKxRLSC SZ+rRsH+UzFc1NNFr2vRuevaYnp1PxMinPS33tpKLlB+ARwy6PnTqEcuVApYc8lRBOvG g4ig== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to; bh=Sm41XHC5xigAyBt3CbFYo72T1WI+D1IQfnZOspSZqfM=; b=FRkAOOQWDZcuIOqmcupMmqH9hMj8H8WVZwSviIzcUyrwuk66khDQ6jiPUAdXzWQMPV fAc2RcQAucoyuV3uPfb+5frJaM5ZT4+MmSy9E5SR2LJngcwf99zasgErX8cxR9Qw9Gu1 ogz3gqy0irMX44kCPSEHCMF+KAmiQTOZBWHViyyGZV/kLbDL6/PQg34j60IJ+UGWdb7p cnJiW1R9O3oEvBqf7gceYVEypeNqcqWqr08EZ/u0po+NT+dg+DdfI7NYHv4BcxGlCOZ9 iDJfqJvcFvaZajkvvc8rgJI9h2QjHRwS0gPyI36MzXiSFeed90Rw6a9YnZ1ayhCR8HdJ iLtQ== X-Gm-Message-State: AKS2vOwwbjNOhayYaTiOByxMvad6RzHQLJBmfaMBrcAxnKyqkZS6VcDk XLgAqHRgr0CivTeGpDCtWicY3xaVVvJV+Zk= X-Received: by 10.31.172.88 with SMTP id v85mr5908997vke.57.1498659915740; Wed, 28 Jun 2017 07:25:15 -0700 (PDT) MIME-Version: 1.0 Received: by 10.103.143.155 with HTTP; Wed, 28 Jun 2017 07:25:15 -0700 (PDT) In-Reply-To: <31f372f2-f70e-782c-aa73-d211b3e333fc@pritambaral.com> References: <31f372f2-f70e-782c-aa73-d211b3e333fc@pritambaral.com> From: =?UTF-8?Q?Ulf_Lohbr=C3=BCgge?= Date: Wed, 28 Jun 2017 16:25:15 +0200 Message-ID: Subject: Re: Performance of information_schema with many schemata and tables To: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary="001a11434e26eac1d7055305f3c5" List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --001a11434e26eac1d7055305f3c5 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 2017-06-28 10:43 GMT+02:00 Pritam Baral : > > > On Wednesday 28 June 2017 02:00 PM, Ulf Lohbr=C3=BCgge wrote: > > Nope, I didn't try that yet. But I don't have the impression that > reindexing the indexes in information_schema will help. The table > information_schema.tables consists of the following indexes: > > > > "pg_class_oid_index" UNIQUE, btree (oid) > > "pg_class_relname_nsp_index" UNIQUE, btree (relname, relnamespace) > > "pg_class_tblspc_relfilenode_index" btree (reltablespace, > relfilenode) > > information_schema.tables is not a table, it's a view; at least on 9.5[0]= . > These indexes you list are actually indexes on the pg_catalog.pg_class > table. > Yes, it's a view. \d+ information_schema.tables gives: View definition: SELECT current_database()::information_schema.sql_identifier AS table_catalog, nc.nspname::information_schema.sql_identifier AS table_schema, c.relname::information_schema.sql_identifier AS table_name, CASE WHEN nc.oid =3D pg_my_temp_schema() THEN 'LOCAL TEMPORARY'::tex= t WHEN c.relkind =3D 'r'::"char" THEN 'BASE TABLE'::text WHEN c.relkind =3D 'v'::"char" THEN 'VIEW'::text WHEN c.relkind =3D 'f'::"char" THEN 'FOREIGN TABLE'::text ELSE NULL::text END::information_schema.character_data AS table_type, NULL::character varying::information_schema.sql_identifier AS self_referencing_column_name, NULL::character varying::information_schema.character_data AS reference_generation, CASE WHEN t.typname IS NOT NULL THEN current_database() ELSE NULL::name END::information_schema.sql_identifier AS user_defined_type_catalog= , nt.nspname::information_schema.sql_identifier AS user_defined_type_schema, t.typname::information_schema.sql_identifier AS user_defined_type_name, CASE WHEN c.relkind =3D 'r'::"char" OR (c.relkind =3D ANY (ARRAY['v'::"char", 'f'::"char"])) AND (pg_relation_is_updatable(c.oid::regclass, false) & 8) =3D 8 THEN 'YES'::te= xt ELSE 'NO'::text END::information_schema.yes_or_no AS is_insertable_into, CASE WHEN t.typname IS NOT NULL THEN 'YES'::text ELSE 'NO'::text END::information_schema.yes_or_no AS is_typed, NULL::character varying::information_schema.character_data AS commit_action FROM pg_namespace nc JOIN pg_class c ON nc.oid =3D c.relnamespace LEFT JOIN (pg_type t JOIN pg_namespace nt ON t.typnamespace =3D nt.oid) ON c.reloftype =3D = t.oid WHERE (c.relkind =3D ANY (ARRAY['r'::"char", 'v'::"char", 'f'::"char"])) AND NOT pg_is_other_temp_schema(nc.oid) AND (pg_has_role(c.relowner, 'USAGE'::text) OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES'::text)); > > > > > The costly sequence scan in question on pg_class happens with the > following WHERE clause: > > > > WHERE (c.relkind =3D ANY (ARRAY['r'::"char", 'v'::"char", 'f'::"char"])= ) > AND NOT pg_is_other_temp_schema(nc.oid) AND (pg_has_role(c.relowner, > 'USAGE'::text) OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, > DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) OR has_any_column_privilege= (c.oid, > 'SELECT, INSERT, UPDATE, REFERENCES'::text)); > > This is not the bottleneck WHERE clause the query plan from your first > mail shows. That one is: > > ((relkind =3D ANY ('{r,v,f}'::"char"[])) AND (((relname)::information_ > schema.sql_identifier)::text =3D 'bar'::text) AND (pg_has_role(relowner, > 'USAGE'::text) OR has_table_privilege(oid, 'SELECT, INSERT, UPDATE, DELET= E, > TRUNCATE, REFERENCES, TRIGGER'::text) OR has_any_column_privilege(oid, > 'SELECT, INSERT, UPDATE, REFERENCES'::text))) > The part you copied is from the EXPLAIN ANALYZE output. The WHERE clause I posted earlier (or see view definition) above does unfortunately not contain the relname. > > I can say with certainty that an index on pg_catalog.pg_class.relname is > going to speed this up. Postgres doesn't allow modifying system catalogs, > but the `REINDEX SYSTEM ;` command should rebuild the system > indexes and pg_catalog.pg_class.relname should be included in them (I > tested on 9.6). > > Do try that once. If you still see sequential scans, check what indexes > are present on pg_catalog.pg_class. > I just fired a 'REINDEX SYSTEM ;' but the output of EXPLAIN ANALYZE is unchanged and the query duration did not change. Best Regards, Ulf > > > > > > Besides pg_class_oid_index none of the referenced columns is indexed. I > tried to add an index on relowner but didn't succeed because the column i= s > used in the function call pg_has_role and the query is still forced to do= a > sequence scan. > > > > Regards, > > Ulf > > > > 2017-06-28 3:31 GMT+02:00 Pritam Baral pritam@pritambaral.com>>: > > > > On Wednesday 28 June 2017 05:27 AM, Ulf Lohbr=C3=BCgge wrote: > > > Hi all, > > > > > > we use schemata to separate our customers in a multi-tenant setup > (9.5.7, Debian stable). Each tenant is managed in his own schema with all > the tables that only he can access. All tables in all schemata are the sa= me > in terms of their DDL: Every tenant uses e.g. his own table 'address'. We > currently manage around 1200 schemata (i.e. tenants) on one cluster. Ever= y > schema consists currently of ~200 tables - so we end up with ~240000 tabl= es > plus constraints, indexes, sequences et al. > > > > > > Our current approach is quite nice in terms of data privacy > because every tenant is isolated from all other tenants. A tenant uses hi= s > own user that gives him only access to the corresponding schema. > Performance is great for us - we didn't expect Postgres to scale so well! > > > > > > But performance is pretty bad when we query things in the > information_schema: > > > > > > SELECT > > > * > > > FROM information_schema.tables > > > WHERE table_schema =3D 'foo' > > > AND table_name =3D 'bar';`` > > > > > > Above query results in a large sequence scan with a filter that > removes 1305161 rows: > > > > > > > > QUERY PLAN > > > ------------------------------------------------------------ > ------------------------------------------------------------ > ------------------------------------------------------------ > ------------------------------------------------------------ > ------------------------------------------------------------ > ------------------------------------------------------- > > > Nested Loop Left Join (cost=3D0.70..101170.18 rows=3D3 width=3D= 265) > (actual time=3D383.505..383.505 rows=3D0 loops=3D1) > > > -> Nested Loop (cost=3D0.00..101144.65 rows=3D3 width=3D141) > (actual time=3D383.504..383.504 rows=3D0 loops=3D1) > > > Join Filter: (nc.oid =3D c.relnamespace) > > > -> Seq Scan on pg_class c (cost=3D0.00..101023.01 > rows=3D867 width=3D77) (actual time=3D383.502..383.502 rows=3D0 loops=3D1= ) > > > Filter: ((relkind =3D ANY ('{r,v,f}'::"char"[])) A= ND > (((relname)::information_schema.sql_identifier)::text =3D 'bar'::text) AN= D > (pg_has_role(relowner, 'USAGE'::text) OR has_table_privilege(oid, 'SELECT= , > INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) OR > has_any_column_privilege(oid, 'SELECT, INSERT, UPDATE, REFERENCES'::text)= )) > > > Rows Removed by Filter: 1305161 > > > -> Materialize (cost=3D0.00..56.62 rows=3D5 width=3D68= ) > (never executed) > > > -> Seq Scan on pg_namespace nc (cost=3D0.00..56.= 60 > rows=3D5 width=3D68) (never executed) > > > Filter: ((NOT pg_is_other_temp_schema(oid)) > AND (((nspname)::information_schema.sql_identifier)::text =3D 'foo'::text= )) > > > -> Nested Loop (cost=3D0.70..8.43 rows=3D1 width=3D132) (nev= er > executed) > > > -> Index Scan using pg_type_oid_index on pg_type t > (cost=3D0.42..8.12 rows=3D1 width=3D72) (never executed) > > > Index Cond: (c.reloftype =3D oid) > > > -> Index Scan using pg_namespace_oid_index on > pg_namespace nt (cost=3D0.28..0.30 rows=3D1 width=3D68) (never executed) > > > Index Cond: (oid =3D t.typnamespace) > > > Planning time: 0.624 ms > > > Execution time: 383.784 ms > > > (16 rows) > > > > > > We noticed the degraded performance first when using the psql cli= . > Pressing tab after beginning a WHERE clause results in a query against th= e > information_schema which is pretty slow and ends in "lag" when trying to > enter queries. > > > > > > We also use Flyway (https://flywaydb.org/) to handle our database > migrations. Unfortunately Flyway is querying the information_schema to > check if specific tables exist (I guess this is one of the reasons > information_schema exists) and therefore vastly slows down the migration = of > our tenants. Our last migration run on all tenants (schemata) almost took > 2h because the above query is executed multiple times per tenant. The > migration run consisted of multiple sql files to be executed and triggere= d > more than 10 queries on information_schema per tenant. > > > > > > I don't think that Flyway is to blame because querying the > information_schema should be a fast operation (and was fast for us when w= e > had less schemata). I tried to speedup querying pg_class by adding indexe= s > (after enabling allow_system_table_mods) but didn't succeed. The function > call 'pg_has_role' is probably not easy to optimize. > > > > > > Postgres is really doing a great job to handle those many schemat= a > and tables but doesn't scale well when querying information_schema. I > actually don't want to change my current multi-tenant setup (one schema p= er > tenant) as it is working great but the slow information_schema is killing > our deployments. > > > > > > Are there any other options besides switching from > one-schema-per-tenant-approach? Any help is greatly appreciated! > > > > Have you tried a `REINDEX SYSTEM `? > > > > > > > > Regards, > > > Ulf > > > > -- > > #!/usr/bin/env regards > > Chhatoi Pritam Baral > > > > > > > > -- > > Sent via pgsql-performance mailing list ( > pgsql-performance@postgresql.org >) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-performance < > http://www.postgresql.org/mailpref/pgsql-performance> > > > > > > [0]: https://www.postgresql.org/docs/9.5/static/infoschema-tables.html > > -- > #!/usr/bin/env regards > Chhatoi Pritam Baral > > --001a11434e26eac1d7055305f3c5 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


2017-06-28 10:43 GMT+02:00 Pritam Baral <pritam@pritambaral.com= >:


On Wednesday 28 June 2017 02:00 PM, Ulf Lohbr=C3=BCgge wrote:
> Nope, I didn't try that yet. But I don't have the impression t= hat reindexing the indexes in information_schema will help. The table infor= mation_schema.tables consists of the following indexes:
>
>=C2=A0 =C2=A0 =C2=A0"pg_class_oid_index" UNIQUE, btree (oid)<= br> >=C2=A0 =C2=A0 =C2=A0"pg_class_relname_nsp_index" UNIQUE, btre= e (relname, relnamespace)
>=C2=A0 =C2=A0 =C2=A0"pg_class_tblspc_relfilenode_index" = btree (reltablespace, relfilenode)

information_schema.tables is not a table, it's a view; at least = on 9.5[0]. These indexes you list are actually indexes on the pg_catalog.pg= _class table.

Yes, it's a view. \d+= information_schema.tables gives:

View defini= tion:
=C2=A0SELECT current_database()::information_schema.sql_ide= ntifier AS table_catalog,
=C2=A0 =C2=A0 nc.nspname::information_s= chema.sql_identifier AS table_schema,
=C2=A0 =C2=A0 c.relname::in= formation_schema.sql_identifier AS table_name,
=C2=A0 =C2=A0 =C2= =A0 =C2=A0 CASE
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 WHEN nc= .oid =3D pg_my_temp_schema() THEN 'LOCAL TEMPORARY'::text
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 WHEN c.relkind =3D 'r'::= "char" THEN 'BASE TABLE'::text
=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 WHEN c.relkind =3D 'v'::"char"= ; THEN 'VIEW'::text
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 WHEN c.relkind =3D 'f'::"char" THEN 'FOREIGN T= ABLE'::text
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ELSE NU= LL::text
=C2=A0 =C2=A0 =C2=A0 =C2=A0 END::information_schema.char= acter_data AS table_type,
=C2=A0 =C2=A0 NULL::character varying::= information_schema.sql_identifier AS self_referencing_column_name,
=C2=A0 =C2=A0 NULL::character varying::information_schema.character_data = AS reference_generation,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 CASE
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 WHEN t.typname IS NOT NULL THE= N current_database()
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 EL= SE NULL::name
=C2=A0 =C2=A0 =C2=A0 =C2=A0 END::information_schema= .sql_identifier AS user_defined_type_catalog,
=C2=A0 =C2=A0 nt.ns= pname::information_schema.sql_identifier AS user_defined_type_schema,
=
=C2=A0 =C2=A0 t.typname::information_schema.sql_identifier AS user_def= ined_type_name,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 CASE
=C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 WHEN c.relkind =3D 'r'::"c= har" OR (c.relkind =3D ANY (ARRAY['v'::"char", '= f'::"char"])) AND (pg_relation_is_updatable(c.oid::regclass, = false) & 8) =3D 8 THEN 'YES'::text
=C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 ELSE 'NO'::text
=C2=A0 =C2=A0 = =C2=A0 =C2=A0 END::information_schema.yes_or_no AS is_insertable_into,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 CASE
=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 WHEN t.typname IS NOT NULL THEN 'YES'::text
=
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ELSE 'NO'::text
=C2=A0 =C2=A0 =C2=A0 =C2=A0 END::information_schema.yes_or_no AS is_t= yped,
=C2=A0 =C2=A0 NULL::character varying::information_schema.c= haracter_data AS commit_action
=C2=A0 =C2=A0FROM pg_namespace nc<= /div>
=C2=A0 =C2=A0 =C2=A0JOIN pg_class c ON nc.oid =3D c.relnamespace<= /div>
=C2=A0 =C2=A0 =C2=A0LEFT JOIN (pg_type t
=C2=A0 =C2=A0 = =C2=A0JOIN pg_namespace nt ON t.typnamespace =3D nt.oid) ON c.reloftype =3D= t.oid
=C2=A0 WHERE (c.relkind =3D ANY (ARRAY['r'::"= char", 'v'::"char", 'f'::"char"]))= AND NOT pg_is_other_temp_schema(nc.oid) AND (pg_has_role(c.relowner, '= USAGE'::text) OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE= , DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) OR has_any_column_privi= lege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES'::text));
=C2=A0

>
> The costly sequence scan in question on pg_class happens with the foll= owing WHERE clause:
>
> WHERE (c.relkind =3D ANY (ARRAY['r'::"char", 'v&= #39;::"char", 'f'::"char"])) AND NOT pg_is_othe= r_temp_schema(nc.oid) AND (pg_has_role(c.relowner, 'USAGE'::te= xt) OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUN= CATE, REFERENCES, TRIGGER'::text) OR has_any_column_privilege(c.oi= d, 'SELECT, INSERT, UPDATE, REFERENCES'::text));

This is not the bottleneck WHERE clause the query plan from your fir= st mail shows. That one is:

((relkind =3D ANY ('{r,v,f}'::"char"[])) AND (((relname):= :information_
schema.sql_identifier)::text =3D 'bar'::text) AND (pg_has_role(relo= wner, 'USAGE'::text) OR has_table_privilege(oid, 'SELECT, INSER= T, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) OR has_any_col= umn_privilege(oid, 'SELECT, INSERT, UPDATE, REFERENCES'::text)))

The part you copied is from the EX= PLAIN ANALYZE output. The WHERE clause I posted earlier (or see view defini= tion) above does unfortunately not contain the relname.
=C2=A0

I can say with certainty that an index on pg_catalog.pg_class.relnam= e is going to speed this up. Postgres doesn't allow modifying system ca= talogs, but the `REINDEX SYSTEM <dbname>;` command should rebuild the= system indexes and pg_catalog.pg_class.relname should be included in them = (I tested on 9.6).

Do try that once. If you still see sequential scans, check what indexes are= present on pg_catalog.pg_class.

I just= fired a 'REINDEX SYSTEM <dbname>;' but the output of EXPLAIN= ANALYZE is unchanged and the query duration did not change.

=
Best Regards,
Ulf
=C2=A0


>
> Besides pg_class_oid_index none of the referenced columns is indexed. = I tried to add an index on relowner but didn't succeed because the colu= mn is used in the function call pg_has_role and the query is still forced t= o do a sequence scan.
>
> Regards,
> Ulf
>
> 2017-06-28 3:31 GMT+02:00 Pritam Baral <pritam@pritambaral.com <mailto:pritam@pritambaral.com>>:
>
>=C2=A0 =C2=A0 =C2=A0On Wednesday 28 June 2017 05:27 AM, Ulf Lohbr=C3=BC= gge wrote:
>=C2=A0 =C2=A0 =C2=A0> Hi all,
>=C2=A0 =C2=A0 =C2=A0>
>=C2=A0 =C2=A0 =C2=A0> we use schemata to separate our customers in a= multi-tenant setup (9.5.7, Debian stable). Each tenant is managed in his o= wn schema with all the tables that only he can access. All tables in all sc= hemata are the same in terms of their DDL: Every tenant uses e.g. his own t= able 'address'. We currently manage around 1200 schemata (i.e. tena= nts) on one cluster. Every schema consists currently of ~200 tables - so we= end up with ~240000 tables plus constraints, indexes, sequences et al.
>=C2=A0 =C2=A0 =C2=A0>
>=C2=A0 =C2=A0 =C2=A0> Our current approach is quite nice in terms of= data privacy because every tenant is isolated from all other tenants. A te= nant uses his own user that gives him only access to the corresponding sche= ma. Performance is great for us - we didn't expect Postgres to scale so= well!
>=C2=A0 =C2=A0 =C2=A0>
>=C2=A0 =C2=A0 =C2=A0> But performance is pretty bad when we query th= ings in the information_schema:
>=C2=A0 =C2=A0 =C2=A0>
>=C2=A0 =C2=A0 =C2=A0> SELECT
>=C2=A0 =C2=A0 =C2=A0>=C2=A0 =C2=A0*
>=C2=A0 =C2=A0 =C2=A0> FROM information_schema.tables
>=C2=A0 =C2=A0 =C2=A0> WHERE table_schema =3D 'foo'
>=C2=A0 =C2=A0 =C2=A0> AND table_name =3D 'bar';``
>=C2=A0 =C2=A0 =C2=A0>
>=C2=A0 =C2=A0 =C2=A0> Above query results in a large sequence scan w= ith a filter that removes 1305161 rows:
>=C2=A0 =C2=A0 =C2=A0>
>=C2=A0 =C2=A0 =C2=A0>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0QUERY PLAN
>=C2=A0 =C2=A0 =C2=A0> -----------------------------------------= -----------------------------------------------------------------= -----------------------------------------------------------------= -----------------------------------------------------------------= ----------------------------------------------------------------<= wbr>-------------------------------------------------------
>=C2=A0 =C2=A0 =C2=A0>=C2=A0 Nested Loop Left Join=C2=A0 (cost=3D0.70= ..101170.18 rows=3D3 width=3D265) (actual time=3D383.505..383.505 rows=3D0 = loops=3D1)
>=C2=A0 =C2=A0 =C2=A0>=C2=A0 =C2=A0 ->=C2=A0 Nested Loop=C2=A0 (co= st=3D0.00..101144.65 rows=3D3 width=3D141) (actual time=3D383.504..383.504 = rows=3D0 loops=3D1)
>=C2=A0 =C2=A0 =C2=A0>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Join Filter:= (nc.oid =3D c.relnamespace)
>=C2=A0 =C2=A0 =C2=A0>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ->=C2=A0 = Seq Scan on pg_class c=C2=A0 (cost=3D0.00..101023.01 rows=3D867 width=3D77)= (actual time=3D383.502..383.502 rows=3D0 loops=3D1)
>=C2=A0 =C2=A0 =C2=A0>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 Filter: ((relkind =3D ANY ('{r,v,f}'::"char"[]= )) AND (((relname)::information_schema.sql_identifier)::text =3D '= bar'::text) AND (pg_has_role(relowner, 'USAGE'::text) OR has_ta= ble_privilege(oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCE= S, TRIGGER'::text) OR has_any_column_privilege(oid, 'SELECT, INSERT= , UPDATE, REFERENCES'::text)))
>=C2=A0 =C2=A0 =C2=A0>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 Rows Removed by Filter: 1305161
>=C2=A0 =C2=A0 =C2=A0>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ->=C2=A0 = Materialize=C2=A0 (cost=3D0.00..56.62 rows=3D5 width=3D68) (never executed)=
>=C2=A0 =C2=A0 =C2=A0>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 ->=C2=A0 Seq Scan on pg_namespace nc=C2=A0 (cost=3D0.00..56.6= 0 rows=3D5 width=3D68) (never executed)
>=C2=A0 =C2=A0 =C2=A0>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Filter: ((NOT pg_is_other_temp_schema(oid))= AND (((nspname)::information_schema.sql_identifier)::text =3D 'fo= o'::text))
>=C2=A0 =C2=A0 =C2=A0>=C2=A0 =C2=A0 ->=C2=A0 Nested Loop=C2=A0 (co= st=3D0.70..8.43 rows=3D1 width=3D132) (never executed)
>=C2=A0 =C2=A0 =C2=A0>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ->=C2=A0 = Index Scan using pg_type_oid_index on pg_type t=C2=A0 (cost=3D0.42..8.12 ro= ws=3D1 width=3D72) (never executed)
>=C2=A0 =C2=A0 =C2=A0>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 Index Cond: (c.reloftype =3D oid)
>=C2=A0 =C2=A0 =C2=A0>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ->=C2=A0 = Index Scan using pg_namespace_oid_index on pg_namespace nt=C2=A0 (cost=3D0.= 28..0.30 rows=3D1 width=3D68) (never executed)
>=C2=A0 =C2=A0 =C2=A0>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 Index Cond: (oid =3D t.typnamespace)
>=C2=A0 =C2=A0 =C2=A0>=C2=A0 Planning time: 0.624 ms
>=C2=A0 =C2=A0 =C2=A0>=C2=A0 Execution time: 383.784 ms
>=C2=A0 =C2=A0 =C2=A0> (16 rows)
>=C2=A0 =C2=A0 =C2=A0>
>=C2=A0 =C2=A0 =C2=A0> We noticed the degraded performance first when= using the psql cli. Pressing tab after beginning a WHERE clause results in= a query against the information_schema which is pretty slow and ends in &q= uot;lag" when trying to enter queries.
>=C2=A0 =C2=A0 =C2=A0>
>=C2=A0 =C2=A0 =C2=A0> We also use Flyway (https://flywaydb.org/) to = handle our database migrations. Unfortunately Flyway is querying the inform= ation_schema to check if specific tables exist (I guess this is one of the = reasons information_schema exists) and therefore vastly slows down the migr= ation of our tenants. Our last migration run on all tenants (schemata) almo= st took 2h because the above query is executed multiple times per tenant. T= he migration run consisted of multiple sql files to be executed and trigger= ed more than 10 queries on information_schema per tenant.
>=C2=A0 =C2=A0 =C2=A0>
>=C2=A0 =C2=A0 =C2=A0> I don't think that Flyway is to blame beca= use querying the information_schema should be a fast operation (and was fas= t for us when we had less schemata). I tried to speedup querying pg_class b= y adding indexes (after enabling allow_system_table_mods) but didn't su= cceed. The function call 'pg_has_role' is probably not easy to opti= mize.
>=C2=A0 =C2=A0 =C2=A0>
>=C2=A0 =C2=A0 =C2=A0> Postgres is really doing a great job to handle= those many schemata and tables but doesn't scale well when querying in= formation_schema. I actually don't want to change my current multi-tena= nt setup (one schema per tenant) as it is working great but the slow inform= ation_schema is killing our deployments.
>=C2=A0 =C2=A0 =C2=A0>
>=C2=A0 =C2=A0 =C2=A0> Are there any other options besides switching = from one-schema-per-tenant-approach? Any help is greatly appreciated!<= br> >
>=C2=A0 =C2=A0 =C2=A0Have you tried a `REINDEX SYSTEM <dbname>`? >
>=C2=A0 =C2=A0 =C2=A0>
>=C2=A0 =C2=A0 =C2=A0> Regards,
>=C2=A0 =C2=A0 =C2=A0> Ulf
>
>=C2=A0 =C2=A0 =C2=A0--
>=C2=A0 =C2=A0 =C2=A0#!/usr/bin/env regards
>=C2=A0 =C2=A0 =C2=A0Chhatoi Pritam Baral
>
>
>
>=C2=A0 =C2=A0 =C2=A0--
>=C2=A0 =C2=A0 =C2=A0Sent via pgsql-performance mailing list= (pgsql-performance@pos= tgresql.org <mailto:pgsql-performance@postgresql.org>)
>=C2=A0 =C2=A0 =C2=A0To make changes to your subs= cription:
>=C2=A0 =C2=A0 =C2=A0http://www.postgr= esql.org/mailpref/pgsql-performance <http://www.postgresql.org/mailpref/pgsql-performance>
>
>

[0]: https://www.postgresql.org/docs/9.5/static/infoschema-tables.html

--
#!/usr/bin/env regards
Chhatoi Pritam Baral


--001a11434e26eac1d7055305f3c5--