I've got a multi-tenant app on postgres 9.5 DB with some 200+ schemas each with about 170 tables on. pg_dump operations have become a frustrating hit and hope exercise where I fiddle with max_locks_per_transaction and shared memory and eventually the dump runs, but this is based on me doubling the values in the various files with no science behind the number other than new_value = 2 * current_value.

I noticed this blog post where Josh Berkus says:

Given that a lock in Postgres is usually a lock on an object (like a table or part of a table) and not on a row...

So I'm wondering if there's a way to calculate the objects in a given DB? That way I could start to calculate:

  • The required amount of locks and shared memory required
  • The growth over time of the values
  • The tables and views in pg_catalog and information_schema contain information about objects. For a starter to build your queries, you may use psql (\dt, \di and other commands), doing a \set ECHO_HIDDEN on beforehand. – dezso Oct 10 '16 at 8:14

Your Answer

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

Browse other questions tagged or ask your own question.