PostgreSQL Advisory Locks for Distributed Rate Limiting

---
title: "PostgreSQL Advisory Locks for Distributed Rate Limiting in Your Mobile API Gateway"
published: true
description: "Replace Redis with pg_try_advisory_xact_lock for sliding-window rate limiting. Benchmarks, Ktor/Exposed implementation, and PgBouncer gotchas included."
tags: kotlin, postgresql, api, architecture
canonical_url: https://blog.mvpfactory.co/postgresql-advisory-locks-for-distributed-rate-limiting
---

## What We're Building

Let me show you a pattern I use in every project that runs a Ktor-based mobile API gateway backed by PostgreSQL: sliding-window rate limiting using `pg_try_advisory_xact_lock`, with zero Redis dependency.

By the end of this tutorial, you'll have a working rate limiter that handles up to ~15k requests/second per node, saves around $200/month on lean infrastructure, and removes an entire failure domain from your stack.

## Prerequisites

- A Kotlin project with **Ktor** and **Exposed** ORM
- PostgreSQL 12+
- PgBouncer (optional but common — we'll cover the traps)
- Familiarity with database transactions

## Step 1: Understand Why This Works

PostgreSQL advisory locks are application-level cooperative locks that live outside the normal table-locking mechanism. The key function is `pg_try_advisory_xact_lock(key bigint)` — it attempts to acquire a transaction-scoped lock and returns `true` or `false` immediately, without blocking.

The trick: map each rate-limit bucket to a lock namespace, and use a counting table to track the sliding window.

## Step 2: Design Your Lock Namespace

Use the two-argument form `pg_try_advisory_xact_lock(classid, objid)`. Reserve `classid` for the bucket type and `objid` for the entity hash.

| Bucket Type | Lock Key Strategy | Example Key |
|---|---|---|
| Per-user global | `hash(user_id)` | `hash("user-42")` → `982374` |
| Per-user per-endpoint | `hash(user_id \|\| endpoint)` | `hash("user-42:/api/feed")` → `571923` |
| Per-IP global | `hash(ip_address)` | `hash("10.0.1.5")` → `384756` |

## Step 3: Implement in Ktor/Exposed

Here is the minimal setup to get this working:

kotlin
fun Transaction.tryConsumeRateLimit(
userId: Long,
endpoint: String,
maxRequests: Int,
windowSeconds: Int
): Boolean {
val bucketKey = "$userId:$endpoint".hashCode().toLong()

// Acquire advisory lock for this bucket — prevents race conditions
val lockAcquired = exec(
    "SELECT pg_try_advisory_xact_lock(1, $bucketKey)"
) { rs -> rs.next() && rs.getBoolean(1) } ?: false

if (!lockAcquired) return false // Contention — treat as rate limited

val cutoff = Instant.now().minusSeconds(windowSeconds.toLong())

// Clean old entries and count within window
RateLimitEntries.deleteWhere {
    (RateLimitEntries.bucketKey eq bucketKey) and
    (RateLimitEntries.timestamp less cutoff)
}

val currentCount = RateLimitEntries
    .select { RateLimitEntries.bucketKey eq bucketKey }
    .count()

if (currentCount >= maxRequests) return false

RateLimitEntries.insert {
    it[this.bucketKey] = bucketKey
    it[this.timestamp] = Instant.now()
}
return true

}

The function acquires a lock per bucket, cleans expired entries, checks the count, and inserts a new entry — all inside a single transaction.

## Step 4: Isolate Your Connection Pool

Run rate-limit checks through a small dedicated pool in **session mode** (2–4 connections) while keeping your main pool in transaction mode. This is the simplest path to reliable advisory lock behavior with PgBouncer.

## Gotchas

**PgBouncer in transaction mode with prepared statements.** The docs do not mention this, but `pg_try_advisory_xact_lock` is transaction-scoped, which *should* work in transaction mode. However, prepared statements can cause subtle bugs — PgBouncer may route a `DEALLOCATE` to a different backend connection than the one holding your lock context. Disable server-side prepared statements for rate-limit queries, or use `DISCARD ALL` at transaction boundaries.

| PgBouncer Mode | Advisory Lock Support | Prepared Statements |
|---|---|---|
| Session mode | Full support | Full support |
| Transaction mode | Works with caveats | Disable or use protocol-level |
| Statement mode | **Broken, do not use** | Not supported |

Here is the gotcha that will save you hours: **statement mode is completely broken for advisory locks.** Don't even try it.

**Know your ceiling.** I benchmarked this pattern against Redis `EVALSHA` with a sliding-window Lua script on equivalent hardware:

| Metric | PostgreSQL Advisory | Redis EVALSHA |
|---|---|---|
| p50 latency | 0.4ms | 0.1ms |
| p99 latency | 2.1ms | 0.3ms |
| Max throughput/node | ~15k req/s | ~80k req/s |
| Additional infra cost | $0 | ~$200/mo |
| Failure domains | 0 added | +1 |

Past ~15k req/s per node, lock contention on hot buckets and connection pool pressure degrade tail latencies fast.

## When to Migrate to Redis

Monitor `pg_stat_activity` and p99 latency. When rate-limit queries become a measurable percentage of total database load, or your p99 crosses your SLA threshold, it's time. By that point you probably need Redis for caching or pub/sub anyway, and adding rate limiting on top costs nothing extra.

## Conclusion

Start with PostgreSQL advisory locks if rate limiting is your only Redis use case. You drop a failure domain and save real money during the stage when operational simplicity matters most. Isolate your rate-limit pool to 2–4 session-mode connections, set a clear migration trigger at 15k req/s, and move to Redis when — not before — you actually need it.

For most mobile API gateways serving tens of thousands of DAUs at the startup stage, you are comfortably within the PostgreSQL envelope. Ship it, monitor it, and promote complexity only when the numbers demand it.