Postgres tricks we reach for

We are not database maximalists. But a rule that lives in application code is a rule that holds until the second service arrives, and every product eventually gets a second service — a cron, an import script, an admin tool written in a hurry.

Row-level security, for real tenancy#

Januna is multi-tenant. Every query in the application filters by tenant_id, and every one of those filters is a chance to forget. Row-level security turns that from a convention into a constraint the database enforces regardless of who is asking.

alter table sale enable row level security;

create policy tenant_isolation on sale
  using (tenant_id = current_setting('app.tenant', true)::uuid);

-- the app sets this once per connection checkout
set local app.tenant = '…';

Generated columns, for things that must not drift#

Anything derived — a search vector, a lowercased email, a total with tax — is a chance for two code paths to disagree. A generated column can only have one answer, and it is computed by the thing that stores it.

alter table product
  add column search tsvector
  generated always as (
    to_tsvector('simple', coalesce(name,'') || ' ' || coalesce(sku,''))
  ) stored;

create index product_search_idx on product using gin (search);

Partial indexes, for the query you actually run#

Most tables are read with a filter attached — open orders, active sessions, unarchived rows. A partial index covers exactly that slice and skips the rest, which on Januna's sales table means an index a twentieth of the size answering the query that runs a thousand times an hour.

create index sale_open_idx on sale (tenant_id, created_at desc)
  where closed_at is null;

Advisory locks, for the job that must not run twice#

Before reaching for a queue or a scheduler with leader election, try the lock the database already has. A session-level advisory lock gives you mutual exclusion across every process pointed at the same database, and it disappears when the connection does.

select pg_try_advisory_lock(hashtext('nightly-rollup'));
-- false → someone else is already doing it, go home

The one we stopped using#

listen/notify for cache invalidation. It is elegant, it is instant, and it silently drops everything on a reconnect — which means the cache is correct until exactly the moment the network is not, and then it is wrong with no signal at all. We moved to short TTLs plus a version column. Less elegant, no failure mode that requires you to notice.