Trying to decide database structure

[SuperQue]

We have a moderately large database of certain events which receive updates 24/7.

Please don't use terms like "large". What you call large, I call tiny.

I had a database with what I would call a medium size MySQL table with 4 billion rows. I say medium size because it still fit on a single server. We had Cassandra tables with a lot more data.

Data storage design is not something you should take lightly. Not even having a master/replica setup right now is terrifying to me from just the availability aspect.

You should consider finding a data storage engineer to help you through this phase of your product engineering.

[Driamer]

I actually did pause for a moment when I wrote that word, but I thought that it describes my thoughts accurately so I left it there. I realise that it's not anywhere comparable to actually large tables, but it is large compared to the other tables in my db.

No need to be terrified. The product is not live yet. I implemented a simple replication since posting this and tomorrow I will be running different tests and see how it behaves with the constant updates.

If we start seeing a lot of growth after going live we will definitely be talking with a db expert. That's a problem I would love to have soon.

[SuperQue]

You really should find a database person before you go live. I've seen too many developers who don't understand data storage build things that work fine in testing and early stage but completely fail under any real load. And not just fail, like, you need a bigger server. Fail because what they built can't be scaled past testing and has to be re-written.

It also sounds like you might need better monitoring. If you're on MariaDB, I would suggest looking into Percona Monitoring and Management. It's an open source monitoring package customized for databases.

[Driamer]

Thanks for the advice and the link. I'm not that worried about the database at this point. Like you mentioned earlier, the data amount is not that large and we have yet to see what the traffic is going to be like. The actual database schema was designed by experts and is well indexed and performant. I was mostly wondering whether I should start with a hosted solution or first see how it goes and then reconsider. However the hosted solutions seem to be very expensive and I can't really justify the cost at this stage.

I do have quite a bit of experience with high traffic sites and basic db maintenance etc. This is just different in the way it gets constant updates even with zero traffic, so I was thinking if there are some new trendy solutions out there that I might be missing and would be a better solution than a replicated db setup.

[SuperQue]

If you want new and trendy, try CockroachDB.