Opened 5 years ago

Closed 5 years ago

Last modified 5 years ago

#28334 closed Cleanup/optimization (fixed)

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

Reported by: Igor Gumenyuk Owned by: Igor Gumenyuk
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 (13)

comment:1 Changed 5 years ago by Igor Gumenyuk

Description: modified (diff)

comment:2 Changed 5 years ago by Claude Paroz

Triage Stage: UnreviewedAccepted

comment:3 Changed 5 years ago by Igor Gumenyuk

Owner: set to Igor Gumenyuk
Status: newassigned

comment:4 Changed 5 years ago by Igor Gumenyuk

Has patch: set
Last edited 5 years ago by Tim Graham (previous) (diff)

comment:5 Changed 5 years ago by Tim Graham

Patch needs improvement: set

There are test failures on the pull request.

comment:6 Changed 5 years ago by Igor Gumenyuk

Patch needs improvement: unset

comment:7 Changed 5 years ago by Igor Gumenyuk

All issues resolved, there are no failed tests

comment:8 Changed 5 years ago by Claude Paroz

Needs tests: set

comment:9 Changed 5 years ago by Igor Gumenyuk

Needs tests: unset

comment:10 Changed 5 years ago by Tim Graham

Patch needs improvement: set

comment:11 Changed 5 years ago by Tim Graham

Patch needs improvement: unset
Triage Stage: AcceptedReady for checkin

comment:12 Changed 5 years ago by Tim Graham <timograham@…>

Resolution: fixed
Status: assignedclosed

In 86a18dc:

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

comment:13 Changed 5 years ago by Tim Graham <timograham@…>

In f7b0532:

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

Note: See TracTickets for help on using tickets.
Back to Top