Should I in general partition tables by tenant IDs in a multi-tenant system? We're building a system where the data in 10 tables is associated with Accounts. A typical table looks like this:
create table Things(
    accountId varchar(64) not null,
    internalId varchar(64) not null,
    externalId varchar(256) as (concat(accountId, '-', internalId)) stored,
    ...
    primary key (accountId, sourcedId),
    unique (externalId),
    foreign key (accountId) references Accounts (id)
);

All queries either have an accountId in the where clause, or they use externalId. There are no cross-account queries.
We're expecting to have a total of 200 accounts. The sizes of the other tables (like Things) vary from 5 rows per account for some tables (1000 rows total) to 225K rows per account for some other tables (45M rows total). (These are the numbers we use for performance testing - they're the max numbers)
The DB size is ~150 GB. 95% of the scenarios are reads.
The RDBMS is Mysql 8.0.16 (AWS RDS).
We don't have any performance issues at the moment and we're not trying to make anything work faster. But I'm wondering if NOT partitioning the tables like Things by accountId is a &quot;premature pessimization&quot;?
Partitioning is often more about administration (swapping partitions in and up while updating a warehouse, etc.) than performance in general OLTP scenarios and, especially if all your data is on the same drive/array, can result in a performance decrease.
If you need partitioning later you may choose to do it by a different property, perhaps partitioning such that newer, more active, data can be kept on your fastest drives. If this is the case then you have made more work for yourself as you will need to unpick the existing partitioning scheme before implementing the new one.
So while this is at least a little opinion and not 100% fact, I would say partitioning now is premature.