[tor-bugs] #13803 [Ooni]: Better and more efficient database schema

[Tor Bug Tracker & Wiki]

#13803: Better and more efficient database schema
--------------------+-------------------------
 Reporter:  otr     |          Owner:  hellais
     Type:  defect  |         Status:  new
 Priority:  Medium  |      Milestone:
Component:  Ooni    |        Version:
 Severity:  Normal  |     Resolution:
 Keywords:          |  Actual Points:
Parent ID:          |         Points:
  Sponsor:          |
--------------------+-------------------------
Changes (by tyler):

 * severity:   => Normal


Comment:

 Hello,
 I will be working with @hellais to improve the database schema used by
 ooni-api and ooni-pipeline to allow for more efficient queries - to be
 more specific, we're going to be moving to a relational model which uses
 PostgreSQL, and materialized views to allow for analytic queries to be
 performed.

 To be short, and sweet, I've proposed the use of a star schema to store
 individual test metrics with materialized views to store test-specific
 metrics (i.e. a materialized view for the `dns_consistency` test results,
 and one for `bridge_reachability` and so on, and so forth).

 The suggested means of maintaining these materialized views is to create
 an indexed view which rebuilds when the underlying tables change. Feedback
 is welcome, and always appreciated.

 • More details are available in this ticket:
 https://github.com/TheTorProject/ooni-api/issues/4
 • Proposed API specification for OONI: https://github.com/TheTorProject
 /ooni-api/wiki/API-Design

--
Ticket URL: <https://trac.torproject.org/projects/tor/ticket/13803#comment:2>
Tor Bug Tracker & Wiki <https://trac.torproject.org/>
The Tor Project: anonymity online