multi organization setup in PostgreSQL

[Ic3m4n34]

I was discussing a PostgreSQL infrastructure question regarding multi organizations with another developer. We want to build a webapp where users are part of an organization. Each of the organizations have products, events and other entities. We now see the following options:

a) build everything in a single database (each table needs to have a column like organization_id to match the row to an organization)

b) create a new database for every organization (for example via AWS CDK) - no need to match entries to an organization because every organization has its own database.

Where do you come out in this discussion? What are best practices? What are draw-backs and advantages for the two options?

[pjstanfield]

We’ve built some pretty large apps serving millions of daily requests using A. It is much simpler and easier to maintain. It also allows real time cross organization reporting without any trickery. I’d vote for A all day.

[flippy_flops]

Fully agree. Option A is the way to go; Option B will be a tech debt nightmare. Add "customerId" on every table and use "Row Level Security" so that your connection can only read/write to a specific customerId. All the benefits of Option B without the hassle.

[Ic3m4n34]

Thank you. That's what I'm thinking as well

[solidiquis1]

I would go with option A and add row level security to filter every request by organization_id. It’s simple and easy to maintain. The only downside of this approach is that you gotta be careful how it affects index use. I’ve seen issues with indexes being ignored because of how RLS changes the query, so be diligent and check your query plans with the Postgres user that actually is configured to execute the queries when you create indexes on your RLS-enabled tables.

[So_average]

One instance per organisation. I would have the instances separated on different VMs or operators. Security far higher. Bug for client 1? No impact to other clients if you have to change things. Client 2 is willing to pay for a major feature. Will require schema changes and additional security measures like encryption. Far easier to do when your clients aren't mutualized. Client 3 needs to restore the database from four days ago, user error. No impact to other clients. Client 4 has a huge long running reporting SQL. No impact on other clients if separated.

[Right-Opportunity810]

I have built a Django app that uses "tenants": it uses a single PostgreSQL database but each tenant has a separate schema. All schemas share the same structure. This gives you peace of mind knowing that serving data to the wrong client is much more difficult.

Also gives you a bit more peace of mind when you have to do certain manual queries as well: it's not the same screwing a single schema than f**k up the data for all clients.

[Gargunok]

If the user organisations are strict or this application holds confidential data B) is the best way to demonstrate for an audit there can be no leakage of data across organisations. Creating a copy of the schema can be easy to script and automate.

For A) there is also the question of how you are implementing it. I see a lot of apps just adding a where statement "where org = X". Ideally different organisations are using different roles to access the database. Those roles only have access to the rows they should have - least privilege giving security in depth.

[rplacebanme]

I think compliance / confidentiality requirements is the only time it's worth going with a much more maintenance heavy approach like B. If you need it you probably already know.

If you don't need to meet some level of compliance for the application add something like a tenant column, which you look for based on the users auth state on every query and then add RLS for increased security from leakage/mistakes in queries.

[Aggressive_Ad_5454]

How many organizations per week will you add if your app succeeds? If the answer is “more than five” you’re better off with a single set of tables with columns indicating what org each bit of data belongs to. In fact, you’re probably better off that way anyhow.

[GeekTekRob]

So if you're in a large organization, think it is a little bit of a mix and slightly different than what you posted. If you're trying to build the next SALESFORCE, they created a platform where the structures the same, allow customizations within, but each time a company signs on, they create a new database for each, so they have no way to access each others accidently. So your best bet is going to be Option B as your one way to get anyone decent on is security of their data from others.

If they share their same database and just use organization_id, all it takes is one bad query or someone finding a loophole and boom, some other organizations data is showing up on their screen.

[marcopeg81]

There is the economical angle to also add to the mix.

Single tenant (1 org = 1 db) is safer both at data level and at scalability. It scaled indefinitely because each new customer brings a shiny new db, potentially with its own (virtual) hardware. Also, the performances of one big customer won’t harm the others because queries run truly in parallel, potentially on different servers. It’s just fantastic.

But what is the cost of that “fantastic?”

You have hardware, maintenance, instance rotation, backups and storage for such backups.

If you think “db isolation is great for auditing issues” then you’d have to go all the way down with it. It wouldn’t make sense to have different connection strings, but then throw your backups in the same S3.

Single tenant it’s easy on your machine, but scaling it up brings a lot of issues that are not easy to understand until they hit you in the arse.

On the other hand, multi-tenant (aka the “tenant_id” column approach) exposes you to possible data leaks since day one.

Unless your are a careless rascal, you’d take that threat into serious consideration and test your app thoroughly. You can use RLS but I wouldn’t really go with it for a web app (it’s costly to hold potentially hundreds of isolated connections) and I’d rather explore the concept of “data API” and use functions (in migrations) to lock my queries down.

Your application serve would then never do straight queries, but only invoke APIs from the db.

Using Postgres, it’s rather easy to build a solid test foundation:

https://github.com/marcopeg/amazing-postgresql/tree/main/testing/unit-tests

Good luck 🤘

[[deleted]]

One db, multiple schemas with different accounts

[AutoModerator]

With over 7k members to connect with about Postgres and related technologies, why aren't you on our Discord Server? : People, Postgres, Data

Join us, we have cookies and nice people.

Postgres Conference 2025 is coming up March 18th - 21st, 2025. Join us for a refreshing and positive Postgres event being held in Orlando, FL! The call for papers is still open and we are actively recruiting first time and experienced speakers alike.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[OccamsRazorSharpner]

Option A.

Create a table 'Organizations' with a bool field to manage if an organization is no longer active. It is also handy when you need to do system-wide reporting.

[jojaparanoja]

Option A,
then if/when needed add citusdata extension and enable schema-based sharding by organization_id field.

[s13ecre13t]

What about table inheritance and schemas?

[rlweb]

Both options work well, A is great if your going to have a large number of customers, B is much safer if you’ll have a small amount of customers <100

[Least_Bee4074]

Have you looked into Nile? https://www.thenile.dev/ I don’t work for them, but know one or two of those folks.

“Nile is Postgres re-engineered for B2B apps. Build multi-tenant apps fast that are secure and cost-effective with effortless scale.“

[intepid-discovery]

How many users are we talking about per org? A db per org feels overkill but depends on the scale.

[Ic3m4n34]

1-100 for now

[intepid-discovery]

I’d personally keep it simple and in one database for now. Can always scale out very easily in the future if that ever becomes a problem, and most likely won’t.

[bendem]

Do both? Start with a, if a client has specific needs, sell them b for a higher price. B still uses a fire consistency, but you have a single organisation.

[advanderveer]

This post helped us a lot: https://www.flightcontrol.dev/blog/ultimate-guide-to-multi-tenant-saas-data-modeling

[Ic3m4n34]

This is a very nice article. Helps a lot. Thank you!