Multi-tenant Postgres In The Real World

Back in the days when MySQL was the undisputed open source database champion, there was a sense that people who took their databases seriously would choose PostgreSQL instead. Everyone else chose MySQL; it was fast and it was easy.

Today, Postgres seems suddenly popular. Not only has Postgres remained the choice of database aficionados, thanks to behaving more precisely as a relational SQL database should,  but these days it’s just as fast and easy as MySQL ever was. In the most recent Stack Overflow developer survey, Postgres took the “second most used database” spot from Microsoft SQL Server. Yet its popularity is not just in the scenarios where MySQL once thrived. Increasingly, Postgres is finding a home in very large deployments that might previously have gone to a proprietary database management system.

One such very large deployment lives at Heroku. Heroku is one of the largest providers of managed PostgreSQL and, so, runs one of the world’s largest multi-tenant Postgres installations with more than 2 million customer databases spread across tens of thousands of individual AWS instances.

What does it take to run so much PostgreSQL for so many people?

The challenges of multi-tenancy

There are plenty of large databases out there that have many users. In those situations, application code is usually enough to prevent one user from encroaching on another’s data. Think about booking airline tickets. All the people using a particular ticketing site will be reading from and writing to the same database, each without having access to their fellow travellers’ data. It’s only the people who run the ticketing site that have admin rights to the database.

Multi-tenancy is different. In a multi-tenant environment, many databases live on the same server. Each database owner has admin rights over their database and that database has its own set of users. Separating the concerns of one tenant from the next has to happen at a more fundamental level.

Commonly, that separation is there to overcome three challenges:

• Security
• Uncomplicated upgrades
• Noisy neighbours.

In a true multi-tenant scenario, Heroku would run multiple customer databases on one Postgres instance and use rights management to prevent one customer from interfering with another’s data. Even if that were a perfect solution to the security issue, and it’s not, then it would do nothing for the other two challenges.

So, how does Heroku manage those issues when hosting so many Postgres databases?

The answer to multi-tenancy might be single tenancy

At Heroku, we’ve found that for development and testing instances a multi-tenant approach offers a good trade-off. However, production databases are different. Their stricter performance requirements alone make multi-tenant operation tricky. That’s why our production-grade Postgres databases run as stand-alone instances.

Behind the scenes, each production Heroku Postgres instance runs in its own LXC container. This complete separation of customers deals neatly with the security challenge, but what about the twin problems of capacity optimization and noisy neighbours?

Heroku’s internal service orchestration tool is a vital component in ensuring that individual Postgres instances get the resources they need and that they don’t encroach on other customers’ resources. For example, if an instance fails, the orchestration tool automatically replaces it with an equivalent; if there’s a high availability follower, the orchestration tool uses that as the replacement.

Scaling (almost) infinitely

Running huge numbers of Postgres databases in this way enables Heroku to scale their already enormous PostgreSQL operation to even greater numbers of instances and customers. For a similar story, read how we at Heroku run our multitenant Kafka service.