Utilize database table partitioning for File and ContentNode

Current behavior

As studio usage has grown, database tables have grown to a size that makes performance optimization very difficult. The size makes queries or joins against these large tables very slow and therefore user operations to feel slow.

Desired behavior

There are several reasons to use table partitioning, as outlined by the postgresql documentation:

• Partitioning the ContentNode table by tree_id could dramatically improve query performance since most will heavily access rows in a single partition and sequential scans should be quicker
• Partitioning makes it more likely that heavily-used parts of the indexes fit in memory
• Bulk loads and deletes are more efficient
• Seldom-used data can be migrated to cheaper and slower storage media.

Value add

• Pave the way for 10x data growth with current database resources

Possible tradeoffs

• We need to update to Postgresql 12 at least
• Might make it more difficult to migrate to Cloud Spanner afterwards
  • Cloud Spanner is globally available database that recently released postgres compatibility
  • Most postgres features we use are supported, except for transactions, which would require code updates
  • It would easily allow geographically distributed instances of Studio and greatly reduce latency
  • It may also invalidate the need for table partitioning
  • It's possible to do something like Cloud Spanner with postgres instances but would require infra
• Determining the best strategy for partitioning the file table and achieving all the benefits

Add labels