PostgreSQL pg_sleep Function: Syntax, Examples, and Locking Caveats

pg_sleep() in PostgreSQL pauses the current session for a specified duration. It blocks only the calling connection, not the server. It is used for testing timeouts, simulating slow operations, and pacing batch jobs. PostgreSQL also provides pg_sleep_for(interval) and pg_sleep_until(timestamptz) variants for clearer intent. The effective sleep resolution is platform-specific - typically 0.01 seconds - and the sleep is always at least as long as requested.

Syntax

pg_sleep(seconds double precision)             -> void
pg_sleep_for(duration interval)                -> void
pg_sleep_until(wake_time timestamp with time zone) -> void

Official reference: PostgreSQL delaying execution.

All three return void. They participate in normal query execution, so they honor statement_timeout and respond to pg_cancel_backend(). The sleep is broken by query cancellation but not by signals to the server process.

Parameters

Examples

-- 1. Basic delay
SELECT pg_sleep(2);              -- 2 seconds
SELECT pg_sleep(0.25);            -- 250 ms

-- 2. Interval form is clearer for long sleeps
SELECT pg_sleep_for('5 minutes');
SELECT pg_sleep_for('1 hour 30 minutes');

-- 3. Absolute wake time
SELECT pg_sleep_until(now() + interval '10 seconds');
SELECT pg_sleep_until('2026-12-31 23:59:59+00');

-- 4. Test statement_timeout behavior
SET statement_timeout = '3s';
SELECT pg_sleep(10);              -- cancelled at 3s with 57014 query_canceled

-- 5. Pace a long-running maintenance loop
DO $$
BEGIN
    FOR i IN 1..100 LOOP
        DELETE FROM job_queue
        WHERE id IN (SELECT id FROM job_queue
                     WHERE done = false LIMIT 1000);
        COMMIT;
        PERFORM pg_sleep(0.5);   -- 500 ms breathing room between batches
    END LOOP;
END $$;

-- 6. Reproduce a race condition for testing
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
SELECT pg_sleep(2);               -- gives other session time to read
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- 7. Exponential backoff inside a retry block
DO $$
DECLARE attempt int := 0;
BEGIN
    LOOP
        BEGIN
            PERFORM external_call();
            EXIT;
        EXCEPTION WHEN OTHERS THEN
            attempt := attempt + 1;
            IF attempt >= 5 THEN RAISE; END IF;
            PERFORM pg_sleep(power(2, attempt));   -- 2s, 4s, 8s, 16s
        END;
    END LOOP;
END $$;

Common Issues and Gotchas

1. Sleeping inside a transaction holds locks. Any row locks, advisory locks, or table locks acquired earlier in the transaction remain held for the duration of the sleep. This blocks other sessions trying to acquire conflicting locks. Move pg_sleep outside transactions when possible.
2. pg_sleep blocks the connection, not the cluster. Other sessions are unaffected unless they need a lock the sleeping session holds. The CPU cost during the sleep is near zero.
3. Statement timeout fires during the sleep. If statement_timeout is shorter than the sleep, the session receives SQLSTATE 57014 query_canceled at the timeout boundary. Tests for timeout handling use this combination.
4. Resolution is platform-dependent. PostgreSQL's documentation cites 0.01 seconds as a typical floor. pg_sleep(0.0001) may actually sleep for ~10 ms. For precise timing, do it in the application.
5. pg_sleep_until returns immediately for past times. If wake_time is already in the past, the function returns without sleeping. Use greatest(wake_time, now()) if you need a no-op safety check.
6. Cancellation is supported. SELECT pg_cancel_backend(pid) interrupts a pg_sleep and returns control with 57014. pg_terminate_backend ends the session entirely.
7. Not a substitute for proper scheduling. Using pg_sleep in long-running batch jobs ties up a connection. Prefer pg_cron, the OS scheduler, or an external worker for anything beyond ad hoc pacing.

Performance Notes

pg_sleep consumes minimal CPU during the sleep itself - the backend is parked in a nanosleep system call. The connection slot, transaction snapshot, and any locks held are kept alive, which is the real cost. On a connection pool with limited size, a few long sleeps can exhaust available slots.

For pacing background jobs that may run for hours, the right pattern is LIMIT + COMMIT + short pg_sleep, not a single long sleep. This releases the snapshot and locks between batches, letting autovacuum and other sessions make progress.

Frequently Asked Questions

Q: Does pg_sleep block other PostgreSQL connections?
A: No, pg_sleep only blocks the calling connection. Other sessions continue running. The exception is when the sleeping session holds locks that other sessions need - in that case, the lock waiters block until the sleep ends or the session is cancelled.

Q: How do I sleep for milliseconds in PostgreSQL?
A: Pass a fractional value: pg_sleep(0.05) sleeps for ~50 milliseconds. The effective resolution is platform-specific, typically 0.01 seconds, and the actual sleep may be slightly longer than requested due to OS scheduling.

Q: Is pg_sleep affected by statement_timeout?
A: Yes. pg_sleep is a normal SQL statement and is subject to statement_timeout. If the sleep is longer than the timeout, the session receives SQLSTATE 57014 query_canceled at the timeout boundary. This is the canonical pattern for testing timeout handling.

Q: What is the difference between pg_sleep and pg_sleep_for?
A: pg_sleep(seconds) takes a double precision number of seconds. pg_sleep_for(interval) takes an INTERVAL value, which is more readable for long durations: pg_sleep_for('30 minutes') versus pg_sleep(1800). Functionally they sleep for the same duration.

Q: How do I cancel a running pg_sleep?
A: From another session, run SELECT pg_cancel_backend(pid) where pid is the sleeping session's PID (visible in pg_stat_activity). The sleep returns with query_canceled. pg_terminate_backend(pid) is the heavier option and disconnects the session.

Q: Why does my long pg_sleep block other queries?
A: The sleeping session is holding a lock those queries need. Long sleeps inside transactions are the usual cause - the held row, table, or advisory lock blocks waiters until the sleep ends. Sleep outside the transaction, or split the work into smaller transactions with short pauses between commits.

Related Reading

• PostgreSQL Could Not Serialize Access: serialization failures that pg_sleep is often used to reproduce.
• PostgreSQL Connection Pooling with PgBouncer: pooler behavior when sessions sleep.
• PostgreSQL Age Function: related date/time function for measuring elapsed intervals.
• PostgreSQL CTE: structure batch jobs that may need pacing.
• Common PostgreSQL Errors: including 57014 query_canceled.
• PostgreSQL Connection String: connection-level settings that interact with long-running queries.