SQL Performance and Index Optimization

The goal of this phase is to analyze and improve the performance of the most important database operations in the Online Learning Platform Management System.

The application contains several frequent operations:

browsing and filtering courses checking existing enrollments validating active user subscriptions loading course modules and lessons managing support tickets generating reports for enrollments, certificates and quiz attempts

The following indexes are not automatically created by primary key constraints, but they can improve the performance of WHERE filters, JOIN operations and report queries.

Testing Methodology

The performance analysis is done using PostgreSQL EXPLAIN ANALYZE.

The testing process is:

first, the query is executed without additional indexes then, the required index is created the same query is executed again the execution plan and execution time are compared

This allows us to check whether PostgreSQL uses Seq Scan, Index Scan or another optimized query plan.

Scenario 1: Course Browsing and Filtering

Users frequently browse available courses and filter them by course status, name or price.

SQL query:

EXPLAIN ANALYZE
SELECT c.course_id, c.name, c.price, c.status
FROM course c
WHERE c.status = 'ACTIVE'
ORDER BY c.price;

Indexes:

CREATE INDEX IF NOT EXISTS idx_course_status_price
ON course(status, price);

CREATE INDEX IF NOT EXISTS idx_course_name
ON course(name);

Explanation:

The composite index on (status, price) is useful because the query filters by status and sorts by price.

The index on name improves course searching by course name.

Scenario 2: Enrollment Checking

Before a user enrolls in a course, the system checks whether the user is already enrolled in that course.

SQL query:

EXPLAIN ANALYZE
SELECT e.enrollment_id, e.user_id, e.course_id, e.completion_status
FROM enrollment e
WHERE e.user_id = 1
AND e.course_id = 3;

Index:

CREATE INDEX IF NOT EXISTS idx_enrollment_user_course
ON enrollment(user_id, course_id);

Explanation:

This index is useful because the application frequently searches enrollments by both user_id and course_id.

Without this index, PostgreSQL may scan the whole enrollment table. With the index, the lookup is faster and more direct.

Scenario 3: Active Subscription Validation

When a user wants to access course content, the system checks whether the user has an active subscription.

SQL query:

EXPLAIN ANALYZE
SELECT us.subscription_id, us.user_id, us.plan_id, us.status, us.end_date
FROM user_subscription us
WHERE us.user_id = 1
AND us.status = 'ACTIVE'
AND us.end_date >= CURRENT_DATE;

Index:

CREATE INDEX IF NOT EXISTS idx_user_subscription_active
ON user_subscription(user_id, status, end_date);

Explanation:

The index improves subscription validation because the query filters by user_id, status and end_date.

This is important because subscription validation is performed before allowing access to paid course content.

Scenario 4: Instructor Course Management

Instructors frequently manage their own courses. The system displays all courses created by a selected instructor.

SQL query:

EXPLAIN ANALYZE
SELECT c.course_id, c.name, c.price, c.status
FROM course c
WHERE c.instructor_id = 5;

Index:

CREATE INDEX IF NOT EXISTS idx_course_instructor_id
ON course(instructor_id);

Explanation:

The instructor_id column is used for filtering courses by instructor. This index improves the performance of the instructor dashboard.

Scenario 5: Course Content Loading

When a user opens a course, the application loads all modules and lessons for that course.

SQL query:

EXPLAIN ANALYZE
SELECT
m.module_id,
m.title AS module_title,
l.lesson_id,
l.title AS lesson_title
FROM module m
JOIN lesson l ON l.module_id = m.module_id
WHERE m.course_id = 3
ORDER BY m.module_id, l.lesson_id;

Indexes:

CREATE INDEX IF NOT EXISTS idx_module_course_id
ON module(course_id);

CREATE INDEX IF NOT EXISTS idx_lesson_module_id
ON lesson(module_id);

Explanation:

The index on module(course_id) improves loading modules for a selected course.

The index on lesson(module_id) improves loading lessons for each module.

Scenario 6: Admin Support Ticket Filtering

Administrators frequently filter support tickets by status and check the newest tickets first.

SQL query:

EXPLAIN ANALYZE
SELECT st.ticket_id, st.subject, st.status, st.created_at
FROM support_ticket st
WHERE st.status = 'OPEN'
ORDER BY st.created_at DESC;

Index:

CREATE INDEX IF NOT EXISTS idx_support_ticket_status_created
ON support_ticket(status, created_at DESC);

Explanation:

This index is useful because the admin panel filters tickets by status and sorts them by created_at.

Scenario 7: Annual Enrollment and Certificate Report

This report analyzes enrollments, completed courses and issued certificates per year.

SQL query:

EXPLAIN ANALYZE
SELECT
EXTRACT(YEAR FROM e.enroll_date) AS year,
COUNT(DISTINCT e.enrollment_id) AS total_enrollments,
COUNT(DISTINCT CASE
WHEN e.completion_status = 'COMPLETED' THEN e.enrollment_id
END) AS completed_enrollments,
COUNT(DISTINCT c.certificate_id) AS issued_certificates,
AVG(e.progress_percentage) AS average_progress
FROM enrollment e
LEFT JOIN certificate c ON c.enrollment_id = e.enrollment_id
GROUP BY EXTRACT(YEAR FROM e.enroll_date)
ORDER BY year;

Indexes:

CREATE INDEX IF NOT EXISTS idx_enrollment_enroll_date
ON enrollment(enroll_date);

CREATE INDEX IF NOT EXISTS idx_enrollment_completion_status
ON enrollment(completion_status);

CREATE INDEX IF NOT EXISTS idx_certificate_enrollment_id
ON certificate(enrollment_id);

Explanation:

The index on enroll_date improves time-based reports.

The index on completion_status helps when filtering completed enrollments.

The index on certificate(enrollment_id) improves the join between certificate and enrollment.

Scenario 8: Quiz Attempts Report

The system can analyze quiz attempts by user, quiz and attempt date.

SQL query:

EXPLAIN ANALYZE
SELECT
qa.user_id,
qa.quiz_id,
COUNT(*) AS total_attempts,
AVG(qa.score) AS average_score
FROM quiz_attempt qa
GROUP BY qa.user_id, qa.quiz_id
ORDER BY qa.user_id;

Indexes:

CREATE INDEX IF NOT EXISTS idx_quiz_attempt_user_id
ON quiz_attempt(user_id);

CREATE INDEX IF NOT EXISTS idx_quiz_attempt_quiz_id
ON quiz_attempt(quiz_id);

CREATE INDEX IF NOT EXISTS idx_quiz_attempt_date
ON quiz_attempt(attempt_date);

Explanation:

These indexes improve reports that analyze quiz attempts by user, quiz or date.

Security and Protection Measures

Spring Security Authentication

The application uses Spring Security for authentication and authorization.

Unauthenticated users can access public pages such as:

home page login page registration page static resources such as CSS and JavaScript files

All other requests require authentication.

Example configuration:

@Bean
public SecurityFilterChain securityFilterChain(HttpSecurity http) throws Exception {

http
    .csrf(Customizer.withDefaults())
    .authorizeHttpRequests(auth -> auth
        .requestMatchers("/", "/login", "/register", "/css/**", "/js/**").permitAll()
        .anyRequest().authenticated()
    )
    .formLogin(form -> form
        .defaultSuccessUrl("/", true)
        .permitAll()
    )
    .logout(logout -> logout
        .logoutUrl("/logout")
        .logoutSuccessUrl("/login?logout")
        .permitAll()
    );

return http.build();

}

Explanation:

Spring Security protects the application by requiring users to log in before accessing protected resources.

The configuration also defines login and logout behavior.

Password Hashing with BCrypt

User passwords are not stored in plain text. The application uses BCrypt for password hashing.

Example:

@Bean
public PasswordEncoder passwordEncoder() {
return new BCryptPasswordEncoder();
}

During login, the raw password entered by the user is compared with the stored hashed password.

passwordEncoder.matches(rawPassword, storedHashedPassword);

Explanation:

This protects user accounts because even if someone gets access to the database, the original passwords are not directly visible.

Role-Based Access Control

The database contains a role attribute in the user_entity table.

role VARCHAR(20) NOT NULL CHECK (role IN ('USER', 'ADMIN', 'INSTRUCTOR'))

The system supports three roles:

USER — can browse courses, enroll in courses and access learning content INSTRUCTOR — can manage courses, modules, lessons and quizzes ADMIN — can manage categories, subscription plans and support tickets

Explanation:

The CHECK constraint prevents invalid roles from being inserted into the database.

The separate tables users, administrators and instructors also support the supertype/subtype structure of the system.

SQL Injection Prevention

SQL Injection is prevented by using Spring Data JPA and parameterized queries instead of directly concatenating user input into SQL strings.

Safe example:

@Query("SELECT u FROM UserEntity u WHERE u.email = ")
Optional findByEmail(@Param("email") String email);

Unsafe example:

String query = "SELECT * FROM user_entity WHERE email = '" + email + "'";

Explanation:

The safe approach treats user input as data, not as executable SQL code.

This prevents malicious input from changing the meaning of the SQL query.

Database Constraints and Data Integrity

The database schema contains constraints that protect the data from invalid states.

Examples:

email VARCHAR(150) UNIQUE NOT NULL

This prevents duplicate user accounts with the same email.

progress_percentage INT CHECK (progress_percentage BETWEEN 0 AND 100)

This prevents invalid course progress values.

certificate_code VARCHAR(100) UNIQUE NOT NULL

This ensures that every certificate has a unique certificate code.

enrollment_id INT UNIQUE NOT NULL

This ensures that one enrollment can have only one certificate.

Foreign keys are also used to protect relationships between tables:

FOREIGN KEY (user_id) REFERENCES users(id)
FOREIGN KEY (course_id) REFERENCES course(course_id)
FOREIGN KEY (enrollment_id) REFERENCES enrollment(enrollment_id)

Explanation:

These constraints prevent orphan records and keep the database consistent.

Transaction Management

Some operations must be executed as one atomic unit.

For example, when a user enrolls in a course, the system may need to:

check whether the user has an active subscription check whether the user is already enrolled insert a new enrollment record update related data

Example:

@Transactional
public void enrollUserInCourse(Long userId, Long courseId) {
// check active subscription
// check existing enrollment
// create new enrollment
}

Explanation:

If one step fails, the whole transaction is rolled back.

This protects the database from partial or inconsistent changes.

Database Connection Configuration

The application uses PostgreSQL as the database system.

Example configuration:

spring.application.name=db
spring.datasource.url=jdbc:postgresql://localhost:3307/db_202526z_va_prj_olpms
spring.datasource.username=USERNAME
spring.datasource.password=PASSWORD
spring.datasource.driver-class-name=org.postgresql.Driver

spring.jpa.hibernate.ddl-auto=validate
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect

Explanation:

Real database credentials should not be published on the wiki page. Because of that, the username and password are replaced with placeholders.

Spring Boot uses HikariCP as the default database connection pool implementation. Connection pooling improves performance by reusing already opened database connections instead of opening a new connection for every request.