contrib.postgresql overwhelms database with "select from pg_type" queries on each request

[Igor Gumenyuk]

On each newly created database connection django.contrib.postgresql tries to register hstore type for this connection via connection_created signal and register_hstore function.

​https://github.com/django/django/blob/master/django/contrib/postgres/apps.py#L20
​https://github.com/django/django/blob/stable/1.11.x/django/contrib/postgres/signals.py#L16

Behind the scenes register_hstore runs sql query to get oid of hstore type:
​https://github.com/psycopg/psycopg2/blob/master/lib/extras.py#L885

This happens on every(!) request (unless persistent connections enabled)

SELECT t.oid, %s
FROM pg_type t JOIN pg_namespace ns
ON typnamespace = ns.oid
WHERE typname = 'hstore';

This is just huge overhead, since register_hstore accepts oid argument to avoid hitting database every call.
We have seen significant latency spikes because of this.

Possible solution would be to have configurable GET_HSTORE_OID_FUNC in DATABASES->OPTIONS setting which can be cached in any way.
This will also maintain backwards compatibility.

I can send patch for this if proposed solution is acceptable to be merged in master.

[Igor Gumenyuk]

​PR

[Tim Graham]

There are test failures on the pull request.

[Igor Gumenyuk]

All issues resolved, there are no failed tests

[Tim Graham <timograham@…>]

In 86a18dc:

Fixed #28334 -- Added caching for hstore/citext OIDs.

[Tim Graham <timograham@…>]

In f7b0532:

Refs #28334 -- Fixed crash in hstore/citext oid caching during test db creation.