Other topics (Performance, Security, …)

Scenario 1 - Top Selling Products and Restock Plan

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.

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: 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

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

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.