Slow retrieval of record_changes on production

[eugenegoncharuk]

What happened?

Pomerium with AD integration is used.
We have around 50k-60k rows in "pomerium.record_changes" table.
It takes from 20ms to 120ms sometimes to retrieve data from this table with SQL query used in Pomerium implementation:

FROM pomerium.record_changes
WHERE version > $1
AND type = $2
ORDER BY version ASC

Investigating Schema of the database I found there is no index on compound key "version + type" and there is full table scan is used.

What did you expect to happen?

Data is retrieved fast by using index.

How'd it happen?

Runtime in production

What's your environment like?

• Pomerium version v0.19.1

What's your config.yaml?

autocert: false
dns_lookup_family: V4_ONLY
address: :443
grpc_address: :443
certificate_authority_file: "/pomerium/ca/ca.crt"
certificates:
authenticate_service_url: <authenticate_service_url>
authorize_service_url: <authorize_service_url>
databroker_service_url: <databroker_service_url>
idp_provider: azure
idp_scopes:
idp_provider_url: https://login.microsoftonline.com/<id>/v2.0

signout_redirect_url: https://login.microsoftonline.com/<id>/oauth2/v2.0/logout
forward_auth_url: <forward_auth_url>
idp_client_id: REPLACE_ME
idp_client_secret: REPLACE_ME
databroker_storage_tls_skip_verify: false
routes:
  - <list of routes>

What did you see in the logs?

2023-08-07 04:14:27 UTC [157685]: [21-1] 64d06e2c.267f5 0 db db_owner_user [unknown] 10.2.178.83 DETAIL:  parameters: $1 = '294295', $2 = 'type.googleapis.com/pomerium.config.Config'
2023-08-07 04:14:27 UTC [157273]: [228-1] 64d06810.26659 0 db db_owner_user [unknown] 10.2.184.28 LOG:  duration: **140.390 ms**  execute lrupsc_2441_1:
			SELECT type, id, version, data, modified_at, deleted_at
			FROM pomerium.record_changes
			WHERE version > $1
		 AND type = $2
			ORDER BY version ASC
			LIMIT 1

2023-08-07 04:14:27 UTC [157273]: [229-1] 64d06810.26659 0 db db_owner_user [unknown] 10.2.184.28 DETAIL:  parameters: $1 = '294295', $2 = 'type.googleapis.com/pomerium.config.Config'
2023-08-07 04:14:27 UTC [157348]: [222-1] 64d0692d.266a4 0 db db_owner_user [unknown] 10.2.184.28 LOG:  duration: **127.223 ms**  execute lrupsc_2447_0:
			SELECT type, id, version, data, modified_at, deleted_at
			FROM pomerium.record_changes
			WHERE version > $1
		 AND type = $2
			ORDER BY version ASC
			LIMIT 1

2023-08-07 04:14:27 UTC [157348]: [223-1] 64d0692d.266a4 0 db db_owner_user [unknown] 10.2.184.28 DETAIL:  parameters: $1 = '294295', $2 = 'type.googleapis.com/pomerium.config.Config'
2023-08-07 04:14:27 UTC [157349]: [192-1] 64d0692d.266a5 0 db db_owner_user [unknown] 10.2.184.28 LOG:  duration: **100.007 ms**  execute lrupsc_2448_0:
			SELECT type, id, version, data, modified_at, deleted_at
			FROM pomerium.record_changes
			WHERE version > $1
		 AND type = $2
			ORDER BY version ASC
			LIMIT 1

2023-08-07 04:14:27 UTC [157349]: [193-1] 64d0692d.266a5 0 db db_owner_user [unknown] 10.2.184.28 DETAIL:  parameters: $1 = '294334', $2 = 'type.googleapis.com/pomerium.config.Config'
2023-08-07 04:14:27 UTC [157222]: [118-1] 64d06740.26626 0 db db_owner_user [unknown] 10.2.178.83 LOG:  duration: **111.084 ms**  execute lrupsc_2260_1:
			SELECT type, id, version, data, modified_at, deleted_at
			FROM pomerium.record_changes
			WHERE version > $1
		 AND type = $2
			ORDER BY version ASC
			LIMIT 1

[eugenegoncharuk]

Any news on this?

[desimone]

\cc @eugenegoncharuk yes :)