Problem
Terraform Enterprise UI and APIs are slow and exhibiting degraded performance. The PostgreSQL server logs are showing many instances of duplicate key errors involving the following Terraform Registry query:
PostgreSQL logs
2025-05-15 20:09:16 UTC:10.0.40.1(57846):hashicorp@hashicorp:[12058]:ERROR: duplicate key value violates unique constraint "pg_class_relname_nsp_index"
2025-05-15 20:09:16 UTC:10.0.40.1(57846):hashicorp@hashicorp:[12058]:DETAIL: Key (relname, relnamespace)=(views_module_1_versions_memoized_submodules_id_idx39001, 16476) already exists.
2025-05-15 20:09:16 UTC:10.0.40.1(57846):hashicorp@hashicorp:[12058]: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(
json_build_object(
'path', module_submodules.path,
'dependencies', COALESCE((
SELECT json_agg(
json_build_object(
'name', module_deps.name,
'source', module_deps.source
)
)
FROM module_deps
WHERE module_deps.module_submodule_id = module_submodules.id
), '[]'::json),
'providers', COALESCE((
SELECT json_agg(
json_build_object(
'name', module_provider_deps.provider,
'namespace', '',
'source', module_provider_deps.source,
'version', module_provider_deps.version_constraints
)
)
FROM module_provider_deps
WHERE module_provider_deps.module_submodule_id = module_submodules.id
), '[]'::json)
)
)
FROM module_submodules
WHERE
module_submodules.module_version_id = module_versions.id
AND module_submodules.path NOT LIKE 'examples/%'
AND module_submodules.path != ''
), '[]'::json) AS submodules_json,
(
SELECT json_build_object(
'dependencies', COALESCE((
SELECT json_agg(
json_build_object(
'name', module_deps.name,
'source', module_deps.source
)
)
FROM module_deps
WHERE module_deps.module_submodule_id = module_submodules.id
), '[]'::json),
'providers', COALESCE((
SELECT json_agg(
json_build_object(
'name', module_provider_deps.provider,
'namespace', '',
'source', module_provider_deps.source,
'version', module_provider_deps.version_constraints
)
)
FROM module_provider_deps
WHERE module_provider_deps.module_submodule_id = module_submodules.id
), '[]'::json)
)
FROM module_submodules
WHERE
module_submodules.module_version_id = module_versions.id
AND module_submodules.path = ''
) AS submodule_root_json
FROM module_versions
WHERE module_versions.module_provider_id = 1
ORDER BY id;
CREATE UNIQUE INDEX ON views_module_1_versions_memoized_submodules (id);Additionally, the pg_stat_activity table shows that there are many long running instances of the query referenced in the log above:
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';
Cause
This is a bug which can impact Terraform Enterprise instances with Private Registry modules with over 300 versions.
When a Terraform Private Registry module accumulates over 300 module versions, the Terraform Registry initiates a memoization function intended to improve querying at this level. There is a bug in this functionality which causes the Terraform Registry to overzealously execute a query to attempt to create a materialized view and a unique index on the view on every request to the Terraform Registry's /v1/modules/{namespace}/{name}/{provider}/versions endpoint. This can result in many executions of this query as requests to that endpoint are made, for example, during each terraform init in workspaces consuming the registry module. This results in an increasingly expensive operation as more indexes accumulate on the view, eventually leading to database contention (e.g., duplicate key errors, locking) and resource exhaustion under higher traffic loads.
Solution
Recovery
If Terraform Enterprise is actively impacted and experiencing degraded performance because of this issue , gracefully cancel the long running queries using the following SQL to restore service to the application:
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, restart Terraform Enterprise to clear active database sessions executing the impacting query.
Short Term
As a short term solution, identify Registry modules with over 300 versions and reduce their number of versions to below 300 by deleting earlier module versions from Terraform Enterprise. To identify registry modules with over 300 versions, use the following steps:
- Start a Rails console
- Run the following Ruby code to query for registry modules with over 300 versions
RegistryModule.joins(:versions)
.group('registry_modules.id')
.having('COUNT(registry_module_versions.id) > 300')Once the identified modules have had their total number of versions reduced to below 300, maintain a limit of 300 module versions on these modules until the long-term solution below can be implemented.
Long Term
This bug will been fixed in v202506-1. Upgrade to that version or later for a permanent solution to this issue.
Additional Information