What's the best way to split client sites in a PostgreSQL installation?

Question

I need to host several client sites using one PostgreSQL installation. What's the best way to split them -- by schema or by instance?

The factors would be what makes admin easier (especially backup/restore of individual sites), performance of the sites, and overall efficiency of memory usage.

Thanks Jason

Answer 1

The "best" way is heavily dependent on your use case, however back in my hosting days we used to simply give each client their own DB user which was DBO on their particular database, with appropriate permission & login restrictions (in Postgres and pg_hba.conf respectively) so they couldn't mess with anyone else's data.

Backup/Restore can be handled using pg_dump (dump each database individually so they can be easily restored without having to pick through the dump file), and performance/tuning is the same as any medium-to-large instance of Postgres.

---

Splitting by schema (within the same database) is also an option, but it doesn't enforce the same level of separation as a separate DB, and you obviously can't let clients create their own schemas (or do anything else that requires DBO) if you do this.

---

Splitting by instance (Separate Postgres server for each client) is the ultimate level of separation, but really requires an individual OS user for each client (so Postgres is running under totally separate users). This is also a huge memory/resource sink: Postgres (like most DB systems) should ideally be the only thing running on a server, and only one instance.