Use Unique Indexes on AO Tables

You can add a unique index to an Append-Optimized (AO) or AOCS table in SynxDB. With a unique index, SynxDB enforces uniqueness constraints when inserting data into an AO table, thereby ensuring data uniqueness. This also allows the optimizer to optimize specific queries, improving database query performance. However, this comes with some overhead for maintaining the unique index, especially during data insertion.

How it works

In scenarios where a unique index exists, when inserting data into an AO table, SynxDB inserts a placeholder into the BlockDirectory, which is an auxiliary index structure of the AO table. This blocks the insertion of the same key, thus implementing the unique index.

How to add a unique index to an AO table

There are two ways to add a unique index to an AO table. You can choose based on your business needs:

• Specify a unique key or constraint when creating the table:

  CREATE TABLE foo(i int UNIQUE) USING ao_row;
  CREATE TABLE bar(i int UNIQUE) USING ao_column;
  CREATE TABLE foo2(i int, CONSTRAINT iuniq UNIQUE(i)) USING ao_row;
  

• Add a unique index or constraint separately after creating the table:

  CREATE TABLE baz(i int) with (appendonly=true);
  CREATE UNIQUE INDEX on baz(i);
  
  CREATE TABLE foobar(i int) USING ao_row;
  ALTER TABLE foobar ADD CONSTRAINT unique_i UNIQUE (i);
  

Usage examples

1. Create an example AO table with a unique constraint.

   postgres=# CREATE TABLE foo(i int UNIQUE) USING ao_row;
   CREATE TABLE
   
   postgres=# \d foo
                  Table "public.foo"
   Column |  Type   | Collation | Nullable | Default
   --------+---------+-----------+----------+---------
   i      | integer |           |          |
   Compression Type: None
   Compression Level: 0
   Block Size: 32768
   Checksum: t
   Indexes:
      "foo_i_key" UNIQUE CONSTRAINT, btree (i)
   Distributed by: (i)
   

2. Inserting the same key value into the table will return a duplicate key error.

   postgres=# INSERT INTO foo VALUES(1);
   INSERT 0 1
   
   postgres=# INSERT INTO foo VALUES(1);
   ERROR:  duplicate key value violates unique constraint "foo_i_key"  (seg1 127.0.1.1:8003 pid=557)
   DETAIL:  Key (i)=(1) already exists.
   

Example of concurrent data insertion: At the READ COMMITTED transaction level, two transactions concurrently insert the same value into the same AO table. One succeeds, and the other is blocked.

The transaction that starts later does not fail immediately. Its INSERT waits until the earlier transaction commits or rolls back before returning a result. The error that session 2 reports in the following example therefore appears only after session 1 has finished.

1. If one transaction succeeds, the other insertion fails.

   -- Session 1.
   postgres=# BEGIN;
   BEGIN
   
   postgres=*# INSERT INTO foo VALUES(1);
   INSERT 0 1  -- Insertion successful
   
   postgres=*# COMMIT;
   COMMIT
   

   -- Session 2.
   postgres=# BEGIN;
   BEGIN
   
   postgres=*# INSERT INTO foo VALUES(1);
   ERROR:  duplicate key value violates unique constraint "foo_i_key"  (seg1 127.0.1.1:8003 pid=2726)
   DETAIL:  Key (i)=(1) already exists.  -- Insertion failed
   
   postgres=!# END;
   ROLLBACK
   

2. If one transaction fails, the other transaction will succeed.

   postgres=# BEGIN;
   BEGIN
   postgres=*# INSERT INTO foo VALUES(1);
   INSERT 0 1
   postgres=*# ROLLBACK;
   ROLLBACK
   

   postgres=# BEGIN;
   BEGIN
   postgres=*# INSERT INTO foo VALUES(1);
   INSERT 0 1
   postgres=*# COMMIT;
   COMMIT
   

Usage restrictions

Note the following restrictions when using a unique index on an AO table:

• A unique index or constraint must contain all the distribution key columns of the table. Creating an index that omits the distribution key fails:

  CREATE TABLE u1(a int, b int) USING ao_row DISTRIBUTED BY (a);
  CREATE UNIQUE INDEX ON u1(b);
  ERROR:  UNIQUE index must contain all columns in the table's distribution key
  DETAIL:  Distribution key column "a" is not included in the constraint.
  

  Including the distribution key in the index resolves it:

  CREATE UNIQUE INDEX ON u1(a, b);
  

• When a UNIQUE constraint and a DISTRIBUTED BY clause are both given at table creation, the distribution key must be a subset of the unique constraint columns:

  CREATE TABLE u2(a int, b int UNIQUE) USING ao_row DISTRIBUTED BY (a);
  ERROR:  UNIQUE constraint and DISTRIBUTED BY definitions are incompatible
  HINT:  When there is both a UNIQUE constraint and a DISTRIBUTED BY clause, the DISTRIBUTED BY clause must be a subset of the UNIQUE constraint.
  

• A randomly distributed table (DISTRIBUTED RANDOMLY) cannot have a unique index:

  CREATE TABLE u3(a int) USING ao_row DISTRIBUTED RANDOMLY;
  CREATE UNIQUE INDEX ON u3(a);
  ERROR:  UNIQUE and DISTRIBUTED RANDOMLY are incompatible
  

• Uniqueness is enforced on UPDATE as well. Changing the unique column of a row to an existing value raises the same error:

  UPDATE bar SET i = 2 WHERE i = 1;
  ERROR:  duplicate key value violates unique constraint "bar_i_key"
  DETAIL:  Key (i)=(2) already exists.