Database Index Maintenance Tutorial: Complete MySQL, PostgreSQL, and MariaDB Optimization Guide for Linux VPS in 2026

Understanding Database Index Health on Your Linux VPS

Database performance degrades over time as indexes fragment and accumulate bloat. Your MySQL, PostgreSQL, or MariaDB database might start slowly even with proper initial configuration.

This tutorial covers comprehensive database index maintenance strategies. These techniques restore query performance and prevent future slowdowns on your Linux VPS.

Index maintenance differs significantly between database engines. MySQL's B-tree indexes require different care than PostgreSQL's MVCC-aware indexes or MariaDB's optimized storage engines.

Prerequisites for Index Management

You need root access to your Linux VPS and database administrator privileges. This guide works with Ubuntu 24.04, Debian 12, AlmaLinux 9, and Rocky Linux 9.

Verify your database versions:

# MySQL/MariaDB
mysql -V

# PostgreSQL
psql --version

Install essential monitoring tools:

# Ubuntu/Debian
sudo apt update
sudo apt install mysql-client postgresql-client-common percona-toolkit

# AlmaLinux/Rocky Linux
sudo dnf install mysql postgresql percona-toolkit

MySQL Index Health Assessment

Start by identifying fragmented indexes in MySQL. Connect to your database and examine index statistics:

mysql -u root -p

SELECT 
    table_schema,
    table_name,
    index_name,
    cardinality,
    sub_part,
    packed,
    nullable,
    index_type
FROM information_schema.STATISTICS 
WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
ORDER BY table_schema, table_name;

Check for table fragmentation that affects index performance:

SELECT 
    table_schema,
    table_name,
    round(((data_length + index_length) / 1024 / 1024), 2) AS 'DB Size MB',
    round((data_free / 1024 / 1024), 2) AS 'Free Space MB'
FROM information_schema.TABLES 
WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
    AND data_free > 0
ORDER BY data_free DESC;

Analyze slow queries that indicate index problems:

# Enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;
SET GLOBAL log_queries_not_using_indexes = 'ON';

PostgreSQL Index Monitoring

PostgreSQL provides detailed statistics about index usage and bloat. Check index efficiency:

\c your_database

SELECT 
    schemaname,
    tablename,
    indexname,
    idx_tup_read,
    idx_tup_fetch,
    idx_scan,
    idx_tup_read / GREATEST(idx_scan, 1) AS avg_tuples_per_scan
FROM pg_stat_user_indexes 
WHERE idx_scan > 0
ORDER BY idx_scan DESC;

Identify bloated indexes that need rebuilding:

SELECT 
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    pg_stat_get_live_tuples(indexrelid) AS live_tuples,
    pg_stat_get_dead_tuples(indexrelid) AS dead_tuples
FROM pg_stat_user_indexes 
WHERE pg_stat_get_dead_tuples(indexrelid) > pg_stat_get_live_tuples(indexrelid) * 0.1
ORDER BY pg_stat_get_dead_tuples(indexrelid) DESC;

Find unused indexes consuming storage:

SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes 
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

MySQL Index Rebuilding and Optimization

Rebuild fragmented MySQL indexes using several methods. The OPTIMIZE TABLE command rebuilds both data and indexes:

# For MyISAM tables
OPTIMIZE TABLE your_table;

# For InnoDB tables (creates a new table copy)
ALTER TABLE your_table ENGINE=InnoDB;

Rebuild specific indexes without touching table data:

# Drop and recreate index
DROP INDEX index_name ON table_name;
CREATE INDEX index_name ON table_name (column_name);

Use Percona Toolkit for online index operations:

# Install pt-online-schema-change
wget https://downloads.percona.com/downloads/percona-toolkit/3.5.7/binary/debian/bookworm/x86_64/percona-toolkit_3.5.7-1.bookworm_amd64.deb
sudo dpkg -i percona-toolkit_3.5.7-1.bookworm_amd64.deb

# Rebuild table with minimal downtime
pt-online-schema-change --alter "ENGINE=InnoDB" \
  --execute h=localhost,D=database_name,t=table_name

Monitor the rebuilding process:

SHOW PROCESSLIST;
SELECT * FROM information_schema.INNODB_TRX;

For HostMyCode VPS users, these operations run efficiently on our optimized MySQL configurations. We provide dedicated I/O resources that handle rebuilding operations smoothly.

PostgreSQL Database Index Maintenance Operations

PostgreSQL offers concurrent index rebuilding that doesn't block queries. Rebuild bloated indexes:

# Concurrent rebuild (recommended for production)
REINDEX INDEX CONCURRENTLY index_name;

# Rebuild entire table indexes
REINDEX TABLE CONCURRENTLY table_name;

# System-wide reindexing (use carefully)
REINDEX SCHEMA public;

Monitor long-running reindex operations:

SELECT 
    pid,
    now() - pg_stat_activity.query_start AS duration,
    query 
FROM pg_stat_activity 
WHERE state = 'active' AND query LIKE '%REINDEX%';

Update table statistics after major changes:

# Analyze specific table
ANALYZE table_name;

# Update all statistics
ANALYZE;

Configure automatic vacuuming for ongoing maintenance:

# Edit postgresql.conf
sudo nano /etc/postgresql/16/main/postgresql.conf

# Add these settings
autovacuum = on
autovacuum_naptime = 1min
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 50

MariaDB Index Optimization Techniques

MariaDB combines MySQL compatibility with enhanced optimization features. Check storage engine specifics:

SHOW TABLE STATUS WHERE Name = 'your_table';

Optimize Aria storage engine tables:

# Aria-specific optimization
REPAIR TABLE table_name QUICK;
OPTIMIZE TABLE table_name;

Use MariaDB's improved ALTER TABLE syntax:

# Online DDL operations
ALTER TABLE table_name 
    DROP INDEX old_index,
    ADD INDEX new_index (column1, column2),
    ALGORITHM=INPLACE, LOCK=NONE;

Monitor MariaDB-specific performance schema:

SELECT 
    object_schema,
    object_name,
    index_name,
    count_read,
    sum_timer_read,
    avg_timer_read
FROM performance_schema.table_io_waits_summary_by_index_usage 
WHERE object_schema NOT IN ('mysql', 'information_schema', 'performance_schema')
ORDER BY sum_timer_read DESC;

Automated Index Maintenance Scripts

Create automated maintenance scripts for consistent index health. Here's a MySQL maintenance script:

#!/bin/bash
# mysql_index_maintenance.sh

DB_USER="maintenance_user"
DB_PASS="secure_password"
LOG_FILE="/var/log/mysql_maintenance.log"

echo "$(date): Starting MySQL index maintenance" >> $LOG_FILE

# Find fragmented tables
mysql -u $DB_USER -p$DB_PASS -e "
SELECT CONCAT('OPTIMIZE TABLE ', table_schema, '.', table_name, ';') AS optimize_cmd
FROM information_schema.TABLES 
WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
    AND data_free > (data_length + index_length) * 0.1
    AND engine IN ('MyISAM', 'InnoDB');
" --skip-column-names > /tmp/optimize_commands.sql

# Execute optimization commands
if [ -s /tmp/optimize_commands.sql ]; then
    mysql -u $DB_USER -p$DB_PASS < /tmp/optimize_commands.sql
    echo "$(date): Optimization completed" >> $LOG_FILE
else
    echo "$(date): No tables need optimization" >> $LOG_FILE
fi

rm -f /tmp/optimize_commands.sql

PostgreSQL maintenance script:

#!/bin/bash
# postgresql_index_maintenance.sh

DB_NAME="your_database"
LOG_FILE="/var/log/postgresql_maintenance.log"

echo "$(date): Starting PostgreSQL index maintenance" >> $LOG_FILE

# Find bloated indexes and rebuild them
psql -d $DB_NAME -t -c "
SELECT 'REINDEX INDEX CONCURRENTLY ' || indexname || ';'
FROM pg_stat_user_indexes 
WHERE pg_stat_get_dead_tuples(indexrelid) > pg_stat_get_live_tuples(indexrelid) * 0.2
    AND idx_scan > 100;
" | while read cmd; do
    if [ -n "$cmd" ]; then
        echo "Executing: $cmd" >> $LOG_FILE
        psql -d $DB_NAME -c "$cmd"
    fi
done

# Update statistics
psql -d $DB_NAME -c "ANALYZE;" >> $LOG_FILE
echo "$(date): Maintenance completed" >> $LOG_FILE

Make scripts executable and schedule them:

chmod +x mysql_index_maintenance.sh postgresql_index_maintenance.sh

# Add to crontab for weekly execution
crontab -e

# Add these lines
0 2 * * 0 /path/to/mysql_index_maintenance.sh
30 2 * * 0 /path/to/postgresql_index_maintenance.sh

Monitoring Index Performance

Set up comprehensive monitoring to track index health over time. Create a monitoring database:

# MySQL monitoring table
CREATE TABLE index_performance_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    database_name VARCHAR(64),
    table_name VARCHAR(64),
    index_name VARCHAR(64),
    cardinality BIGINT,
    data_length BIGINT,
    index_length BIGINT,
    data_free BIGINT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

PostgreSQL monitoring view:

CREATE VIEW index_health_report AS
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    CASE 
        WHEN idx_scan = 0 THEN 'Unused'
        WHEN idx_tup_read / GREATEST(idx_scan, 1) > 1000 THEN 'Inefficient'
        ELSE 'Healthy'
    END AS health_status
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

You can reference our database monitoring and alerting guide for comprehensive performance tracking setup.

Index Maintenance Best Practices

Follow these practices for optimal index health:

• Schedule maintenance during low traffic: Run OPTIMIZE TABLE and REINDEX during off-peak hours
• Monitor before and after: Record query performance metrics before maintenance operations
• Test on staging first: Validate maintenance scripts on non-production databases
• Backup before major changes: Create database backups before rebuilding critical indexes
• Use concurrent operations: Prefer PostgreSQL's CONCURRENTLY option for production systems

Avoid these common mistakes:

• Running OPTIMIZE TABLE on large InnoDB tables during peak hours
• Rebuilding all indexes simultaneously on high-traffic databases
• Ignoring query plan changes after index maintenance
• Skipping statistics updates after major reorganizations

Troubleshooting Index Maintenance Issues

Common problems during maintenance and their solutions:

MySQL OPTIMIZE TABLE hanging:

# Check for locks
SHOW PROCESSLIST;
SELECT * FROM information_schema.INNODB_LOCKS;

# Kill blocking queries if necessary
KILL QUERY process_id;

PostgreSQL REINDEX CONCURRENTLY failing:

# Check for invalid indexes
SELECT indexname, indexdef 
FROM pg_indexes 
WHERE schemaname = 'public' 
    AND indexdef LIKE '%INVALID%';

# Drop invalid indexes
DROP INDEX CONCURRENTLY invalid_index_name;

Disk space issues during rebuilding:

# Monitor disk usage
df -h
du -sh /var/lib/mysql/
du -sh /var/lib/postgresql/

# Clean up temporary files
sudo find /tmp -name '*sql*' -mtime +1 -delete

For users experiencing persistent issues, our query execution plan analysis guide provides deeper troubleshooting techniques.

Frequently Asked Questions

How often should I rebuild database indexes?

Rebuild indexes when fragmentation exceeds 20% or query performance degrades significantly. Most production databases benefit from weekly analysis and monthly maintenance windows for major rebuilds.

Can I rebuild indexes on a live production database?

Yes, using PostgreSQL's REINDEX CONCURRENTLY or MySQL's pt-online-schema-change tool. Always test the process on staging environments first and monitor system resources during execution.

What's the difference between OPTIMIZE TABLE and REINDEX?

OPTIMIZE TABLE (MySQL) rebuilds the entire table structure including data and indexes. REINDEX (PostgreSQL) only rebuilds index structures. PostgreSQL's approach is typically faster and less resource-intensive.

How do I know if an index needs rebuilding?

Monitor index scan efficiency, fragmentation levels, and query execution times. Indexes with high dead tuple ratios (PostgreSQL) or significant data_free space (MySQL) typically need rebuilding.

Should I drop unused indexes to improve performance?

Generally yes, but verify they're truly unused by monitoring for at least 30 days. Some indexes might only be used for specific reports or batch operations that run infrequently.