Sleeping with Postgres

Do you want to sleep with PostgreSQL or maybe just have your session sleep for a bit? Well, we have an answer for you, the pg_sleep() function. It’s a pretty simple function and does what you would expect and sleeps for a specified period of time.

SELECT pg_sleep(2);
+----------+
| pg_sleep |
+----------+
|          |
+----------+
(1 row)

Time: 2009.332 ms (00:02.009)

As you can see the SQL takes approximately 2 seconds to return. You can also you fractional seconds if needed. The pg_sleep() function also has a few variants including pg_sleep_for() and pg_sleep_until().

pg_sleep_for() is simply a function that utilizes a plain text timeframe. For example, if you wanted to sleep for 2 seconds like the example about you could execute something like this.

SELECT pg_sleep_for('2 seconds');
+--------------+
| pg_sleep_for |
+--------------+
|              |
+--------------+
(1 row)

Time: 2004.185 ms (00:02.004)

You can pass any string that works are an interval. For example, if you wanted to sleep for 1 minute this would work.

SELECT pg_sleep_for('1 minute');
+--------------+
| pg_sleep_for |
+--------------+
|              |
+--------------+
(1 row)

Time: 60061.970 ms (01:00.062)

pg_sleep_until() works by sleeping until a specific time. For example at the time of this post, I want PostgreSQL to sleep until 4:08PM today so this statement will accomplish just that.

SELECT pg_sleep_until('2020-01-17 16:08'); 
+----------------+
| pg_sleep_until |
+----------------+
|                |
+----------------+
(1 row)

Time: 51018.980 ms (00:51.019)

Keep in mind while you might have a good use case for sleeping in the database it’s generally not a good idea to sleep for too long. While your session is sleeping you are keeping a transaction open. Too many of these transactions or long-running transactions will upset your local DBA. Use the PostgreSQL sleep command sparingly.