Feasible to have thousands of users in Postgres?

Question

We are creating SAAS where we will at most have 50.000 customers. We are considering creating a user in the Postgres database for each customer. We will map each user that logs into our service to a user in the database in order to be very sure that they only have access to their own data. We also want to implement an audit trail directly in the database by this solutions, which utilizes triggers. If each customer has its own database user, then it would be very easy to see who did what, even if two customers would share the same data.

Will we be running into some unexpected problems because we have 50.000 users in our database? Performance-wise or administration-wise. Maybe connection pooling would be more difficult, but I do not really know whether we would need it.

Answer 1

Yes, it should be fine. You should use connection pooling though, as pg uses a fair amount of memory per connection (about 10MB AFAIK).

More than 500 simultaneous connections per box will be a problem though (like actively querying the database at the exact same time). More cpus/cores is better. Use SSDs with RAID 10.

Your SaaS application should connect as one user, then set role to the real user. This allows you to use connection pooling, as the connection string will be the same, but use different users. You should reset role when returning connection to the pool. Details here

This is not really database authentication. It's proxy authentication (aka Impersonation).

You could also consider separate pools per company or per role.

To make admin easier, you can put users into groups and set permissions via groups. This is called RBAC.

Update: I was able to create 50,000 users in 2.4 seconds. PGAdmin III is noticeably slower, due to the number of users. However connecting via JDBC is as fast as before. I was unable to drop 50,000 users at once, but could do about 10,000 at a time.

Answer 2

Performance : thousands concurrent connection will eat up Your memory, approximately a value above 1,000 concurrent connections advised to use connection pooling, pgbouncer is a good one, developed by skype.

Administering : Administer 50,000 users will be a big job IMO. How about differentiate costumer with same data access using different application_name, so each costumer will connect to database using same user name.

Example :

using different user name, the connection string of each client would be : --user user1, --user user2, etc.

But using different application_name, the connection string of each client would be : --user user1 --application_name costumer1, --user user1 --aplication_name costumer2, etc.

The application_name is recorded in pg_stat_activity and could also be logged. I think that would be easier to implement. And the application_name is also logged in the audit trigger you want to apply. More details here.

Hope it helps.