I am building a large web app that will help a "region manager" to manage multiple schools in multiple districts.
In total, there are about 400,000 students & teachers.
On top of managing the obvious things like grades, etc. We will also have to manage attendance (daily).
I am used to building web apps at a smaller scale, which I deploy to Heroku. Given a system of this scale, should I be thinking about using a non-relational DB from now or should I just stick to PostgreSQL and do specific optimizations to ensure high-speed and data integrity?
If it isn't clear, the main concern is one of the system being so slow for managing so many records across so many tables - in a relational db system.
Also, what are some common optimizations I can do to ensure speed - if the recommendation is to use a relational DB? The biggest, most obvious one is using indexes on the most commonly accessed information.....anything else like that would be greatly appreciated.
Thanks.
P.S. My team is split on what we should go with, so you guys will lend a useful voice in helping tip the balance :)
Stick with postgresql. Why would something else be better?
With the little info you provided, I can guess your performance is going to probably come down to two things:
Postgresql offers a datastore on disk. Caching pages with redis allows database queries and rendered parts of HTML to be cached in memory, as to avoid touching disk.