Enhancement request: Consider improvements to hierarchy caching

Summary

Cached hierarchies take up the largest amount of space in the core OpenCodelists database – 2,133,488kB/3,806,276kB in a recently-taken database backup.

sqlite> SELECT SUM(pgsize)/1024 table_size  FROM "dbstat";
3806276
sqlite> SELECT name ,SUM(pgsize)/1024 table_size  FROM "dbstat" GROUP BY name ORDER BY table_size desc;
codelists_cachedhierarchy|2133448
codelists_codeobj|303288
codelists_codeobj_version_id_code_15071849_uniq|269368
codelists_codelistversion|220892
codelists_searchresult|157488
codelists_searchresult_search_id_code_obj_id_c66641de_uniq|157456
codelists_codeobj_version_id_4aab319f|139336
codelists_searchresult_code_obj_id_97ce8249|123796
codelists_searchresult_search_id_f68e07fc|114776
ctv3sctmap2_mapping|56752
sqlite_autoindex_ctv3sctmap2_mapping_1|29048
bnfdmd_mapping|14844
ctv3sctmap2_mapping_sct_description_id_ba16bdec|10632
ctv3sctmap2_mapping_sct_concept_id_6723b886|10420
sqlite_autoindex_bnfdmd_mapping_1|9956
ctv3sctmap2_mapping_ctv3_term_id_2cc31edf|8940
ctv3sctmap2_mapping_ctv3_concept_id_e4210d33|8940
bnfdmd_mapping_bnf_concept_id_36992164|8660
codelists_codelist|3048
django_session|2968
[...]

The data field of this table contains a JSON representation of a CodelistVersions's Hierarchy with a text data type.

sqlite> .schema codelists_cachedhierarchy
CREATE TABLE IF NOT EXISTS "codelists_cachedhierarchy" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "data" text NOT NULL, "version_id" integer NOT NULL UNIQUE REFERENCES "codelists_codelistversion" ("id") DEFERRABLE INITIALLY DEFERRED);

SQLite offers a jsonb datatype, which claims

The advantage of JSONB over ordinary text RFC 8259 JSON is that JSONB is both slightly smaller (by between 5% and 10% in most cases) and can be processed in less than half the number of CPU cycles.

testing this with our cached hierarchy data:

sqlite> CREATE TABLE IF NOT EXISTS "codelists_cachedhierarchy_jsonb" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "data" jsonb NOT NULL, "version_id" integer NOT NULL UNIQUE REFERENCES "codelists_codelistversion" ("id") DEFERRABLE INITIALLY DEFERRED);
sqlite> insert into codelists_cachedhierarchy_jsonb(id,data,version_id) select id, jsonb(data),version_id from codelists_cachedhierarchy;
sqlite> SELECT SUM(pgsize)/1024 table_size  FROM "dbstat" WHERE name = "codelists_cachedhierarchy_jsonb";
1665968

results in a table that is 78% the size of the text-typed one.

By migrating CachedHierarchy.data to models.JSONField (which I think should store this as jsonb in the db, maybe with turning on some options )

There is a good bit more validation work to be done here, but superficially it seems quite appealing to me.

A more proper solution might be to move these objects out of the database entirely and into a dedicated cache.

What would you like to achieve?

• Faster reading of cached hierarchies
• Less disk usage for cached hierarchies

Who would benefit and how?

Those who use and maintain OpenCodelists by having faster page loads, less server load, and less disk usage.

Start in codelists/models.py at CachedHierarchy.data and review Django's JSONField and SQLite JSON/JSONB support. Reproduce the dbstat comparison from the issue, then investigate migration and validation implications for cached hierarchy data. Done should document validated size and performance results and a chosen storage approach.