PostgreSQL advisory locks beat Redis for rate limiting

Meta description: Replace Redis with PostgreSQL advisory locks for distributed rate limiting in your mobile API gateway. Benchmarks, Ktor gotchas, and architecture tradeoffs explained.

Tags: kotlin, backend, api, startup, architecture

TL;DR

You can implement sliding-window rate limiting using pg_try_advisory_xact_lock directly in PostgreSQL, cutting Redis out entirely. This works up to roughly 15k requests/second per node, saves around $200/month on lean infrastructure, and removes a failure domain from your stack. The tradeoffs are real but manageable, especially for startups running mobile API gateways that don’t yet need Redis for anything else.

The problem: Redis as a rate limiting tax

Most teams reach for Redis by default. Not because they need it, but because every tutorial says to. For a startup running a Ktor-based mobile API gateway backed by PostgreSQL, adding Redis means:

Another managed service ($50-200/month depending on provider)
Another connection pool to manage
Another failure domain to monitor
Another backup and recovery story to document

If your only Redis use case is rate limiting, you’re paying an operational tax for a problem PostgreSQL already solves.

How advisory locks work for rate limiting

PostgreSQL advisory locks are application-level cooperative locks that exist 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.

Lock namespace design

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`

The two-argument form pg_try_advisory_xact_lock(classid, objid) is cleaner. Use classid for the bucket type and objid for the entity hash.

Ktor/Exposed implementation

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 PgBouncer gotcha

This is where teams hit their first wall. PgBouncer in transaction mode does not support advisory locks reliably in all configurations.

The issue: pg_try_advisory_xact_lock is transaction-scoped, which should work in transaction mode. But prepared statements can cause subtle bugs. PgBouncer may route a DEALLOCATE to a different backend connection than the one holding your lock context.

The fix

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

For Ktor with Exposed, disable server-side prepared statements for rate-limit queries, or use DISCARD ALL at transaction boundaries. The simpler approach: run rate-limit checks through a small dedicated pool in session mode (2-4 connections) while keeping your main pool in transaction mode.

Where this breaks down

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

Below 15k requests/second per node, PostgreSQL holds up fine. The p50 stays sub-millisecond. Past that threshold, lock contention on hot buckets and connection pool pressure start degrading tail latencies fast.

For most mobile API gateways at the startup stage, serving tens of thousands of DAUs, you’re comfortably within the PostgreSQL envelope.

When to move to Redis

Once your rate-limit queries start showing up in pg_stat_activity as a measurable percentage of total database load, or your p99 latencies cross 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.

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 connection pool from your main PgBouncer pool. Two to four dedicated session-mode connections prevent the prepared-statement issues that cause silent lock failures in transaction mode.

Set a clear migration trigger. Monitor pg_stat_activity and p99 latency. When you cross 15k req/s per node or Redis becomes necessary for other features, migrate the rate limiter as part of that rollout. Don’t add Redis just because you might need it later.