= 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=75.74..75.74 rows=2 width=180) (actual time=0.185..0.189 rows=0 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=3 -> Nested Loop (cost=56.78..75.73 rows=2 width=180) (actual time=0.163..0.166 rows=0 loops=1) Buffers: shared hit=3 -> Hash Join (cost=56.63..75.21 rows=2 width=92) (actual time=0.162..0.165 rows=0 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: 5 Buffers: shared hit=3 -> Seq Scan on products p (cost=0.00..16.80 rows=680 width=28) (actual time=0.018..0.020 rows=17 loops=1) Buffers: shared hit=1 -> Hash (cost=56.57..56.57 rows=5 width=72) (actual time=0.117..0.119 rows=5 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB Buffers: shared hit=2 -> Subquery Scan on psy (cost=56.40..56.57 rows=5 width=72) (actual time=0.084..0.093 rows=5 loops=1) Buffers: shared hit=2 -> GroupAggregate (cost=56.40..56.52 rows=5 width=72) (actual time=0.083..0.091 rows=5 loops=1) Group Key: op.product_id Buffers: shared hit=2 -> Sort (cost=56.40..56.42 rows=5 width=16) (actual time=0.073..0.076 rows=12 loops=1) Sort Key: op.product_id Sort Method: quicksort Memory: 25kB Buffers: shared hit=2 -> Hash Join (cost=34.09..56.34 rows=5 width=16) (actual time=0.050..0.059 rows=12 loops=1) Hash Cond: (op.order_id = o.order_id) Buffers: shared hit=2 -> Seq Scan on order_products op (cost=0.00..19.70 rows=970 width=24) (actual time=0.007..0.009 rows=21 loops=1) Buffers: shared hit=1 -> Hash (cost=34.01..34.01 rows=6 width=8) (actual time=0.029..0.029 rows=9 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB Buffers: shared hit=1 -> Seq Scan on orders o (cost=0.00..34.01 rows=6 width=8) (actual time=0.013..0.019 rows=9 loops=1) Filter: ((status = ANY ('{PAID,SHIPPED,DELIVERED}'::order_status_type[])) AND (purchase_date >= (CURRENT_DATE - '1 year'::interval))) Rows Removed by Filter: 8 Buffers: shared hit=1 -> Index Scan using releases_pkey on releases r (cost=0.15..0.24 rows=1 width=40) (never executed) Index Cond: (release_id = p.release_id) Planning Time: 0.555 ms Execution Time: 0.272 ms }}} The query was executed 10 times and 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. The query was again executed 10 times and 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 {{{#!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. {{{ Seq Scan on orders rows=2549 Rows Removed by Filter: 22468 }}} 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 and 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}}}. The query was again executed 10 times and 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 {{{#!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. {{{ Seq Scan on order_products rows=75021 Seq Scan on orders rows=5009 Rows Removed by Filter: 20008 }}} The query was executed 10 times and 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. The query was executed 10 times with both indexes present and 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. }}}