How many tables are there in a database?
The number of tables in a relational database is a good measure of the complexity of a database, so it is a simple way to get to know any database. But the complexity of what? Well, a complex database may have been designed to be deliberately flexible in order to cover a variety of business situations, or a complex business process may have a limited portion of its details covered in the database. So, a large number of tables might reveal a complex business process or just a complex piece of software.
In this recipe, we will show you how to compute the number of tables.
How to do it...
From any interface, type the following SQL command:
SELECT count(*) FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','pg_catalog');
You can also look at the list of tables directly, and judge whether the list is a small or large number.
$ psql -c "\d" List of relations Schema | Name | Type | Owner --------+----------+-------+---------- public | accounts | table | postgres public | branches | table | postgres
How it works…
PostgreSQL stores information about the database in catalog tables. They describe every aspect of the way the database has been defined. There is a main set of catalog tables stored in a schema, called
pg_catalog. There is a second set of catalog objects called the information schema, which is the standard SQL way of accessing information in a relational database.
We want to exclude both of these schemas from our query, to avoid counting non-user objects. We excluded them in the preceding query using the
NOT IN phrase in the
Excluding partitions from the count is more involved. The information schema shows partitions as the same as tables, which is true for PostgreSQL, so somewhat misleading. So, what we want to do is exclude tables that are also partitions. Partitions are marked in the
pg_catalog.pg_class table, with a Boolean column of relispartition. If we use
pg_class, we also need to exclude non-tables and ensure we don't include internal schemas, which leaves us with this much more complex query:
SELECT count(*) FROM pg_class WHERE relkind = 'r' AND not relispartition AND relnamespace NOT IN ( SELECT oid FROM pg_namespace WHERE nspname IN ('information_schema','pg_catalog', 'pg_toast') AND nspname NOT LIKE 'pg_temp%' AND nspname NOT LIKE 'pg_toast_temp%' );
Note that this query shows only the number of tables in one of the databases on the PostgreSQL server. You can only see the tables in the database to which you are currently connected, so you'll need to run the same query on each database in turn.
Of course, you can't always easily tell which tables are entities, so we just need to count the tables. Some databases use a lot of partitions or similar tables, so the numbers can grow dramatically. I've seen databases with up to 200,000 tables (of any kind). That's not recommended, however, as the database catalog tables then begin to become awfully large.