Select on pg_type takes a few seconds

Hello,

I hope this is the right spot for my problem. :slight_smile:

From time to time, i see selects on pg_type that take 4-10 seconds and i can’t figure out why that is the case. As i understand it, pg_type is a virtual table whose content rarely changes if at all. Other statements before and after those on pg_type are within a few millliseconds.
Those requests are also not from me, it looks to me that ActiveRecord is requesting some information after a new connection is created.

It’s not a big issue, since we keep all idle connections open. But i would still like to understand whats going on here.
Has anybody experienced that behavior before?

Best regards,
Eric

Hi @eric.koch,

Welcome to Cockroach Forum!

Sorry for the delay in response.

When investigating this issue, I did see that active_record*/connection_adapters/postgresql_adapter.rb does query the pg_type table twice when opening a new connection. This page explains that ActiveRecord queries pg_type to check if there are new user-defined types. Users can add new types to PostgreSQL using the CREATE TYPE command or new domains using CREATE DOMAIN. Also, in PostgreSQL, when you create a table or a view, the corresponding composite type with the same name is automatically created.

These features are not yet implemented in CockroachDB: CREATE TYPE, CREATE DOMAIN, and composite type for newly created tables or views. As you mentioned, in CockroachDB pg_type is a virtual table.

So while I was able to reproduce ActiveRecord querying pg_type table twice, I did not reproduce the query times of 4-10 seconds that you saw.

If you continue to see these multi-second times for these queries, please send additional environment details: version of CockroachDB, Ruby, Rails, ActiveRecord. What adapter and version are you using: activerecord-cockroachdb-adapter or pg?

Regards,
Florence
Technical Support Engineer

Hi @florence-crl,

Thank you for your detailed response. Also sorry for my delay in response. :slight_smile:

The long query times are still there but currently on our low priority list. So far we didn’t investigate any further.

We are using the activerecord-cockroachdb-adapter with some small modifications: https://github.com/sumcumo/activerecord-cockroachdb-adapter
Ruby 2.6.3
Rails & ActiveRecord 6.0.2.1
CockroachDB is v19.2.4 // 5 nodes with Kubernetes on AWS (3 x m5a.2xlarge)

But I have a suspicion that it might have to do with the way we have integrated the cockroachdb in our feature development workflow. For every push to a feature branch our CI pipeline creates a database + tables + seeds on the cockrochdb cluster. It also drops the databases after the integration tests did run.
So we do have some more DDL statements during the day.

We didn’t had the time to further investigate it, yet. So it might be that my suspicion is utterly wrong.

Best regards,
Eric