= Other topics (Performance, Security, …) {{{ WITH new_orders AS ( INSERT INTO project.orders ( user_id, payment_method, purchase_date, points_earned, points_used, status ) SELECT (ARRAY[1,2,3,5,6,7,8,10,11,12,13,14]) [1 + floor(random() * 12)::int], (ARRAY[ 'CARD'::project.payment_method_type, 'PAYPAL'::project.payment_method_type, 'CASH'::project.payment_method_type ]) [1 + floor(random() * 3)::int], CURRENT_DATE - floor(random() * 365)::int, floor(random() * 100)::bigint, NULL, (ARRAY[ 'PAID'::project.order_status_type, 'SHIPPED'::project.order_status_type, 'DELIVERED'::project.order_status_type ]) [1 + floor(random() * 3)::int] FROM generate_series(1, 5000) RETURNING order_id ) INSERT INTO project.order_products ( order_id, product_id, price_at_purchase, quantity ) SELECT no.order_id, p.product_id, p.price, 1 + floor(random() * 4)::bigint FROM new_orders no CROSS JOIN LATERAL ( SELECT product_id, price FROM project.products ORDER BY random() LIMIT 3 ) p; }}} {{{ WITH new_orders AS ( INSERT INTO project.orders ( user_id, payment_method, purchase_date, points_earned, points_used, status ) SELECT (ARRAY[1,2,3,5,6,7,8,10,11,12,13,14]) [1 + floor(random() * 12)::int], (ARRAY[ 'CARD'::project.payment_method_type, 'PAYPAL'::project.payment_method_type, 'CASH'::project.payment_method_type ]) [1 + floor(random() * 3)::int], CURRENT_DATE - 366 - floor(random() * 730)::int, floor(random() * 100)::bigint, NULL, 'CANCELLED'::project.order_status_type FROM generate_series(1, 20000) RETURNING order_id ) INSERT INTO project.order_products ( order_id, product_id, price_at_purchase, quantity ) SELECT no.order_id, p.product_id, p.price, 1 + floor(random() * 4)::bigint FROM new_orders no CROSS JOIN LATERAL ( SELECT product_id, price FROM project.products ORDER BY random() LIMIT 3 ) p; }}} == Scenario 1 - Top Selling Products and Restock Plan {{{#!div style="text-align: justify; width: 100%;" ==== Query Used {{{ SET search_path TO project; EXPLAIN (ANALYZE, BUFFERS) WITH product_sales_yearly AS ( SELECT op.product_id, SUM(op.quantity) AS total_sold_yearly, SUM(op.quantity) / 365.0 AS daily_sales_velocity FROM order_products op JOIN orders o ON op.order_id = o.order_id WHERE o.purchase_date >= CURRENT_DATE - INTERVAL '1 year' AND o.status IN ('PAID', 'SHIPPED', 'DELIVERED') GROUP BY op.product_id ), inventory_velocity AS ( SELECT p.product_id, p.format, p.stock, p.price, r.title AS release_title, psy.total_sold_yearly, psy.daily_sales_velocity, CASE WHEN psy.daily_sales_velocity > 0 THEN p.stock / psy.daily_sales_velocity ELSE 9999 END AS days_until_out_of_stock FROM products p JOIN releases r ON p.release_id = r.release_id JOIN product_sales_yearly psy ON p.product_id = psy.product_id ) SELECT product_id, release_title, format, stock AS current_stock, total_sold_yearly, ROUND(CAST(daily_sales_velocity AS NUMERIC), 2) AS daily_velocity, ROUND(CAST(days_until_out_of_stock AS NUMERIC), 1) AS days_left, CEIL((daily_sales_velocity * 90) - stock) AS recommended_restock_quantity FROM inventory_velocity WHERE days_until_out_of_stock < 30 ORDER BY daily_velocity DESC, days_left ASC; }}} ==== Without indexes The query was tested on approximately 25,017 orders and 75,021 order-product records. Before indexing, PostgreSQL used sequential scans on both {{{orders}}} and {{{order_products}}}. {{{ Sort (cost=2270.51..2270.52 rows=3 width=158) (actual time=27.771..27.775 rows=3 loops=1) Sort Key: (round(psy.daily_sales_velocity, 2)) DESC, (round(CASE WHEN (psy.daily_sales_velocity > '0'::numeric) THEN ((p.stock)::numeric / psy.daily_sales_velocity) ELSE '9999'::numeric END, 1)) Sort Method: quicksort Memory: 25kB Buffers: shared hit=763 -> Hash Join (cost=2269.23..2270.49 rows=3 width=158) (actual time=27.754..27.764 rows=3 loops=1) Hash Cond: (r.release_id = p.release_id) Buffers: shared hit=763 -> Seq Scan on releases r (cost=0.00..1.12 rows=12 width=18) (actual time=0.015..0.017 rows=12 loops=1) Buffers: shared hit=1 -> Hash (cost=2269.19..2269.19 rows=3 width=92) (actual time=27.724..27.727 rows=3 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB Buffers: shared hit=762 -> Hash Join (cost=2267.97..2269.19 rows=3 width=92) (actual time=27.713..27.724 rows=3 loops=1) Hash Cond: (p.product_id = psy.product_id) Join Filter: (CASE WHEN (psy.daily_sales_velocity > '0'::numeric) THEN ((p.stock)::numeric / psy.daily_sales_velocity) ELSE '9999'::numeric END < '30'::numeric) Rows Removed by Join Filter: 4 Buffers: shared hit=762 -> Seq Scan on products p (cost=0.00..1.17 rows=17 width=28) (actual time=0.006..0.008 rows=17 loops=1) Buffers: shared hit=1 -> Hash (cost=2267.86..2267.86 rows=9 width=72) (actual time=27.694..27.696 rows=7 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB Buffers: shared hit=761 -> Subquery Scan on psy (cost=2267.64..2267.86 rows=9 width=72) (actual time=27.684..27.692 rows=7 loops=1) Buffers: shared hit=761 -> HashAggregate (cost=2267.64..2267.77 rows=9 width=72) (actual time=27.684..27.690 rows=7 loops=1) Group Key: op.product_id Batches: 1 Memory Usage: 24kB Buffers: shared hit=761 -> Hash Join (cost=753.27..2252.45 rows=3038 width=16) (actual time=6.357..24.965 rows=15012 loops=1) Hash Cond: (op.order_id = o.order_id) Buffers: shared hit=761 -> Seq Scan on order_products op (cost=0.00..1302.21 rows=75021 width=24) (actual time=0.008..7.114 rows=75021 loops=1) Buffers: shared hit=552 -> Hash (cost=740.61..740.61 rows=1013 width=8) (actual time=6.341..6.342 rows=5009 loops=1) Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 260kB Buffers: shared hit=209 -> Seq Scan on orders o (cost=0.00..740.61 rows=1013 width=8) (actual time=0.016..5.416 rows=5009 loops=1) Filter: ((status = ANY ('{PAID,SHIPPED,DELIVERED}'::order_status_type[])) AND (purchase_date >= (CURRENT_DATE - '1 year'::interval))) Rows Removed by Filter: 20008 Buffers: shared hit=209 Planning: Buffers: shared hit=70 dirtied=2 Planning Time: 0.940 ms Execution Time: 27.871 ms }}} The query was executed 10 times with each execution time shown bellow: {{{ Execution 1: 25.357 ms Execution 2: 25.319 ms Execution 3: 25.272 ms Execution 4: 25.354 ms Execution 5: 25.359 ms Execution 6: 27.907 ms Execution 7: 25.414 ms Execution 8: 25.416 ms Execution 9: 25.488 ms Execution 10: 25.572 ms }}} The average execution time without indexes was: **25.646 ms** ==== Indexes {{{ CREATE INDEX idx_orders_status_purchase_date ON project.orders (status, purchase_date, order_id); CREATE INDEX idx_order_products_order_product_quantity ON project.order_products (order_id, product_id, quantity); ANALYZE project.orders; ANALYZE project.order_products; }}} * The first index targets the filters on {{{status}}} and {{{purchase_date}}}, while also including {{{order_id}}} for the join. * The second index was tested to support the join and aggregation on {{{order_products}}}. ==== With indexes After indexing, PostgreSQL used: {{{ Index Only Scan using idx_orders_status_purchase_date on orders Heap Fetches: 0 }}} This replaced the previous sequential scan on orders. However, PostgreSQL did not use {{{idx_order_products_order_product_quantity}}}. It continued using: {{{ Seq Scan on order_products }}} because scanning the table and performing a hash join was estimated to be cheaper. This is visible in the output, shown bellow: {{{ Sort (cost=1575.04..1575.05 rows=3 width=158) (actual time=21.944..21.948 rows=3 loops=1) Sort Key: (round(psy.daily_sales_velocity, 2)) DESC, (round(CASE WHEN (psy.daily_sales_velocity > '0'::numeric) THEN ((p.stock)::numeric / psy.daily_sales_velocity) ELSE '9999'::numeric END, 1)) Sort Method: quicksort Memory: 25kB Buffers: shared hit=576 -> Hash Join (cost=1573.75..1575.02 rows=3 width=158) (actual time=21.927..21.937 rows=3 loops=1) Hash Cond: (r.release_id = p.release_id) Buffers: shared hit=576 -> Seq Scan on releases r (cost=0.00..1.12 rows=12 width=18) (actual time=0.014..0.016 rows=12 loops=1) Buffers: shared hit=1 -> Hash (cost=1573.72..1573.72 rows=3 width=92) (actual time=21.899..21.902 rows=3 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB Buffers: shared hit=575 -> Hash Join (cost=1572.50..1573.72 rows=3 width=92) (actual time=21.888..21.899 rows=3 loops=1) Hash Cond: (p.product_id = psy.product_id) Join Filter: (CASE WHEN (psy.daily_sales_velocity > '0'::numeric) THEN ((p.stock)::numeric / psy.daily_sales_velocity) ELSE '9999'::numeric END < '30'::numeric) Rows Removed by Join Filter: 4 Buffers: shared hit=575 -> Seq Scan on products p (cost=0.00..1.17 rows=17 width=28) (actual time=0.007..0.008 rows=17 loops=1) Buffers: shared hit=1 -> Hash (cost=1572.39..1572.39 rows=9 width=72) (actual time=21.870..21.873 rows=7 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB Buffers: shared hit=574 -> Subquery Scan on psy (cost=1572.16..1572.39 rows=9 width=72) (actual time=21.861..21.869 rows=7 loops=1) Buffers: shared hit=574 -> HashAggregate (cost=1572.16..1572.30 rows=9 width=72) (actual time=21.861..21.866 rows=7 loops=1) Group Key: op.product_id Batches: 1 Memory Usage: 24kB Buffers: shared hit=574 -> Hash Join (cost=57.80..1556.97 rows=3038 width=16) (actual time=1.810..19.144 rows=15012 loops=1) Hash Cond: (op.order_id = o.order_id) Buffers: shared hit=574 -> Seq Scan on order_products op (cost=0.00..1302.21 rows=75021 width=24) (actual time=0.010..5.596 rows=75021 loops=1) Buffers: shared hit=552 -> Hash (cost=45.14..45.14 rows=1013 width=8) (actual time=1.790..1.791 rows=5009 loops=1) Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 260kB Buffers: shared hit=22 -> Index Only Scan using idx_orders_status_purchase_date on orders o (cost=0.29..45.14 rows=1013 width=8) (actual time=0.040..0.939 rows=5009 loops=1) Index Cond: ((status = ANY ('{PAID,SHIPPED,DELIVERED}'::order_status_type[])) AND (purchase_date >= (CURRENT_DATE - '1 year'::interval))) Heap Fetches: 0 Buffers: shared hit=22 Planning: Buffers: shared hit=19 Planning Time: 0.712 ms Execution Time: 22.040 ms }}} The query was again executed 10 times with each execution time shown bellow: {{{ Execution 1: 22.096 ms Execution 2: 21.997 ms Execution 3: 22.138 ms Execution 4: 22.185 ms Execution 5: 23.046 ms Execution 6: 22.113 ms Execution 7: 22.039 ms Execution 8: 22.084 ms Execution 9: 23.219 ms Execution 10: 22.068 ms }}} The average execution time with indexes was: **22.299 ms** ==== Performance comparison and conclusion {{{ Without indexes: 25.646 ms With indexes: 22.299 ms Improvement: 13.05% }}} * {{{idx_orders_status_purchase_date}}} was successfully used as an Index Only Scan and reduced the cost of filtering orders by status and purchase date. * {{{idx_order_products_order_product_quantity}}} was not used by the optimizer, because a sequential scan of order_products was still considered cheaper for the current dataset. }}} == Scenario 2 - Slow Moving Products ==== Query Used {{{ SET search_path TO project; EXPLAIN (ANALYZE, BUFFERS) WITH product_sales_6months AS ( SELECT op.product_id, SUM(op.quantity) AS units_sold_6m, MAX(o.purchase_date) AS last_purchase_date FROM order_products op JOIN orders o ON op.order_id = o.order_id WHERE o.purchase_date >= CURRENT_DATE - INTERVAL '6 months' AND o.status IN ('PAID', 'SHIPPED', 'DELIVERED') GROUP BY op.product_id ), product_wishlist_counts AS ( SELECT product_id, COUNT(wishlist_id) AS wishlist_addition_count FROM wishlist_products GROUP BY product_id ) SELECT p.product_id, r.title AS release_title, p.format, p.stock AS unsold_stock_quantity, p.price AS current_unit_price, (p.stock * p.price) AS frozen_capital, COALESCE(TO_CHAR(ps.last_purchase_date, 'YYYY-MM-DD'), 'NEVER BOUGHT') AS last_sold_date, COALESCE(pw.wishlist_addition_count, 0) AS times_on_wishlists, CASE WHEN COALESCE(pw.wishlist_addition_count, 0) > 0 THEN 'Discount Target (Wishlisted)' ELSE 'Deep Liquidation/Clearance Target' END AS inventory_action_plan FROM products p JOIN releases r ON p.release_id = r.release_id LEFT JOIN product_sales_6months ps ON p.product_id = ps.product_id LEFT JOIN product_wishlist_counts pw ON p.product_id = pw.product_id WHERE ps.product_id IS NULL AND p.stock > 0 ORDER BY frozen_capital DESC, times_on_wishlists DESC; }}} {{{#!div style="text-align: justify; width: 100%;" ==== Without indexes The query was tested on approximately 25,017 orders and 75,021 order-product records. Before indexing, PostgreSQL used a sequential scan on {{{orders}}} to find orders from the last 6 months with a completed status. {{{ Sort (cost=2087.37..2087.38 rows=7 width=140) (actual time=11.602..11.606 rows=9 loops=1) Sort Key: (((p.stock)::numeric * p.price)) DESC, (COALESCE((count(wishlist_products.wishlist_id)), '0'::bigint)) DESC Sort Method: quicksort Memory: 26kB Buffers: shared hit=5338 -> Hash Join (cost=2082.49..2087.27 rows=7 width=140) (actual time=11.568..11.591 rows=9 loops=1) Hash Cond: (p.release_id = r.release_id) Buffers: shared hit=5338 -> Hash Anti Join (cost=2081.22..2085.88 rows=7 width=46) (actual time=11.533..11.550 rows=9 loops=1) Hash Cond: (p.product_id = ps.product_id) Buffers: shared hit=5337 -> Hash Right Join (cost=36.91..41.45 rows=16 width=42) (actual time=0.047..0.059 rows=16 loops=1) Hash Cond: (wishlist_products.product_id = p.product_id) Buffers: shared hit=2 -> HashAggregate (cost=35.50..37.50 rows=200 width=16) (actual time=0.022..0.026 rows=9 loops=1) Group Key: wishlist_products.product_id Batches: 1 Memory Usage: 40kB Buffers: shared hit=1 -> Seq Scan on wishlist_products (cost=0.00..27.00 rows=1700 width=16) (actual time=0.007..0.008 rows=15 loops=1) Buffers: shared hit=1 -> Hash (cost=1.21..1.21 rows=16 width=34) (actual time=0.017..0.018 rows=16 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 10kB Buffers: shared hit=1 -> Seq Scan on products p (cost=0.00..1.21 rows=16 width=34) (actual time=0.009..0.013 rows=16 loops=1) Filter: (stock > 0) Rows Removed by Filter: 1 Buffers: shared hit=1 -> Hash (cost=2044.19..2044.19 rows=10 width=12) (actual time=11.480..11.481 rows=7 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB Buffers: shared hit=5335 -> Subquery Scan on ps (cost=2043.99..2044.19 rows=10 width=12) (actual time=11.474..11.477 rows=7 loops=1) Buffers: shared hit=5335 -> HashAggregate (cost=2043.99..2044.09 rows=10 width=44) (actual time=11.473..11.475 rows=7 loops=1) Group Key: op.product_id Batches: 1 Memory Usage: 24kB Buffers: shared hit=5335 -> Nested Loop (cost=0.29..2036.24 rows=1550 width=12) (actual time=0.022..9.997 rows=7633 loops=1) Buffers: shared hit=5335 -> Seq Scan on orders o (cost=0.00..740.61 rows=517 width=12) (actual time=0.011..4.065 rows=2549 loops=1) Filter: ((status = ANY ('{PAID,SHIPPED,DELIVERED}'::order_status_type[])) AND (purchase_date >= (CURRENT_DATE - '6 mons'::interval))) Rows Removed by Filter: 22468 Buffers: shared hit=209 -> Index Only Scan using order_products_pk on order_products op (cost=0.29..2.48 rows=3 width=16) (actual time=0.001..0.002 rows=3 loops=2549) Index Cond: (order_id = o.order_id) Heap Fetches: 67 Buffers: shared hit=5126 -> Hash (cost=1.12..1.12 rows=12 width=18) (actual time=0.024..0.024 rows=12 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB Buffers: shared hit=1 -> Seq Scan on releases r (cost=0.00..1.12 rows=12 width=18) (actual time=0.014..0.017 rows=12 loops=1) Buffers: shared hit=1 Planning: Buffers: shared hit=50 dirtied=1 Planning Time: 0.896 ms Execution Time: 11.750 ms }}} The existing primary-key index on {{{order_products}}} was already used: {{{ Index Only Scan using order_products_pk on order_products }}} The query was executed 10 times with each execution time shown bellow: {{{ Execution 1: 11.890 ms Execution 2: 11.966 ms Execution 3: 11.938 ms Execution 4: 11.937 ms Execution 5: 11.936 ms Execution 6: 11.896 ms Execution 7: 11.932 ms Execution 8: 12.307 ms Execution 9: 13.441 ms Execution 10: 12.003 ms          }}} The average execution time without indexes was: **12.125 ms** ==== Indexes {{{ CREATE INDEX idx_orders_status_purchase_date ON project.orders (status, purchase_date, order_id); ANALYZE project.orders; }}} The index targets the {{{status}}} and {{{purchase_date}}} filters and also includes {{{order_id}}} for the join with {{{order_products}}}. ==== With indexes After indexing, PostgreSQL used: {{{ Index Only Scan using idx_orders_status_purchase_date on orders Heap Fetches: 0 }}} This replaced the sequential scan on {{{orders}}} and reduced the number of pages that had to be read. The existing {{{order_products_pk}}} index continued to be used for the join with {{{order_products}}}. This is shown in the output bellow: {{{ Sort (cost=1369.95..1369.97 rows=7 width=140) (actual time=8.476..8.481 rows=9 loops=1) Sort Key: (((p.stock)::numeric * p.price)) DESC, (COALESCE((count(wishlist_products.wishlist_id)), '0'::bigint)) DESC Sort Method: quicksort Memory: 26kB Buffers: shared hit=5146 -> Hash Join (cost=1365.08..1369.86 rows=7 width=140) (actual time=8.442..8.465 rows=9 loops=1) Hash Cond: (p.release_id = r.release_id) Buffers: shared hit=5146 -> Hash Anti Join (cost=1363.81..1368.47 rows=7 width=46) (actual time=8.405..8.422 rows=9 loops=1) Hash Cond: (p.product_id = ps.product_id) Buffers: shared hit=5145 -> Hash Right Join (cost=36.91..41.45 rows=16 width=42) (actual time=0.045..0.057 rows=16 loops=1) Hash Cond: (wishlist_products.product_id = p.product_id) Buffers: shared hit=2 -> HashAggregate (cost=35.50..37.50 rows=200 width=16) (actual time=0.021..0.025 rows=9 loops=1) Group Key: wishlist_products.product_id Batches: 1 Memory Usage: 40kB Buffers: shared hit=1 -> Seq Scan on wishlist_products (cost=0.00..27.00 rows=1700 width=16) (actual time=0.007..0.008 rows=15 loops=1) Buffers: shared hit=1 -> Hash (cost=1.21..1.21 rows=16 width=34) (actual time=0.016..0.017 rows=16 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 10kB Buffers: shared hit=1 -> Seq Scan on products p (cost=0.00..1.21 rows=16 width=34) (actual time=0.008..0.012 rows=16 loops=1) Filter: (stock > 0) Rows Removed by Filter: 1 Buffers: shared hit=1 -> Hash (cost=1326.77..1326.77 rows=10 width=12) (actual time=8.354..8.355 rows=7 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB Buffers: shared hit=5143 -> Subquery Scan on ps (cost=1326.57..1326.77 rows=10 width=12) (actual time=8.347..8.351 rows=7 loops=1) Buffers: shared hit=5143 -> HashAggregate (cost=1326.57..1326.67 rows=10 width=44) (actual time=8.347..8.349 rows=7 loops=1) Group Key: op.product_id Batches: 1 Memory Usage: 24kB Buffers: shared hit=5143 -> Nested Loop (cost=0.58..1318.82 rows=1550 width=12) (actual time=0.045..6.925 rows=7633 loops=1) Buffers: shared hit=5143 -> Index Only Scan using idx_orders_status_purchase_date on orders o (cost=0.29..23.20 rows=517 width=12) (actual time=0.036..0.466 rows=2549 loops=1) Index Cond: ((status = ANY ('{PAID,SHIPPED,DELIVERED}'::order_status_type[])) AND (purchase_date >= (CURRENT_DATE - '6 mons'::interval))) Heap Fetches: 0 Buffers: shared hit=17 -> Index Only Scan using order_products_pk on order_products op (cost=0.29..2.48 rows=3 width=16) (actual time=0.002..0.002 rows=3 loops=2549) Index Cond: (order_id = o.order_id) Heap Fetches: 67 Buffers: shared hit=5126 -> Hash (cost=1.12..1.12 rows=12 width=18) (actual time=0.026..0.026 rows=12 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB Buffers: shared hit=1 -> Seq Scan on releases r (cost=0.00..1.12 rows=12 width=18) (actual time=0.016..0.019 rows=12 loops=1) Buffers: shared hit=1 Planning: Buffers: shared hit=52 Planning Time: 0.988 ms Execution Time: 8.596 ms }}} The query was again executed 10 times with each execution time shown bellow: {{{ Execution 1: 9.484 ms Execution 2: 8.827 ms Execution 3: 8.856 ms Execution 4: 9.122 ms Execution 5: 8.683 ms Execution 6: 9.017 ms Execution 7: 8.697 ms Execution 8: 8.747 ms Execution 9: 8.830 ms Execution 10: 8.676 ms }}} The average execution time with indexes was: **8.894 ms** ==== Performance comparison and conclusion {{{ Without indexes: 12.125 ms With indexes: 8.894 ms Improvement: 26.65% }}} {{{idx_orders_status_purchase_date}}} was successfully used as an Index Only Scan and improved the filtering of orders by status and purchase date. The query improved by approximately 26.65%, while the existing {{{order_products_pk}}} index continued to support the join efficiently. }}} == Scenario 3 - Impact of Admin Discounts on Sales Numbers ==== Query Used {{{ SET search_path TO project; EXPLAIN (ANALYZE, BUFFERS) WITH discount_events AS ( SELECT m.modification_id, m.admin_id, m.date_modified, m.discount AS discount_percentage, mp.product_id FROM modifications m JOIN modification_products mp ON m.modification_id = mp.modification_id WHERE m.type_of_modification = 'DISCOUNT' ), pre_promo_sales AS ( SELECT de.modification_id, de.product_id, COALESCE( SUM(op.quantity) FILTER (WHERE o.order_id IS NOT NULL), 0 ) AS units_sold_before, COALESCE( SUM(op.quantity * op.price_at_purchase) FILTER (WHERE o.order_id IS NOT NULL), 0.00 ) AS revenue_before FROM discount_events de LEFT JOIN order_products op ON de.product_id = op.product_id LEFT JOIN orders o ON op.order_id = o.order_id AND o.purchase_date >= de.date_modified - INTERVAL '30 days' AND o.purchase_date < de.date_modified AND o.status IN ('PAID', 'SHIPPED', 'DELIVERED') GROUP BY de.modification_id, de.product_id ), post_promo_sales AS ( SELECT de.modification_id, de.product_id, COALESCE( SUM(op.quantity) FILTER (WHERE o.order_id IS NOT NULL), 0 ) AS units_sold_after, COALESCE( SUM(op.quantity * op.price_at_purchase) FILTER (WHERE o.order_id IS NOT NULL), 0.00 ) AS revenue_after FROM discount_events de LEFT JOIN order_products op ON de.product_id = op.product_id LEFT JOIN orders o ON op.order_id = o.order_id AND o.purchase_date > de.date_modified AND o.purchase_date <= de.date_modified + INTERVAL '30 days' AND o.status IN ('PAID', 'SHIPPED', 'DELIVERED') GROUP BY de.modification_id, de.product_id ), promo_summary AS ( SELECT de.product_id, r.title AS release_title, p.format AS product_format, de.date_modified AS promotion_start_date, de.discount_percentage AS discount_applied, pre.units_sold_before, post.units_sold_after, (post.units_sold_after - pre.units_sold_before) AS volume_change, pre.revenue_before, post.revenue_after, (post.revenue_after - pre.revenue_before) AS net_revenue_impact FROM discount_events de JOIN products p ON de.product_id = p.product_id JOIN releases r ON p.release_id = r.release_id JOIN pre_promo_sales pre ON de.modification_id = pre.modification_id AND de.product_id = pre.product_id JOIN post_promo_sales post ON de.modification_id = post.modification_id AND de.product_id = post.product_id ) SELECT product_id, release_title, product_format, promotion_start_date, discount_applied, units_sold_before, units_sold_after, volume_change, revenue_before, revenue_after, net_revenue_impact, CASE WHEN net_revenue_impact > 0 AND volume_change > 0 THEN 'SUCCESS: Volume generated profit' WHEN net_revenue_impact < 0 AND volume_change > 0 THEN 'MARGIN LOSS: Volume rose but lost overall revenue' WHEN volume_change <= 0 THEN 'FAILURE: No demand increase observed' ELSE 'NEUTRAL' END AS promotion_verdict FROM promo_summary ORDER BY promotion_start_date DESC, net_revenue_impact DESC; }}} {{{#!div style="text-align: justify; width: 100%;" ==== Without indexes The query was tested on approximately 25,017 orders and 75,021 order-product records. Before indexing, PostgreSQL used sequential scans on both {{{orders}}} and {{{order_products}}} while calculating the 30-day periods before and after each discount. {{{ Sort (cost=8603.00..8603.01 rows=1 width=282) (actual time=103.277..103.288 rows=6 loops=1) Sort Key: de.date_modified DESC, (((COALESCE(sum(((op_1.quantity)::numeric * op_1.price_at_purchase)) FILTER (WHERE (o_1.order_id IS NOT NULL)), 0.00)) - pre.revenue_before)) DESC Sort Method: quicksort Memory: 25kB Buffers: shared hit=1537 CTE discount_events -> Hash Join (cost=22.19..55.57 rows=10 width=60) (actual time=0.033..0.039 rows=6 loops=1) Hash Cond: (mp.modification_id = m.modification_id) Buffers: shared hit=2 -> Seq Scan on modification_products mp (cost=0.00..28.50 rows=1850 width=16) (actual time=0.007..0.009 rows=16 loops=1) Buffers: shared hit=1 -> Hash (cost=22.12..22.12 rows=5 width=52) (actual time=0.016..0.017 rows=5 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB Buffers: shared hit=1 -> Seq Scan on modifications m (cost=0.00..22.12 rows=5 width=52) (actual time=0.009..0.011 rows=5 loops=1) Filter: (type_of_modification = 'DISCOUNT'::modification_type) Rows Removed by Filter: 8 Buffers: shared hit=1 -> Nested Loop (cost=8545.32..8547.43 rows=1 width=282) (actual time=103.241..103.270 rows=6 loops=1) Buffers: shared hit=1537 -> Hash Join (cost=8545.18..8546.43 rows=1 width=184) (actual time=103.196..103.209 rows=6 loops=1) Hash Cond: (p.product_id = de.product_id) Buffers: shared hit=1525 -> Seq Scan on products p (cost=0.00..1.17 rows=17 width=20) (actual time=0.012..0.015 rows=17 loops=1) Buffers: shared hit=1 -> Hash (cost=8545.17..8545.17 rows=1 width=188) (actual time=103.173..103.179 rows=6 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB Buffers: shared hit=1524 -> Nested Loop (cost=8544.61..8545.17 rows=1 width=188) (actual time=103.116..103.167 rows=6 loops=1) Join Filter: ((de.product_id = de_2.product_id) AND (de.modification_id = de_2.modification_id)) Rows Removed by Join Filter: 15 Buffers: shared hit=1524 -> Merge Join (cost=4272.70..4272.86 rows=1 width=132) (actual time=53.280..53.318 rows=6 loops=1) Merge Cond: ((de.product_id = pre.product_id) AND (de.modification_id = pre.modification_id)) Buffers: shared hit=763 -> Sort (cost=0.37..0.39 rows=10 width=52) (actual time=0.050..0.052 rows=6 loops=1) Sort Key: de.product_id, de.modification_id Sort Method: quicksort Memory: 25kB Buffers: shared hit=2 -> CTE Scan on discount_events de (cost=0.00..0.20 rows=10 width=52) (actual time=0.036..0.043 rows=6 loops=1) Buffers: shared hit=2 -> Sort (cost=4272.33..4272.36 rows=10 width=80) (actual time=53.225..53.255 rows=6 loops=1) Sort Key: pre.product_id, pre.modification_id Sort Method: quicksort Memory: 25kB Buffers: shared hit=761 -> Subquery Scan on pre (cost=4271.92..4272.17 rows=10 width=80) (actual time=53.212..53.221 rows=6 loops=1) Buffers: shared hit=761 -> HashAggregate (cost=4271.92..4272.07 rows=10 width=80) (actual time=53.211..53.218 rows=6 loops=1) Group Key: de_1.modification_id, de_1.product_id Batches: 1 Memory Usage: 24kB Buffers: shared hit=761 -> Hash Left Join (cost=615.92..3146.60 rows=75021 width=39) (actual time=4.919..41.555 rows=50007 loops=1) Hash Cond: (op.order_id = o.order_id) Join Filter: ((o.purchase_date < de_1.date_modified) AND (o.purchase_date >= (de_1.date_modified - '30 days'::interval))) Rows Removed by Join Filter: 9178 Buffers: shared hit=761 -> Hash Right Join (cost=0.33..2334.07 rows=75021 width=43) (actual time=0.023..25.566 rows=50007 loops=1) Hash Cond: (op.product_id = de_1.product_id) Buffers: shared hit=552 -> Seq Scan on order_products op (cost=0.00..1302.21 rows=75021 width=31) (actual time=0.011..5.656 rows=75021 loops=1) Buffers: shared hit=552 -> Hash (cost=0.20..0.20 rows=10 width=20) (actual time=0.004..0.005 rows=6 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> CTE Scan on discount_events de_1 (cost=0.00..0.20 rows=10 width=20) (actual time=0.001..0.002 rows=6 loops=1) -> Hash (cost=552.98..552.98 rows=5009 width=12) (actual time=4.879..4.879 rows=5009 loops=1) Buckets: 8192 Batches: 1 Memory Usage: 280kB Buffers: shared hit=209 -> Seq Scan on orders o (cost=0.00..552.98 rows=5009 width=12) (actual time=0.011..3.828 rows=5009 loops=1) Filter: (status = ANY ('{PAID,SHIPPED,DELIVERED}'::order_status_type[])) Rows Removed by Filter: 20008 Buffers: shared hit=209 -> HashAggregate (cost=4271.92..4272.07 rows=10 width=80) (actual time=8.305..8.307 rows=4 loops=6) Group Key: de_2.modification_id, de_2.product_id Batches: 1 Memory Usage: 24kB Buffers: shared hit=761 -> Hash Left Join (cost=615.92..3146.60 rows=75021 width=39) (actual time=4.801..38.509 rows=50007 loops=1) Hash Cond: (op_1.order_id = o_1.order_id) Join Filter: ((o_1.purchase_date > de_2.date_modified) AND (o_1.purchase_date <= (de_2.date_modified + '30 days'::interval))) Rows Removed by Join Filter: 9680 Buffers: shared hit=761 -> Hash Right Join (cost=0.33..2334.07 rows=75021 width=43) (actual time=0.045..23.831 rows=50007 loops=1) Hash Cond: (op_1.product_id = de_2.product_id) Buffers: shared hit=552 -> Seq Scan on order_products op_1 (cost=0.00..1302.21 rows=75021 width=31) (actual time=0.012..5.643 rows=75021 loops=1) Buffers: shared hit=552 -> Hash (cost=0.20..0.20 rows=10 width=20) (actual time=0.010..0.010 rows=6 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> CTE Scan on discount_events de_2 (cost=0.00..0.20 rows=10 width=20) (actual time=0.001..0.003 rows=6 loops=1) -> Hash (cost=552.98..552.98 rows=5009 width=12) (actual time=4.695..4.695 rows=5009 loops=1) Buckets: 8192 Batches: 1 Memory Usage: 280kB Buffers: shared hit=209 -> Seq Scan on orders o_1 (cost=0.00..552.98 rows=5009 width=12) (actual time=0.009..3.563 rows=5009 loops=1) Filter: (status = ANY ('{PAID,SHIPPED,DELIVERED}'::order_status_type[])) Rows Removed by Filter: 20008 Buffers: shared hit=209 -> Index Scan using releases_pkey on releases r (cost=0.14..0.86 rows=1 width=18) (actual time=0.007..0.007 rows=1 loops=6) Index Cond: (release_id = p.release_id) Buffers: shared hit=12 Planning: Buffers: shared hit=16 Planning Time: 1.786 ms Execution Time: 103.499 ms }}} The query was executed 10 times with each execution time shown bellow: {{{ Execution 1: 102.707 ms Execution 2: 103.007 ms Execution 3: 103.043 ms Execution 4: 103.082 ms Execution 5: 103.161 ms Execution 6: 102.745 ms Execution 7: 104.608 ms Execution 8: 102.366 ms Execution 9: 102.757 ms Execution 10: 102.562 ms }}} The average execution time without indexes was: **103.004 ms** ==== Indexes {{{ CREATE INDEX idx_order_products_product_order ON project.order_products (product_id, order_id) INCLUDE (quantity, price_at_purchase); CREATE INDEX idx_orders_status_purchase_date ON project.orders (status, purchase_date, order_id); ANALYZE project.order_products; ANALYZE project.orders; }}} * {{{idx_order_products_product_order}}} was tested to support lookups of order products by {{{product_id}}} and provide the quantity and purchase price required by the aggregation. * {{{idx_orders_status_purchase_date}}} targets the order status filter and includes the purchase date and {{{order_id}}} needed for the sales-period joins. ==== With indexes PostgreSQL used: {{{ Index Only Scan using idx_orders_status_purchase_date on orders Heap Fetches: 0 }}} The index was used for both the pre-promotion and post-promotion order lookups. However, {{{idx_order_products_product_order}}} was not used. PostgreSQL continued using: {{{ Seq Scan on order_products }}} because scanning the table and performing the hash joins was estimated to be cheaper for the current data distribution. All of this is shown in the output bellow: {{{ Sort (cost=7826.09..7826.10 rows=1 width=282) (actual time=98.314..98.323 rows=6 loops=1) Sort Key: de.date_modified DESC, (((COALESCE(sum(((op_1.quantity)::numeric * op_1.price_at_purchase)) FILTER (WHERE (o_1.order_id IS NOT NULL)), 0.00)) - pre.revenue_before)) DESC Sort Method: quicksort Memory: 25kB Buffers: shared hit=1163 CTE discount_events -> Hash Join (cost=22.19..55.57 rows=10 width=60) (actual time=0.047..0.054 rows=6 loops=1) Hash Cond: (mp.modification_id = m.modification_id) Buffers: shared hit=2 -> Seq Scan on modification_products mp (cost=0.00..28.50 rows=1850 width=16) (actual time=0.007..0.008 rows=16 loops=1) Buffers: shared hit=1 -> Hash (cost=22.12..22.12 rows=5 width=52) (actual time=0.017..0.018 rows=5 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB Buffers: shared hit=1 -> Seq Scan on modifications m (cost=0.00..22.12 rows=5 width=52) (actual time=0.010..0.012 rows=5 loops=1) Filter: (type_of_modification = 'DISCOUNT'::modification_type) Rows Removed by Filter: 8 Buffers: shared hit=1 -> Nested Loop (cost=7768.41..7770.52 rows=1 width=282) (actual time=98.280..98.307 rows=6 loops=1) Buffers: shared hit=1163 -> Hash Join (cost=7768.27..7769.52 rows=1 width=184) (actual time=98.265..98.276 rows=6 loops=1) Hash Cond: (p.product_id = de.product_id) Buffers: shared hit=1151 -> Seq Scan on products p (cost=0.00..1.17 rows=17 width=20) (actual time=0.015..0.018 rows=17 loops=1) Buffers: shared hit=1 -> Hash (cost=7768.26..7768.26 rows=1 width=188) (actual time=98.239..98.244 rows=6 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB Buffers: shared hit=1150 -> Nested Loop (cost=7767.70..7768.26 rows=1 width=188) (actual time=98.220..98.239 rows=6 loops=1) Join Filter: ((de.product_id = de_2.product_id) AND (de.modification_id = de_2.modification_id)) Rows Removed by Join Filter: 15 Buffers: shared hit=1150 -> Merge Join (cost=3884.24..3884.40 rows=1 width=132) (actual time=51.554..51.563 rows=6 loops=1) Merge Cond: ((de.product_id = pre.product_id) AND (de.modification_id = pre.modification_id)) Buffers: shared hit=576 -> Sort (cost=0.37..0.39 rows=10 width=52) (actual time=0.065..0.066 rows=6 loops=1) Sort Key: de.product_id, de.modification_id Sort Method: quicksort Memory: 25kB Buffers: shared hit=2 -> CTE Scan on discount_events de (cost=0.00..0.20 rows=10 width=52) (actual time=0.050..0.057 rows=6 loops=1) Buffers: shared hit=2 -> Sort (cost=3883.87..3883.90 rows=10 width=80) (actual time=51.485..51.488 rows=6 loops=1) Sort Key: pre.product_id, pre.modification_id Sort Method: quicksort Memory: 25kB Buffers: shared hit=574 -> Subquery Scan on pre (cost=3883.46..3883.71 rows=10 width=80) (actual time=51.472..51.480 rows=6 loops=1) Buffers: shared hit=574 -> HashAggregate (cost=3883.46..3883.61 rows=10 width=80) (actual time=51.472..51.478 rows=6 loops=1) Group Key: de_1.modification_id, de_1.product_id Batches: 1 Memory Usage: 24kB Buffers: shared hit=574 -> Hash Left Join (cost=227.47..2758.14 rows=75021 width=39) (actual time=1.840..39.403 rows=50007 loops=1) Hash Cond: (op.order_id = o.order_id) Join Filter: ((o.purchase_date < de_1.date_modified) AND (o.purchase_date >= (de_1.date_modified - '30 days'::interval))) Rows Removed by Join Filter: 9178 Buffers: shared hit=574 -> Hash Right Join (cost=0.33..2334.07 rows=75021 width=43) (actual time=0.024..26.496 rows=50007 loops=1) Hash Cond: (op.product_id = de_1.product_id) Buffers: shared hit=552 -> Seq Scan on order_products op (cost=0.00..1302.21 rows=75021 width=31) (actual time=0.012..6.080 rows=75021 loops=1) Buffers: shared hit=552 -> Hash (cost=0.20..0.20 rows=10 width=20) (actual time=0.005..0.005 rows=6 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> CTE Scan on discount_events de_1 (cost=0.00..0.20 rows=10 width=20) (actual time=0.001..0.002 rows=6 loops=1) -> Hash (cost=164.53..164.53 rows=5009 width=12) (actual time=1.798..1.798 rows=5009 loops=1) Buckets: 8192 Batches: 1 Memory Usage: 280kB Buffers: shared hit=22 -> Index Only Scan using idx_orders_status_purchase_date on orders o (cost=0.29..164.53 rows=5009 width=12) (actual time=0.034..0.937 rows=5009 loops=1) Index Cond: (status = ANY ('{PAID,SHIPPED,DELIVERED}'::order_status_type[])) Heap Fetches: 0 Buffers: shared hit=22 -> HashAggregate (cost=3883.46..3883.61 rows=10 width=80) (actual time=7.777..7.778 rows=4 loops=6) Group Key: de_2.modification_id, de_2.product_id Batches: 1 Memory Usage: 24kB Buffers: shared hit=574 -> Hash Left Join (cost=227.47..2758.14 rows=75021 width=39) (actual time=1.816..35.408 rows=50007 loops=1) Hash Cond: (op_1.order_id = o_1.order_id) Join Filter: ((o_1.purchase_date > de_2.date_modified) AND (o_1.purchase_date <= (de_2.date_modified + '30 days'::interval))) Rows Removed by Join Filter: 9680 Buffers: shared hit=574 -> Hash Right Join (cost=0.33..2334.07 rows=75021 width=43) (actual time=0.025..23.451 rows=50007 loops=1) Hash Cond: (op_1.product_id = de_2.product_id) Buffers: shared hit=552 -> Seq Scan on order_products op_1 (cost=0.00..1302.21 rows=75021 width=31) (actual time=0.011..5.512 rows=75021 loops=1) Buffers: shared hit=552 -> Hash (cost=0.20..0.20 rows=10 width=20) (actual time=0.005..0.005 rows=6 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> CTE Scan on discount_events de_2 (cost=0.00..0.20 rows=10 width=20) (actual time=0.001..0.003 rows=6 loops=1) -> Hash (cost=164.53..164.53 rows=5009 width=12) (actual time=1.773..1.773 rows=5009 loops=1) Buckets: 8192 Batches: 1 Memory Usage: 280kB Buffers: shared hit=22 -> Index Only Scan using idx_orders_status_purchase_date on orders o_1 (cost=0.29..164.53 rows=5009 width=12) (actual time=0.029..0.923 rows=5009 loops=1) Index Cond: (status = ANY ('{PAID,SHIPPED,DELIVERED}'::order_status_type[])) Heap Fetches: 0 Buffers: shared hit=22 -> Index Scan using releases_pkey on releases r (cost=0.14..0.86 rows=1 width=18) (actual time=0.002..0.002 rows=1 loops=6) Index Cond: (release_id = p.release_id) Buffers: shared hit=12 Planning: Buffers: shared hit=56 Planning Time: 2.028 ms Execution Time: 98.520 ms }}} The query was executed 10 times with each execution time shown bellow: {{{ Execution 1: 97.293 ms Execution 2: 97.255 ms Execution 3: 97.952 ms Execution 4: 99.562 ms Execution 5: 96.874 ms Execution 6: 96.652 ms Execution 7: 98.997 ms Execution 8: 97.512 ms Execution 9: 98.037 ms Execution 10: 96.970 ms }}} The average execution time was: **97.710 ms** The unused {{{idx_order_products_product_order}}} index was removed after testing. ==== Performance comparison and conclusion {{{ Without indexes: 103.004 ms With indexes: 97.710 ms Improvement: 5.14% }}} * {{{idx_orders_status_purchase_date}}} was successfully used as an Index Only Scan in both the pre-promotion and post-promotion parts of the query. * {{{idx_order_products_product_order}}} was not used by the optimizer and was removed. }}} == Scenario 4 - Customer Habits and Points Spending ==== Without indexes The query was tested on approximately 25,017 orders and 75,021 order-product records. Before indexing, PostgreSQL used a sequential scan on {{{orders}}} and scanned all {{{order_products}}} records to calculate order totals. {{{ Seq Scan on orders rows=5009 Rows Removed by Filter: 20008 Seq Scan on order_products rows=75021 }}} The {{{order_products}}} aggregation also required temporary disk usage: {{{ HashAggregate Batches: 5 Disk Usage: 760kB }}} The query was executed 10 times and the average execution time without indexes was: **93.895 ms** ==== Indexes {{{ CREATE INDEX idx_orders_status_purchase_date ON project.orders (status, purchase_date, order_id); ANALYZE project.orders; }}} The index targets the {{{status}}} and {{{purchase_date}}} filters on {{{orders}}} and includes {{{order_id}}} for the join with aggregated order totals. ==== With indexes After indexing, PostgreSQL used: {{{ Bitmap Index Scan on idx_orders_status_purchase_date Bitmap Heap Scan on orders }}} This replaced the sequential scan on {{{orders}}}. However, {{{order_products}}} was still processed using: {{{ Seq Scan on order_products rows=75021 }}} because the query needs to aggregate essentially the whole {{{order_products}}} table to calculate total spend per order. The query was executed 10 times and the average execution time with indexes was: **90.547 ms** ==== Performance comparison and conclusion {{{ Without indexes: 93.895 ms With indexes: 90.547 ms Improvement: 3.57% }}} * {{{idx_orders_status_purchase_date}}} was successfully used through a Bitmap Index Scan and reduced the cost of filtering orders. * The overall improvement was approximately 3.57% because the main remaining cost is the full aggregation of {{{order_products}}}, which still requires a sequential scan and temporary disk usage. == Security {{{#!div style="text-align: justify; width: 100%;" ==== Cookie-based Authentication For authentication in our application, we use ASP.NET Core cookie authentication. After a user successfully logs in, the server creates an authentication cookie containing information about the authenticated user. This allows the application to recognize the user on subsequent requests without requiring them to log in again for every request. Cookie authentication is configured in {{{Program.cs}}}: {{{ builder.Services .AddAuthentication("Cookies") .AddCookie("Cookies", options => { options.LoginPath = "/Account/Login"; options.AccessDeniedPath = "/Account/Login"; options.ExpireTimeSpan = TimeSpan.FromHours(8); options.SlidingExpiration = true; }); builder.Services.AddAuthorization(); }}} The authentication cookie is valid for 8 hours. Sliding expiration is enabled, meaning the authentication period can be renewed while the user remains active. After successful login, claims containing information about the user are created: {{{ var claims = new List { new Claim( ClaimTypes.NameIdentifier, user.UserId.ToString()), new Claim( ClaimTypes.Name, user.Username), new Claim( ClaimTypes.Email, user.Email), new Claim( ClaimTypes.Role, role) }; }}} An identity and authentication principal are then created: {{{ var identity = new ClaimsIdentity( claims, "Cookies"); var principal = new ClaimsPrincipal(identity); await HttpContext.SignInAsync( "Cookies", principal); }}} The role claim allows us to distinguish between consumers and administrators and can be used to restrict access to specific functionality. When the user logs out, the authentication cookie is invalidated: {{{ await HttpContext.SignOutAsync("Cookies"); HttpContext.Session.Clear(); }}} ==== Password Storage For password hashing, we use ASP.NET Core's {{{PasswordHasher}}}: {{{ builder.Services.AddScoped< IPasswordHasher, PasswordHasher>(); }}} When a new user registers, their password is hashed before it is stored in the database: {{{ user.Password = _passwordHasher.HashPassword( user, model.Password); _context.Users.Add(user); _context.SaveChanges(); }}} Because password hashing is a one-way operation, the original password cannot be obtained from the stored value. During login, we first retrieve the user by username: {{{ var user = _context.Users .FirstOrDefault(x => x.Username == model.Username); }}} The entered password is then verified against the stored password hash: {{{ var result = _passwordHasher.VerifyHashedPassword( user, user.Password, model.Password); if (result == PasswordVerificationResult.Failed) { ModelState.AddModelError( "", "Invalid username or password."); return View(model); } }}} This allows the application to verify a password without ever storing or comparing plaintext passwords in the database. ==== Protection Against CSRF For POST requests that modify application data, ASP.NET Core anti-forgery protection is used. Controller actions that receive POST requests are marked with: {{{ [HttpPost] [ValidateAntiForgeryToken] }}} For example: {{{ [HttpPost] [ValidateAntiForgeryToken] public IActionResult Register(RegisterViewModel model) { // ... } }}} The anti-forgery token protects the application against Cross-Site Request Forgery (CSRF) attacks by ensuring that the submitted request originates from a valid application form. ==== HTTPS and HSTS The application redirects HTTP requests to HTTPS: {{{ app.UseHttpsRedirection(); }}} Additionally, outside the development environment, HTTP Strict Transport Security (HSTS) is enabled: {{{ if (!app.Environment.IsDevelopment()) { app.UseExceptionHandler("/Home/Error"); app.UseHsts(); } }}} HTTPS protects communication between the browser and the server by encrypting transmitted information, while HSTS instructs browsers to use HTTPS when communicating with the application. }}}