Too slow to create new schema and their tables, functions, triggers.

From: PegoraroF10 <marcos(at)f10(dot)com(dot)br>
To: pgsql-general(at)postgresql(dot)org
Subject: Too slow to create new schema and their tables, functions, triggers.
Date: 2019-07-23 19:20:33
Message-ID: 1563909633976-0.post@n3.nabble.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

We have in a single database 190 identical schemas. Now, when we create a new
one, with exactly same structure as the previous ones, it takes 20 or 30
minutes to finish. Usual time to finish that script was 30 seconds.

Basically, my script creates an entire structure for a new customer:
- Create schema TempSchema; --just to be sure that nobody will connect until
it finishes
- create tables (100), constraints, functions, etc.
- import data using restore only data.
- rename TempSchema to production name.

On second step, when creating tables, functions and so on, it takes
sometimes a minute just to create one table or one function.

So, does this happens because we have almost 200 schemas on that database ?
I´m almost sure about.
What do I need do to run my script as before ? Do I need to Reindex ? Vacuum
? Or am I reaching a limit in a number of schemas in a Postgres database ?

--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2019-07-23 19:36:18 Re: Too slow to create new schema and their tables, functions, triggers.
Previous Message Perumal Raj 2019-07-23 17:28:32 Re: pg_upgrade : 9.X to 11.X issue CentoOS 7.6