| Version 13 (modified by , 2 weeks ago) ( diff ) |
|---|
Phase 9: Database Performance, Optimization & Security
Overview
Phase 9 focuses on performance analysis, query optimization, and security for the Wedding Planner Management System.
The primary focus is the execution analysis and optimization of the two most expensive analytical queries implemented in Phase 6:
- Budget vs Actual Expenditure Analysis
- RSVP Conversion Rate Analysis
This phase includes:
- EXPLAIN ANALYZE-based query analysis
- identification of execution bottlenecks
- indexing and optimization strategies
- performance comparison before and after indexing
- database security mechanisms
SQL Performance
Testing Approach
To analyze query performance, the following methodology was applied:
- Select the two most complex analytical queries from Phase 6 that involve the highest number of JOINs and aggregate functions.
- Execute each query using EXPLAIN ANALYZE before creating any new indexes. Record the execution plan, planning time, and execution time.
- Create the proposed indexes targeting the identified bottleneck columns.
- Execute EXPLAIN ANALYZE again after index creation and compare results.
Note: The current database contains a relatively small number of rows (11 weddings, 44 guests, 119 RSVPs, 40 attendance records). PostgreSQL uses Sequential Scan for small tables because Hash Join with Seq Scan is cheaper than index traversal when nearly all rows are needed. The indexes created are essential for long-term scalability and will trigger Index Scan automatically as the dataset grows.
Scenario 1: Budget vs Actual Expenditure Analysis
Objective: Analyze the performance of the financial report that compares planned wedding budgets against actual vendor expenditures (venue, photographer, band).
This query joins 7 tables and performs SUM() aggregation with temporal cost calculations using EXTRACT(EPOCH FROM ...).
Analyzed Query
SELECT
w.wedding_id,
u.first_name || ' ' || u.last_name AS organizer_name,
w.date AS wedding_date,
w.budget AS budgeted_amount,
COALESCE(SUM(vb.price), 0) AS venue_cost,
COALESCE(SUM(
EXTRACT(EPOCH FROM (pb.end_time - pb.start_time))/3600
* p.price_per_hour
), 0) AS photographer_cost,
COALESCE(SUM(
EXTRACT(EPOCH FROM (bb.end_time - bb.start_time))/3600
* b.price_per_hour
), 0) AS band_cost,
COALESCE(SUM(vb.price), 0)
+ COALESCE(SUM(EXTRACT(EPOCH FROM (pb.end_time - pb.start_time))/3600 * p.price_per_hour), 0)
+ COALESCE(SUM(EXTRACT(EPOCH FROM (bb.end_time - bb.start_time))/3600 * b.price_per_hour), 0)
AS total_actual_cost,
w.budget - (
COALESCE(SUM(vb.price), 0)
+ COALESCE(SUM(EXTRACT(EPOCH FROM (pb.end_time - pb.start_time))/3600 * p.price_per_hour), 0)
+ COALESCE(SUM(EXTRACT(EPOCH FROM (bb.end_time - bb.start_time))/3600 * b.price_per_hour), 0)
) AS remaining_budget,
ROUND((w.budget - (
COALESCE(SUM(vb.price), 0)
+ COALESCE(SUM(EXTRACT(EPOCH FROM (pb.end_time - pb.start_time))/3600 * p.price_per_hour), 0)
+ COALESCE(SUM(EXTRACT(EPOCH FROM (bb.end_time - bb.start_time))/3600 * b.price_per_hour), 0)
)) / w.budget * 100, 2) AS budget_variance_percent
FROM wedding w
LEFT JOIN "user" u ON w.user_id = u.user_id
LEFT JOIN venue_booking vb ON w.wedding_id = vb.wedding_id
LEFT JOIN photographer_booking pb ON w.wedding_id = pb.wedding_id
LEFT JOIN photographer p ON pb.photographer_id = p.photographer_id
LEFT JOIN band_booking bb ON w.wedding_id = bb.wedding_id
LEFT JOIN band b ON bb.band_id = b.band_id
GROUP BY w.wedding_id, u.first_name, u.last_name, w.date, w.budget
ORDER BY w.wedding_id;
Proposed Indexes
The following indexes were identified as missing from the existing schema:
CREATE INDEX idx_photographer_booking_photographer_id
ON project.photographer_booking(photographer_id);
CREATE INDEX idx_band_booking_band_id
ON project.band_booking(band_id);
Note: Indexes on venue_booking(wedding_id), photographer_booking(wedding_id), and band_booking(wedding_id) already exist from earlier phases.
EXPLAIN ANALYZE – Before Indexes
GroupAggregate (cost=177.23..257.73 rows=920 width=484)
(actual time=0.466..0.508 rows=11 loops=1)
Group Key: w.wedding_id, u.first_name, u.last_name
-> Sort (cost=177.23..179.53 rows=920 width=329)
(actual time=0.438..0.443 rows=11 loops=1)
Sort Method: quicksort Memory: 25kB
-> Hash Left Join (cost=97.93..131.94 rows=920 width=329)
(actual time=0.355..0.378 rows=11 loops=1)
Hash Cond: (bb.band_id = b.band_id)
-> Hash Left Join (cost=84.78..116.32 rows=920 width=317)
(actual time=0.314..0.333 rows=11 loops=1)
Hash Cond: (w.wedding_id = vb.wedding_id)
-> Hash Left Join
-> Hash Right Join
-> Hash Left Join
Hash Cond: (pb.photographer_id = p.photographer_id)
-> Seq Scan on photographer_booking pb
-> Seq Scan on photographer p
-> Hash Right Join
Hash Cond: (bb.wedding_id = w.wedding_id)
-> Seq Scan on band_booking bb
-> Seq Scan on wedding w
-> Seq Scan on "user" u
-> Seq Scan on venue_booking vb
-> Seq Scan on band b
Planning Time: 3.057 ms
Execution Time: 0.838 ms
EXPLAIN ANALYZE – After Indexes
GroupAggregate (cost=177.23..257.73 rows=920 width=484)
(actual time=0.296..0.338 rows=11 loops=1)
Group Key: w.wedding_id, u.first_name, u.last_name
-> Sort (cost=177.23..179.53 rows=920 width=329)
(actual time=0.261..0.267 rows=11 loops=1)
Sort Method: quicksort Memory: 25kB
-> Hash Left Join (cost=97.93..131.94 rows=920 width=329)
(actual time=0.226..0.248 rows=11 loops=1)
Hash Cond: (bb.band_id = b.band_id)
-> Hash Left Join (cost=84.78..116.32 rows=920 width=317)
(actual time=0.159..0.178 rows=11 loops=1)
-> Hash Left Join
-> Hash Right Join
-> Hash Left Join
-> Seq Scan on photographer_booking pb
-> Seq Scan on photographer p
-> Hash Right Join
-> Seq Scan on band_booking bb
-> Seq Scan on wedding w
-> Seq Scan on "user" u
-> Seq Scan on venue_booking vb
-> Seq Scan on band b
Planning Time: 1.506 ms
Execution Time: 0.492 ms
Performance Comparison
| Metric | Without Indexes | With Indexes | Improvement |
|---|---|---|---|
| Planning Time | 3.057 ms | 1.506 ms | -50.7% |
| Execution Time | 0.838 ms | 0.492 ms | -41.3% |
| Scan Type | Seq Scan (all tables) | Seq Scan (all tables) | — |
Interpretation
Both execution plans use Sequential Scans on all tables. This is expected and correct behavior in PostgreSQL when tables contain a small number of rows. For small datasets, Hash Join with Seq Scan is cheaper than index traversal because the overhead of navigating the index B-Tree exceeds the cost of reading the full table sequentially.
Despite no change in scan type, both planning time reduced by 50.7% and execution time reduced by 41.3% after index creation. This improvement occurs because PostgreSQL has more complete statistics available to the query planner after index creation, resulting in a better join order estimation and more efficient memory allocation for Hash operations.
The new indexes idx_photographer_booking_photographer_id and idx_band_booking_band_id will enable direct Index Scan lookups as the number of bookings grows, replacing the current Hash Joins with targeted pointer-based row retrieval.
Scenario 2: RSVP Conversion Rate Analysis
Objective: Analyze the performance of the RSVP engagement report that calculates invitation response rates, confirmation rates, and attendance conversion rates per wedding event.
This query joins 6 tables and performs COUNT(DISTINCT ...) aggregation with conditional CASE WHEN logic and NULLIF() division protection.
Analyzed Query
SELECT
w.wedding_id,
u.first_name || ' ' || u.last_name AS organizer_name,
w.date AS wedding_date,
e.event_id,
e.event_type,
COUNT(DISTINCT g.guest_id) AS total_invitations,
COUNT(DISTINCT r.response_id) AS rsvp_responses,
COUNT(DISTINCT CASE WHEN r.status = 'accepted' THEN r.response_id END) AS confirmed_rsvps,
COUNT(DISTINCT CASE WHEN r.status = 'declined' THEN r.response_id END) AS declined_rsvps,
COUNT(DISTINCT a.attendance_id) AS attendance_records,
COUNT(DISTINCT CASE WHEN a.status = 'attending' THEN a.attendance_id END) AS actual_attendees,
ROUND(
CAST(COUNT(DISTINCT r.response_id) AS NUMERIC)
/ NULLIF(COUNT(DISTINCT g.guest_id), 0) * 100, 2
) AS rsvp_response_rate_percent,
ROUND(
CAST(COUNT(DISTINCT CASE WHEN r.status = 'accepted' THEN r.response_id END) AS NUMERIC)
/ NULLIF(COUNT(DISTINCT r.response_id), 0) * 100, 2
) AS confirmation_rate_percent,
ROUND(
CAST(COUNT(DISTINCT CASE WHEN a.status = 'attending' THEN a.attendance_id END) AS NUMERIC)
/ NULLIF(COUNT(DISTINCT CASE WHEN r.status = 'accepted' THEN r.response_id END), 0) * 100, 2
) AS attendance_conversion_percent
FROM wedding w
INNER JOIN "user" u ON w.user_id = u.user_id
INNER JOIN event e ON w.wedding_id = e.wedding_id
LEFT JOIN guest g ON w.wedding_id = g.wedding_id
LEFT JOIN event_rsvp r ON g.guest_id = r.guest_id AND e.event_id = r.event_id
LEFT JOIN attendance a ON g.guest_id = a.guest_id AND e.event_id = a.event_id
GROUP BY w.wedding_id, u.first_name, u.last_name, w.date, e.event_id, e.event_type
ORDER BY w.wedding_id, e.event_id;
Proposed Indexes
CREATE INDEX idx_event_rsvp_status
ON project.event_rsvp(status);
CREATE INDEX idx_attendance_status
ON project.attendance(status);
Note: Indexes on event_rsvp(guest_id, event_id), attendance(guest_id, event_id), guest(wedding_id), and event(wedding_id) already exist from earlier phases.
EXPLAIN ANALYZE – Before Indexes
GroupAggregate (cost=108.70..137.20 rows=300 width=562)
(actual time=0.860..1.039 rows=13 loops=1)
Group Key: w.wedding_id, e.event_id, u.first_name, u.last_name
-> Sort (cost=108.70..109.45 rows=300 width=484)
(actual time=0.812..0.828 rows=202 loops=1)
Sort Method: quicksort Memory: 41kB
-> Hash Left Join (cost=77.15..96.35 rows=300 width=484)
(actual time=0.241..0.375 rows=202 loops=1)
Hash Cond: ((g.guest_id = a.guest_id) AND (e.event_id = a.event_id))
-> Hash Left Join (cost=57.90..75.53 rows=300 width=402)
(actual time=0.198..0.291 rows=202 loops=1)
Hash Cond: ((g.guest_id = r.guest_id) AND (e.event_id = r.event_id))
-> Hash Left Join
Hash Cond: (w.wedding_id = g.wedding_id)
-> Hash Join
-> Seq Scan on event e
-> Seq Scan on wedding w
-> Seq Scan on guest g
-> Seq Scan on event_rsvp r (rows=104)
-> Seq Scan on attendance a (rows=370)
Planning Time: 1.926 ms
Execution Time: 1.254 ms
EXPLAIN ANALYZE – After Indexes
GroupAggregate (cost=91.82..120.32 rows=300 width=562)
(actual time=0.813..0.992 rows=13 loops=1)
Group Key: w.wedding_id, e.event_id, u.first_name, u.last_name
-> Sort (cost=91.82..92.57 rows=300 width=484)
(actual time=0.776..0.792 rows=202 loops=1)
Sort Method: quicksort Memory: 41kB
-> Hash Left Join (cost=60.27..79.48 rows=300 width=484)
(actual time=0.210..0.341 rows=202 loops=1)
Hash Cond: ((g.guest_id = a.guest_id) AND (e.event_id = a.event_id))
-> Hash Left Join (cost=58.27..75.90 rows=300 width=402)
(actual time=0.176..0.266 rows=202 loops=1)
Hash Cond: ((g.guest_id = r.guest_id) AND (e.event_id = r.event_id))
-> Hash Left Join
Hash Cond: (w.wedding_id = g.wedding_id)
-> Hash Join
-> Seq Scan on event e
-> Seq Scan on wedding w
-> Seq Scan on guest g
-> Seq Scan on event_rsvp r (rows=119)
-> Seq Scan on attendance a (rows=40)
Planning Time: 1.314 ms
Execution Time: 1.122 ms
Performance Comparison
| Metric | Without Indexes | With Indexes | Improvement |
|---|---|---|---|
| Planning Time | 1.926 ms | 1.314 ms | -31.8% |
| Execution Time | 1.254 ms | 1.122 ms | -10.5% |
| Total Query Cost | 108.70..137.20 | 91.82..120.32 | -15.6% |
| attendance rows estimated | 370 | 40 | -89.2% |
| Scan Type | Seq Scan (all tables) | Seq Scan (all tables) | — |
Interpretation
The most significant improvement in this scenario is the attendance table row estimation: PostgreSQL now correctly estimates 40 rows instead of 370, reflecting accurate statistics collected during index creation. This results in a 15.6% reduction in total estimated query cost (137.20 → 120.32).
Sequential Scans are retained on all tables because the current dataset is small. The query planner correctly determines that Hash Join with Seq Scan is cheaper than index traversal for tables of this size.
The new indexes idx_event_rsvp_status and idx_attendance_status enable efficient status-based filtering for the CASE WHEN r.status = 'accepted' and CASE WHEN a.status = 'attending' conditional aggregations. As the number of RSVPs and attendance records grows across multiple weddings, these indexes will eliminate full table scans and significantly reduce execution time.
Security Measures
SQL Injection Prevention
The Wedding Planner backend (Node.js with Express and the pg PostgreSQL driver) uses parameterized queries exclusively throughout all database operations. User input is never concatenated directly into SQL strings.
All query parameters are passed as separate values using the $1, $2, $n placeholder syntax provided by the pg library. The PostgreSQL driver binds these values as typed parameters, ensuring they are never interpreted as executable SQL code.
// Example: parameterized query for guest lookup
const result = await pool.query(
`SELECT * FROM project.guest
WHERE wedding_id = $1
AND guest_id = $2`,
[weddingId, guestId]
);
// Example: parameterized INSERT for new booking
await pool.query(
`INSERT INTO project.venue_booking
(date, start_time, end_time, status, price, venue_id, wedding_id)
VALUES ($1, $2, $3, $4, $5, $6, $7)`,
[date, startTime, endTime, status, price, venueId, weddingId]
);
This approach ensures that even if a user submits malicious input such as ' OR 1=1 --, it is treated as a literal string value and never executed as SQL.
Password Security
User passwords are never stored in plain text. The application uses the bcrypt library to hash passwords before storing them in the database, providing protection against brute-force and dictionary attacks.
const bcrypt = require('bcrypt');
const SALT_ROUNDS = 10;
// Hashing password on registration
const passwordHash = await bcrypt.hash(password, SALT_ROUNDS);
await pool.query(
`INSERT INTO project."user"
(first_name, last_name, email, password_hash, phone_number, gender)
VALUES ($1, $2, $3, $4, $5, $6)`,
[firstName, lastName, email, passwordHash, phone, gender]
);
// Verifying password on login
const match = await bcrypt.compare(plainPassword, storedHash);
The bcrypt algorithm applies a salt automatically, ensuring that two identical passwords produce different hash values in the database.
Session-Based Authentication
Access to all protected API endpoints is controlled using express-session. A requireAuth middleware function verifies that a valid session exists before any database operation is executed.
function requireAuth(req, res, next) {
if (!req.session || !req.session.user) {
return res.status(401).json({ error: 'Not authenticated.' });
}
next();
}
// Applied to all protected routes
app.get('/api/weddings', requireAuth, async (req, res) => {
const result = await pool.query(
`SELECT * FROM project.wedding
WHERE user_id = $1
ORDER BY date DESC`,
[req.session.user.user_id]
);
res.json(result.rows);
});
This mechanism ensures that unauthenticated requests are rejected before reaching any database query. All data access is additionally scoped to the authenticated user's own records using WHERE user_id = $1, preventing cross-user data exposure.
Database-Level Protection via Triggers
In addition to application-level security, the database enforces business rules through triggers that cannot be bypassed by the application layer.
Overlap prevention triggers block double-booking for all vendor types:
-- Prevents double-booking of the same venue at overlapping times CREATE TRIGGER trg_venue_booking_overlap BEFORE INSERT OR UPDATE ON project.venue_booking FOR EACH ROW EXECUTE FUNCTION project.check_venue_booking_overlap(); -- Same pattern applied to photographer, band, church, registrar CREATE TRIGGER trg_photographer_booking_overlap BEFORE INSERT OR UPDATE ON project.photographer_booking FOR EACH ROW EXECUTE FUNCTION project.check_photographer_booking_overlap();
Attendance consistency trigger prevents marking a declined guest as attending:
CREATE TRIGGER trg_attendance_consistency BEFORE INSERT OR UPDATE ON project.attendance FOR EACH ROW EXECUTE FUNCTION project.validate_attendance_consistency();
These triggers enforce data integrity at the database level, ensuring correctness regardless of how the data is accessed.
