| Version 3 (modified by , 3 days ago) ( diff ) |
|---|
Security and Optimization
Assertions (CHECK Constraints)
To guarantee fundamental data validity at the lowest level, we utilize CHECK constraints. These declarative rules prevent invalid data from ever being written to the database, ensuring a baseline of data quality across the system.
| Table | Constraint Name | Rule | Purpose |
|---|---|---|---|
| employees | employees_net_salary_nonneg | net_salary >= 0 (or NULL) | Prevents negative payroll values while allowing them to be unset. |
| employees | employees_gross_salary_nonneg | gross_salary >= 0 (or NULL) | Prevents negative payroll values while allowing them to be unset. |
| inventories | inventories_qty_nonneg | quantity >= 0 | Ensures that product stock levels cannot be negative. |
| inventories | inventories_restock_nonneg | restock_level >= 0 (or NULL) | Ensures that restock thresholds cannot be negative. |
| products | products_price_nonneg | price >= 0 | Disallows products from having negative prices. |
| order_items | order_items_qty_pos | quantity > 0 | Requires that every item added to an order has a positive quantity. |
| order_items | order_items_price_nonneg | price >= 0 | Disallows negative unit prices on ordered items. |
| payments | payments_amount_nonneg | amount >= 0 | Ensures all payment and tip amounts are non-negative. |
| payments | payments_tip_nonneg | tip_amount >= 0 | Ensures all payment and tip amounts are non-negative. |
| tables | tables_capacity_pos | seat_capacity > 0 | Requires that every restaurant table has a positive seating capacity. |
| reservations | reservations_people_pos | number_of_people > 0 | Ensures that a reservation is made for at least one person. |
| front_staff | front_staff_tip_pct | tip_percent BETWEEN 0 AND 100 (or NULL) | Confines the tip percentage to a valid range if specified. |
Index Performance Analysis
We conducted a thorough analysis of key indexes to validate their performance impact on critical query patterns. The following sections detail the results, comparing query plans using EXPLAIN (ANALYZE, BUFFERS) with and without each index.
Index: frontstaff_managed_reservations(table_number) — fmr_table_idx
This B-Tree index is designed to accelerate lookups on the frontstaff_managed_reservations table for a specific table_number. This is a frequent operation for front-of-house staff managing table assignments.
Query Analyzed: SELECT * FROM frontstaff_managed_reservations WHERE table_number = 1;
Selectivity: High (~0.1% of 200k rows)
WITHOUT INDEX:
Seq Scan on frontstaff_managed_reservations (cost=0.00..3971.01 rows=260 width=32) (actual time=0.061..16.195 rows=201 loops=1) Filter: (table_number = 1) Rows Removed by Filter: 199800 Buffers: shared hit=32 read=1439 Planning: Buffers: shared hit=14 read=1 Planning Time: 0.524 ms Execution Time: 16.235 ms
WITH INDEX:
Bitmap Heap Scan on frontstaff_managed_reservations (cost=5.87..529.57 rows=187 width=32) (actual time=0.068..0.270 rows=201 loops=1) Recheck Cond: (table_number = 1) Heap Blocks: exact=201 Buffers: shared hit=204 read=3 -> Bitmap Index Scan on fmr_table_idx (cost=0.00..5.82 rows=187 width=0) (actual time=0.044..0.044 rows=201 loops=1) Index Cond: (table_number = 1) Buffers: shared hit=3 read=3 Planning: Buffers: shared hit=16 read=1 Planning Time: 0.176 ms Execution Time: 0.297 ms
Performance Impact: The index provides a ~54x speedup in execution time (16.235 ms → 0.297 ms). More importantly, it reduces disk reads by over 450x (from 1439 to 3), transforming an inefficient full table scan into a highly targeted index lookup.
Conclusion: This index is essential and provides a massive performance gain for selective table lookups, a core workflow of the application. Retain.
GiST Index: reservations_span_expr_gist
This is a GiST expression index on a tsrange created from reservation start times and stay lengths. It is built to optimize time-based overlap queries, which are critical for preventing double bookings and checking table availability.
Query Analyzed: tsrange(...) && tsrange(...) (checking for overlap in the next 30 minutes)
Selectivity: Medium (~2.1% of 500k rows)
WITHOUT INDEX:
Gather (cost=1000.00..13985.52 rows=5000 width=8) (actual time=59.230..141.002 rows=10627 loops=1) Workers Planned: 2 Workers Launched: 2 Buffers: shared hit=94 read=4641 -> Parallel Seq Scan on reservations (cost=0.00..12485.52 rows=2083 width=8) (actual time=55.539..135.006 rows=3542 loops=3) " Filter: (tsrange(datetime, (datetime + ((COALESCE(stay_length, '2'::numeric))::double precision * '01:00:00'::interval)), '[)'::text) && tsrange((now())::timestamp without time zone, ((now())::timestamp without time zone + '00:30:00'::interval), '[)'::text))" Rows Removed by Filter: 163125 Buffers: shared hit=94 read=4641 Planning: Buffers: shared hit=5 Planning Time: 0.111 ms Execution Time: 141.415 ms
WITH INDEX:
Bitmap Heap Scan on reservations (cost=480.45..5507.06 rows=9310 width=8) (actual time=2.161..3.291 rows=10515 loops=1) " Recheck Cond: (tsrange(datetime, (datetime + ((COALESCE(stay_length, '2'::numeric))::double precision * '01:00:00'::interval)), '[)'::text) && tsrange((now())::timestamp without time zone, ((now())::timestamp without time zone + '00:30:00'::interval), '[)'::text))" Heap Blocks: exact=167 Buffers: shared hit=249 -> Bitmap Index Scan on reservations_span_expr_gist (cost=0.00..478.12 rows=9310 width=0) (actual time=2.117..2.117 rows=10515 loops=1) " Index Cond: (tsrange(datetime, (datetime + ((COALESCE(stay_length, '2'::numeric))::double precision * '01:00:00'::interval)), '[)'::text) && tsrange((now())::timestamp without time zone, ((now())::timestamp without time zone + '00:30:00'::interval), '[)'::text))" Buffers: shared hit=82 Planning Time: 0.101 ms Execution Time: 3.646 ms
Performance Impact: The GiST index is purpose-built for this type of query and delivers a ~39x speedup (141.415 ms → 3.646 ms). It completely eliminates disk reads for this query by efficiently handling the range overlap && operator.
Conclusion: This index is fundamental for enforcing time-overlap constraints efficiently. It is critical for availability checks, double-booking prevention, and time-window dashboards. Retain.
Index: payments(order_id) — idx_payments_order
A standard B-Tree index on the order_id foreign key column in the payments table. This is intended to speed up joins and direct lookups from an order to its associated payments.
Queries Analyzed: COUNT(*) and JOIN on payments for a specific order_id.
Selectivity: Extremely high (finding a few payments among ~700k rows)
WITHOUT INDEX (COUNT Query):
Finalize Aggregate (cost=14543.27..14543.28 rows=1 width=8) (actual time=32.037..34.410 rows=1 loops=1) Buffers: shared hit=3849 read=4489 InitPlan 2 (returns $1) -> Result (cost=0.65..0.66 rows=1 width=8) (actual time=0.016..0.018 rows=1 loops=1) Buffers: shared hit=4 InitPlan 1 (returns $0) -> Limit (cost=0.41..0.65 rows=1 width=8) (actual time=0.014..0.015 rows=1 loops=1) Buffers: shared hit=4 -> Index Only Scan using orders_pkey on orders (cost=0.41..4750.47 rows=20003 width=8) (actual time=0.012..0.013 rows=1 loops=1) Index Cond: (id IS NOT NULL) Heap Fetches: 0 Buffers: shared hit=4 -> Gather (cost=14542.39..14542.60 rows=2 width=8) (actual time=31.865..34.400 rows=3 loops=1) Workers Planned: 2 Params Evaluated: $1 Workers Launched: 2 Buffers: shared hit=3849 read=4489 -> Partial Aggregate (cost=13542.39..13542.40 rows=1 width=8) (actual time=27.452..27.453 rows=1 loops=3) Buffers: shared hit=3845 read=4489 -> Parallel Seq Scan on payments (cost=0.00..13542.34 rows=21 width=0) (actual time=27.448..27.448 rows=0 loops=3) Filter: (order_id = $1) Rows Removed by Filter: 333334 Buffers: shared hit=3845 read=4489 Planning: Buffers: shared hit=5 Planning Time: 0.166 ms Execution Time: 34.445 ms
WITH INDEX (COUNT Query):
Aggregate (cost=6.08..6.09 rows=1 width=8) (actual time=0.095..0.096 rows=1 loops=1) Buffers: shared hit=4 read=3 InitPlan 2 (returns $1) -> Result (cost=0.65..0.66 rows=1 width=8) (actual time=0.012..0.013 rows=1 loops=1) Buffers: shared hit=4 InitPlan 1 (returns $0) -> Limit (cost=0.41..0.65 rows=1 width=8) (actual time=0.010..0.011 rows=1 loops=1) Buffers: shared hit=4 -> Index Only Scan using orders_pkey on orders (cost=0.41..4750.47 rows=20003 width=8) (actual time=0.009..0.009 rows=1 loops=1) Index Cond: (id IS NOT NULL) Heap Fetches: 0 Buffers: shared hit=4 -> Index Only Scan using idx_payments_order on payments (cost=0.42..5.30 rows=50 width=0) (actual time=0.092..0.092 rows=0 loops=1) Index Cond: (order_id = $1) Heap Fetches: 0 Buffers: shared hit=4 read=3 Planning: Buffers: shared hit=5 read=1 Planning Time: 0.258 ms Execution Time: 0.120 ms
WITHOUT INDEX (JOIN Query):
Nested Loop (cost=1001.07..14552.93 rows=50 width=21) (actual time=31.125..33.280 rows=0 loops=1) Buffers: shared hit=3949 read=4393 InitPlan 2 (returns $1) -> Result (cost=0.65..0.66 rows=1 width=8) (actual time=0.013..0.015 rows=1 loops=1) Buffers: shared hit=4 InitPlan 1 (returns $0) -> Limit (cost=0.41..0.65 rows=1 width=8) (actual time=0.011..0.012 rows=1 loops=1) Buffers: shared hit=4 -> Index Only Scan using orders_pkey on orders (cost=0.41..4750.47 rows=20003 width=8) (actual time=0.009..0.010 rows=1 loops=1) Index Cond: (id IS NOT NULL) Heap Fetches: 0 Buffers: shared hit=4 -> Index Only Scan using orders_pkey on orders o (cost=0.41..4.43 rows=1 width=8) (actual time=0.020..0.025 rows=1 loops=1) Index Cond: (id = $1) Heap Fetches: 0 Buffers: shared hit=8 -> Gather (cost=1000.00..14547.34 rows=50 width=29) (actual time=31.097..33.248 rows=0 loops=1) Workers Planned: 2 Params Evaluated: $1 Workers Launched: 2 Buffers: shared hit=3941 read=4393 -> Parallel Seq Scan on payments p (cost=0.00..13542.34 rows=21 width=29) (actual time=25.975..25.976 rows=0 loops=3) Filter: (order_id = $1) Rows Removed by Filter: 333334 Buffers: shared hit=3941 read=4393 Planning: Buffers: shared hit=14 Planning Time: 0.225 ms Execution Time: 33.313 ms
WITH INDEX (JOIN Query):
Nested Loop (cost=5.88..199.41 rows=50 width=21) (actual time=0.033..0.034 rows=0 loops=1) Buffers: shared hit=11 InitPlan 2 (returns $1) -> Result (cost=0.65..0.66 rows=1 width=8) (actual time=0.015..0.016 rows=1 loops=1) Buffers: shared hit=4 InitPlan 1 (returns $0) -> Limit (cost=0.41..0.65 rows=1 width=8) (actual time=0.013..0.014 rows=1 loops=1) Buffers: shared hit=4 -> Index Only Scan using orders_pkey on orders (cost=0.41..4750.47 rows=20003 width=8) (actual time=0.011..0.012 rows=1 loops=1) Index Cond: (id IS NOT NULL) Heap Fetches: 0 Buffers: shared hit=4 -> Index Only Scan using orders_pkey on orders o (cost=0.41..4.43 rows=1 width=8) (actual time=0.022..0.023 rows=1 loops=1) Index Cond: (id = $1) Heap Fetches: 0 Buffers: shared hit=8 -> Bitmap Heap Scan on payments p (cost=4.81..193.82 rows=50 width=29) (actual time=0.008..0.008 rows=0 loops=1) Recheck Cond: (order_id = $1) Buffers: shared hit=3 -> Bitmap Index Scan on idx_payments_order (cost=0.00..4.80 rows=50 width=0) (actual time=0.006..0.006 rows=0 loops=1) Index Cond: (order_id = $1) Buffers: shared hit=3 Planning: Buffers: shared hit=16 dirtied=5 Planning Time: 0.258 ms Execution Time: 0.075 ms
Performance Impact: For highly selective equality lookups, the index offers a dramatic ~280-440x speedup. It allows the database to avoid scanning hundreds of thousands of payment rows by jumping directly to the relevant entries. The ability to perform an "Index Only Scan" for the count is especially efficient.
Conclusion: This index is indispensable for fast foreign key lookups and joins, a foundational operation for retrieving order and payment details. Retain.
Index: payments(created_at) — idx_payments_created_at
A time-series B-Tree index on the created_at timestamp in the payments table. Its value depends heavily on the selectivity of the time window being queried.
Queries Analyzed: COUNT(*) on payments over a broad (90-day) and a narrow (1-minute) time window.
WITHOUT INDEX (90-day window):
Finalize Aggregate (cost=34333.68..34333.69 rows=1 width=8) (actual time=231.944..234.358 rows=1 loops=1) Buffers: shared hit=7491 read=9176 -> Gather (cost=34333.47..34333.68 rows=2 width=8) (actual time=231.831..234.350 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 Buffers: shared hit=7491 read=9176 -> Partial Aggregate (cost=33333.47..33333.48 rows=1 width=8) (actual time=221.112..221.113 rows=1 loops=3) Buffers: shared hit=7491 read=9176 -> Parallel Seq Scan on payments (cost=0.00..31250.34 rows=833250 width=0) (actual time=0.033..185.312 rows=666667 loops=3) Filter: (created_at >= (now() - '90 days'::interval)) Buffers: shared hit=7491 read=9176 Planning: Buffers: shared hit=7 Planning Time: 0.117 ms Execution Time: 234.384 ms
WITH INDEX (90-day window):
Finalize Aggregate (cost=34333.89..34333.90 rows=1 width=8) (actual time=236.606..239.043 rows=1 loops=1) Buffers: shared hit=7907 read=8760 -> Gather (cost=34333.68..34333.89 rows=2 width=8) (actual time=236.487..239.034 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 Buffers: shared hit=7907 read=8760 -> Partial Aggregate (cost=33333.68..33333.69 rows=1 width=8) (actual time=232.049..232.050 rows=1 loops=3) Buffers: shared hit=7907 read=8760 -> Parallel Seq Scan on payments (cost=0.00..31250.34 rows=833334 width=0) (actual time=0.038..194.022 rows=666667 loops=3) Filter: (created_at >= (now() - '90 days'::interval)) Buffers: shared hit=7907 read=8760 Planning: Buffers: shared hit=7 Planning Time: 0.194 ms Execution Time: 239.074 ms
WITHOUT INDEX (1-minute window):
Finalize Aggregate (cost=32250.76..32250.77 rows=1 width=8) (actual time=116.163..119.672 rows=1 loops=1) Buffers: shared hit=7587 read=9080 -> Gather (cost=32250.55..32250.76 rows=2 width=8) (actual time=115.972..119.662 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 Buffers: shared hit=7587 read=9080 -> Partial Aggregate (cost=31250.55..31250.56 rows=1 width=8) (actual time=111.887..111.889 rows=1 loops=3) Buffers: shared hit=7587 read=9080 -> Parallel Seq Scan on payments (cost=0.00..31250.34 rows=83 width=0) (actual time=111.883..111.884 rows=0 loops=3) Filter: (created_at >= (now() - '00:01:00'::interval)) Rows Removed by Filter: 666667 Buffers: shared hit=7587 read=9080 Planning Time: 0.086 ms Execution Time: 119.739 ms
WITH INDEX (1-minute window):
Aggregate (cost=4.45..4.46 rows=1 width=8) (actual time=0.023..0.023 rows=1 loops=1) Buffers: shared hit=3 -> Index Only Scan using idx_payments_created_at on payments (cost=0.43..4.45 rows=1 width=0) (actual time=0.021..0.021 rows=0 loops=1) Index Cond: (created_at >= (now() - '00:01:00'::interval)) Heap Fetches: 0 Buffers: shared hit=3 Planning: Buffers: shared hit=2 read=2 Planning Time: 0.288 ms Execution Time: 0.069 ms
Performance Impact: The analysis shows two distinct behaviors:
Broad Window (Low Selectivity): When the query selects a large fraction of the table (e.g., 90 days of data), the planner correctly determines that a full Parallel Seq Scan is more efficient. The index is ignored and offers no benefit.
Narrow Window (High Selectivity): When querying a tiny, recent time slice (e.g., the last minute), the index is overwhelmingly effective, providing a ~1,700x speedup (119.739 ms → 0.069 ms) and using an extremely fast Index Only Scan.
Conclusion: This index is highly valuable for "recent data" use cases like real-time dashboards or alerts. It is neutral for broad analytical queries where sequential scans are appropriate. The planner correctly chooses the best path in both scenarios. Retain.
Triggered Business Rules
For more complex, state-dependent business logic that cannot be handled by simple CHECK constraints, we leverage triggers. These automatically execute procedural functions in response to specific data modification events (e.g., INSERT, UPDATE), thereby enforcing sophisticated application rules directly within the database.
TRIGGER: Prevent Overlapping Table Bookings
Objective: To guarantee that a single restaurant table cannot be assigned to two different reservations that overlap in time.
Associated Function: enforce_no_double_booking()
Events: AFTER INSERT, AFTER UPDATE on the frontstaff_managed_reservations table. This is implemented as a CONSTRAINT trigger, which is DEFERRABLE INITIALLY DEFERRED.
Implementation Details: The trigger calculates the time interval of the incoming reservation using PostgreSQL's tsrange type, with a default stay length of 2 hours if not specified. The range is defined with inclusive start and exclusive end bounds ([)) to allow for back-to-back bookings. It then queries existing reservations for the same table to check for any temporal overlap using the && operator. If a conflict is detected, the transaction is rolled back with an exception.
Critically, the use of DEFERRABLE INITIALLY DEFERRED means the constraint is checked at the end of the transaction, not after each row modification. This is essential for correctly handling multi-row inserts or updates that might otherwise fail prematurely.
CREATE OR REPLACE FUNCTION enforce_no_double_booking() RETURNS trigger AS $$ DECLARE new_span tsrange; conflict_exists boolean; BEGIN SELECT tsrange( r.datetime, r.datetime + (COALESCE(r.stay_length, 2) * INTERVAL '1 hour'), '[)' ) INTO new_span FROM reservations r WHERE r.id = NEW.reservation_id; IF new_span IS NULL THEN RAISE EXCEPTION 'Reservation % not found or invalid', NEW.reservation_id; END IF; SELECT EXISTS ( SELECT 1 FROM frontstaff_managed_reservations fmr JOIN reservations r2 ON r2.id = fmr.reservation_id WHERE fmr.table_number = NEW.table_number AND (NEW.id IS NULL OR fmr.id <> NEW.id) -- Exclude self in UPDATE AND tsrange( r2.datetime, r2.datetime + (COALESCE(r2.stay_length, 2) * INTERVAL '1 hour'), '[)' ) && new_span ) INTO conflict_exists; IF conflict_exists THEN RAISE EXCEPTION 'Double booking prevented: table % has overlapping reservations', NEW.table_number; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql;
Supporting Indexes for Performance:
An index on frontstaff_managed_reservations(table_number) to rapidly locate all reservations for the target table.
A functional GiST index on the tsrange expression (reservations_span_expr_gist) to allow for highly efficient range overlap (&&) searches.
TRIGGER: Enforce Seating Capacity
Objective: To prevent a reservation from being assigned to a table that has insufficient seating capacity for the party size.
Associated Function: fmr_capacity_guard()
Events: BEFORE INSERT, BEFORE UPDATE on frontstaff_managed_reservations.
Implementation Details: Before a row is written, this trigger fetches the seat_capacity of the target table and the number_of_people from the associated reservation. It verifies that the party size does not exceed the table's capacity. If it does, an exception is raised, preventing the invalid assignment.
CREATE OR REPLACE FUNCTION fmr_capacity_guard() RETURNS trigger AS $$ DECLARE cap int; party bigint; BEGIN SELECT seat_capacity INTO cap FROM tables WHERE table_number = NEW.table_number; SELECT number_of_people INTO party FROM reservations WHERE id = NEW.reservation_id; IF cap IS NULL OR party IS NULL THEN RAISE EXCEPTION 'Invalid reservation % or table %', NEW.reservation_id, NEW.table_number; END IF; IF party > cap THEN RAISE EXCEPTION 'Party size % exceeds capacity % for table %', party, cap, NEW.table_number; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql;
Supporting Indexes for Performance:
No special indexes are needed. The trigger's lookups are highly efficient as they operate on the primary keys of the tables and reservations tables.
TRIGGER: Update Order Status Upon Payment
Objective: To automate the business process of marking an order as 'PAID' as soon as a payment is recorded for it.
Associated Function: payments_mark_order_paid()
Events: AFTER INSERT on the payments table.
Implementation Details: Immediately after a new record is successfully inserted into the payments table, this trigger fires an UPDATE statement on the orders table. It sets the status of the corresponding order (identified by NEW.order_id) to 'PAID', ensuring the system state remains consistent.
CREATE OR REPLACE FUNCTION payments_mark_order_paid() RETURNS trigger AS $$ BEGIN UPDATE orders SET status = 'PAID' WHERE id = NEW.order_id; RETURN NEW; END; $$ LANGUAGE plpgsql;
Supporting Indexes for Performance:
The UPDATE operation is optimized by the primary key index on orders(id).
Future Considerations:
This implementation assumes that any single payment settles the full order amount. For a system supporting partial payments, this logic would need to be enhanced to check if the sum of payments for an order meets or exceeds the order's total before changing the status.
MATERIALIZED VIEW: Pre-Aggregated Daily Sales Analytics
To support high-frequency analytical queries without imposing a heavy real-time aggregation load on the core transactional tables, we employ a materialized view. This view, mv_payments_daily_channel, pre-computes daily revenue, tips, and order counts, segmented by the sales channel (TAB or ONLINE).
CREATE MATERIALIZED VIEW IF NOT EXISTS mv_payments_daily_channel AS
WITH orders_channel AS (
SELECT
o.id AS order_id,
CASE
WHEN EXISTS (SELECT 1 FROM tab_orders t WHERE t.order_id = o.id) THEN 'TAB'
WHEN EXISTS (SELECT 1 FROM online_orders oo WHERE oo.order_id = o.id) THEN 'ONLINE'
ELSE 'UNKNOWN'
END AS channel
FROM orders o
)
SELECT
(date_trunc('day', p.created_at))::date AS day,
oc.channel,
COUNT(DISTINCT p.order_id) AS paid_orders_cnt,
SUM(p.amount)::numeric(14,2) AS revenue,
SUM(p.tip_amount)::numeric(14,2) AS tip_total
FROM payments p
JOIN orders_channel oc ON oc.order_id = p.order_id
GROUP BY (date_trunc('day', p.created_at))::date, oc.channel;
Indexing Strategy:
A UNIQUE index on (day, channel) (ux_mv_payments_daily_channel) ensures data integrity within the view and is a prerequisite for enabling concurrent refreshes (REFRESH MATERIALIZED VIEW CONCURRENTLY).
Indexes on payments(order_id) and payments(created_at) are crucial for efficiently building and refreshing the materialized view's data from the underlying base tables.
