0

I have a production database with 60K database schemas. PgAdmin4 is unusable, I cannot even open "Query Tool" window. I suppose that PgAdmin tries to load database metadata, so that's the reason why it's so slow. Is that true and can it be disabled? Can I do anything to speed it up?

I tried to use other DB clients like DBeaver or TablePlus and I have the same problem.

5
  • If I am not mistaken some PostgreSQL clients can connect to only one schema, then you can connect to the schema you want to work. For example a plugin for phpstorm that is very nice, and better than pgadmin itself. But i never used none of them (only see peoples using it), but for everything only psql and vim combined - including for plpgsql developement. – Luciano Andress Martini Dec 4 '18 at 12:37
  • psql works without any issues :) – elBastarde Dec 5 '18 at 16:45
  • Fortunately for you, psql is the best admin tool there is, why would you need anything else :) I've verified that with pgAdmin4, the problem is entirely on the client said. The database server finishes all its work in a timely manner, at least when all you try to do is open the schema folder. So if you want an improvement, lobby the pgadmin4 guys. On a broader note, you will run into lots of problems going down this road. Perhaps it is the best option, but life with 60,000 schemas won't be easy on several fronts. – jjanes Dec 5 '18 at 20:36
  • @LucianoAndressMartini: you connect to one database, but each database can contain many schema. – a_horse_with_no_name Dec 6 '18 at 11:48
  • I know, but some clients, just don't know that, it connects to one database and one schema... they don't have the idea that you have other schemas, i think if you client uses libodbc for example... So you will manipulate the multiple schemas like separated databases... – Luciano Andress Martini Dec 6 '18 at 11:49
0

Yes, it will be because some of the system views will be getting extremely slow. No, switching tools is unlikely to help. There have been updates to how the system views are generated over the years to improve performance with complex databases. But 60,000 schemas? Not even tables, but schemas? That is so far beyond what 99.9% of users run that you are unlikely to find much help.

If you are committed to this approach, you should probably subscribe to the pg mailing lists where you can have an extended discussion about the beat way to manage your database.

2
  • We are using schema per client, because single client can have 10 million or more records per table (event 50 million), so than we can scale it easily. This solution was suggested by one of the most famous Postgres consultant companies. So right now, I am using PSQL command line tool to manage database. But today, I tried PgAdmin4 version 3.0 and it works without any issues! But newer version do not work. Anyway, thank you for the comment! – elBastarde Dec 5 '18 at 16:45
  • 1
    @elBastarde The tool I am using has no problems with such a number of schemas. Obviously loading a list of 60000 schemas takes a bit longer than loading a list with 6 schemas but never to the point where this would block the usability. However, It seems that the information_schema views are much slower with that number of schemas compare to the views/tables in pg_catalog. So maybe pgAdmin is using the information_schema views which makes it really slow – a_horse_with_no_name Dec 6 '18 at 14:55
0

You can administrate it from vim, if you are a vim lover (or not)....

Configure vim for running psql when you press F5:

echo 'syn on' >>~/.vimrc
echo 'map <F5>  :silent w  !psql \| less <CR>:!<CR>' >>~/.vimrc

And now start working by calling vim with your database credentials...

$ PGUSER=postgres PGPASSWORD=pass PGHOST=127.0.0.1 PGDATABASE=dbname vim job.sql
[enter] 

Presse "i" -> to enter insertion mode and start typing some queries...

Press ESC to quit insertion mode them type F5 to run the script...

Connect to another database:

:let $PGDATABASE = 'yourdatabase' 

Do some query, transactions, list tables with \dt, list databases with \l, schemas with \dn, learn more about postgresql client with \? and much much more... , ...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.