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
pg_catalog
andinformation_schema
contain information about objects. For a starter to build your queries, you may usepsql
(\dt
,\di
and other commands), doing a\set ECHO_HIDDEN on
beforehand.