Still using Jaeger/Sentry? Uptrace is an open source APM for OpenTelemetry that you can use to monitor applications and set up alerts to receive notifications via email, Slack, Telegram, and more.

Soft deletes in PostgreSQL and MySQL

Introduction

Soft deletes allow marking rows as deleted without actually deleting them from a database. You can achieve that by using an auxiliary flag column and modifying queries to check the flag value.

For example, to soft delete a row using deleted_at timestamptz column as a flag:

UPDATE users SET deleted_at = now() WHERE id = 1

To select undeleted (live) rows:

SELECT * FROM users WHERE deleted_at IS NULL

Using Bun models

Bun supports soft deletes using time.Time column as a flag that reports whether the row is deleted or not. Bun automatically adjust queries to check the flag.

To enable soft deletes on a model, add DeletedAt field with soft_delete tag:

type User struct {
	ID int64
	CreatedAt time.Time `bun:",nullzero,notnull,default:current_timestamp"`
	DeletedAt time.Time `bun:",soft_delete,nullzero"`
}

For such models Bun updates rows instead of deleting them:

_, err := db.NewDelete().Model(user).Where("id = ?", 123).Exec(ctx)
UPDATE users SET deleted_at = current_timestamp WHERE id = 123

Bun also automatically excludes soft-deleted rows from SELECT queries results:

err := db.NewSelect().Model(&users).Scan(ctx)
SELECT * FROM users WHERE deleted_at IS NULL

To select soft-deleted rows:

err := db.NewSelect().Model(&users).WhereDeleted().Scan(ctx)
SELECT * FROM users WHERE deleted_at IS NOT NULL

To select all rows including soft-deleted rows:

err := db.NewSelect().Model(&users).WhereAllWithDeleted().Scan(ctx)
SELECT * FROM users

Finally, to actually delete rows from a database, whether previously soft deleted or not:

db.NewDelete().Model(user).Where("id = ?", 123).ForceDelete().Exec(ctx)
DELETE FROM users WHERE id = 123

Using table views

You can also implement soft deletes using table views. Given the following table schema:

CREATE TABLE all_users (
  id int8 PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  name varchar(500),

  created_at timestamptz NOT NULL DEFAULT now(),
  deleted_at timestamptz
);

You can create a view that omits deleted users:

CREATE VIEW users AS
SELECT * FROM all_users
WHERE deleted_at IS NULL

PostgreSQL views support inserts and deletes without any gotchas so you can use them in models:

type User struct {
	bun.BaseModel `bun:"users"`

	ID   uint64
	Name string
}

To query deleted rows, use ModelTableExpr to change the table:

var deletedUsers []User
err := db.NewSelect().
	Model(&deletedUsers).
	ModelTableExpr("all_users").
	Where("deleted_at IS NOT NULL").
	Scan(ctx)

Unique indexes

Using soft deletes with unique indexes can cause conflicts on insert queries because soft-deleted rows are included in unique indexes just like normal rows.

With some DBMS, you can exclude soft-deleted rows from an index:

CREATE UNIQUE INDEX index_name ON table (column1) WHERE deleted_at IS NULL;

Alternatively, you can include deleted_at column to indexed columns using coalesce function to convert NULL time because NULL is not equal to any other value including itself:

CREATE UNIQUE INDEX index_name ON table (column1, coalesce(deleted_at, '1970-01-01 00:00:00'))

If your DBMS does not allow to use expressions in indexed columns, you can configure Bun to append zero time as 1970-01-01 00:00:00+00:00 by removing nullzero option:

type User struct {
	ID int64
	CreatedAt time.Time `bun:",nullzero,notnull,default:current_timestamp"`
-	 DeletedAt time.Time `bun:",soft_delete,nullzero"`
+	 DeletedAt time.Time `bun:",soft_delete"`
}

Monitoring performance

To monitor Bun performance, you can use OpenTelemetry instrumentation that comes with Bun and Uptrace.

Uptrace is an open source DataDog competitoropen in new window that supports OpenTelemetry tracingopen in new window, OpenTelemetry metricsopen in new window, and logs. You can use it to monitor applications and set up automatic alerts to receive notifications via email, Slack, Telegram, and more.

You can also check the following guides to monitor features specific to your RDBMS: