Large Data Table with Excessive Number of Indices

Description

Large Data Table with Excessive Number of Indices occurs when a product implements a large data table that contains an excessively large number of indices. CISQ establishes recommended thresholds: tables exceeding 1,000,000 rows are considered "large," and more than 3 indices is considered "excessive." While indices improve read performance, they have significant overhead for write operations. Each index must be updated on every INSERT, UPDATE, or DELETE, and indices consume storage space. On very large tables, excessive indices dramatically slow down write operations.

Risk

Excessive indices on large tables have security implications. Write operations become slow, creating denial-of-service opportunities. Attackers can exploit slow write paths to exhaust database resources. Lock contention increases with more indices, enabling deadlock attacks. The storage overhead from indices can exhaust disk space. Index maintenance operations (rebuild, reorganize) take longer and can block other operations. Slow write performance can cause transaction timeouts and data inconsistency. Backup and recovery times increase significantly.

Solution

Analyze query patterns to identify truly necessary indices. Remove unused indices using database monitoring tools. Use composite indices instead of multiple single-column indices. Consider partial indices that index only relevant rows. Implement covering indices for frequently-run queries. Use appropriate index types (B-tree, hash, GiST) based on query patterns. Monitor index usage statistics and remove low-value indices. Consider index-organized tables or clustered indices carefully. Use database advisors to recommend optimal indexing. Balance read vs. write performance requirements.

Common Consequences

Impact	Details
Availability	Scope: Availability DoS: Resource Consumption - Write operations consume excessive resources updating all indices.
Availability	Scope: Availability Reduce Performance - INSERT, UPDATE, DELETE operations become very slow.
Other	Scope: Other Quality Degradation - Storage space and maintenance overhead increase significantly.

Example Code

Vulnerable Code

-- Vulnerable: Large table with too many indices
CREATE TABLE vulnerable_user_activity (
    id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    activity_type VARCHAR(50) NOT NULL,
    activity_timestamp TIMESTAMP NOT NULL,
    ip_address VARCHAR(45),
    user_agent TEXT,
    request_path VARCHAR(500),
    response_code INT,
    processing_time_ms INT,
    session_id VARCHAR(100),
    country_code CHAR(2),
    device_type VARCHAR(20),
    browser VARCHAR(50),
    os_name VARCHAR(50),
    referrer VARCHAR(500),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table will have 10+ million rows

-- Excessive indices (more than 3 on large table)
CREATE INDEX idx_user_id ON vulnerable_user_activity(user_id);
CREATE INDEX idx_activity_type ON vulnerable_user_activity(activity_type);
CREATE INDEX idx_timestamp ON vulnerable_user_activity(activity_timestamp);
CREATE INDEX idx_ip_address ON vulnerable_user_activity(ip_address);
CREATE INDEX idx_session_id ON vulnerable_user_activity(session_id);
CREATE INDEX idx_country ON vulnerable_user_activity(country_code);
CREATE INDEX idx_device ON vulnerable_user_activity(device_type);
CREATE INDEX idx_browser ON vulnerable_user_activity(browser);
CREATE INDEX idx_os ON vulnerable_user_activity(os_name);
CREATE INDEX idx_response_code ON vulnerable_user_activity(response_code);
CREATE INDEX idx_created_at ON vulnerable_user_activity(created_at);

-- 11 indices! Every INSERT must update ALL of them
-- With 10M+ rows, writes become extremely slow

-- Insert performance impact:
-- Without indices: ~1000 inserts/second
-- With 11 indices: ~50 inserts/second (or worse)

-- Storage impact:
-- Data: 5 GB
-- Indices: 15+ GB (3x the data!)

// Vulnerable: ORM with excessive indices defined
@Entity
@Table(name = "user_activity", indexes = {
    // Excessive - 10 indices on what will be a large table
    @Index(name = "idx_user", columnList = "userId"),
    @Index(name = "idx_type", columnList = "activityType"),
    @Index(name = "idx_time", columnList = "activityTimestamp"),
    @Index(name = "idx_ip", columnList = "ipAddress"),
    @Index(name = "idx_session", columnList = "sessionId"),
    @Index(name = "idx_country", columnList = "countryCode"),
    @Index(name = "idx_device", columnList = "deviceType"),
    @Index(name = "idx_browser", columnList = "browser"),
    @Index(name = "idx_os", columnList = "osName"),
    @Index(name = "idx_response", columnList = "responseCode")
})
public class VulnerableUserActivity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "user_id", nullable = false)
    private Long userId;

    @Column(name = "activity_type", length = 50, nullable = false)
    private String activityType;

    @Column(name = "activity_timestamp", nullable = false)
    private LocalDateTime activityTimestamp;

    // Many more columns...
}

// Batch insert becomes very slow
@Service
public class VulnerableActivityService {

    @Transactional
    public void logActivities(List<UserActivity> activities) {
        // With 10 indices, this batch insert is extremely slow
        // Each insert updates all 10 indices
        for (UserActivity activity : activities) {
            entityManager.persist(activity);
        }
        // 1000 activities might take 30+ seconds instead of < 1 second
    }
}

Fixed Code

-- Fixed: Large table with optimized indices
CREATE TABLE fixed_user_activity (
    id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    activity_type VARCHAR(50) NOT NULL,
    activity_timestamp TIMESTAMP NOT NULL,
    ip_address VARCHAR(45),
    user_agent TEXT,
    request_path VARCHAR(500),
    response_code INT,
    processing_time_ms INT,
    session_id VARCHAR(100),
    country_code CHAR(2),
    device_type VARCHAR(20),
    browser VARCHAR(50),
    os_name VARCHAR(50),
    referrer VARCHAR(500),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Fixed: Only 3 carefully chosen indices

-- Index 1: Composite index for most common query pattern
-- Covers: user lookup + time range + activity type filtering
CREATE INDEX idx_user_time_type ON fixed_user_activity(user_id, activity_timestamp, activity_type);

-- Index 2: For session-based queries
CREATE INDEX idx_session ON fixed_user_activity(session_id);

-- Index 3: For time-based cleanup/archival operations
CREATE INDEX idx_timestamp ON fixed_user_activity(activity_timestamp);

-- That's it! Only 3 indices

-- For other query patterns, use the table scan or consider:
-- 1. Materialized views for reporting
-- 2. Separate analytics tables
-- 3. Time-based partitioning

-- Partitioning for very large tables (alternative approach)
CREATE TABLE fixed_user_activity_partitioned (
    id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    activity_type VARCHAR(50) NOT NULL,
    activity_timestamp TIMESTAMP NOT NULL,
    -- other columns...
    PRIMARY KEY (id, activity_timestamp)
) PARTITION BY RANGE (activity_timestamp);

-- Create partitions by month
CREATE TABLE activity_2024_01 PARTITION OF fixed_user_activity_partitioned
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE activity_2024_02 PARTITION OF fixed_user_activity_partitioned
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
-- etc.

-- Each partition has smaller indices, faster writes

// Fixed: Optimized indices with proper strategy
@Entity
@Table(name = "user_activity", indexes = {
    // Only 3 carefully designed indices
    @Index(name = "idx_user_time_type",
           columnList = "userId, activityTimestamp, activityType"),
    @Index(name = "idx_session", columnList = "sessionId"),
    @Index(name = "idx_timestamp", columnList = "activityTimestamp")
})
public class FixedUserActivity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "user_id", nullable = false)
    private Long userId;

    @Column(name = "activity_type", length = 50, nullable = false)
    private String activityType;

    @Column(name = "activity_timestamp", nullable = false)
    private LocalDateTime activityTimestamp;

    @Column(name = "session_id", length = 100)
    private String sessionId;

    // Other columns without individual indices...
}

@Service
public class FixedActivityService {

    private final EntityManager entityManager;

    // Fixed: Efficient batch insert
    @Transactional
    public void logActivities(List<UserActivity> activities) {
        int batchSize = 50;

        for (int i = 0; i < activities.size(); i++) {
            entityManager.persist(activities.get(i));

            // Batch flush for better performance
            if (i > 0 && i % batchSize == 0) {
                entityManager.flush();
                entityManager.clear();
            }
        }
    }

    // Fixed: For analytics queries that don't need real-time data,
    // use separate reporting table or materialized view
    public List<ActivitySummary> getActivitySummary(Long userId,
            LocalDateTime start, LocalDateTime end) {
        // Query against materialized view instead of main table
        return entityManager.createQuery(
            "SELECT new ActivitySummary(a.activityType, COUNT(a)) " +
            "FROM ActivitySummaryView a " +
            "WHERE a.userId = :userId " +
            "AND a.activityDate BETWEEN :start AND :end " +
            "GROUP BY a.activityType",
            ActivitySummary.class)
            .setParameter("userId", userId)
            .setParameter("start", start)
            .setParameter("end", end)
            .getResultList();
    }
}

# Fixed: SQLAlchemy with optimized indices
from sqlalchemy import Column, BigInteger, String, DateTime, Index
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class FixedUserActivity(Base):
    """User activity with optimized indexing."""

    __tablename__ = 'user_activity'

    id = Column(BigInteger, primary_key=True)
    user_id = Column(BigInteger, nullable=False)
    activity_type = Column(String(50), nullable=False)
    activity_timestamp = Column(DateTime, nullable=False)
    ip_address = Column(String(45))
    session_id = Column(String(100))
    country_code = Column(String(2))
    device_type = Column(String(20))
    browser = Column(String(50))
    # More columns...

    __table_args__ = (
        # Fixed: Only 3 strategically designed indices

        # Composite index for common query pattern
        Index('idx_user_time_type', user_id, activity_timestamp, activity_type),

        # Session lookup
        Index('idx_session', session_id),

        # Time-based operations (cleanup, archival)
        Index('idx_timestamp', activity_timestamp),
    )


class ActivityService:
    """Service with optimized bulk operations."""

    def __init__(self, session):
        self._session = session

    def log_activities_bulk(self, activities: list) -> None:
        """Efficient bulk insert."""
        # Use bulk_insert_mappings for best performance
        self._session.bulk_insert_mappings(
            FixedUserActivity,
            [a.to_dict() for a in activities]
        )
        self._session.commit()

    def cleanup_old_activities(self, before_date: datetime) -> int:
        """Delete old activities using indexed timestamp."""
        # Uses idx_timestamp index
        result = self._session.query(FixedUserActivity).filter(
            FixedUserActivity.activity_timestamp < before_date
        ).delete(synchronize_session=False)
        self._session.commit()
        return result


# Alternative: Time-series database for very high volume
# Consider using TimescaleDB, InfluxDB, or similar for
# activity logging at scale

---

CVE Examples

This CWE is marked as PROHIBITED for direct CVE mapping as it represents a performance/quality concern rather than a direct security vulnerability.

---

Related CWEs

• CWE-405: Asymmetric Resource Consumption (Amplification) (parent)
• CWE-400: Uncontrolled Resource Consumption (can lead to)
• CWE-1067: Excessive Execution of Sequential Searches (related)

---

References

1. MITRE Corporation. "CWE-1089: Large Data Table with Excessive Number of Indices." https://cwe.mitre.org/data/definitions/1089.html
2. CISQ. "Automated Source Code Quality Measures."
3. Use The Index, Luke. "Too Many Indexes." https://use-the-index-luke.com/