Database structure

constraints, indexes, normalization, migrations

What constraints are there and how do you test them?

PRIMARY KEY: a unique row identifier.
FOREIGN KEY: a reference must point to an existing record.
NOT NULL: the value is required.
UNIQUE: no repeats, for example email.
CHECK: an arbitrary condition, for example balance >= 0.
DEFAULT: the value if none was passed.

For QA constraints are a ready list of negative cases: what does the application do when the database rejects a record? The message must be clear to the user, not a "500 Internal Server Error" with the database's error text.

ALTER TABLE accounts ADD CONSTRAINT balance_non_negative CHECK (balance >= 0);
ALTER TABLE users    ADD CONSTRAINT users_email_unique UNIQUE (email);

-- test: registration with an already existing email through the UI/API
-- expected: 409 or a clear message, not 500

In an interview: The main constraints are primary and foreign keys, NOT NULL, UNIQUE, CHECK and default values. For me they are ready negative cases: I check how the application reacts when the database rejects a record, for example registration with an existing email must give a clear message, not a five hundred error with text from the database.

What happens to child records when a parent is deleted?

It is determined by the foreign key's ON DELETE option:

RESTRICT / NO ACTION (the default): the parent can't be deleted while child records exist.
CASCADE: child records are deleted together with the parent.
SET NULL: the reference in the children becomes NULL.

What to test: deleting a user with accounts: an error, a cascade or a "soft delete"? Was history that must be kept removed by a cascade?

In an interview: This is determined by the foreign key's setting: RESTRICT forbids deletion while child records exist, CASCADE deletes them together with the parent, and SET NULL nulls the reference. I check that the behaviour matches the requirements: for example, that deleting a user won't destroy by cascade the transaction history that must be kept by law.

What is an index and why is it needed?

An index is a separate data structure (most often a B-tree) that lets you find rows without scanning the whole table. An analogy is a subject index in a book.

Pros: fast search, JOIN and sorting by the indexed columns.
Cons: takes space and slows down INSERT, UPDATE, DELETE, because the index also has to be updated.

A primary key and UNIQUE automatically create an index. Foreign keys in PostgreSQL don't, and they are often indexed manually.

For QA: if a search or a filter in the UI is slow on large data, one of the first hypotheses is a missing index.

CREATE INDEX idx_transactions_account_created
  ON transactions (account_id, created_at);

In an interview: An index is a separate structure, like a pointer in a book, that lets you find rows without scanning the whole table. It speeds up search and JOIN, but takes space and slows down writes. As QA I mention indexes when a filter or a report is slow on a large amount of data, and then together with a developer we look at the query plan.

What is EXPLAIN and how do you use it?

EXPLAIN shows the query's execution plan: how the database is going to look for data. EXPLAIN ANALYZE also actually runs the query and shows the real time and row counts.

Seq Scan: a scan of the whole table. On a large table with a selective filter it is suspicious.
Index Scan / Index Only Scan: search through an index.
A big difference between the expected and actual number of rows means stale statistics.

Careful: EXPLAIN ANALYZE on an UPDATE or DELETE will really change data: run it only in a transaction with ROLLBACK.

EXPLAIN ANALYZE
SELECT * FROM transactions
WHERE account_id = 10 AND created_at >= '2026-09-01';

-- Index Scan using idx_transactions_account_created on transactions
--   (actual time=0.03..0.21 rows=48 loops=1)

In an interview: EXPLAIN shows the query's execution plan, and EXPLAIN ANALYZE also runs it and shows the actual time. I look at whether an index is used or a full table scan happens. In a bug report about a slow page this lets me give the developer specifics, not just "it's slow". And I remember that ANALYZE on an UPDATE really changes the data.

What are normalisation and denormalisation?

Normalisation is designing tables so that each fact is stored once. In simple words about the first three normal forms:

1NF: one value in a cell, no comma-separated lists.
2NF: each non-key column depends on the whole key, not on part of it.
3NF: non-key columns don't depend on each other: the user's email isn't repeated in every transaction.

Denormalisation is deliberate duplication for read speed, for example storing an account's balance separately rather than computing it from transactions every time.

For QA denormalisation means a risk of getting out of sync: duplicated data must be checked for consistency.

In an interview: Normalisation is designing tables so that each fact is stored in one place, without duplication. Denormalisation is deliberate duplication for speed, for example a separately stored balance instead of constantly computing it from transactions. For me as QA every such duplication is a risk of falling out of sync, so I check that the copies of data match.

How do you test database migrations?

A migration is a script that changes the schema or the data: adds a column, renames, moves data. What to check:

Counts: the number of rows before and after is the same (or changed as expected).
Values: selectively and with aggregates: sums and counts by status match.
New columns: how old records were filled: NULL, the default, a computed value?
Constraints: the new NOT NULL and UNIQUE didn't fail on existing data.
Application: works with old data after the migration; old records open and can be edited.
Rollback: if there is a down migration, does it restore the state without loss.
Time: how long the migration takes on a volume close to production and whether it locks the table.

-- before and after the migration: identical checksums
SELECT status, COUNT(*), SUM(amount)
FROM transactions
GROUP BY status
ORDER BY status;

In an interview: For migrations I compare the state before and after: the row count and checksums by groups must match. I check how the new columns were filled for old records, whether the new constraints failed, whether the application works with the old data and whether the rollback is correct. And separately, how long the migration takes on a large volume of data, because it may lock the table in production.