wiki:OtherTopics

Context Navigation

Version 9 (modified by 232012, 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 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. 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

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

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

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.

Download in other formats:

Plain Text

Context Navigation

Other topics (Performance, Security, …)

Scenario 1 - Top Selling Products and Restock Plan

Query Used

Without indexes

Indexes

With indexes

Performance comparison and conclusion

Scenario 2 - Slow Moving Products

Without indexes

Indexes

With indexes

Performance comparison and conclusion

Scenario 3 - Impact of Admin Discounts on Sales Numbers

Without indexes

Indexes

With indexes

Performance comparison and conclusion

Scenario 4 - Customer Habits and Points Spending

Without indexes

Indexes

With indexes

Performance comparison and conclusion

Security

Cookie-based Authentication

Password Storage

Protection Against CSRF

HTTPS and HSTS

Download in other formats: