Upgrade Binary Trees to Generalized Search Trees

[TheLoneRonin]

Description

Currently, the codebase uses Binary Trees for indices. This can be optimized by implementing Generalized Search Trees instead. This will help scale tag searches and reduce bloat in the indices as well too.

Solution

There are a couple moving components to the project. We would need to enable GIST by running the SQL command.

CREATE EXTENSION btree_gist;

In development, this is fairly trivial as you can just ssh into Postgres and run the command. However, with docker-compose we would need to have a boot sql script to run this command as an adminstrator.

After, all indices that are VARCHAR should be upgraded to GIST as opposed to HASH or BTREE.

Reference

• https://www.postgresql.org/docs/9.5/gist-intro.html