Soft Delete Patterns in Go and Their Hidden Costs Later
Soft delete with a deleted_at column costs more than it looks: blocked unique keys, forgotten filters and rows that never leave. Fixes with Postgres
The idea is attractive: instead of delete from users where id = 7, run update users set deleted_at = now() where id = 7. Nothing is lost, an accidental delete is undone with another update, and the audit trail is intact. The cost arrives later and in four places, and each has a plain fix in PostgreSQL.
Tested with Go 1.25.6, pgx v5.11.0, PostgreSQL 18.6 in Docker, macOS on Apple Silicon (arm64)
The four costs
package main
import (
"context"
"database/sql"
"errors"
"fmt"
"log"
"github.com/jackc/pgx/v5/pgconn"
_ "github.com/jackc/pgx/v5/stdlib"
)
const dsn = "postgres://postgres:[email protected]:55432/lab?sslmode=disable"
func must(db *sql.DB, q string, args ...any) {
if _, err := db.Exec(q, args...); err != nil {
log.Fatalf("%s: %v", q, err)
}
}
func main() {
ctx := context.Background()
db, err := sql.Open("pgx", dsn)
if err != nil {
log.Fatal(err)
}
defer db.Close()
count := func(q string) (n int) {
_ = db.QueryRowContext(ctx, q).Scan(&n)
return
}
must(db, "drop table if exists users623 cascade")
must(db, "create table users623 (id bigserial primary key, email text not null unique, deleted_at timestamptz)")
must(db, "insert into users623 (email) values ('[email protected]'), ('[email protected]')")
// 1. Soft delete one user, then the person signs up again.
must(db, "update users623 set deleted_at = now() where email = '[email protected]'")
_, err = db.ExecContext(ctx, "insert into users623 (email) values ('[email protected]')")
var pgErr *pgconn.PgError
if errors.As(err, &pgErr) {
fmt.Printf("1. sign up again after a soft delete: SQLSTATE %s %s\n", pgErr.Code, pgErr.Message)
}
// 2. The unique rule belongs only to live rows.
must(db, "alter table users623 drop constraint users623_email_key")
must(db, "create unique index users623_email_live on users623 (email) where deleted_at is null")
must(db, "insert into users623 (email) values ('[email protected]')")
fmt.Println("2. with a partial unique index the sign up works; live rows:", count("select count(*) from users623 where deleted_at is null"), "total rows:", count("select count(*) from users623"))
_, err = db.ExecContext(ctx, "insert into users623 (email) values ('[email protected]')")
if errors.As(err, &pgErr) {
fmt.Printf(" a second live ada is still refused: %s %s\n", pgErr.Code, pgErr.Message)
}
// 3. A query that forgot the filter.
fmt.Println("3. select count(*) from users where email like 'ada%' with no deleted_at filter:", count("select count(*) from users623 where email like 'ada%'"))
// 4. A view that holds the filter in one place.
must(db, "create view live_users623 as select id, email from users623 where deleted_at is null")
fmt.Println("4. the same count through the view:", count("select count(*) from live_users623 where email like 'ada%'"))
// 5. What the table keeps, and how to purge it.
must(db, "insert into users623 (email, deleted_at) select 'old' || i || '@example.test', now() - interval '400 days' from generate_series(1, 5000) i")
fmt.Println("5. rows soft deleted more than 90 days ago:", count("select count(*) from users623 where deleted_at < now() - interval '90 days'"))
res, _ := db.ExecContext(ctx, "delete from users623 where deleted_at < now() - interval '90 days'")
n, _ := res.RowsAffected()
fmt.Println(" purged:", n, "rows left:", count("select count(*) from users623"))
}1. sign up again after a soft delete: SQLSTATE 23505 duplicate key value violates unique constraint "users623_email_key"
2. with a partial unique index the sign up works; live rows: 2 total rows: 3
a second live ada is still refused: 23505 duplicate key value violates unique constraint "users623_email_live"
3. select count(*) from users where email like 'ada%' with no deleted_at filter: 2
4. the same count through the view: 1
5. rows soft deleted more than 90 days ago: 5000
purged: 5000 rows left: 3Unique keys. email text unique still sees the soft deleted row, so a person who deleted their account cannot sign up again. The first line of the output is the error: SQLSTATE 23505, duplicate key value violates unique constraint "users623_email_key". Replace the constraint with a partial unique index, create unique index ... on users (email) where deleted_at is null. The second line shows it working: the sign up succeeded, there are 2 live rows among 3, and a second live address was still refused. Foreign keys have the mirror problem: a child row can point at a parent that is deleted but present, so decide what a delete of a parent means for its children.
The forgotten filter. Every query must add where deleted_at is null. Miss it once and deleted data appears in a list, a count or an export. A like 'ada%' count with no filter returned 2, the live row and the deleted one. Put the condition in one place, a view such as live_users, and have application code read from the view; the same count through it returned 1. A repository method or a generated query can do the same job, but a view also protects ad hoc SQL.
Data that never leaves. A soft deleted row is still personal data. If a user asks for erasure, or retention rules say delete after 90 days, the row must really go. Schedule a purge. In the lab, 5,000 rows deleted over 90 days ago were removed with one delete, leaving 3. Index deleted_at (partial, where deleted_at is not null) so the purge does not scan the table, and delete in batches if the number is large.
Table growth. Deleted rows are still in the table and its indexes, so scans and vacuums read them. A table where 90 percent of rows are deleted is slow for the 10 percent that matter. Partial indexes on live rows keep the hot paths small, and the purge keeps the table honest.
When to avoid it
If you only need history, an audit table fed by a trigger keeps the old row without touching the live table's constraints; see audit logs with triggers or application code. If you need undo for a few minutes, a short lived "trash" table or a status column with a reaper may fit. Use soft delete where users really restore things, and for the rest delete rows. Pair it with unique violation handling so the sign up error above becomes a clear message. PostgreSQL documents the technique under partial indexes, including the unique case used here.
