Multi-schematic Postgres on Heroku

Question

I am expanding an existing Rails application and I need to add support for multiple tenants. I did some reading, and when I saw how this application would be hosted on Heroku, I thought that I could take advantage of Postgres multi-circuit functionality.

I read that it seems that some backup performance issues are being used when using multiple schemes. This information I felt was a bit outdated. Does anyone know if this is still the case?

Also, are there any other performance issues or reservations that I should take into account?

I was already thinking about adding a field to each table, so I can use one scheme and have a link to the field for the tenant table, but when using time windows, several schemes seem to be the best solution.

Answer 1

I use postgres schemes for a multi-tenant site based on some work by Ryan Bigg and the “Flat” jewelry.

---

https://leanpub.com/multi-tenancy-rails

https://github.com/influitive/apartment

---

I find that for each client there are separate schemes, an elegant solution that provides a higher degree of data segregation. Personally, I believe that performance improves because Postgres can simply return all the results from a table without filtering on "owner_id".

I also think that it simplifies migration and allows you to configure individual client data without global changes. For example, you can add columns to specific client schemas and use function flags to enable custom functions.

My main argument regarding performance will be that backing up is a batch process, while client table coverage will be on every access. Based on this, I would jeopardize the effectiveness of backups, slowing down the work with clients.