Opened 7 years ago

Last modified 7 years ago

#28334 closed Cleanup/optimization

contrib.postgresql overwhelms database with "select from pg_type" queries on each request — at Version 1

Reported by: Igor Gumenyuk Owned by:
Component: contrib.postgres Version: 1.11
Severity: Normal Keywords:
Cc: Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by 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.

Change History (1)

comment:1 by Igor Gumenyuk, 7 years ago

Description: modified (diff)
Note: See TracTickets for help on using tickets.
Back to Top