Using/querying multiple databases in parallel

[Sebastien <le...@gmail.com>]

In our current project, so far, we have decided to store data in multiple
databases, for a matter of data segregation/tenant isolation. The idea
being that different organizations can use the product and have their own
private databases. All of those databases contain documents having the same
schema.
In practice though, users can have access to one or more of these databases.

On the client-side, users can select the databases that they want to see in
the user interface. Based on this selection, we interrogate the relevant
databases to fetch results.

In the case of list views, this is complex because we have to query
multiple databases and then regroup the result sets, sort the data and
apply pagination. Of course this is really cumbersome to do on the
client-side and we would prefer to be able to directly perform paginated
queries against n databases at the database server level.

Do you think that this is a reasonable approach? Do you have ideas that
could simplify our life while maintaining a good isolation between tenants
and allowing us to query data from different organizations depending on the
access rights of the connected user?

Also, are there any plans to incorporate a way to query multiple databases
(with pagination) directly through CouchDB?

kr,
Sébastien D.

Re: Using/querying multiple databases in parallel

[Willem van der Westhuizen <wi...@kwantu.net>]

Hi Sebastien,

I do not know the busieness case, but in our case where we have a 
similar requirement, there are 412 databases (each representing a 
government body) and the user would select the government body first. 
Then the rest is easy. We have found the lucene search implementation of 
Rnewson to be very effective, and we use that almost exclusively for 
search and list views.

If the user needs offline access, we place those documents into a single 
peruser database. Each document has a meta-data tag which points to the 
database that is the original "owner" of the document, so that the 
saving and filtering can be done correctly.

With this approach, no querying accross databases are required.

Willem

On 2019/11/06 14:14, Sebastien wrote:
> In our current project, so far, we have decided to store data in multiple
> databases, for a matter of data segregation/tenant isolation. The idea
> being that different organizations can use the product and have their own
> private databases. All of those databases contain documents having the same
> schema.
> In practice though, users can have access to one or more of these databases.
>
> On the client-side, users can select the databases that they want to see in
> the user interface. Based on this selection, we interrogate the relevant
> databases to fetch results.
>
> In the case of list views, this is complex because we have to query
> multiple databases and then regroup the result sets, sort the data and
> apply pagination. Of course this is really cumbersome to do on the
> client-side and we would prefer to be able to directly perform paginated
> queries against n databases at the database server level.
>
> Do you think that this is a reasonable approach? Do you have ideas that
> could simplify our life while maintaining a good isolation between tenants
> and allowing us to query data from different organizations depending on the
> access rights of the connected user?
>
> Also, are there any plans to incorporate a way to query multiple databases
> (with pagination) directly through CouchDB?
>
> kr,
> Sébastien D.
>