PostgreSQL pg_sleep Function

The pg_sleep() function in PostgreSQL pauses execution for a specified number of seconds. It's useful for testing, simulating delays, rate limiting, and debugging time-sensitive operations.

Syntax

-- Sleep for specified seconds (accepts decimals)
pg_sleep(seconds)

-- Sleep until specific timestamp
pg_sleep_until(timestamp)

-- Sleep for duration (can be interrupted by signals)
pg_sleep_for(interval)

Official Documentation

Example usage

-- Pause for 5 seconds
SELECT pg_sleep(5);

-- Pause for 1.5 seconds
SELECT pg_sleep(1.5);

-- Pause for milliseconds
SELECT pg_sleep(0.1);  -- 100ms

-- Use in a script with multiple statements
BEGIN;
    INSERT INTO logs (message) VALUES ('Starting process');
    SELECT pg_sleep(2);
    INSERT INTO logs (message) VALUES ('Process complete');
COMMIT;

-- Simulate slow query for testing
SELECT
    id,
    name,
    pg_sleep(0.01) AS delay  -- 10ms delay per row
FROM products;

-- Sleep until specific time
SELECT pg_sleep_until('2025-12-26 15:00:00');

-- Sleep for interval
SELECT pg_sleep_for('5 minutes');
SELECT pg_sleep_for('1 hour');
SELECT pg_sleep_for('30 seconds');

-- Rate limiting in loops (PL/pgSQL)
DO $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN SELECT * FROM large_table LOOP
        -- Process record
        PERFORM pg_sleep(0.1);  -- 100ms delay between iterations
    END LOOP;
END $$;

-- Testing timeout behavior
BEGIN;
    SET statement_timeout = '3s';
    SELECT pg_sleep(5);  -- Will be cancelled after 3 seconds
COMMIT;

-- Debugging race conditions
CREATE OR REPLACE FUNCTION test_race_condition() RETURNS VOID AS $$
BEGIN
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    PERFORM pg_sleep(5);  -- Simulate slow operation
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
END;
$$ LANGUAGE plpgsql;

-- Staggered batch processing
DO $$
BEGIN
    FOR i IN 1..10 LOOP
        -- Process batch
        DELETE FROM queue WHERE processed = false LIMIT 100;
        PERFORM pg_sleep(1);  -- Wait 1 second between batches
    END LOOP;
END $$;

-- Measuring query performance
DO $$
DECLARE
    start_time TIMESTAMP;
    end_time TIMESTAMP;
BEGIN
    start_time := clock_timestamp();
    PERFORM pg_sleep(2.5);
    end_time := clock_timestamp();
    RAISE NOTICE 'Elapsed time: %', end_time - start_time;
END $$;

-- Simulating external API delay
CREATE OR REPLACE FUNCTION simulate_api_call() RETURNS TEXT AS $$
BEGIN
    PERFORM pg_sleep(RANDOM() * 2);  -- 0-2 second random delay
    RETURN 'API response';
END;
$$ LANGUAGE plpgsql;

-- Connection throttling
SELECT
    pg_sleep(0.5),
    *
FROM generate_series(1, 10);

-- Testing trigger timing
CREATE OR REPLACE FUNCTION slow_trigger() RETURNS TRIGGER AS $$
BEGIN
    PERFORM pg_sleep(1);  -- Simulate slow trigger
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Retry with backoff
DO $$
DECLARE
    retry_count INT := 0;
    max_retries INT := 5;
BEGIN
    LOOP
        BEGIN
            -- Attempt operation
            PERFORM risky_operation();
            EXIT;  -- Success, exit loop
        EXCEPTION WHEN OTHERS THEN
            retry_count := retry_count + 1;
            IF retry_count >= max_retries THEN
                RAISE;  -- Re-raise after max retries
            END IF;
            PERFORM pg_sleep(POWER(2, retry_count));  -- Exponential backoff
        END;
    END LOOP;
END $$;

Common issues

1. Blocking: pg_sleep blocks the connection. Other queries on same connection must wait.
2. Transaction locks: Sleeping inside transaction holds locks, potentially blocking other sessions.
3. Not for production delays: Don't use for production rate limiting; use proper queuing systems.
4. No cancellation: pg_sleep can be cancelled with query cancellation, but timing isn't precise.

Best practices

1. Avoid using pg_sleep in production queries as it wastes connection resources.
2. For rate limiting, consider application-level throttling or pg_background extension.
3. Use pg_sleep_for for clearer intent when sleeping for intervals.
4. Keep sleeps outside transactions when possible to avoid holding locks.

Frequently Asked Questions

Q: Can I sleep for milliseconds?
A: Yes, use decimal seconds: pg_sleep(0.001) sleeps for 1 millisecond. The precision depends on OS clock resolution.

Q: Does pg_sleep block other connections?
A: No, it only blocks the current connection. Other connections continue normally. However, if sleeping inside a transaction, held locks may block other connections.

Q: How do I cancel a pg_sleep?
A: Use query cancellation: SELECT pg_cancel_backend(pid) or CTRL+C in psql. The sleep will be interrupted immediately.

Q: What's the difference between pg_sleep and pg_sleep_for?
A: pg_sleep(seconds) takes a number of seconds. pg_sleep_for(interval) takes an INTERVAL for clearer intent: pg_sleep_for('5 minutes') is more readable than pg_sleep(300).

Q: Is pg_sleep affected by statement_timeout?
A: Yes, if statement_timeout is set and pg_sleep exceeds it, the query will be cancelled and an error raised.