Mastering ID Management in Vertica: Sequences, Identity, Timestamps & Performance

Overview

Managing ID values in Vertica is critical for many real-world workloads—whether you're dealing with event logs, transactional data, or distributed ingestion pipelines. Choosing between uniqueness, gapless, order, or performance in ID generation is not a one-size-fits-all decision. This blog explores several Vertica techniques for generating ID values and guides you on choosing the best method for your specific requirements.

 To help you choose the right method before diving into the detailed examples, here’s a precise summary of the tested ID generation techniques, including their behavior and trade-offs:

               ⚠️ (Requires staging)

Understanding Sequence Caching and IDENTITY Behavior in Vertica

Vertica offers two primary methods to auto-generate ID values: IDENTITY columns and named SEQUENCE objects. Both rely on a caching mechanism designed to maximize performance in distributed environments, especially during large data loads such as INSERT-SELECT or COPY.

Sequence Caching Overview

By default, Vertica sets a cache size of 250,000 sequence values per node per session. In multi-node clusters:

• The initiator node of a query allocates and distributes cache blocks to participating nodes.
• This design reduces catalog locking and improves concurrency.
• However, unused cached values on any node can result in gaps in ID sequences.

To reduce contention, Vertica provides a config setting:
ClusterSequenceCacheMode = 1 (default) - only the initiator locks the catalog.
Setting this to 0 forces every node to lock the catalog individually - hurting performance and increasing the chance of delays!

IDENTITY Columns

The IDENTITY column is a table-embedded auto-increment field that behaves like a named sequence internally.
It supports:

• START, INCREMENT, and CACHE configuration
• Fast inserts with minimal overhead
• Internal naming via <table>_<column>_seq
• One IDENTITY column per table

Because caching is enabled by default, even IDENTITY columns can experience gaps, especially in multi node cluster.

Reusable Test Driver for ID Generation Scenarios

The following initial bash shell function, insert_it(), is used to populate a test table mytbl with sample data and Observe how different ID generation strategies behave under various configurations. It is designed to be reused across multiple test scenarios to ensure consistent input while varying only the table definition and ID logic.

🔍 What it does:
       Inserts fixed myvalue values (100, 200, ..., 600) into the target table mytbl.
       Each pair of inserts is committed separately to simulate batch inserts.
       After all data is inserted, it selects and displays the entire table ordered by the id column to evaluate how the IDs were assigned. 

#!/bin/bash
insert_it()
{
vsql -f - <<-EOF
\o /dev/null
insert into mytbl (myvalue) values (100);
insert into mytbl (myvalue) values (200);
commit;
EOF
 
vsql -f - <<-EOF
\o /dev/null
insert into mytbl (myvalue) values (300);
insert into mytbl (myvalue) values (400);
commit;
EOF
 
vsql -f - <<-EOF
\o /dev/null
insert into mytbl (myvalue) values (500);
insert into mytbl (myvalue) values (600);
commit;
EOF
 
vsql -c "select * from mytbl order by 1;"
}

🧪 Test 01 – Evaluating IDENTITY Column Behavior with Default Caching

This test defines a table mytbl with an IDENTITY(1,1) column named id, which automatically generates sequential numeric values starting from 1 and incrementing by 1 for each inserted row. The goal is to observe how Vertica assigns ID values under concurrent insert operations when default caching behavior is in place.
When you use an IDENTITY column, Vertica automatically generates and manages a backing sequence. By default, this sequence uses a cache size of 250,000 values per node, unless explicitly overridden. In a multi-node cluster, this caching mechanism allows each node to reserve its own block of ID values. This improves insert performance by reducing catalog lock contention - but it can also lead to gaps in the ID column values.
In this test, the insert_it function inserts three batches of two rows each. The inserts simulate multiple sessions or execution steps where different nodes may participate, and you'll see how the cached ID ranges come into play - even though only a few rows are inserted.

echo "TEST 01:________________________________________________________________________"
vsql -f - <<-EOF
drop table if exists mytbl cascade;
create table mytbl( id IDENTITY(1,1), myvalue int);
EOF
insert_it

🖨️ Output:

TEST 01:________________________________________________________________________
   id    | myvalue
---------+---------
       1 |     100
       2 |     200
 1250001 |     300
 1250002 |     400
 2500001 |     500
 2500002 |     600
(6 rows)

🧪 Test 02 – Named SEQUENCE with High Cache Size for Performance

In this test, we define a named sequence (my_seq) and use it as the default value for the id column in the mytbl table. The sequence is created with a large cache size of 10 million, which has a profound impact on both performance and sequence behavior. The following CREATE SEQUENCE my_seq setup means:

• The sequence starts at 1 and increments by 1.
• A huge cache of 10 million values is reserved and preallocated across nodes.
• Gaps can occur when nodes don’t consume all of their cached IDs

⚙️ Why this matters
This large cache size eliminates catalog locking, making it ideal for massive insert workloads or highly parallel COPY/INSERT-SELECT operations. However, the side effect is visible gaps in the sequence values. 

echo "TEST 02:________________________________________________________________________"
vsql -f - <<-EOF
drop table if exists mytbl cascade;
DROP SEQUENCE IF EXISTS my_seq;
 
CREATE SEQUENCE my_seq
    INCREMENT 1
    MINVALUE 1
    NO MAXVALUE
    START 1
    CACHE 10000000
    NO CYCLE;
 
create table mytbl( id INTEGER DEFAULT my_seq.NEXTVAL, myvalue int);
EOF
insert_it

🖨️ Output:

TEST 02:________________________________________________________________________
    id     | myvalue
-----------+---------
         1 |     100
         2 |     200
  50000001 |     300
  50000002 |     400
 100000001 |     500
 100000002 |     600
(6 rows) 

🧪 Test 03 – Timestamp-Based ID Using Julian Day + Time Components

This test introduces a time-based approach to ID generation by defining the id column as a computed value derived from the current timestamp, broken down into granular components to simulate a millisecond-level number.

The logic combines:

• JULIAN_DAY(mnow) – ensures date-level uniqueness
• HOUR, MINUTE, SECOND, MICROSECOND – contributes time precision

The resulting value is intended to represent a pseudo-unique and monotonically increasing numeric ID, even across days.

🔍 Behavior & Limitations

• This option serves demonstration purposes only, given that it utilizes multiple resource-intensive operations (5 functions per row insertion).
• The IDs are time-based and increment over time, but uniqueness is not guaranteed during high-speed or parallel inserts. If two rows are inserted within the same microsecond, they may receive identical ID values.
• No caching or locking is involved but the time functions involved may consume higher system resources than row_number() function calls.

• Good for event logs, audit trails, or time-stamped records where approximate ordering is more important than absolute uniqueness.
• Not recommended for use as a primary key unless combined with additional uniqueness logic.

While they generally increase with each insert, occasional duplicate IDs are possible during closely timed inserts. This tradeoff gives a readable, ordered, and scalable ID mechanism—without using Vertica’s internal sequence system.

echo "TEST 03:________________________________________________________________________"
vsql -f - <<-EOF
drop table if exists mytbl cascade;
create table mytbl( id INTEGER DEFAULT (
WITH mytime AS (
  SELECT CLOCK_TIMESTAMP() AS mnow
)
SELECT
  JULIAN_DAY(mnow)         * 10000000000000 +
  HOUR(mnow)               * 100000000000   +
  MINUTE(mnow)             * 1000000000     +
  SECOND(mnow)             * 10000000       +
  MICROSECOND(mnow)        AS unique_number
FROM mytime) , myvalue int);
EOF
insert_it

🖨️ Output:

TEST 03:________________________________________________________________________
         id          | myvalue
---------------------+---------
 6161727561350690130 |     100
 6161727561350709241 |     200
 6161727561350736715 |     300
 6161727561350756070 |     400
 6161727561350789907 |     500
 6161727561350809514 |     600
(6 rows) 

🧪 Test 04 – Timestamp-Based ID Using TO_CHAR(CLOCK_TIMESTAMP()) Formatting

This test explores another timestamp-driven strategy to generate ID values, but with a more human-readable format. The id column is assigned a default value using the TO_CHAR() function to format the current timestamp. 
TO_CHAR(CLOCK_TIMESTAMP(), 'YYMMDDHH24MISSUS')::INT

This format expands into a compact integer representing:    YYMMDD → date      and     HH24MISSUS → hour, minute, second, microsecond
For example, a timestamp like June 20, 2025, 16:35:06.462181 might produce: 250620163506462181

🔍 Behavior & Limitations:

• The resulting IDs are easily interpretable for logging and debugging.
• CLOCK_TIMESTAMP() returns a TIMESTAMP WITH TIMEZONE value representing the current system clock time. It retrieves the date and time from the server's operating system, and the value changes with each call.

In contrast, other functions like now() or CURRENT_TIMESTAMP() return the same value when called multiple times, representing the transaction's start time. The use of CLOCK_TIMESTAMP() ensures that the current timestamp is captured each time it's called, even when used repeatedly within the same session. However, uniqueness is not guaranteed. If multiple rows are inserted in the same microsecond, they may receive the same ID value. Unlike sequences, there's no coordination or caching.

echo "TEST 04:________________________________________________________________________"
vsql -f - <<-EOF
drop table if exists mytbl cascade;
create table mytbl( id INT DEFAULT TO_CHAR(CLOCK_TIMESTAMP(), 'YYMMDDHH24MISSUS')::INT, myvalue int) order by id;
EOF
insert_it

🖨️ Output:

TEST 04:________________________________________________________________________
         id         | myvalue
--------------------+---------
 250620163506462181 |     100
 250620163506476503 |     200
 250620163506500562 |     300
 250620163506517189 |     400
 250620163506541566 |     500
 250620163506556300 |     600
(6 rows)

🧪 Test 05 – Using ROW_NUMBER() Over a Temporary Staging Table for Gapless, Custom ID Assignment

This test demonstrates a practical technique to generate continuous, gap-free IDs by using a local temporary table and calculating IDs at insert time with the ROW_NUMBER() window function. Unlike built-in SEQUENCE or IDENTITY methods, this approach puts you in full control of how IDs are assigned. Here’s what happens in this two-part process:
Part 1:
           - A permanent table mytbl is created with an id column and a myvalue column.
           - A local temporary table my_temp_t is created for staging input values.
           - Data is loaded into the temp table using COPY.
           - An INSERT INTO selects data from the staging table and calculates new IDs as: 
             row_number() OVER () + (SELECT COALESCE(MAX(id), 0) FROM mytbl)
             This ensures the new IDs start one after the current max in mytbl.

Part 2:
           - The process is repeated for a second batch of data.
           - Because the logic dynamically looks up the current MAX(id), it continues counting from where the last batch left off.

 🔍 Key Advantages:

• Absolutely no gaps in the ID column, even if rows are rolled back or skipped.
• No dependency on SEQUENCE or Vertica’s caching mechanism.
• Works great for controlled loads or ETL pipelines.
• Easy to maintain logical or sort order with an ORDER BY clause in the final INSERT.
• This method is ideal when precision in ID continuity is required, such as financial records, audit logs, or transactional imports where skipping numbers is unacceptable.

⚠️ Limitations:

• Slight performance cost vs direct INSERT/COPY, since data is staged then reinserted.
• Not ideal for high-concurrency inserts, unless access is synchronized.
• You must manage ID calculation manually.

echo "TEST 05:________________________________________________________________________"
vsql -f - <<-EOF
drop table if exists mytbl cascade;
create table mytbl( id INT DEFAULT 0, myvalue int) order by id;
create local temporary table my_temp_t(id INT DEFAULT 0, myvalue int) on commit preserve rows ksafe 0;
COPY my_temp_t(myvalue) FROM STDIN DELIMITER ',' ABORT ON ERROR;
100,
200,
300,
\.

INSERT INTO mytbl select row_number() over() + (select COALESCE(MAX(id), 0) from mytbl) as id, myvalue from my_temp_t order by myvalue;
COMMIT;
EOF
 
vsql -f - <<-EOF
create local temporary table my_temp_t(id INT DEFAULT 0, myvalue int) on commit preserve rows ksafe 0;
COPY my_temp_t(myvalue) FROM STDIN DELIMITER ',' ABORT ON ERROR;
400,
500,
600,
\.
 
INSERT INTO mytbl select row_number() over() + (select COALESCE(MAX(id), 0) from mytbl) as id, myvalue from my_temp_t order by myvalue;
commit;
select * from mytbl order by 1;
EOF

🖨️ Output:

 id | myvalue
----+---------
  1 |     200
  2 |     300
  3 |     100
  4 |     600
  5 |     400
  6 |     500
(6 rows)

🧪 Test 06 – Assigning Timestamp-Based IDs During COPY Load Using AS Clause

In this test, we explore how Vertica’s COPY statement can be used to dynamically assign values during the data load, instead of relying on default column expressions or post-processing. The id column is populated on-the-fly using a formatted timestamp expression in the COPY command:
id as TO_CHAR(CLOCK_TIMESTAMP(), 'YYMMDDHH24MISSUS')::INT
Here, CLOCK_TIMESTAMP() is evaluated per row at load time, generating a numeric timestamp-like ID such as: 250620163506888642
The ido filler column is used to ignore the first field in the incoming data (a common technique to satisfy column order during custom mapping).

🔍 Behavior & Benefits:

• No need to define a default value on the table schema.
• You can override or derive values dynamically from the load source or system functions.
• Useful for real-time ingest pipelines, where you want to tag rows with load time metadata.
• This test effectively shows the flexibility and power of the COPY ... AS syntax in Vertica but also highlights the risks of using millisecond-resolution timestamps as identifiers without additional uniqueness logic.

⚠️ Limitations:

• No uniqueness guarantee: since CLOCK_TIMESTAMP() is evaluated per row, rows loaded in the same millisecond might get identical IDs.
• Especially during fast COPY operations, collisions are likely unless inserts are throttled, or additional entropy is added (e.g. a random suffix or sequence).

echo "TEST 06:________________________________________________________________________"
vsql -f - <<-EOF
drop table if exists mytbl cascade;
create table mytbl( id INT DEFAULT 0, myvalue int) order by id;
COPY mytbl ( ido filler int, id as TO_CHAR(CLOCK_TIMESTAMP(), 'YYMMDDHH24MISSUS')::INT, myvalue)
FROM STDIN DELIMITER ',' ABORT ON ERROR;;
0,100,
0,200,
0,300,
\.
 
COPY mytbl ( ido filler int, id as TO_CHAR(CLOCK_TIMESTAMP(), 'YYMMDDHH24MISSUS')::INT, myvalue)
FROM STDIN DELIMITER ',' ABORT ON ERROR;
0,400,
0,500,
0,600,
\.
 
select * from mytbl order by 1;
EOF

🖨️ Output:

         id         | myvalue
--------------------+---------
 250620163506888642 |     100
 250620163506888644 |     200
 250620163506888644 |     300
 250620163506911721 |     400
 250620163506911726 |     500
 250620163506911726 |     600
(6 rows)

 🧪 Test 07 – Generating ID Values on Load When Input Data Has No IDs

This test builds on the previous example by demonstrating how Vertica's COPY statement can be used to generate missing ID values during data load. The input only includes a single column (myvalue), and the id column is dynamically derived during the COPY using:
id as TO_CHAR(CLOCK_TIMESTAMP(), 'YYMMDDHH24MISSUS')::INT
This means that the ID column isn’t present in the source data at all—it’s constructed entirely at load time using the current timestamp formatted as:
                      YYMMDDHH24MISSUS (e.g., 250620163507016516)
Each inserted row gets a numeric representation of the time it was loaded.

🔍 Key Highlights:

• No need to include IDs in your data files—Vertica fills them in automatically.
• Good for logging, ingestion tracking, or when assigning approximate load-time IDs.
• This avoids any pre-processing on the source data to add synthetic keys.

⚠️ Limitations:

• No guarantee of uniqueness: When multiple rows are inserted quickly, several rows can receive identical IDs if evaluated in the same microsecond.

echo "TEST 07:________________________________________________________________________"
vsql -ef - <<-EOF
drop table if exists mytbl cascade;
create table mytbl( id INT DEFAULT 0, myvalue int) order by id;

COPY mytbl ( id as TO_CHAR(CLOCK_TIMESTAMP(), 'YYMMDDHH24MISSUS')::INT, myvalue)
FROM STDIN DELIMITER ',' ABORT ON ERROR;;
100,
200,
300,
400,
500,
600,
\.
 
select * from mytbl order by 1;
EOF

🖨️ Output:

         id         | myvalue
--------------------+---------
 250620163507016511 |     100
 250620163507016515 |     200
 250620163507016516 |     400
 250620163507016516 |     600
 250620163507016516 |     300
 250620163507016516 |     500
(6 rows)

🧪 Test 08 – Forcing Gapless IDs by Disabling Sequence Caching (CACHE 1)

This test demonstrates how to eliminate gaps in sequence-generated IDs by setting the sequence’s cache size to 1.   This forces Vertica to allocate and increment sequence values one at a time, which ensures every issued ID is used in order, with no gaps—even if a node doesn't use its full cache.  With CACHE 1, every call to my_seq.NEXTVAL results in a synchronous catalog access and lock, which guarantees no ID gaps, even in multi-node in parallel workloads.  
However, the cost is high: catalog locks are acquired for every single row, which degrades performance - especially in large-scale inserts.
In this test:

• The table mytbl is created with a default id value of 0.
• Three concurrent COPY processes insert different values into the table while using my_seq.NEXTVAL to populate id.
• Because the sequence cache is 1, all nodes must individually request and lock the catalog for every row they insert.

🔍 Recommendation:

This approach guarantees ID continuity, but at the cost of scalability.
In general, avoid using CACHE 1 unless you're working in a very controlled, low-volume setting. If you need gapless IDs with better performance, consider ROW_NUMBER() with staging (Test 05) as a safer and more efficient alternative. 

echo "TEST 08:________________________________________________________________________"
vsql -ef - <<-EOF
drop table if exists mytbl cascade;
DROP SEQUENCE IF EXISTS my_seq;
 
CREATE SEQUENCE my_seq
    INCREMENT 1
    MINVALUE 1
    NO MAXVALUE
    START 1
    CACHE 1
    NO CYCLE;
 
create table mytbl( id INT DEFAULT 0, myvalue int) order by id;
EOF
 
cat <<EOF | vsql -c "COPY mytbl ( id as my_seq.NEXTVAL, myvalue) FROM STDIN DELIMITER ',' ABORT ON ERROR;" &
100,
200,
300,
400,
500,
EOF
 
cat <<EOF | vsql -c "COPY mytbl ( id as my_seq.NEXTVAL, myvalue) FROM STDIN DELIMITER ',' ABORT ON ERROR;" &
110,
220,
330,
440,
550,
EOF
 
cat <<EOF | vsql -c "COPY mytbl ( id as my_seq.NEXTVAL, myvalue) FROM STDIN DELIMITER ',' ABORT ON ERROR;" &
111,
222,
333,
444,
555,
EOF
 
wait
vsql -c "select * from mytbl order by 1;"

🖨️ Output:

 id | myvalue
----+---------
  1 |     110
  2 |     100
  3 |     111
  4 |     220
  5 |     200
  6 |     222
  7 |     330
  8 |     300
  9 |     333
 10 |     440
 11 |     400
 12 |     444
 13 |     550
 14 |     500
 15 |     555
(15 rows)