Re: Connection hangs on new created schema

Juan Manuel Cuello wrote:
> I have a postgresql database with around 4000 schemas. Each schema has around
> the same 65 tables. There are 8 processes connected to the database (it is a web app).
> Each process has only one direct connection to the database (no connection pool).
>
> Everything works ok until a new schema (with all its tables, indices, etc)
> is added to the database. When this happens, the next query made to the database
> takes too long, sometimes more than a minute o two. Also I see a high DB CPU usage
> during that lapse. Then everything continues working ok as usual and CPU drops to normal levels.
>
> I'm mostly sure this has to do with the amount of schemas, maybe related to relcache,
> but not sure why creating new schemas causes all this and where to look to solve this problem.

If you know the slow query EXPLAIN (ANALYZE, BUFFERS) it and see where the time
is spent.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com