Too many tables are bad for you
The article explains that having an excessive number of tables in a PostgreSQL database can lead to performance issues, increased maintenance complexity, and slower query planning due to catalog lookups and statistics overhead. It recommends keeping the number of tables manageable by using partitioning, schema organization, or consolidation strategies.
Background
PostgreSQL is an open-source relational database used by many companies and organizations. A core design feature is that each table stores data in multiple "pages" (typically 8 KB blocks), and the database maintains a lock on the *relation* (table) when certain operations are performed — for example, when collecting statistics or creating a backup. A lesser-known but important PostgreSQL internal is that autovacuum (the process that reclaims storage occupied by dead rows) also needs to hold a lock on each table it processes.
The database tracks all its tables and indexes via internal catalogs and needs to scan these catalogs frequently. If a database has tens of thousands of tables (common in multi-tenant SaaS apps that create a separate set of tables per customer), these catalog scans and lock acquisitions become a massive overhead. Instead of spending resources on actual queries, the database spends CPU and I/O just enumerating and locking all those tables, causing performance degradation across the board. The article's author explains this hidden cost and recommends keeping the number of tables in a single database well below a few thousand.