Database table size limit

@lbecket Thank you so much for the answer, presumably the design is at its best, the index are set and everything is okay, since using serverless aws the hardware shouldn't be a problem.

Yet I still don't know if there is a table with multi billion records in it, inserting one more removing, updating one row, getting 10 rows of it with a join to another table (at max 3 or 4 tables get joined which also each can be huge) with proper indexing, would these be pretty slow because of those many records or pretty fast because it is handled somehow by aws or vapor? Considering the queries are simple.

@stevemoretz Maybe this is a cop out on my part, but can you load these tables and just test it? If it performs, then you've got your answer. If not, then you can start investigating alternatives.

@lbecket Thank you for your reply again, actually this is an investigation before getting the vapor, if I had it already I would've tested it. Your idea makes a lot of sense. But I'm just starting a new project which is going to turn like that (billions of rows) eventually, and I need to know what to do about it beforehand so I won't get myself in a lot of trouble.

@stevemoretz There are many ways to speed up a big database. One thing you can do is use partitions on your tables that way they are divided into several smaller pieces and thus keeping the speed when doing queries.

However why in the world would you have billions of rows in your tables?

@Tray2 hello Tray, thank you for your response, I've thought of partitioning which was my last resort, I thought when doing a query like a search upon all your data, ie all your partitions, either run several queries and union them or join them all and run one query, in fact I thought laravel vapor would do something like this by default, yet it has a lot of edge cases such as, you can not sort easily across multiple tables so this really limits the usage to the application.

In response to your question we're trying to build a social media platform like Instagram, as an example the posts in Instagram always are available and old posts don't get deleted, it has millions of users and billions of records.

What if we use Mongo DB instead, since Mongo DB in nosql and has a non official but pretty good driver for laravel, it could be used but I'm not sure this is the first time I've ever considered using a nosql database.

I've read multiple articles mentioning nosql are used in these cases, yet I don't know if that driver would even work on the serverless technology, that's why I really need your opinion, I'm kind of lost here.

By the way I was talking about this library : https://github.com/jenssegers/laravel-mongodb

@stevemoretz I never used MongoDB but I'm pretty well versed when it comes to Oracle and MySQL/MariaDB and I would stick with a RDBMS (Relational Database Management System).

If I were to keep everything in a single table I would partition it most likely per month in the beginning and then maybe if needed go down to every week. You will probably need more than one database as well to keep up with millions of users.

Another way is to move records older than a certain time to an archive table and have that one partitioned.

If you launch this and it gets popular you need to get some specialist help on databases and database clusters.

However here is a start https://www.mysql.com/products/cluster/start.html

@stevemoretz I realize that this doesn't address the question that you're asking, but I'm assuming that you don't yet have millions of users, which means that you don't yet have billions of records. Instead of trying to solve a problem that you don't have, perhaps you should build the platform with a technology that will meet the immediate need and then migrate to a more robust architecture as you start to push the limits of the initial one. I don't mean to be dismissive of the question, it's a fair one, but if I were facing such a scenario, then I would try to get the thing launched first and then go hire developers smarter than myself once my user base grew to a point where I could justify/afford it. It's good to plan ahead, but it seems to me like you're focusing on a problem that won't arise until much later.

@Tray2 even though I totally agree with you and I love relational database as well, I'm still doubting because of all that extra work you just mentioned comes with it, nosql sucks for small projects but many people think it would be more suitable for these kinds of projects.

Yet that archive atrichecture you just mentioned gave me a new way of thinking and now I probably can do it with mysql just fine, just a little harder implementation, but I could always migrate later if it couldn't work :)

About the multiple databases, could you tell me what makes us to use a separate database, is there any limits for reads and writes to a particular database? If yes that would be a problem because many people need to read and write to the last partition anyways right?

@lbecket actually what you are saying makes total sense so you agree too that I start with mysql for now and later if needed migrate to mongodb?

The only reason I asked the question right now was that I'm afraid I screw up the code base and the migration would be hell, otherwise I wouldn't worry.

So makes sense to use only features that, Mongodb supports, on mysql and I'd be fine, makes sense? Or am I wrong?

@stevemoretz That logic seems sound. I don't know enough about MongoDB to be able to speak to any potential issues that might arise when refactoring a code base that has been written for MySQL. Maybe that's a good place to start your research: conversion from MySQL to MongoDB. But again, when you get to that point you'll likely want to bring in a specialist, so I personally wouldn't be too worried about it at this stage.

@lbecket I totally agree

@stevemoretz A database cluster work in similar ways as a load balancer.

For a high performance application you don't only have one web server hosting the application, you have several behind a load balancer.

It's a similar thing with a database cluster, it spreads the load between the databases so not all requests goes to the same database server.

@stevemoretz Well, @tray2 has more "best-replies" than me, so I need it more for my fake internet points and my fragile ego. No, he's clearly more knowledgeable here and I'll count it as a partial win to have his chosen answer be the one agreeing with me. That's actually hilarious.

Glad to be able to help. Hope the project is a huge success and that you can report back to let us know how the migration went!

Edit: I see that you changed the selected answer. Boooo!!!

Edit2: Now we're talking... if I can't win with knowledge, I'll just shame you.

@lbecket I was doubting what to do there lol, if you need it more there you go, I hope it's fine with him too, I chose him because he explained more that's all,but you were equally helpful.

@stevemoretz It's all in good fun... I'm just playing around. What matters is that you got the help you were looking for. This forum really is amazing that way. Never seen another like it.

@lbecket totally agree, but I wish it had the upvote button just like stackoverflow.Maybe count that like button as upvote? I don't know I'm new. But it's a fantastic forum.