| Version 15 (modified by , 12 days ago) ( diff ) |
|---|
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
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
statusandpurchase_date, while also includingorder_idfor 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_datewas successfully used as an Index Only Scan and reduced the cost of filtering orders by status and purchase date.
idx_order_products_order_product_quantitywas 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;
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;
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_orderwas tested to support lookups of order products byproduct_idand provide the quantity and purchase price required by the aggregation.
idx_orders_status_purchase_datetargets the order status filter and includes the purchase date andorder_idneeded 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_datewas successfully used as an Index Only Scan in both the pre-promotion and post-promotion parts of the query.
idx_order_products_product_orderwas 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_datewas 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
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<Claim>
{
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<User>:
builder.Services.AddScoped<
IPasswordHasher<User>,
PasswordHasher<User>>();
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.
Note:
See TracWiki
for help on using the wiki.
