Rate limiting

Ever needed to rate-limit inserts? Did you consider letting your source of truth, the database, enforce it for you? I did. Let’s explore one unconventional but elegant^{[citation needed]} approach.

My workload is strictly insert-only: only SELECT and INSERT, never UPDATE or DELETE. The rate limit is equally simple, a fixed minimum interval (cooldown) between inserts from the same user.

Imagine a table called message that stores chat messages sent by users. A minimal DDL might look like this:

create table message (
  user_id text        not null,
  content text        not null,
  sent_at timestamptz not null
);

A straightforward solution is a BEFORE INSERT trigger that checks whether the user is still on cooldown. I’m not a simple man though, so here we are.

Can we enforce the cooldown without an explicit check? Yes, by combining two powerful PostgreSQL features:

• Range types (specifically tstzrange)
• Exclusion constraints backed by a GiST index

Exclusion constraints guarantee that no two rows have overlapping values for the specified expression(s). PostgreSQL 18 made the syntax for “no overlaps” much cleaner with the WITHOUT OVERLAPS clause.

The idea is simple: give each message a half-open time range [now(), now() + cooldown) that belongs exclusively to that user. Because the exclusion constraint prevents overlapping ranges for the same user_id, a second insert within the cooldown window is rejected at the constraint level, no trigger required.

Here’s the complete DDL:

drop table if exists message;

create table message (
  user_id         text        not null,
  content         text        not null,
  sent_at         timestamptz not null default now(),
  cooldown_period tstzrange   not null default tstzrange(
    now(), now() + interval '30 seconds',
    '[)'
  ),

  unique ( user_id, cooldown_period without overlaps )
);

Let’s test it:

insert into message (user_id, content)
values ('bananilson', 'nada acontece feijoada');

-- this second insert happens immediately,
-- still inside the 30-second window
insert into message (user_id, content)
values ('bananilson', 'supimpa');

The second insert fails with:

ERROR: conflicting key value violates exclusion constraint
"message_user_id_cooldown_period_key"

After 30 seconds (or whatever interval you choose), the next message from the same user succeeds again.

That’s it! With a single declarative constraint we get:

• Atomic, race-condition-free rate limiting;
• No application-level locking or querying;
• Enforcement at the only true source of truth.

That's all, folks.