Schema optimisations and consistency of indexes between Django and SqlAlchemy

[giovannipizzi]

There are (still) important differences between the schema generated by SQLA and Django.
To make sure they are the same, we SHOULD NOT check only the python code, but actually the information in the DB schema, asking psql.
For instance, while Django correctly adds two indices for type:

=> select * from pg_indexes where tablename='db_dbnode' and indexdef LIKE '%type%';
 schemaname | tablename |              indexname               | tablespace |                                                   indexdef                                                   
------------+-----------+--------------------------------------+------------+--------------------------------------------------------------------------------------------------------------
 public     | db_dbnode | db_dbnode_599dcce2                   |            | CREATE INDEX db_dbnode_599dcce2 ON public.db_dbnode USING btree (type)
 public     | db_dbnode | db_dbnode_type_4cda33f938ccd765_like |            | CREATE INDEX db_dbnode_type_4cda33f938ccd765_like ON public.db_dbnode USING btree (type varchar_pattern_ops)
(2 rows)

SQLA does not generate the second index (varchar_pattern_ops) that is essential for making queries for "startwith" in a string (LIKE 'xxx%') that are used a lot in queries on the type. See also Postgres docs.

Tasks:

• Make sure all relevant char fields have also a varchar_pattern_ops
• Make a test that checks the schema autogenerated by Django as SQLA and compares it with some reference data to check if indexes and similar are the same (same uniqueness, same indexes and index types, ...)