Terraform Enterprise PostgreSQL Performance Issues Caused by Terraform Registry Query

Problem

Terraform Enterprise UI and APIs are slow or exhibit degraded performance. The PostgreSQL server logs show many duplicate key errors related to a Terraform Registry query.

An example from the PostgreSQL logs shows the following error.

ERROR: duplicate key value violates unique constraint "pg_class_relname_nsp_index"
DETAIL: Key (relname, relnamespace)=(views_module_1_versions_memoized_submodules_id_idx39001, 16476) already exists.
STATEMENT:
CREATE MATERIALIZED VIEW IF NOT EXISTS views_module_1_versions_memoized_submodules AS
SELECT
module_versions.id,
module_versions.version,COALESCE((
SELECT json_agg(
## ...

Additionally, the pg_stat_activity table may show many long-running instances of the query. You can check for these queries with the following command.

SELECT pid, query, now() - query_start AS duration
FROM pg_stat_activity
WHERE datname = '<TFE_DATABASE_NAME>'
AND state = 'active'
AND now() - query_start > interval '5 minutes';

The terraform-registry-api.log may contain warnings about large modules not being memoized.

{
  "@level": "warn",
  "@message": "127.0.0.1 - - [...] \"GET /v1/modules/Test/mymodule/aws/versions HTTP/1.1\" 200 122981 \"\" \"rest-client/2.1.0 (linux x86_64) ruby/3.3.5p100\"\n",
  "@module": "http",
  "authentication": {
    "validation": "no token provided",
    "session-error": "missing data: unauthenticated session",
    "user": {
      "session": false
    }
  },
  "module_id": 189,
  "module_provider_id": 191,
  "module_versions_count": 438,
  "name": "config",
  "namespace": "Test",
  "provider": "aws",
  "registry-env": "private",
  "request-id": "1x7uppav88bc4o2s",
  "response_time_ms": 662,
  "route": "/v1/modules/{namespace}/{name}/{provider}/versions",
  "warning": "large module was not memoized, memoization created"
}

In some cases, the log may also show an error related to creating the view.

{
  "@level": "warn",
  "@message": "127.0.0.1 - - [...] \"GET /v1/modules/Test/mymodule/aws/versions HTTP/1.1\" 200 113899 \"\" \"rest-client/2.1.0 (linux x86_64) ruby/3.3.5p100\"\n",
  "@module": "http",
  "error": {
    "kind": "errors.stackedError",
    "message": "error creating view module_44_versions_memoized_submodules: duplicated key not allowed",
    "stack": "..."
  },
  "module_versions_count": 446,
  "warning": "large module was not memoized, memoization created"
}

Cause

This issue is caused by a bug that affects Terraform Enterprise instances with private registry modules that have over 300 versions.

When a module exceeds 300 versions, the Terraform Registry attempts to create a materialized view to improve query performance. Due to the bug, this query runs on every request to the /v1/modules/{namespace}/{name}/{provider}/versions endpoint. Frequent requests, such as during terraform init operations, lead to many concurrent executions of this query. This causes an increasingly expensive operation as more indexes accumulate, eventually leading to database contention, locking, and resource exhaustion.

Solutions

Here are three approaches to resolve the issue, from immediate recovery to a permanent fix.

Solution 1: Recover Service Immediately

If Terraform Enterprise is actively impacted, you can restore service by gracefully canceling the long-running queries. Execute the following SQL command against the Terraform Enterprise database.

SELECT pg_cancel_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE datname = '<TFE_DATABASE_NAME>'
AND state = 'active'
AND pid <> pg_backend_pid()
AND now() - pg_stat_activity.query_start > interval '5 minutes';

Alternatively, restarting the Terraform Enterprise application will clear all active database sessions, including the problematic queries.

Solution 2: Reduce Module Versions (Short-Term)

As a short-term workaround, identify private registry modules with over 300 versions and reduce their version count to below 300.

1. Start a session in the Terraform Enterprise Rails Console.

2. Run the following Ruby code to find modules with more than 300 versions.

   RegistryModule.joins(:versions)
   .group('registry_modules.id')
   .having('COUNT(registry_module_versions.id) > 300')

3. For each identified module, delete older versions until the total count is below 300. Maintain this limit until you can implement the long-term solution.

Solution 3: Upgrade Terraform Enterprise (Long-Term)

The permanent solution is to upgrade Terraform Enterprise to version v202506-1 or a later release, where this bug has been fixed.

Additional Information

• For more details on managing module versions, refer to the documentation on Deleting versions and modules.