types: possible to support both JSON/JSONB

[lnielsen]

Currently SQLAlchemy has JSONB support for PostgreSQL:
http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#sqlalchemy.dialects.postgresql.JSONB

However, it doesn't seem like it will go into JSONType in SQLAlchemy-Utils due to difficulties in making it behave nicely across DBs:
kvesteri/sqlalchemy-utils#125

For Invenio-Records we could likely get along with using JSONB when on PostgreSQL (and stay with text no the other platforms), as all we basically do is get and put the entire JSON so comparators is less of an issue.

[lnielsen]

Ping @jirikuncar @tiborsimko

[tiborsimko]

I'd advocate using JSONB with PostgreSQL when it makes sense, even for operators. A flagship example is the authority control where the management utilities will want to do operations like "what records are dependent on this record to see whether we can safely delete it?" These "management machine queries" ideally operate directly on the raw record store, not on the enhanced JSON in Elasticsearch where JSON references may be expanded. Here, using JSONB dedicated indexes and comparison operators can make a world of difference in performance.

Some references of past musings:

• WIP initial implementation inveniosoftware-attic/invenio-authorities#1
• Benchmark: Elasticsearch vs PostgreSQL JSONB for relations handling inspirehep/invenio#231

[jirikuncar]

Good news: JSON support in SQLAlchemy 1.1 http://docs.sqlalchemy.org/en/latest/changelog/migration_11.html#json-support-added-to-core