Check for journal table existence slow on large PostgreSQL instance.

[Kharos]

We have a Postgres aurora instance with ~600K tables. The metadata query that checks if the journal table already exists is very slow in this environment (~76 seconds on a "cold" database instance). This can cause query timeouts and lead to failed service deployments.

The query used by PostgresqlTableJournal is the generic sql metadata query, it is inherited from TableJournal. We could solve the problem by inheriting from PostgresqlTableJournal and replacing the query with a postgres specific query:

        protected override string DoesTableExistSql()
        {
            string tableFullName = string.IsNullOrEmpty(SchemaTableSchema) ? UnquotedSchemaTableName : (SchemaTableSchema + '.' + UnquotedSchemaTableName);
            return $"SELECT CASE WHEN to_regclass('{tableFullName}') IS NOT NULL THEN 1 ELSE 0 END";
        }

This query takes about 8 ms on a "cold" database instance.

The to_regclass function was added in PostgresSQL 9.4. Versions before 9.4 are no longer officially supported, so it might be worthwhile to put this fix into PostgresqlTableJournal.

[Kharos]

I just stumbled over this in our code and decided to check if we still need to keep our workaround code... turns out yes, we do.