0

For one of our SaaS services we're investigating which DB to use as we're re-designing it from scratch.

Our current solution uses MySQL and creates a seperate DB for each new customer. The current (global) structure is:

- globaldb.globaltable
    => some global data shared with all customers
    => big
    => it would be an option to flatten this data in the customerdb.tablewithreportlines, but this increases the size quite a bit

- customerdb.tablewithstaticdata
    => joins with `globaltable`
    => searched on several columns
    => no group by
    => writes throughout the day, in the thousands
    => reads on request by the customer via the application, so not continuesly
    => can be big per customer, serveral GBs

- customerdb.tablewithreports
    => searched on several columns
    => writes throughout the day, but only in the tens
    => reads on request by the customer via the application, so not continuesly
    => quite small

- customerdb.tablewithreportlines
    => joins with `tablewithreports`
    => joins with `globaltable`
    => most columns are 'searchable'
    => most columns are 'groupable'
    => writes throughout the day, in the thousands but only when processing the `tablewithreports` lines
    => reads on request by the customer via the application, so not continuesly
    => can be big per customer, serveral GBs

The customerdb data is never UPDATEd, but only INSERTed (and DELETEd occasionally).

We are preparing for rapid growth and need a structure which is ready for this. It is acceptable to have new instances (if needed) added manually.

We've earlier had a MySQL setup with a very large amount of tables (and databases) for a test project. That project failed as the server was exceeding its maximum file handlers for the MySQL tables. This was at around +-500.000 tables. This new project will definately need to be able to handle 500.000 customers and therefore 1.5 million tables (with this current structure).

The average size per customer database is +- 7,5Mb. No to much, but it is quite spreaded as serveral customers have multiple GBs in their DB.

I've searched SO and Google to find a matching situation, but weren't able to find it.

At this point we're open to any suggestion, both relational, NoSQL or a combination as we're doing a full re-design.

Question what is the best suited database for this use-case?

PS: this is my first post so forgive my I'm incomplete

Community
  • 1
  • 1
DMARC Analyzer - Michiel
  • 81
  • 1
  • 2

1 Answers1

0

I would suggest to consider using a common database for all the customers. This radically reduces the number of tables.

Database management systems were not designed for this large about of databases or tables.

Usually a table is one entity representing a collection of the objects of the same kind. So all the objects of the same kind (like customers in your case) should be placed in one table rather having a separate table for every customer.

Dennis Anikin
  • 961
  • 5
  • 7