When analyzing a large build found the size of the refs table and its indexes dwarfed the rest of the database:
Looking at the table its clear that there is a huge amount repeated string content in this table.
The most important data is object, referenced_object columns. But tracking the property_path and property_type as strings is contributing a lot to the table and index sizes.
Deduplicating those strings into side tables would complicate any queries a little bit if they are accessing those strings (e.g. to add another JOIN). But this can potentially make a huge different in database size, and creating a smaller database could probably improve analyze times.
When analyzing a large build found the size of the refs table and its indexes dwarfed the rest of the database:
Looking at the table its clear that there is a huge amount repeated string content in this table.
The most important data is object, referenced_object columns. But tracking the property_path and property_type as strings is contributing a lot to the table and index sizes.
Deduplicating those strings into side tables would complicate any queries a little bit if they are accessing those strings (e.g. to add another JOIN). But this can potentially make a huge different in database size, and creating a smaller database could probably improve analyze times.