In a previous post, I shared a strategy for a multi-tenant event store. The strategy there is to break up the data by partitioning the events by tenant. As always, the devil is in the details. One thing I later thought to consider was how many tenants this would support. Since each partition is a separate table, I needed to investigate how many tables can be created.

Table Count Limit

Technically Postgres does not have a limit on the number of tables. However, each table is a file on the OS filesystem. And the OS probably has some opinion on how many files is "too many". In particular Linux has an open file limit. You can view it with the command below (in my distro).

cat /proc/sys/fs/file-max

When I checked a standard Amazon Linux EC2 t2.micro instance, this returned 96972. Some quick searching told me the number is calculated based on the amount of RAM. So then I thought to check an RDS Postgres instance. I could not get shell access, so the only way to test it was to create tables until it broke. When I tested on an AWS RDS Postgres 11 Preview instance (also t2.micro) I was able to create 1.3 million tables before it crashed.

As pointed out in the comments below (thanks Vincent Milum Jr!), the limit I probably ran into in this experiment was the inode limit. This limit is based on the size of the file system, rather than amount of RAM.

See this article for more about inode limits.

Reaching the limit

Well before I reached the crashing limit -- in the tens of thousands of tables -- pgAdmin became quite slow to load/refresh. To support a database with this many tables, I would probably need to use command-line tools. Since GUI tools tend to preload a lot of information.

When I reached the table count limit, VERY BAD THINGS happened.

The final CREATE TABLE operation received this error (both on the client and in the logs).

53100: could not create file "<file path>": "No space left on device"`

The RDS service also noticed the problem, and flagged the DB instance status as storage-full in the console. I don't know about you, but this error normally would have made me think the storage drive was full. But that wasn't the case. There was about 13GB of free space. I had used about 4GB just to create empty tables!

The really bad part was that the script I wrote to remove the tables could no longer connect to the DB instance. Apparently each connection creates temporary tables. And since no new tables could be created, no connections could be made either. Additionally, some internal processes of Postgres use temporary tables. For example, I saw in the logs that statistics calculations were failing with the same error.

Fortunately, I still had a connection opened in pgAdmin. So I started issuing DROP TABLE statements there. I had to drop about 2000 tables before my cleanup script could connect again. If that pgAdmin connection had not already been open, the database would have been inoperable.

Moral of the story: Going over this limit breaks your DB instance.

Conclusion

In this post, I wanted to make special note of the table count limit. Many other kinds of limits you might approach will degrade performance (CPU/mem) or are easily fixable / monitored (storage space). But the table count limit is seriously breaking. So keep this limit in mind as you think through your database organization strategy.