Both MySQL and PostgreSQL support table partitioning, that is splitting a single table into multiple tables based on a certain rule. Our best case would be to have per-collection partitions. Querying all items from a specific Collection (the most common case in Akonadi) would be ridiculously fast as the database would not have to filter through all the other PimItems belonging to other Collections. Joining PartTable would also be very fast as we would be joining only Parts belonging to the queried Collection. Could also solve a problem on very huge tables when the entire index does not fit into memory at once. At the same time the partitioning is transparent to clients, so selecting PimItems belonging to different Collections in a single query does not require any special handling. Insert does not require the client to know the name of the partition table either.

Pros:

• much faster listing of Collection content
• better parallel ItemSync, the database can efficiently lock only respective partitions

Cons:

• lots of tables (one per Collection)
• we have to manually keep partitions in sync with existing Collections
• different handling for MySQL and PostgreSQL

Oracle MySQL/MariaDB:

• New partition table must be created manually when a new Collection is created and dropped when Collection is removed
• The partitioning rule for INSERT is part of the partition table definition, no need for a trigger function like with PSQL
• Adding/removing partitions requires reparitioning the entire table, could turn out to be rather expensive,

PostgreSQL:

• PimItemTable becomes a "master" table - an empty table without any indexes
• Partition tables would be called PimItemTable_ColXX and would inherit from PimItemTable
• New partition table must be created manually when a new Collection is created and dropped when Collection is removed
• PimItemTable needs a special trigger function with a huge if () switch to choose the right partition table, must be maintained manually

SQLite:

• Does not support partitioning in the same sense as the others
• Should not be a problem since partitioning is transparent to clients in MySQL/PSQL, so no special queries for SQLite are needed
• Can only split DB into multiple files and merge via "ATTACH DATABASE", only optimizes for DB file size, won't help with query performance

Links:

• http://dev.mysql.com/doc/refman/5.7/en/partitioning-management.html
• https://mariadb.com/kb/en/mariadb/partition-maintenance/
• https://www.postgresql.org/docs/current/static/ddl-partitioning.html
• https://www.sqlite.org/lang_attach.html