How to add database schemas/namespaces

[abelsiqueira]

It might be useful to add database schemas to the DuckDB tables, possibly starting from the TulipaClustering data. We should figure out which database schemas to create and which tables to assign to each.
I feel this is heavily tied to #1156, since DuckDB does not currently support foreign keys between schemas, and also we probably want to keep the schemas as some kind of logical separators.

[suvayu]

My thoughts: we should separate the data/tables based on specific goals, these are some that I think are important.

Considerations when choosing an implementation

• data separation between user/tulipa, because of reasons like
  • efficiency vs convenience, or
  • easier implementation like overwrite without worrying about data loss
  • parallel runs
• within tulipa we can further separate between modules/packages for reasons like
  • easy cleanup of temporary/intermediate tables (good for rerunning)
  • create module specific caches (for performance optimisation)
• things to worry about
  • data lifetime/scope: how long do you need a particular piece of data?
  • did it require significant compute to get it in that form, or is it straightforward transform/reorganisation of existing data?
  • to perform a calculation, what other data do you need (a dependency graph for the data)?

Tools available to us

• simple namespacing by prepending tokens to table names: namespace_*
  • no restrictions, mostly useful for semantic separation, and to document which table is relevant for whom
• using database schema: schema.table_name
  • cannot depend on columns from tables in other schemas in table constraints like FOREIGN KEY
  • access to tables in other schemas could be through VIEWs
  • great for higher level management, you can drop an entire schema and all constituent tables/views will be deleted.
• VIEWs
  • computed at runtime, equivalent to running a query and using the output
  • good for reorganising data into convenient "tables". no need to "update", since they are computed at runtime, which is why they should not include complex calculations (e.g. calculating indices)
• temporary tables:
  • in-memory, easy to create and drop
  • good for communicating large(r) data across functions (useful tool during performance optimisations)
  • in-memory, so should not be long-lived, drop when not needed anymore (reclaims memory)
  • creating regular tables and dropping them is not good for the database (leads to fragmentation), so this is a very helpful alternative
• CTEs using the WITH statement: some benefits of views and temporary tables maybe combined as CTEs that are used multiple times.
• there are some other options, like table macros, I don't know a lot about them

---

The choice between whether to use a view, a temporary table, or CTE will depend on data dependency, and whether something is compute/memory intensive.

We should also look at the how DROP behaves when considering data dependency.