The bit on the database performance issues leads me to my hottest, flamiest take for new projects:
- Design your application's hot path to never use joins. Storage is cheap, denormalize everything and update it all in a transaction. It's truly amazing how much faster everything is when you eliminate joins. For your ad-hoc queries you can replicate to another database for analytical purposes.
On this note, I have mixed feelings about Amazon's DynamoDB, but one things about it is to use it properly you need to plan your use first, and schema second. I think there's something you can take from this even with a RDBMS.
In fact, I'd go as far to say as joins are unnecessary for nonanalytical purposes these days. Storage is so mind booglingly cheap and the major DBs have ACID properties. Just denormalize, forreal.
- Use something more akin to UUIDs to prevent hot partitions. They're not a silver bullet and have their own downsides, but you'll already be used to the consistently "OK" performance that can be horizontally scaled rather than the great performance of say integers that will fall apart eventually.
/hottakes
my sun level take would be also to just index all columns. but that'll have to wait for another day.
- Design your application's hot path to never use joins. Storage is cheap, denormalize everything and update it all in a transaction. It's truly amazing how much faster everything is when you eliminate joins. For your ad-hoc queries you can replicate to another database for analytical purposes.
On this note, I have mixed feelings about Amazon's DynamoDB, but one things about it is to use it properly you need to plan your use first, and schema second. I think there's something you can take from this even with a RDBMS.
In fact, I'd go as far to say as joins are unnecessary for nonanalytical purposes these days. Storage is so mind booglingly cheap and the major DBs have ACID properties. Just denormalize, forreal.
- Use something more akin to UUIDs to prevent hot partitions. They're not a silver bullet and have their own downsides, but you'll already be used to the consistently "OK" performance that can be horizontally scaled rather than the great performance of say integers that will fall apart eventually.
/hottakes
my sun level take would be also to just index all columns. but that'll have to wait for another day.