> Draft proposal ? generated by AI, may not have been reviewed for accuracy.

Drop Redundant Indexes

Problem

Ten tables each carry two indexes on the same column(s): one created automatically by a UNIQUE column constraint (*_key), and one created explicitly with CREATE INDEX in the same migration (idx_*). They are byte-for-byte equivalent in terms of what queries can use them for, so the idx_* copies are pure waste.

PostgreSQL can use a unique-constraint index for any lookup that a plain index would serve, while also enforcing uniqueness. There is no scenario in which the explicit idx_* index adds value over the auto-generated one.

Impact of duplicate indexes

• Extra disk space (proportional to table size ? largest concern on `nodelist` and `echomail`-adjacent tables).
• Every `INSERT`, `UPDATE`, and `DELETE` must maintain both indexes instead of one ? double the write overhead for these columns.
• The duplicate indexes appear as false positives in the "Potentially Redundant Indexes" tab of the admin Database Statistics page, making it harder to spot genuinely unused indexes.

Root Cause

Each case was created the same way in a single migration:

-- UNIQUE constraint ? PostgreSQL auto-creates  *_key  index
CREATE TABLE foo (
    token VARCHAR(64) NOT NULL UNIQUE,   -- creates  foo_token_key
    ...
);

-- Explicit index on the same column ? redundant
CREATE INDEX IF NOT EXISTS idx_foo_token ON foo(token);

This is a common mistake when a developer adds an explicit index for lookup-by-token performance without realising the UNIQUE constraint already provides that index.

Affected Indexes

| Table | Redundant index (to drop) | Kept index (unique constraint) | Migration origin | |---|---|---|---| | binkp_insecure_nodes | idx_binkp_insecure_nodes_address | binkp_insecure_nodes_address_key | v1.7.1_add_crashmail_support.sql | | gateway_tokens | idx_gateway_tokens_token | gateway_tokens_token_key | v1.7.4_gateway_tokens.sql | | nodelist | idx_nodelist_address | nodelist_zone_net_node_point_key | v1.1.0_add_nodelist_support.sql | | password_reset_tokens | idx_password_reset_tokens_token | password_reset_tokens_token_key | v1.6.4_add_password_reset_tokens.sql | | shared_messages | idx_shared_messages_key | shared_messages_share_key_key | v1.4.0_add_message_sharing_fixed.sql | | users | idx_users_referral_code | users_referral_code_key | v1.9.3.2_add_referral_fields.sql | | webdoor_sessions | idx_webdoor_sessions_session_id | webdoor_sessions_session_id_key | v1.7.7_webdoor.sql | | dosbox_doors | idx_dosbox_doors_door_id | dosbox_doors_door_id_key | v1.10.0_dosbox_doors.sql | | door_sessions | idx_door_sessions_session_id | door_sessions_session_id_key | v1.10.0_dosbox_doors.sql | | shared_files | idx_shared_files_share_key | shared_files_share_key_key | v1.10.11_shared_files.sql |

Proposed Fix

Migration

Create database/migrations/v1.11.0.13_drop_redundant_indexes.sql:

-- Remove explicit indexes that duplicate the unique-constraint indexes
-- on the same columns. The *_key indexes (auto-created by UNIQUE constraints)
-- remain and serve both lookup and uniqueness enforcement.

DROP INDEX IF EXISTS idx_binkp_insecure_nodes_address;
DROP INDEX IF EXISTS idx_gateway_tokens_token;
DROP INDEX IF EXISTS idx_nodelist_address;
DROP INDEX IF EXISTS idx_password_reset_tokens_token;
DROP INDEX IF EXISTS idx_shared_messages_key;
DROP INDEX IF EXISTS idx_users_referral_code;
DROP INDEX IF EXISTS idx_webdoor_sessions_session_id;
DROP INDEX IF EXISTS idx_dosbox_doors_door_id;
DROP INDEX IF EXISTS idx_door_sessions_session_id;
DROP INDEX IF EXISTS idx_shared_files_share_key;

Source migration cleanup (optional)

The CREATE INDEX lines in the original migration files could also be removed to prevent confusion when reading the schema history. This is cosmetic only ? the migration above handles the live database regardless.

Risk Assessment

Low risk. DROP INDEX on a non-unique index:

• Does not touch any data.
• Does not remove any constraints ? uniqueness on those columns is enforced by the surviving `*_key` indexes.
• Is instantaneous on PostgreSQL (no table scan required).
• Is fully reversible ? the indexes can be recreated with the original `CREATE INDEX` statements at any time.

No application code references these index names directly ? verified by searching all .php, .twig, and .js files for each name. No code changes are needed alongside the migration.

Testing

After applying the migration, verify the unique constraints still work:

-- Confirm unique constraints still present
SELECT conname, contype
FROM pg_constraint
WHERE conname IN (
    'binkp_insecure_nodes_address_key',
    'gateway_tokens_token_key',
    'nodelist_zone_net_node_point_key',
    'password_reset_tokens_token_key',
    'shared_messages_share_key_key',
    'users_referral_code_key',
    'webdoor_sessions_session_id_key',
    'dosbox_doors_door_id_key',
    'door_sessions_session_id_key',
    'shared_files_share_key_key'
);
-- All 10 rows should be present with contype = 'u'

-- Confirm dropped indexes are gone
SELECT indexname FROM pg_indexes
WHERE indexname LIKE 'idx_%'
AND indexname IN (
    'idx_binkp_insecure_nodes_address',
    'idx_gateway_tokens_token',
    'idx_nodelist_address',
    'idx_password_reset_tokens_token',
    'idx_shared_messages_key',
    'idx_users_referral_code',
    'idx_webdoor_sessions_session_id',
    'idx_dosbox_doors_door_id',
    'idx_door_sessions_session_id',
    'idx_shared_files_share_key'
);
-- Should return 0 rows

The "Potentially Redundant Indexes" tab in the admin Database Statistics page should be empty (or show only genuinely ambiguous cases) after the migration.