Extending the PostgreSQL database with custom tables

[CasperWA]

Is your feature request related to a problem? Please describe

The issue is speeeeeeed. Storing extras will result in "slow" db queries for those (nested) values. This is a problem for REST API(s).

Describe the solution you'd like

Entry points for extending the PostgreSQL database with custom tables. Possibly always including a resource-link to Node ids. This will make queries faster, since it will be on specific table fields instead of within a JSONB field / mock-NoSQL.

Describe alternatives you've considered

Being slow. This is fine, but unsatisfactory for (web)applications filtering on custom values within an AiiDA database.

Additional context

This has come up in the development of aiida-optimade as well as the OPTIMADE Client, where searches on the Materials Cloud databases (AiiDA databases) are quite slow compared to other OPTIMADE providers' databases.

[giovannipizzi]

Adding PSQL tables is a mess... It's easier to create another DB where you create the schema you want. Instead, we should activate an index on the extras - we had mixed experiences but never tested this seriously. Casper could you provide some benchmarks (with scripts to run) and information on the distribution of attributes in your DB? And coordinate/discuss with @chrisjsewell who's working on the general topic of AiiDA performance? The first thing I would do is activate the reverse GIN index on the extras and check if this makes things faster (and fast enough) or slower