3 ms·

I really like SQLite but I wonder if what we are seeing here is a need for conventional OLTP databases to be able to unload parts of indexes from memory. Becau

I really like SQLite but I wonder if what we are seeing here is a need for conventional OLTP databases to be able to unload parts of indexes from memory. Because essentially what’s happening with database per user is that we don’t keep anything in memory for users who are inactive, or only active on another instance, for essentially the same schema (or maybe a couple versions of a schema). This is starting to smell like the JSON situation with Mongo. Where Postgres is now twice as fast as Mongo at the thing Mongo is supposed to be an expert in.

bob1029 2y ago
> Because essentially what’s happening with database per user is that we don’t keep anything in memory for users who are inactive, or only active on another instance, for essentially the same schema (or maybe a couple versions of a schema). The benefits of prefetching and page-level abstractions are certainly reduced when every tenant is guaranteed to live in a different file. I would agree that you should have to deal with an uphill battle when arguing for a DB-per-customer architecture. There needs to be a special reason to not simply add a customer/tenant id column in each table. I think one good argument for placing a tenant per database file is that it makes total destruction of their information possible without any impact to other tenants. If you don't require immediate destruction of the tenant's information (i.e., can wait for background cleanup activities), then I would begin to push back.
julik 2y ago
I wouldn't say so, with use cases I saw the databases of tenants would almost never exceed available memory. A database could be loaded into memory in its entirety for the duration of the request, provided the disk IO on the server is cooperating (i.e. "does not degrade to floppy drive speed once you are out of IOPS credit").

bob1029 2y ago

> Because essentially what’s happening with database per user is that we don’t keep anything in memory for users who are inactive, or only active on another instance, for essentially the same schema (or maybe a couple versions of a schema). The benefits of prefetching and page-level abstractions are certainly reduced when every tenant is guaranteed to live in a different file. I would agree that you should have to deal with an uphill battle when arguing for a DB-per-customer architecture. There needs to be a special reason to not simply add a customer/tenant id column in each table. I think one good argument for placing a tenant per database file is that it makes total destruction of their information possible without any impact to other tenants. If you don't require immediate destruction of the tenant's information (i.e., can wait for background cleanup activities), then I would begin to push back.

julik 2y ago

I wouldn't say so, with use cases I saw the databases of tenants would almost never exceed available memory. A database could be loaded into memory in its entirety for the duration of the request, provided the disk IO on the server is cooperating (i.e. "does not degrade to floppy drive speed once you are out of IOPS credit").