Data: PostgreSQL and EF Core - storing, guarding and forgetting records

How PostgreSQL works inside (schemas, indexes, MVCC and xmin, isolation levels, advisory locks, full-text search, JSON), how EF Core works (DbContext, change tracking, filters, interceptors, migrations, ExecuteUpdate, retries), and the techniques built on them: soft delete, tenant isolation, race-safe writes, expand-and-contract migrations, GDPR erasure and the Data Protection key ring.

In short

{{brand}} keeps its records in PostgreSQL, an open-source relational database, and reaches it through EF Core, the object-relational mapper of .NET. Each module owns one schema in a shared database. A few rules are written once and apply to every query and every save. Deleted rows are hidden, rows the caller may not see are hidden, and changes land in an audit trail through an interceptor. When two requests collide, the database itself decides - through unique indexes, row versions, serializable transactions and advisory locks.

After this lesson you can Explain what MVCC and each isolation level guarantee. Design indexes and race-safe writes that survive two simultaneous requests. Use EF Core filters, interceptors and migrations without their classic traps. Change a live schema with expand and contract, and erase a person's data.

Words you need

• Transaction (ACID) A group of statements that all happen or none do. ACID is the promise: atomic, consistent, isolated, durable.
• Index A sorted structure, usually a B-tree, that finds rows without reading the whole table. A unique index also refuses duplicates.
• Partial index An index over only the rows that match a condition, such as where deleted_at is null.
• MVCC Multi-version concurrency control: an update writes a new version of the row, so readers never wait for writers.
• xmin A hidden column on every PostgreSQL row: the id of the transaction that wrote this version. It changes on every update, so it works as a row version - but it is a transaction id, not a counter.
• Isolation level How much one transaction sees of others running at the same time: Read Committed, Repeatable Read or Serializable.
• Advisory lock A lock on a number you choose, not on a row - for example so that only one server runs a nightly job.
• DbContext EF Core's unit of work: one per request. It runs queries, remembers what was loaded and saves the changes together.
• Change tracker The part of the DbContext that compares loaded values with current ones and writes only what changed.
• Migration A versioned C# file that changes the database structure, generated by comparing the model with a snapshot of the last one.
• Global query filter A condition EF Core adds to every query of one entity type - for example not deleted and in my organization.
• Tenant One customer's space inside a shared system - a company, or a person's own workspace. Multi-tenancy is keeping many of them apart in one database.

How PostgreSQL is organised and how MVCC works

A PostgreSQL server holds databases; a database holds schemas; a schema holds tables, indexes and constraints. One connection talks to one database but can read every schema in it. Rows live in 8 kB pages on disk, and indexes point at rows inside those pages.

• 1. The transaction gets an id PostgreSQL gives each writing transaction a transaction id, say 7502.
• 2. A new version is written UPDATE does not change the row in place. It writes a new version with xmin = 7502 and marks the old one with xmax = 7502.
• 3. Readers keep their snapshot A query that started earlier still sees the old version. Readers never wait for writers, and writers never wait for readers.
• 4. Commit makes it visible When 7502 commits, new snapshots see the new version. If it rolls back, the new version is simply never visible.
• 5. Vacuum cleans up Versions no snapshot can see any more are dead tuples. Autovacuum removes them so the space can be reused.

Three consequences you will meet in real work Because every update writes a new version, xmin is a free row version for optimistic concurrency; a transaction left open for hours keeps dead versions alive and the table grows (bloat); and a row stamped inside a transaction becomes visible only at commit - possibly after rows stamped later. All three come back in this lesson.

Indexes: B-tree, unique and partial

Without an index, PostgreSQL reads every row - a sequential scan. A B-tree index keeps values sorted, so finding one value among a million takes a handful of page reads. A composite index on (workspace_id, name) serves queries on workspace_id alone or on both columns, but it is of little use for name alone. The index is sorted by workspace_id first (the leftmost columns rule), so PostgreSQL would have to scan the whole index.

This is how {{brand}} combines soft delete with unique names: every unique index of a soft-deletable table covers live rows only. One shared model rule adds the condition to each such index, so no developer has to remember it. Without it, deleting a tag named Urgent would block a new Urgent forever.

The planner must be able to prove the condition A partial index serves a query only when the query's own WHERE clause implies the index condition. A lookup by name that does not also say deleted_at is null cannot use tags_name_live. With EF Core's soft-delete filter on every query, the condition is always there - so the index always fits.

Reading a query plan with EXPLAIN

An index only helps if the planner uses it. EXPLAIN shows the plan PostgreSQL chose, and EXPLAIN ANALYZE also runs the statement and reports what really happened. It is the first tool to reach for when a query got slow after a table grew.

EXPLAIN ANALYZE really runs the statement An UPDATE or DELETE under EXPLAIN ANALYZE changes the data. Wrap it in a transaction and finish with rollback. On a tiny table the planner may choose a Seq Scan even when an index exists, because reading a few pages is cheaper - test with realistic row counts.

Transactions and isolation levels

A transaction groups statements so they happen completely or not at all - the A of ACID. The isolation level decides what a transaction sees of others running at the same moment. PostgreSQL has three real levels, and its default is Read Committed.

• A looks Transaction A asks: is there a note for event 42? No.
• B looks Transaction B - a second click a moment later - asks the same and also sees no note.
• A inserts and commits A creates the note and commits.
• B is refused B inserts too, then tries to commit. PostgreSQL's serializable snapshot isolation sees that B read something A has since changed, and aborts B with error 40001, at the insert or at the commit.
• B runs again The retry starts from a fresh snapshot, finds A's note and returns it. Two clicks, one note.

The whole block may run twice With a retrying execution strategy switched on, EF Core refuses a transaction you open yourself outside the strategy, because it could not replay it after a dropped connection. Hand the whole unit to the strategy instead. The Npgsql provider treats a serialization failure as transient, so the same strategy also replays a block that lost a serializable conflict. Keep the block idempotent: no e-mails, payments or HTTP calls inside, or a retry repeats them.

Row locks and advisory locks

PostgreSQL takes a row lock by itself when you update or delete a row; a second writer of the same row waits until the first commits. You can take one on purpose with SELECT ... FOR UPDATE. An advisory lock is different: it locks a number whose meaning your application decides, such as the nightly clean-up job.

• Every server wakes up Each instance's timer fires for the same background sweep at about the same time.
• Each asks for the lock Each opens its own connection and calls pg_try_advisory_lock with the job's number. Nobody waits.
• One wins Exactly one gets true and runs the job. The others get false and skip this round.
• The lock goes back The winner unlocks in a finally block. If it crashes, its connection dies and PostgreSQL releases the lock by itself.

Two typical uses of session advisory locks: a background sweep that must run on one server at a time, and a capacity check followed by a write, so two requests can never both take the last free place.

Turning a name into a lock number Advisory lock keys are 64-bit numbers, so a job name must be hashed. Use a stable hash, such as the first 8 bytes of SHA-256. .NET's string.GetHashCode is randomized per process, so two servers would compute different numbers and both run the job.

Connection pooling

Opening a PostgreSQL connection is expensive: a network handshake, authentication and a separate server process for each connection. The server also has a hard limit, max_connections, 100 by default. Applications therefore keep a pool of open connections and lend them out.

• A request needs the database The code asks the driver for a connection.
• The pool lends one Npgsql keeps a pool per connection string - 100 connections at most by default - and hands out an idle connection at once. Opening a new one is the slow path.
• The connection goes back Disposing the connection does not close it. It returns to the pool, ready for the next request, so hold it only as long as you need it.
• A pooler for many app servers When many servers share one database, a pooler such as PgBouncer sits in front and multiplexes thousands of client connections over a few dozen server connections.

Session advisory locks need a session-mode connection A session advisory lock lives as long as one server connection. In transaction mode the pooler may give your next statement a different connection, so the lock is lost or held by the wrong client. Connect straight to PostgreSQL, or through a session-mode pooler, for work that relies on session state. A slow transaction also holds its connection: pool exhaustion looks like random timeouts.

Full-text search and JSON inside PostgreSQL

Full-text search turns text into a tsvector: a sorted list of word stems with their positions, without stop words such as the and on. A question becomes a tsquery. The @@ operator tests a match, and ts_rank or ts_rank_cd scores it. A GIN index on a stored vector keeps the search fast on millions of rows.

PostgreSQL has two JSON types. json keeps the text exactly as it was sent. jsonb stores a parsed binary form. Its keys are de-duplicated, it can be indexed with GIN, and operators such as ->> (read a value as text) and @> (contains) work inside it.

• A JSON text column Serialized by the application; One column, simple to map; Runs on EF Core's in-memory test provider too; The database cannot search or index inside it; EF Core needs a value comparer to notice changes to the list
• A jsonb column Parsed by PostgreSQL; Indexable with GIN; Query inside with ->> and @>; PostgreSQL-only SQL in the queries; Cannot be tested on the in-memory provider
• A child table One row per item; Constraints, foreign keys and ordinary indexes; A join or a second query on every read; More tables and migrations

How EF Core works

EF Core maps C# classes to tables. You write LINQ; EF Core translates it into SQL for the configured provider - Npgsql for PostgreSQL - runs it, and turns the rows back into objects. A DbContext lives for one request, and its change tracker remembers every object it loaded.

• LINQ becomes a tree db.Invoices.Where(x => x.Total > 100) builds an expression tree, not a loop.
• Filters are added EF Core adds the entity's global query filters - not deleted, my organization - to the tree.
• SQL is generated The provider translates the tree into one parameterized SQL statement; the translation is cached.
• Rows become objects Rows come back and are turned into objects - materialization.
• Tracked or not Each object is tracked with a snapshot of its values. AsNoTracking skips that for read-only screens.

• Interceptors run The saving interceptors are called first, before any change is detected. An interceptor that reads entity states should call ChangeTracker.DetectChanges itself. Typical jobs: stamp times and authors, turn deletes into soft deletes, add audit rows.
• Detect changes The change tracker compares current values with the snapshots and marks entries Added, Modified or Deleted.
• One transaction EF Core opens a transaction if none is open and sends batched INSERT, UPDATE and DELETE statements - only the changed columns.
• Concurrency check An UPDATE that carries a row version and changes 0 rows throws a concurrency exception.
• Commit and accept The transaction commits, the saved interceptors run, and every entry becomes Unchanged.

• ExecuteUpdate and ExecuteDelete (EF Core 7 and later) send one UPDATE or DELETE straight to the database. They are ideal for counters and bulk changes, but they skip the change tracker and every save interceptor - no audit row, no automatic timestamps.
• A database default that differs from the C# default is a trap: EF Core leaves a bool's false out of the INSERT because it looks unset, so a database default of true wins. Keep database defaults equal to C# defaults.
• Paging without an order returns rows in whatever order the database likes, so a row can appear on two pages or on none. Always order by something unique, or add the id as a tie-break. For deep pages prefer keyset paging: continue after the last row you showed instead of skipping rows.
• A query per row (N+1): loading 50 cards and then their tags one card at a time. Load the related rows in one query with Include or a projection, and count statements on busy screens.

The options in the market

• PostgreSQL Open-source relational database; No licence cost per core or per customer installation; The same engine on a laptop, in CI, inside a customer's network and as a managed cloud service; Schemas, partial indexes, MVCC row versions, serializable isolation, advisory locks, full-text search and jsonb built in; Vacuum and connection pooling need care at scale; Fewer built-in admin tools than SQL Server
• SQL Server Microsoft's relational database. Wins when you already license it and need temporal tables.; Excellent tooling and temporal tables; First-class support in .NET; Licence cost - and a customer installing the product would pay it too; Heavier to run in containers
• MySQL / MariaDB Popular open-source relational database. Wins when your team and hosting are built around it and you need none of PostgreSQL's extras.; Widely known, simple to run; Schema and database are the same thing, so one schema per module does not map; No partial indexes
• MongoDB Document database. Wins when you truly have schema-less documents and want horizontal sharding first.; Flexible documents; Easy horizontal scaling; Plans, permissions and boards are relational: joins, constraints and multi-row transactions matter
• SQLite A database in one file. Wins for tools, tests and single-user apps.; Zero setup, great for tools and tests; One writer at a time - not for a busy multi-user server

• EF Core The .NET object-relational mapper; Typed LINQ queries; Migrations generated from the model; Global query filters and interceptors put the rules in one place; Change tracking writes only what changed; Easy to write a query per row without noticing; Some SQL is clearer by hand
• Dapper A thin mapper over your own SQL. Wins for read-heavy services with hand-tuned queries.; Fast and simple; Full control of every statement; No migrations, filters or interceptors - every tenant and delete rule by hand, in every query
• Plain ADO.NET / Npgsql Commands and readers. Wins for bulk loads with COPY and other PostgreSQL-only features.; Every PostgreSQL feature, nothing in between; Mapping, parameters and rules all by hand

What {{brand}} chose, and why

{{brand}} needed one engine that runs the same on a developer's laptop, in CI, as a managed cloud database and inside a customer's own network for the Private edition. PostgreSQL is free to run everywhere. The features this lesson teaches - partial unique indexes, xmin row versions, serializable transactions, advisory locks and full-text ranking - are all built in, and all used.

EF Core was chosen for its global query filters and interceptors. With them, the rules that protect data - hide deleted rows, hide other organizations, audit every change - are written once instead of in every query. Where SQL itself is the point, such as advisory locks, ranking or reading the server's own activity view, {{brand}} sends plain SQL through Npgsql beside EF Core.

For keeping customers apart, {{brand}} uses shared tables with a tenant column and filters written once. A database of their own is an option layered on top, for organizations that need their data in a place they choose.

• No joins across modules. Combining data across modules goes through code contracts, not SQL joins or foreign keys.
• EF Core can hide slow SQL. Busy screens are measured by counting their statements, so a query per row is caught before it ships.
• In-memory tests cannot show races. Most tests use a fast in-memory provider, so races, unique indexes and migrations are checked separately on a real PostgreSQL.
• Lock-in is limited, not zero. Most code does not know it talks to PostgreSQL, but the migrations and a few raw queries are PostgreSQL-specific.

When another choice would be right A company that already runs and licenses SQL Server should use it - EF Core supports it just as well. Truly schema-less documents may fit MongoDB. A read-heavy reporting service with hand-tuned SQL may prefer Dapper next to EF Core.

Soft delete and global query filters

A soft delete marks a row as deleted with a time instead of removing it. The row can be restored, the audit trail can still name it, and a report about last month stays correct. The price is twofold: every query must skip deleted rows, and unique names must ignore them. Global query filters pay the first price; partial unique indexes pay the second.

• Remove Call Remove on the row, as with any EF Core entity.
• The interceptor converts it Before the save, an interceptor turns the Deleted state into an UPDATE that sets the deleted time and who deleted it.
• Filters hide it Every query of that entity carries a not deleted condition, so the row disappears from every screen and API.
• The name is free The unique index covers live rows only, so a new row may take the same name.
• Restore, or really remove Restoring clears the time. Only a privacy erasure, a retention sweep of old technical records, or the final erasure of a closed organization removes a row for real.

A filter may read a property of the DbContext, such as the current organization. EF Core does not bake the value into its cached query: it reads it again for each context instance, so one SQL shape serves every caller with their own parameter. That is why a DbContext is created per request and never shared. Before EF Core 10 an entity had one filter, so conditions were joined with &&; EF Core 10 adds named filters that can be switched off one at a time.

A rule worth following everywhere Never mix a filtered set and IgnoreQueryFilters in one LINQ expression - the leak is silent, and no test that runs as a single tenant will notice it. Load the visible rows first, then read what you need by id in a query of its own.

Interceptors and the audit trail

An interceptor is a class EF Core calls at fixed moments. A save interceptor sees every tracked change just before it is written - the ideal place for rules that no handler may forget. Attach them where every context is registered, so a new module is covered the moment it registers its database.

• Find the real changes For each added, changed or deleted row, list the fields whose value really changed - not every field marked modified.
• Mask what is secret Secret fields, and fields marked as personal data, are logged as changed with the value redacted.
• Say who, when and what Record who acted, when, the action - created, updated, deleted, restored or erased - and which tenant the row belongs to.
• Save together The audit rows join the same save, so they commit in the same transaction as the change - never one without the other.
• Treat the log as write-once Give the application no code path that edits or deletes an audit entry.

One transaction, or none Writing the audit row in the same save as the change is what makes the trail trustworthy. An audit written afterwards by a second call is lost whenever the process stops between the two.

Keeping organizations apart (multi-tenancy)

A tenant is one customer's space - a company, or a person's own workspace. Most SaaS products keep many tenants in one database. The danger is a single missed condition: a query without its tenant check shows one company's data to another. The defence is to make the check impossible to forget and to take the tenant only from the verified caller.

• Shared tables, tenant column, ORM filters The most common choice for SaaS; One database to run, back up and migrate; The filter is written once per entity, not per query; Testable with fast in-memory tests; Raw SQL outside the ORM must add the condition itself; One careless IgnoreQueryFilters can leak
• PostgreSQL row-level security Policies inside the database. Wins when many different programs and raw SQL reach the same tables.; The database enforces it, even for raw SQL; Every transaction must set the tenant first; Table owners bypass it unless it is forced; Invisible to in-memory tests
• A schema per tenant Same tables, one schema each. Wins with a few large tenants.; Clear separation, easy per-tenant export; Thousands of schemas to migrate; Pools and caches per schema
• A database per tenant Strongest isolation, for tenants with special requirements; Strongest isolation; The data can live where the customer requires; Each database is migrated, backed up and reached separately

• Authenticate A verified session or token says who is calling.
• Check membership Before any handler runs, a middleware checks that this person is a member of the tenant the request names; otherwise the request is refused.
• Keep it in a scoped service The verified person and tenant live in one object for this request only.
• Build the context from it Each request gets its own DbContext, built with that object.
• The filter uses it Every query adds tenant = the verified one as a parameter. The client never supplies it, so it cannot be forged.

• Fail closed. A filter also requires a known caller: with no signed-in person it matches nothing.
• Children follow parents. A child row's filter says that its parent must be visible, so everything inside a hidden project is hidden too.
• Never trust a client-sent id for the owner or the tenant; take it from the verified caller.
• Test with two tenants. A leak is silent for a single tenant, so a test needs a second tenant that must see nothing.

When two requests collide

A race condition is a bug that appears only when two requests run at the same moment. The classic is check-then-act: both requests check that a name is free, both see yes, both insert. Code alone cannot close the gap between the check and the write. The database must decide.

• Both read Request A and request B both load subscription 9 with xmin = 812.
• A saves A sends UPDATE ... WHERE id = 9 AND xmin = 812. One row changes, and PostgreSQL gives it a new xmin.
• B saves B sends the same condition. The row's xmin is no longer 812, so 0 rows change.
• EF Core throws EF Core sees 0 rows and throws a concurrency exception instead of silently overwriting A's change.
• B tries again B reloads the row, applies its change to what A left, and saves again. Nobody's change is lost.

xmin is a transaction id, not a counter xmin is a 32-bit transaction id. It wraps around, and vacuum can replace an old value with a frozen marker, so a conflict may be reported although nobody edited the row. Reloading and retrying is always safe. Never sort or compare versions by it.

Commit order is not timestamp order A transaction can stamp a row at 10:00:01 and commit at 10:00:05, after a row stamped 10:00:03 was already visible. A reader that pages through rows by time can skip the late row forever. A reader of a log in time order should stop short of the start of the oldest unfinished write transaction - PostgreSQL shows it in pg_stat_activity - minus a safety margin. Otherwise a late commit can hide behind the cursor.

The transactional outbox

A request often changes data and must tell someone: send an e-mail, update a search index, notify another module. Doing both directly is unsafe. If the process stops between the commit and the call, the change exists and the announcement is lost. The transactional outbox fixes this: write the announcement as a row in the same transaction as the change, and deliver it afterwards. {{brand}} uses this pattern for platform events.

• Write both rows Inside one transaction, update the data and insert an outbox row that describes the event.
• Commit Either both rows exist or neither does. No announcement can describe a change that was rolled back.
• Deliver right away The request tries to hand the event to its handlers straight after the commit - the fast path.
• A worker sweeps up A background worker reads undelivered rows with FOR UPDATE SKIP LOCKED, delivers them and marks them done. Several workers can run without taking the same row.
• Retry, then park A failing handler is retried with a delay. After too many failures the event is set aside for a person to look at.

Delivery is at least once A worker can crash after delivering and before marking the row, so the same event may be delivered twice. Make every handler idempotent: handling an event a second time must change nothing.

Paging and backups

Two habits of a database that holds real customers. Page deliberately, because lists grow. And back up in a way you have actually restored, because a backup nobody has tested is a hope.

Rehearse the restore Restore into an empty server on a schedule and run a few queries. Time it. Keep the encryption keys for any encrypted columns with the backup - see the Data Protection section below.

Migrations and expand-and-contract

An EF Core migration is a C# class with an Up method that applies a change and a Down method that undoes it. It is generated by comparing your model with the model snapshot of the last migration. A history table in the database records which migrations ran, so applying them is repeatable.

• Expand Add the new structure beside the old one, so both the old and the new version of the code keep working.
• Backfill Copy existing data into the new structure in small batches, so no long lock is held.
• Contract Remove the old structure in a later release, once no running version uses it.

• Change the model Add a property, a table or an index in C#.
• Generate dotnet ef migrations add writes the migration, the model it was made from and an updated snapshot.
• Read it Check the generated Up. A DROP or a RENAME you did not intend is the most expensive bug a migration can hold.
• Test it Apply it to an empty real database, roll it back, and apply it again.
• Apply on deploy The migrator runs pending migrations in order and writes each id to the history table.

During a deploy the old version keeps running while the new one starts, and a rollback runs the old code on the new schema. So a migration must never break the version before it. Expand and contract splits every breaking change into safe steps spread over releases.

• Expand Release 1 adds display_name as a nullable column. The code writes both name and display_name.
• Backfill A job copies name into display_name in small batches, so no long lock is held.
• Switch Release 2 reads display_name only and stops writing name.
• Contract Release 3, once no running version uses name, drops it in a migration marked as a contract step.

Many teams start with one baseline migration per context, regenerated on every change, while every database is disposable. Once real data exists, migrations become additive only. A mature team enforces this with a test that fails the build when a migration drops, renames, retypes or adds a required column without a default, with a test that checks each model still matches its last migration, and with a CI job that applies every migration up and down on a real PostgreSQL.

GDPR erasure: forgetting a person for real

The right to erasure (GDPR Article 17) lets a person ask for their personal data to be deleted. A soft delete is not enough - the data is still there. But deleting everything a person ever touched would damage other people's records: a board they commented on, a report with their name in its history. Erasure is therefore a set of techniques, chosen row by row.

• Hard delete The row is removed for real. Use it for rows that are the person's alone.
• Anonymize The row stays for other people, but forgets who created, changed or deleted it.
• Placeholder person One shared Deleted user id takes the person's place in a column that must not be empty.

• Check blockers Anything that would orphan shared data must be handled first, in words the person can act on.
• Run with the actor withheld No save in the pipeline may name the person who is being forgotten.
• Each data owner forgets its part Hard-delete the person's own rows, anonymize shared rows, put the placeholder in required columns. Timestamps are kept, so past reports stay correct.
• Rewrite the references Notifications keep ids, so the erased person's id becomes the placeholder and the inbox reads Deleted user.
• Reach every store Any other database that holds the person's rows is erased the same way.
• The account goes last Only when every part has succeeded is the account deleted. If one part fails, the account stays, and running it again finishes the job.

Erasure is bookkeeping, not an edit An erased or anonymized row keeps its created and updated times and never shows up as a fresh edit. Plan your unique indexes too: if erased people are replaced by one placeholder, any unique index that includes that column must allow repeats of it.

The ASP.NET Core Data Protection key ring

Some values must be stored encrypted and read back later: a third-party API key, a connection string, a one-time link token. ASP.NET Core Data Protection does this. You ask for a protector with a purpose string, and it encrypts with authenticated encryption - AES-256-CBC with HMACSHA256 by default - using the current key of the key ring. Every protected value carries the id of the key that sealed it.

• Create a protector CreateProtector("Shop.ApiKeys.v1"). The purpose isolates this use: a value sealed for one purpose cannot be opened by another.
• Protect The current default key seals the value. The result holds the key id, the ciphertext and a MAC.
• Rotate Every 90 days by default a new key becomes the default. Old keys stay in the ring and are never deleted, because old values still need them.
• Unprotect The key id in the value finds the right key; a single changed byte makes the MAC check fail.

Encryption keeps working - until it cannot With a lost key ring, new values still encrypt, so nothing looks wrong - until the next deploy cannot decrypt anything sealed before. Good practice: refuse to start in production without a persistent key ring, keep the ring where every instance can read it, and back it up together with the data it protects.

Try it yourself: a thirty-minute PostgreSQL and EF Core lab

Everything in this lesson can be seen with Docker, two terminal windows and a small .NET console project. Run the steps side by side where they say both sessions - the interleaving is what makes the conflicts appear.

• Start PostgreSQL Run the container, then open psql in two terminals against the same database.
• Soft delete and a partial index Create the tags table and partial unique index from this lesson. Delete Urgent softly, create it again, then try a second live Urgent and read error 23505.
• Watch MVCC Read xmin before and after an update on the accounts table. Then, in session 2, begin a repeatable read transaction and run a first query - the snapshot is taken at that first statement. Update in session 1, read again in session 2 and see the old value until the transaction ends.
• Read a plan Fill a table with 100,000 rows, run explain analyze on a lookup and see a Seq Scan. Create an index, run it again and compare. Drop the index afterwards.
• Cause a serialization failure In both sessions: begin serializable and count the notes for event 42. Then insert in both. Then commit both - the second commit fails with 40001.
• Take an advisory lock Call pg_try_advisory_lock(424242) in both sessions: one true, one false. Close session 1, then call it again in session 2 and see it succeed.
• Try EF Core Create a small console project with a soft-delete query filter and a save interceptor. Print the generated SQL with ToQueryString() and watch the commands EF Core sends.
• Clean up Remove the container so nothing is left running.

Common mistakes

• Check, then insert, with no unique index: two requests both pass the check. Let a unique index decide, and handle error 23505 by using the winner's row.
• Read, add one, write back for a counter: two requests read 5 and both write 6. Use one UPDATE ... SET count = count + 1.
• Slow work inside a transaction: calling an e-mail or payment API while a transaction is open keeps locks and dead row versions (bloat) alive, and a retried block repeats the call. Keep transactions short, idempotent, and free of external calls.
• IgnoreQueryFilters inside a bigger query: it switches off the tenant filter too. Read unfiltered data in a separate query.
• Dropping or renaming in the release that stops using a column: the old version, still running, breaks. Expand first; contract one release later.
• Missing indexes and a query per row: a filter or foreign-key column without an index becomes a sequential scan as the table grows, and loading related rows in a loop is N+1. Read EXPLAIN, and use Include or a projection.

Interview questions

• What is MVCC, and what does xmin hold? Multi-version concurrency control: an update writes a new version of the row, so readers keep a consistent snapshot without blocking writers. xmin is the id of the transaction that wrote the version, so it changes on every update and can serve as a row version.
• How do PostgreSQL's isolation levels differ? Read Committed takes a fresh snapshot per statement; Repeatable Read keeps one snapshot for the transaction; Serializable also detects dangerous read-write patterns, such as write skew (two transactions each change a different row based on what both read), and aborts one with 40001. The higher the level, the more you need retry logic.
• Two requests insert the same row, or edit one row, at the same moment. What do you do? For inserts, put a unique index on the natural key and let the database pick the winner; the loser catches the unique violation (23505) and uses the winner's row. For edits, use optimistic concurrency: a version column such as xmin goes into the UPDATE's WHERE clause, EF Core throws a concurrency exception when zero rows match, and you reload and apply the change again.
• Why does EF Core refuse your BeginTransaction when retries are on? A retrying execution strategy replays failed work, but it cannot replay a transaction it did not start. Wrap the whole unit in the strategy's ExecuteAsync, clear stale tracked rows on a retry, and keep side effects out of the block.
• How do you keep tenants apart in one shared database? A tenant column on every row and a global query filter that reads the verified caller, written once per entity; child rows inherit through their parent's filter, and the filter fails closed without a caller. The trap to name is IgnoreQueryFilters inside a subquery, which disables every filter of the query.
• How do you rename a column with zero downtime? Expand and contract: add the new column, write both, backfill in batches, switch reads, and drop the old column in a later release once no running version uses it. Never rename in place while the old code runs.
• How do you honour a GDPR erasure request without breaking history? Hard-delete the rows that are the person's alone, anonymize shared rows, put one placeholder person in required columns, and make sure the audit log never stored personal values in the first place. Keep timestamps, so reports about the past stay correct.
• What is the Data Protection key ring, and what happens if you lose it? It holds the keys that encrypt stored secrets, and each protected value names its key. If the ring is not persisted and shared, values become permanently undecryptable after a redeploy - while new encryption keeps appearing to work.
• A query got slow after the table grew. How do you find out why? Run EXPLAIN (ANALYZE, BUFFERS) and look for a Seq Scan on a large table or a big gap between estimated and actual rows. Add or reshape an index, run ANALYZE if statistics are stale, and check that the plan changed.
• What is the N+1 problem and how do you fix it in EF Core? One query for a list plus one more for every row. Fix it with Include or a projection that loads the related data in one query, and count statements in tests.

Related

• /help/technology/architecture
• /help/technology/access
• /help/technology/testing