Postgres Store

The Postgres store enables distributed rate limiting using your existing PostgreSQL database. Ideal for teams that already run Postgres and want to add rate limiting without introducing a new dependency like Redis.

Installation

Node requires pg as a peer dependency. Bun uses native SQL — no extra install needed.

npm install pg
# or: pnpm add pg
# or: yarn add pg

# No extra install needed
# Bun native SQL is built in. pg is only needed for the deprecated { pool } option.

Usage

import { hitlimit } from '@joint-ops/hitlimit'
import { postgresStore } from '@joint-ops/hitlimit/stores/postgres'
import { Pool } from 'pg'

const pool = new Pool({ connectionString: 'postgres://localhost:5432/mydb' })

app.use(hitlimit({
  limit: 100,
  window: '1m',
  store: postgresStore({ pool })
}))

import { hitlimit } from '@joint-ops/hitlimit-bun'
import { postgresStore } from '@joint-ops/hitlimit-bun/stores/postgres'

// Connection string — no extra dependencies
const limiter = hitlimit({
  limit: 100,
  window: '1m',
  store: postgresStore({ url: 'postgres://localhost:5432/mydb' })
})

Options

Connection Options

import { Pool } from 'pg'

const pool = new Pool({
  connectionString: 'postgres://user:password@hostname:5432/mydb'
})

const pool = new Pool({
  host: 'localhost',
  port: 5432,
  database: 'mydb',
  user: 'postgres',
  password: 'secret',
  max: 20  // max pool size
})

postgresStore({ pool })

postgresStore({ url: 'postgres://user:password@hostname:5432/mydb' })

import { SQL } from 'bun'

const sql = new SQL('postgres://user:password@hostname:5432/mydb')

postgresStore({ client: sql })

Full Configuration

postgresStore({
  pool,                           // Node: pg.Pool
  tablePrefix: 'api_limits',
  cleanupInterval: 300000,       // Clean every 5 minutes
  skipTableCreation: false
})

Named Prepared Statements

The Postgres store uses named prepared statements for all hot-path queries. PostgreSQL caches the query plan for named statements, avoiding repeated parsing and planning overhead on every rate limit check.

This provides two key benefits:

• Lower latency — query plans are cached and reused across calls
• True atomicity — ON CONFLICT ... DO UPDATE upserts prevent race conditions

For endpoints with ban detection enabled, the store handles increment, expiry, violation tracking, and ban threshold checking through dedicated prepared statements.

Characteristics

• Speed: Network-bound (latency depends on Postgres server location and network)
• Persistence: Full PostgreSQL durability (WAL, replication)
• Scalability: Shared across all server instances
• Atomic: Upsert queries ensure race-condition-free increments
• Dependencies: Node requires pg (node-postgres). Bun uses native SQL — no extra dependencies.

When to Use

• Teams already running PostgreSQL in production
• Multi-server/container deployments without Redis
• When you want distributed rate limiting with SQL-level observability
• Environments where adding Redis is not feasible or desired

Database Schema

The store automatically creates the following tables (prefix defaults to hitlimit):

CREATE TABLE IF NOT EXISTS hitlimit_hits (
  key TEXT PRIMARY KEY,
  count INTEGER NOT NULL DEFAULT 1,
  reset_at BIGINT NOT NULL
)

CREATE TABLE IF NOT EXISTS hitlimit_bans (
  key TEXT PRIMARY KEY,
  expires_at BIGINT NOT NULL
)

CREATE TABLE IF NOT EXISTS hitlimit_violations (
  key TEXT PRIMARY KEY,
  count INTEGER NOT NULL DEFAULT 1,
  reset_at BIGINT NOT NULL
)

Postgres vs Redis