Best Practices for Creating Databases on Demand in Multi-Tenant Systems

A good way to manage databases is to maintain a ready pool. I usually have about a dozen databases provisioned ahead of time. When someone signs up, I just assign one from the pool. Plus, I keep refilling the pool to ensure there's always one ready to go.

Since you’re dealing with only 1GB per tenant, you might want to evaluate a schema-per-tenant approach. This is often more cost-effective and makes management easier. A shared database can be set up with tenant IDs in tables, making it manageable while reducing overhead. It’s also worth checking AWS costs to ensure your pricing model covers all expenses; isolated databases can be pricier than you think!

To kick things off, how many clients are we talking about? If it’s a small number of valuable clients, consider creating an entirely new AWS account just for them. This method offers maximum isolation and security, especially if data access controls are crucial. If you’re looking at around 500 clients with less than 1GB data each, that’s definitely manageable, but it could get tricky with multiple databases—each one having the same structure, of course.

Generally, the one-database-per-tenant process looks like this:
1. When a user signs up, you trigger a backend process (like a Lambda function).
2. Then you provision a new database for that user's schema.
3. Next, run migrations to set up the necessary structure.
4. Finally, store the database connection details in your main app DB for easy access.

Make sure to keep the database creation process asynchronous to keep the signup experience smooth.

Using the AWS CDK can really simplify the setup. Loop through your clients and create their respective users and databases using cdk-rds-sql. You can trigger a pipeline with your signup form to automatically provision these databases.