Although this question has been asked many times on stackoverflow but none of them fit my situation. I have an application which possibly going to have 2-3 million student records. Each student is going to have somewhere around 250 attendence records in a year and that makes me worried about the database scalability and performence. Even with monthly partitioning and db pruning, each partition would approximately store 2,000,000 * 25(apprx monthly school days excluding holidays) = 5,00,00,000(50 million) records. Is it optimal for postgres? or I should look for alternative architectures or db stack.

For example MongoDB is able to handle nested object array efficiently with query capabilities(not relevant) which greately reduces number of records for attendence.

Here is a Prisma mongodb database schema:-

model StudentAttendence {
  year             DateTime
  studentForeignKey String
  id               String       @id @default(auto()) @map("_id") @db.ObjectId
  email            String
  attendence       Attendence[]

type Attendence {
  date   DateTime
  status AttendenceStatus
  remark String?

enum AttendenceStatus {
  PRESENT  @map("1")
  HALF_DAY @map("2")
  ABSENT   @map("3")
  LEAVE    @map("4")

What would be the most scalable architecture with upto 3 million student records?

  • Postgres and MySQL also has datatypes which support JSON and array data.
    – prasad_
    Commented Jun 24 at 3:12
  • 1
    That should be no problem for PostgreSQL. Don't even think of using JSON there. Commented Jun 24 at 6:14
  • I wont use JSON as I know it's drawbacks but what about MongoDB? Commented Jun 26 at 5:48


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.