[arthurschreiber]

As described in the blog post, large paths of our database schema have grown in an organic fashion. These virtual partitions allow us to prepare our database access for splitting out groups of tables into separate clusters in the medium term, with sharding those clusters being a next step and a long term solution to handle our database growth.

[jayd16]

I understand the desire, but why were db users with rights for specific tables or separate databases not enough separation for your interim testing.

What did the virtual partitioning add that those other techniques could not provide?

[arthurschreiber]

We could enable the linter in production to silently log problematic queries without actually affecting their execution.

If we used separate db users as you're suggesting, any query that we didn't catch beforehand (e.g. via our CI builds) would cause noticeable problems for our users, which is something that we want to avoid.

Additionally, switching to a separate user account would require holding open twice the amount of connections to each database server (old db user plus new db user), which probably would be fine but is still a lot of additional connections at our scale.

[nycjay]

Would it double the number of connections? If you are doing the same volume of total work and not looking to increase concurrency, wouldn't you end up with something closer to two connection pools with ~1/2 the size compared to one connection pool with your old size?

[eska]

I think his suggestion was to use service-specific users with limited grants only in the development environment to catch queries that access tables of other services. The production environment would keep using users with grants on all services‘ tables

[nycjay]

I see a lot of value of using separate users in both dev/test and production environments. That gives you an "easy" way to physically separate your database/schema into multiple databases with minimal changes to your application other than pointing your connection pools/config to the new database endpoint. We do this often - separating the task of breaking up our monolith databases with two phases - logical then physical.