Releem Help Center
PostgreSQL Database Schema Checks: Best Practices and Solutions
A database schema shapes how your application stores, finds, and updates data. Missing primary keys, unnecessary indexes, outdated planner statistics, and sequences approaching their limit can create performance or reliability problems as a database grows.
Releem checks PostgreSQL schema metadata and usage statistics to help you spot these issues and decide what to review next. This guide explains the five schema checks, why each finding matters, and how to approach the recommended changes:
Before changing a production schema, back up your data, test the change on a non-production system, and plan how to recover. Replace the example schema, table, column, sequence, and index names with your own. Use a DBA-reviewed migration plan for changes to large or busy tables.
Releem checks PostgreSQL schema metadata and usage statistics to help you spot these issues and decide what to review next. This guide explains the five schema checks, why each finding matters, and how to approach the recommended changes:
Before changing a production schema, back up your data, test the change on a non-production system, and plan how to recover. Replace the example schema, table, column, sequence, and index names with your own. Use a DBA-reviewed migration plan for changes to large or busy tables.
1. Missing Primary Keys
A primary key gives each row a unique, non-null identity. PostgreSQL creates a unique B-tree index to enforce it, which can also support lookups and joins on the key columns.
Releem identifies tables without a primary key so you can check whether they have a reliable way to identify individual rows. This matters for application updates, data integrity, and change tracking. PostgreSQL logical replication uses the primary key as the default replica identity for updates and deletes; tables without one need an appropriate alternative replica identity.
How to add a primary key:
Choose a column whose values are unique and never null. For an existing table, the basic form is:
Releem identifies tables without a primary key so you can check whether they have a reliable way to identify individual rows. This matters for application updates, data integrity, and change tracking. PostgreSQL logical replication uses the primary key as the default replica identity for updates and deletes; tables without one need an appropriate alternative replica identity.
How to add a primary key:
Choose a column whose values are unique and never null. For an existing table, the basic form is:
ALTER TABLE "schema_name"."table_name"
ADD CONSTRAINT "table_name_pkey" PRIMARY KEY ("id");A primary key can also contain more than one column:
ALTER TABLE "schema_name"."table_name"
ADD CONSTRAINT "table_name_pkey" PRIMARY KEY ("id", "email");These are alternative examples. Choose the key that matches the table's data model.
Existing rows must satisfy the uniqueness and non-null requirements. The columns do not have to have been declared NOT NULL when the table was first created: adding the primary key applies that requirement. Resolve duplicate or null values before attempting the change.
Adding a primary key can scan the table and block other work. For a large production table, have your DBA plan a staged migration using a suitable unique index and an appropriate maintenance window.
See the PostgreSQL documentation on primary keys and replica identity.
Existing rows must satisfy the uniqueness and non-null requirements. The columns do not have to have been declared NOT NULL when the table was first created: adding the primary key applies that requirement. Resolve duplicate or null values before attempting the change.
Adding a primary key can scan the table and block other work. For a large production table, have your DBA plan a staged migration using a suitable unique index and an appropriate maintenance window.
See the PostgreSQL documentation on primary keys and replica identity.
2. Duplicate Indexes
Each index takes disk space and adds maintenance work when data changes. Two indexes with the same effective definition can duplicate that work. An overlapping index may also be worth reviewing when another index covers its leading key columns.
Releem's Duplicate Indexes check covers two cases:
For example, a B-tree index on (customer_id, created_at) may cover the leading key of an index on (customer_id). That overlap alone does not prove the smaller index is unnecessary. Index size, query patterns, and execution plans can still make a difference.
Releem compares more than column names. Its checks account for access methods, expressions, collations, operator classes, sort and null ordering, included columns, partial-index predicates, and relevant uniqueness behavior. A recommendation is retained only when a compatible covering index will remain after the proposed removals.
Where the required metadata is available, Releem excludes primary and unique indexes, unusable indexes, exclusion indexes, partitioned-parent and attached-partition indexes, clustered and replica-identity indexes, and indexes collected as supporting a foreign key.
How to review and remove a duplicate index:
Check the full index definitions, dependencies, and the queries that use the table. Compare EXPLAIN plans for representative queries and test the effect of the change outside production. EXPLAIN ANALYZE executes the statement, so use it only when you intend to run that workload in a controlled environment.
After validating a recommendation, the generated removal statement has this form:
Releem's Duplicate Indexes check covers two cases:
- Exact duplicates: comparable indexes on the same table have the same effective PostgreSQL definition.
- Covering overlaps: another comparable index covers the candidate's complete leading key path and keeps its included columns available.
For example, a B-tree index on (customer_id, created_at) may cover the leading key of an index on (customer_id). That overlap alone does not prove the smaller index is unnecessary. Index size, query patterns, and execution plans can still make a difference.
Releem compares more than column names. Its checks account for access methods, expressions, collations, operator classes, sort and null ordering, included columns, partial-index predicates, and relevant uniqueness behavior. A recommendation is retained only when a compatible covering index will remain after the proposed removals.
Where the required metadata is available, Releem excludes primary and unique indexes, unusable indexes, exclusion indexes, partitioned-parent and attached-partition indexes, clustered and replica-identity indexes, and indexes collected as supporting a foreign key.
How to review and remove a duplicate index:
Check the full index definitions, dependencies, and the queries that use the table. Compare EXPLAIN plans for representative queries and test the effect of the change outside production. EXPLAIN ANALYZE executes the statement, so use it only when you intend to run that workload in a controlled environment.
After validating a recommendation, the generated removal statement has this form:
DROP INDEX CONCURRENTLY IF EXISTS "schema_name"."index_name";DROP INDEX CONCURRENTLY cannot run inside a transaction block. It may wait for long-running transactions, and it cannot remove an index supporting a primary key or unique constraint. Confirm that no critical query, operational tool, or workload outside the collected data needs the index. Save its definition so you can plan recreation if needed.
See the PostgreSQL documentation on DROP INDEX and EXPLAIN.
See the PostgreSQL documentation on DROP INDEX and EXPLAIN.
3. Unused Indexes
An index that receives no scans can still use disk space and add work to INSERT, UPDATE, and DELETE operations. Removing an unnecessary index can reduce that overhead, but an index may serve a critical monthly report or a query that runs on another node.
Releem uses PostgreSQL's idx_scan counter to find eligible non-primary, non-unique indexes with no observed scans in the selected valid snapshots.
By default, the check requires:
These are Releem's eligibility rules, rather than a guarantee that an index has no business use. Where statistics-reset metadata is available, Releem validates the observation window separately for each database catalog and excludes catalogs with recent, invalid, or inconsistent reset data. Protected or unusable indexes and collected foreign-key-supporting indexes are excluded.
How to review and remove an unused index:
Review a complete business cycle, including infrequent jobs, reporting, and maintenance. Account for statistics resets and query distribution across database nodes. A zero counter in the observed data does not establish that the index is unused everywhere.
Test the change in a controlled environment. PostgreSQL does not provide MySQL-style invisible indexes, so a MySQL ALTER INDEX ... INVISIBLE workflow does not apply.
Once the workload and dependencies have been checked, Releem generates:
Releem uses PostgreSQL's idx_scan counter to find eligible non-primary, non-unique indexes with no observed scans in the selected valid snapshots.
By default, the check requires:
- zero idx_scan in every selected valid snapshot for the index;
- at least 30 days of summed server uptime across the selected snapshots;
- more than one day of uptime represented by each snapshot;
- a maximum per-index idx_scan of at least 1,000 on the same table.
These are Releem's eligibility rules, rather than a guarantee that an index has no business use. Where statistics-reset metadata is available, Releem validates the observation window separately for each database catalog and excludes catalogs with recent, invalid, or inconsistent reset data. Protected or unusable indexes and collected foreign-key-supporting indexes are excluded.
How to review and remove an unused index:
Review a complete business cycle, including infrequent jobs, reporting, and maintenance. Account for statistics resets and query distribution across database nodes. A zero counter in the observed data does not establish that the index is unused everywhere.
Test the change in a controlled environment. PostgreSQL does not provide MySQL-style invisible indexes, so a MySQL ALTER INDEX ... INVISIBLE workflow does not apply.
Once the workload and dependencies have been checked, Releem generates:
DROP INDEX CONCURRENTLY IF EXISTS "schema_name"."index_name";Run this outside a transaction block and plan for possible waits. Keep the index definition and monitor the affected queries after an approved removal.
For the meaning of idx_scan, see PostgreSQL's index statistics documentation.
For the meaning of idx_scan, see PostgreSQL's index statistics documentation.
4. Outdated Table Statistics
PostgreSQL's planner uses statistics about table data to estimate how many rows a query will return and choose an execution plan. When the data changes substantially, older statistics may no longer describe it well. The result can be inaccurate estimates and inefficient query plans.
Releem compares the last manual or automatic analyze time with the table size, estimated row count, and modifications since analyze.
For a table with no recorded analyze time, Releem reports a finding when either:
For tables with previous statistics and pending modifications:
How to refresh table statistics:
The generated recommendation targets the affected table:
Releem compares the last manual or automatic analyze time with the table size, estimated row count, and modifications since analyze.
For a table with no recorded analyze time, Releem reports a finding when either:
- the table size is at least 1 GiB; or
- modifications since analyze are at least 50.
For tables with previous statistics and pending modifications:
- Tables of at least 1 GiB: statistics are older than 14 days, or more than 20% of the estimated rows have changed.
- Other tables: statistics are older than 30 days, or more than 30% of the estimated rows have changed.
How to refresh table statistics:
The generated recommendation targets the affected table:
ANALYZE "schema_name"."table_name";ANALYZE collects fresh planner statistics. PostgreSQL normally performs automatic analyze through autovacuum, so recurring findings are also a reason to review whether automatic maintenance is keeping up with the table's changes.
Schedule manual analysis with the table's workload in mind: it consumes resources and can change query plans. Check representative query plans and performance afterward. Updating statistics does not rebuild the table or remove indexes.
See the PostgreSQL documentation on ANALYZE.
Schedule manual analysis with the table's workload in mind: it consumes resources and can change query plans. Check representative query plans and performance afterward. Updating statistics does not rebuild the table or remove indexes.
See the PostgreSQL documentation on ANALYZE.
5. Capacity for Sequences
PostgreSQL sequences generate values for uses such as row identifiers. A non-cycling sequence stops generating new values when it reaches its configured limit. Inserts that depend on the next sequence value can then fail.
Releem flags supported non-cycling smallint and integer sequences when consumed capacity is strictly above 75%. Consumption is measured over the configured minimum-to-maximum range, and the bound in the exhaustion direction must match the native boundary of the sequence type.
The check does not automatically recommend changes for bigint sequences, cycling sequences, sequences with a custom exhaustion-side bound, or sequences whose type or ownership cannot be interpreted safely.
How to increase sequence capacity:
Check both the sequence and the type of the column that receives its values. Widening the sequence alone will not make an integer column accept values beyond its own range.
For a standalone sequence, or one already owned by a compatible wide column, the recommendation has this form:
Releem flags supported non-cycling smallint and integer sequences when consumed capacity is strictly above 75%. Consumption is measured over the configured minimum-to-maximum range, and the bound in the exhaustion direction must match the native boundary of the sequence type.
The check does not automatically recommend changes for bigint sequences, cycling sequences, sequences with a custom exhaustion-side bound, or sequences whose type or ownership cannot be interpreted safely.
How to increase sequence capacity:
Check both the sequence and the type of the column that receives its values. Widening the sequence alone will not make an integer column accept values beyond its own range.
For a standalone sequence, or one already owned by a compatible wide column, the recommendation has this form:
ALTER SEQUENCE "schema_name"."sequence_name" AS bigint;If the sequence belongs to a narrow integer column, Releem recommends widening the column first:
ALTER TABLE "schema_name"."table_name"
ALTER COLUMN "id" TYPE bigint;
ALTER SEQUENCE "schema_name"."sequence_name" AS bigint;Plan this migration with your DBA. A column type change can rewrite the table and rebuild indexes, and related foreign keys, application types, and dependencies need review. Verify the sequence's resulting bounds as part of the migration: changing its type automatically expands bounds that match the old type's native limits, while custom bounds are preserved.
These examples widen the type without restarting the sequence. Do not reset it to a lower value to recover capacity, because generated values could conflict with existing rows.
See the PostgreSQL documentation on ALTER SEQUENCE and ALTER TABLE.
These examples widen the type without restarting the sequence. Do not reset it to a lower value to recover capacity, because generated values could conflict with existing rows.
See the PostgreSQL documentation on ALTER SEQUENCE and ALTER TABLE.
PostgreSQL Schema Checks with Releem
Open the Schema Checks block on your server dashboard to review the affected objects and recommendations. Use each finding to prioritize a change, validate it against your workload, and check the result after an approved migration.
Releem brings these checks together so you can spend less time searching through schema metadata and more time improving your database. Explore Releem for PostgreSQL, or open your dashboard to review your schema checks.
Releem brings these checks together so you can spend less time searching through schema metadata and more time improving your database. Explore Releem for PostgreSQL, or open your dashboard to review your schema checks.