PostgreSQL — When To Use clock_timestamp(), current_timestamp and Unique Timestamps
PostgreSQL offers multiple ways to get the current time of a database. These are similar in intent but differ in usage. It’s possible to generate a unique timestamp, but we’re not sure when you would use it.
The Opinionated TLDR
The Opinionated Architect’s TLDR:
- Use CLOCK_TIMESTAMP() when non-SQL standard functions are acceptable. Note that CURRENT_TIMESTAMP is about 1.28x faster. See Current Date/Time.
- Using CURRENT_TIMESTAMP when counting the approximate number of records changed in a transaction is required noting that a call to CURRENT_TIMESTAMP does not assure a unique timestamp.
- Favor using the timestamptz over the timestamp type. See “Don’t use timestamp (without timezone).”
- It’s possible to generate a unique timestamp, but most likely not necessary.
Introduction
CLOCK_TIMESTAMP() and CURRENT_TIMESTAMP differ as follows (see Current Date/Time for details):
- CLOCK_TIMESTAMP() — A non-SQL standard function that returns the start time of the current statement.
- CURRENT_TIMESTAMP — A SQL standard function that returns a value based on the start time of the current transaction.
We’ll consider when to use CLOCK_TIMESTAMP() and when to use CURRENT_TIMESTAMP. First, let’s see an example usage:
Example Usage
Running the following SQL DDL:
SELECT CLOCK_TIMESTAMP(), CURRENT_TIMESTAMP
FROM generate_series(1,10);Results in:
clock_timestamp | current_timestamp
-------------------------------------------------------------
2022-09-09 17:34:42.960276+00 | 2022-09-09 17:34:42.959754+00
2022-09-09 17:34:42.960297+00 | 2022-09-09 17:34:42.959754+00
2022-09-09 17:34:42.960298+00 | 2022-09-09 17:34:42.959754+00
2022-09-09 17:34:42.960300+00 | 2022-09-09 17:34:42.959754+00
2022-09-09 17:34:42.960301+00 | 2022-09-09 17:34:42.959754+00
2022-09-09 17:34:42.960301+00 | 2022-09-09 17:34:42.959754+00
2022-09-09 17:34:42.960302+00 | 2022-09-09 17:34:42.959754+00
2022-09-09 17:34:42.960302+00 | 2022-09-09 17:34:42.959754+00
2022-09-09 17:34:42.960303+00 | 2022-09-09 17:34:42.959754+00
2022-09-09 17:34:42.960304+00 | 2022-09-09 17:34:42.959754+00The timestamp value returned by CLOCK_TIMESTAMP is increasing ever so slightly. The CURRENT_TIMESTAMP value does not change.
Note that two values are not unique in the CLOCK_TIMESTAMP column!!! We CAN NOT rely on CLOCK_TIMESTAMP( ) being unique: especially on faster databases.
When to Use CLOCK_TIMESTAMP()
Use CLOCK_TIMESTAMP() when:
- You want granular time stamps. A granular timestamp is useful for features like ingesting, where you select all the records since the last ingest time.
- For columns like updated_at, created_at, modified_at, etc.
- You want to know the closest sql statement execution time.
NOTE: Don’t rely on CLOCK_TIMESTAMP() returning unique timestamp values.
When to Use CURRENT_TIMESTAMP
Use CURRENT_TIMESTAMP when:
- You’re interested in the approximate number of records changed during a given transaction. We say approximately because two transactions could potentially have the same timestamp. Note: Use a transaction_id column of type UUID if you need to get the exact number of records updated in a given transaction.
- Speed is of the utmost importance. CLOCK_TIMESTAMP() takes about 6% longer to call than CURRENT_TIMESTAMP.
NOTE: Don’t rely on CURRENT_TIMESTAMP returning unique timestamp values. Two different transactions can end up with the same CURRENT_TIMESTAMP.
CLOCK_TIMESTAMP() vs CURRENT_TIMESTAMP Execution Time
See benchmark gist for benchmarking code.
Running the benchmark:
DO $$
DECLARE d_exec_count int = 20;
DECLARE d_row_count int = 3e6;
BEGIN
CALL benchmark.execute(
'current_timestamp',
'clock_timestamp',
d_row_count,
FORMAT(
'SELECT CURRENT_TIMESTAMP
FROM generate_series(%s,%s) AS series;', 1, d_row_count
),
FORMAT(
'SELECT CLOCK_TIMESTAMP()
FROM generate_series(%s,%s) AS series;', 1, d_row_count
),
d_exec_count
);
END;
$$;Results of running the above code three separate times:
left - right | Times | left (micro-sec)| right (micro-sec)
--------------------------------------------------------------------
curr_ts - clock_ts | 1.28x | 0.1224 ± 0.0002 | 0.1562 ± 0.0006
curr_ts - clock_ts | 1.28x | 0.1225 ± 0.0003 | 0.1569 ± 0.0022
curr_ts - clock_ts | 1.28x | 0.1226 ± 0.0002 | 0.1564 ± 0.0010Note:
- Benchmarks Truncated (upper/lower 10th percentile).
- Bold/Italic items had the fastest execution time. If neither is bolded, then execution times are considered equivalent.
Interestingly, we also see a performance cost using CLOCK_TIMESTAMP() with CURRENT_TIMESTAMP being 1.28x faster; which makes sense as PostgreSQL probably needs to make a function call for each row (each sql statement).
Can We Get a Unique Timestamp
We can’t rely on CLOCK_TIMESTAMP() or CURRENT_TIMESTAMP to return a unique timestamp.
However, generating a unique timestamp that is accurate to one second is possible using a SEQUENCE in conjunction with CLOCK_TIMESTAMP().
CREATE SCHEMA IF NOT EXISTS perf;-- NOTE: There may be faster ways to make a timestamp.
CREATE OR REPLACE FUNCTION perf.unique_timestamp (
p_sequence_name varchar
) RETURNS timestamptz LANGUAGE plpgsql AS
$$
DECLARE d_ct timestamptz = CLOCK_TIMESTAMP();
BEGIN
RETURN make_timestamptz(
date_part('year', d_ct)::int,
date_part('month', d_ct)::int,
date_part('day', d_ct)::int,
date_part('hour', d_ct)::int,
date_part('minute', d_ct)::int,
FLOOR(date_part('seconds', d_ct))
+ MOD(nextval(p_sequence_name),1000*1000) /
(1000*1000::double precision)
);
END;
$$;
COMMENT ON FUNCTION perf.unique_timestamp IS
'Generate a unique timestamp leveraging a sequence.';
NOTE: This approach does not work if we insert more than a million records a second into a given table. We can also increase the resolution of the timestamp at the cost of inserts per second.
Used as follows:
CREATE SEQUENCE IF NOT EXISTS
perf.unique_at_seq AS int INCREMENT BY 1;CREATE TABLE IF NOT EXISTS perf.unique_at (
unique_at_id timestamptz NOT NULL PRIMARY KEY
DEFAULT perf.unique_timestamp('perf.unique_at_seq')
);
Testing it:
INSERT INTO perf.unique_at
SELECT perf.unique_timestamp('perf.unique_at_seq')
FROM generate_series(1,1000000);SELECT *
FROM perf.unique_at
LIMIT 10;
Unique Timestamp Benchmarking
See benchmark gist for benchmarking code.
Running the benchmark:
DO $$
DECLARE d_exec_count int = 20;
DECLARE d_row_count int = 3e6;
BEGIN
CALL benchmark.execute(
'current_timestamp',
'unique_timestamp',
d_row_count,
FORMAT(
'SELECT CURRENT_TIMESTAMP
FROM generate_series(%s,%s) AS series;', 1, d_row_count
),
FORMAT(
'SELECT perf.unique_timestamp(''perf.unique_at_seq'')
FROM generate_series(%s,%s) AS series;', 1, d_row_count
),
d_exec_count
); CALL benchmark.execute(
'clock_timestamp',
'unique_timestamp',
d_row_count,
FORMAT(
'SELECT CLOCK_TIMESTAMP()
FROM generate_series(%s,%s) AS series;', 1, d_row_count
),
FORMAT(
'SELECT perf.unique_timestamp(''perf.unique_at_seq'')
FROM generate_series(%s,%s) AS series;', 1, d_row_count
),
d_exec_count
);
END;
$$;
Results of running the above code three separate times:
left - right | Times | left (micro-sec)| right (micro-sec)
--------------------------------------------------------------------
curr_ts - uniqe_ts | 12.4x | 0.1212 ± 0.0003 | 1.5065 ± 0.0047
clock_ts - uniqe_ts | 9.64x | 0.1558 ± 0.0006 | 1.5018 ± 0.0028Note:
- Benchmarks Truncated (upper/lower 10th percentile).
- Bold/Italic items had the fastest execution time. If neither is bolded, then execution times are considered equivalent.
CLOCK_TIMESTAMP() is about 9.64x faster than perf.unique_timestamp.
When to Use a Unique Timestamp?
We’re not quite sure when to use a unique timestamp. Some thoughts are:
- You would like to save storage space for a log by using a timestamp as a primary key. However, you could use a UUID v1 which is both unique and has a timestamp that can be extracted (see extract timestamp from v1 UUID in PostgreSQL).
If you have any ideas on when to use a unique timestamp, please leave a comment.
Conclusion
We’ve gone over different ways in PostgreSQL to get the current timestamp and considered a way to generate a unique timestamp for a given table.
There are many articles on the differences between a CLOCK_TIMESTAMP() and CURRENT_TIMESTAMP. What we try to do with The Opinionated Architect is provide examples of when to use a particular feature of PostgreSQL. We hope we have done that with this article.
