DDL Implementation Options

[kgodey]

Note: These notes are moved from our wiki, they're a couple of months old, and may not be congruent with the current conception of Mathesar. However, we still need to discuss how to handle splitting tables up, when to create views, etc. and it's useful to have these as a reference.

---

Options to discuss:

1. Map table creation, deletion or modification directly to underlying tables (create, drop, or alter)

   • Conceptually simple
   • Implementation of mapping new input in the original import format to the refined data model would be difficult (maybe this part could be done in python?).
   • If we avoid supporting the initial import format, this would be the "best" design (i.e., the most aligned with standard DBA practices)
   • If we want to support the initial import format, this would result in massive data duplication.
   • If we want to support the initial (or any multiple) input format, we'd need triggers all over the place to ensure data consistency.
   • Could be improved by keeping a table with a "script" of the DDL operations that transformed the initial import model into the current model.

2. Map table operations to underlying materialized views.

   • In Postgres, materialized views are standard tables, but they come with a couple of extra methods that let you regenerate them if you want.
   • Would be good, if we want to emphasize the ability to input more data in the original format and have it show up in the improved data model, since we'd be able to regenerate the tables with the new data included upon some appropriate command.
   • The previous point is only true if there are no intermediate steps or tables; otherwise, we need to either:
   • keep the intermediate views / tables, or
   • keep a "script" to regenerate everything
   • An alternative would be to automatically generate WITH statements as we create these views. It would be a bit gross SQL-wise, but would have the desired functionality.
   • Starting from Materialized View A (MVA), with a \dt+ containing WITH statements to generate MVA from the original input format, we add a WITH statement encoding MVA, along with an appropriate query to generate MVB from MVA.
   • It's not clear this would be functionaly distinct from keeping the "script" to regenerate everything, but it's somehow cleaner.
   • Is it possible to use recursive CTEs to handle continued input? Research needed.
   • Would lend itself to a kind of natural analysis of the steps taken to get to the current data model.
   • Would be difficult to support both input into the improved data model, and also input in the original format. We'd have to set up some intricate system of triggers to make sure data was consistent in the various places it would be stored.

3. Map table operations to underlying (non-materialized) calculated views.

   • The triggers from the previous options would apply here as well. In fact, they'd be enforced by PostgreSQL at this point, since it won't let us insert into these views in most cases otherwise.
   • This is the "safest" route, in that we'll have the DB system there to help us enforce consistency.
   • This will feel the most performant for small(ish) data, since everything would be done lazily.
   • This will quickly become the least performant option for large data sets under many circumstances, since we'd be recalculating all the transformations to produce a given data model any time it was queried.

4. Hybrid approach (Brent's favorite at the moment)

   For tables that we intend the user to be able to insert into, it might be best to use materialized views, with the intricate set of triggers in place to keep things consistent. It should be possible to iteratively generate these triggers as the user creates new tables under most (maybe all? not sure...) circumstances. For other tables, where input to the table makes no sense, we might want to just call them "views" even in the UI, and use normal views (or materialize them if desired for performance). An example of this would be a view involving aggregation of data from another table. It doesn't make sense to insert into such an aggregation.