Tanager primary-storage table design document

[bradbanister]

The purpose of this discussion is for the team to provide feedback and ask questions about the draft primary-storage table design document. PR: #8

A brief document overview:

• The document begins with a list of problems a design should solve. In summary: reference validation, query support, security, streaming out, simplicity of code base and developer experience, and performance.

• The document lists several alternative designs as Options A-D.

  • Option A is the original design most like Meadowlark and was originally the preferred alternative. It has many strengths, including working well with partitioning, but its primary weakness is that GUIDs are clustered primary keys. The SQL Server community generally is not a fan of this. Option A spends some effort explaining that we've thought about this and why it should be ok. All the other options use Option A as a starting point, so it's important to understand it.
  • Option B is what Option A would become if the community said "no way on GUID PKs, go back and remove them". This was not a part of the original brainstorm, but I decided to anticipate that reaction and see what I could come up with. I went into it thinking the result would be inadequate, as I thought that with sequential BIGINT PKs our access patterns would require seeks into every partition, as opposed to narrowing to a single one, and require at least one ugly index across all partitions. But, I eventually found a way to partition without deriving the partition key from a BIGINT PK, and to have only two GUID columns total. Option B can narrow to one partition for all our access patterns, and while those patterns require two GUID indexes, they can be both non-clustered and partition-aligned (index-per-partition rather than across all). This design should make the SQL Server community happy and is now my preferred alternative.
  • Option C is a one-table-per-resource design, which actually explodes in complexity but is what most people instinctively think of first. The problem is that you also need one-table-per-relationship join tables, and so now you need code that knows all the join tables. Also, all these tables are redundant as we already have this information. The best reason for doing one-table-per-resource is as a partitioning scheme (without doing actual partitioning) to reduce table sizes. Regardless, this is better suited to a code generation approach which we are trying to avoid (for simplicity of code base and developer experience).
  • Option D is an attempt to fix the Option C weakness of the explosion of join tables by bringing back a single reference table. The problem here is that you can't use referential integrity to do reference validation. Meadowlark's biggest weakness turned out to be reference validation in code, which was easy to get wrong and was by far the most complex part of the codebase.

[stephenfuqua]

Comments are welcomed at this point. However, please know that we'll be developing a proof-of-concept for Option B in April to test out the feasibility.

[stephenfuqua]

SQL Server and PostgreSQL bigint: max value 9,223,372,036,854,775,807, size is 8 bytes.

Compare to uniqueidentifier: 16 bytes

Is the bigint big enough? Let's say that we have New York City. Using some published data and a bunch of guesswork...

Resource	# Records
Schools	1800
Students	1047895
StudentSchoolAssociations	1047895
StudentEducationOrganizationAssociations	1047895
StudentEdOrg.Race	1100290
StudentEdOrg.Languages	147753
StudentEdOrg.Disabilities	219010
StudentProgramAssociations (ELL)	147753
StudentProgramAssociations (504) (guess)	219010
Contacts (guess)	1571843
StudentContactAssoications	1571843
Staff (75k teachers + guess)	85000
Sections (wild guesss)	864000
StudentSectionAssociations (8/student)	8383160
StaffSectionAssociations (4/staff)	340000
StaffSchoolAssociations	85000
GradeBookEntries (20/section?)	17280000
StudentGradeBookEntries	1.81076E+13
StudentSchoolAttenanceEvents	(180 days) 188621100
SectionAttendanceTakenEvent	155520000
StudentSectionAttendanceEvents	1508968800
StudentAssessments	(2 per student?) 2095790
StudentAssessments.Items (20 per assessment?)	41915800
---	---
Total	1.81096E+13
Budget	9.22337E+18
Remaining	9.22335E+18

There may be other records, but these seem like the biggest tables. Even if I missed a few big tables, they wouldn't make much difference in the overall "budget" of available sequence numbers.

Sometimes a SIS vendor deletes and recreates a record instead of updating it. I don't know how often this happens. Let's say that did that with every record above on all 180 school days. That's still 3.3x10^15 records, much less than the available 9.2x10^18.

States probably collect a lot of program participation that isn't in the analysis above. Let's say that the program participation ends up being 2 programs * 180 days * # of students. And to be conservative, leave the in the demographics, even though many states wouldn't collect that.

Change the number of students to 5,852,500 (California). And again, what if all records were deleted and recreated every day? Then we would have a total of 1.8x10^15, still several orders of magnitude smaller than bigint.

The bigint approach looks safe. The range is so large that the analysis above could be missing large swathes and it still wouldn't make a difference. You could have 10 times the number of attendance records and the number of records would go up by 1x10^13 only. Very safe looking.

[stephenfuqua]

If we go with Option B, would it still makes sense to have a UUID as the record's unique identifier for the outside world? Not the primary key used for indexing perhaps, but an extra key for the URL? Or is this just redundant, with it being safe to use the bigint value? The numeric version is 19 characters, less than a UUID, so still beneficial for any system that ends up storing a string (i.e. the URL itself).

[stephenfuqua]

Current design, based on Option B, is in Primary Data Storage.