Cross-shard INSERT¶

An INSERT statement that doesn't specify a sharding key, has multiple sharding keys, or targets an omnisharded table is a cross-shard statement and requires special handling, as described below.

How it works¶

Cross-shard INSERT statements fall into one of three categories, each one being handled differently:

Statement	Handling
INSERT into an omnisharded table	Sent concurrently to all shards.
INSERT a single tuple into a sharded table	Sent to one shard only, depending on the primary key generation strategy.
INSERT multiple tuples into a sharded table	Rewritten into separate statements and sent separately to the matching shards.

Applications using PgDog don't need to concern themselves with implementation details and can use the database normally. However, there are some trade-offs when using cross-shard queries which are documented below.

Omnisharded tables¶

For queries that target omnisharded tables, the statement is sent to all shards concurrently. This ensures that the data is identical on all shards, for example:

INSERT INTO cities (id, city_name, country_code, created_at)
VALUES ($1, $2, $3, now())

This is a common pattern for tables that don't have a sharding key, or tables that contain small amounts of data. The same row will be created on all shards and queries can then either fetch it directly or join it with other sharded and omnisharded tables.

Omnisharded consistency¶

Unless two-phase commit is enabled, inserts into omnisharded tables are not guaranteed to be atomic. It is possible for the statement to succeed on some of the shards and not others.

If you don't want to or can't enable two-phase commit on your database shards, consider sending cross-shard inserts inside a transaction or writing idempotent statements, for example:

BEGIN;
INSERT INTO cities (id, city_name, country_code, created_at)
VALUES ($1, $2, $3, now())
ON CONFLICT (city_name) DO NOTHING;
-- You will receive an ack or an error from all shards here.
COMMIT;

This gives you a much higher chance of writing rows on all shards, since you will know if your statement violated a constraint (e.g., unique index or NOT NULL check) before committing the transaction.

Two-phase commit

Enabling two-phase commit is highly recommended. It's been tested and works well in production.

Primary keys in omnisharded tables¶

While UUID primary keys are common and are easily generated by the application, it is still common to use BIGSERIAL (or SERIAL) columns to uniquely identify rows in databases.

These are typically powered by a sequence to ensure non-recurring values are automatically generated for each new row. However, sharded databases can't use sequences directly because they are not aware of other shards and will produce duplicate values across databases.

To work around this, PgDog provides a way to generate unique integers in the proxy using a distributed and shard-aware algorithm. Since the integer is generated inside PgDog before sending the query to Postgres, its value will be the same for all rows sent to each shard.

Manual primary key generation¶

To use unique IDs as primary keys (or in any other column) in omnisharded tables, you can call the pgdog.unique_id() function directly in the VALUES clause of an INSERT statement, for example:

INSERT INTO cities (id, city_name, country_code, created_at)
VALUES (pgdog.unique_id(), $1, $2, now());

The function is evaluated inside PgDog, replacing the value it generates directly into the query. This works for all queries, including prepared statements.

Each call to pgdog.unique_id() generates a unique value, so it's possible to use it multiple times inside the same query and get different numbers, for example:

QueryResult

SELECT
    pgdog.unique_id() AS one,
    pgdog.unique_id() AS two;

       one         |        two
-------------------+-------------------
 12014338348945408 | 12014338348945409
(1 row)

This function can be used with any table, not just omnisharded ones, or independently of tables altogether.

Automatic primary key generation¶

If you're using an ORM like ActiveRecord, Prisma, SQLAlchemy, etc., it's often not easy or possible to modify how it generates its INSERT statements. Thankfully, the SQL standard specifies a couple of ways for the client to make the database generate the primary key automatically, which PgDog can intercept and handle.

How it works¶

It's common for some ORMs to omit columns that the database is expected to generate values for. Since PgDog has knowledge of the database schema, it can detect this scenario and inject the call to pgdog.unique_id() automatically, for example:

Original queryRewritten query

INSERT INTO cities (city_name, country_code, created_at)
VALUES ($1, $2, now())

INSERT INTO cities (id, city_name, country_code, created_at)
VALUES ($3, $1, $2, now())

The value of parameter $3 is automatically set by PgDog to the value returned by the unique ID generator.

PgDog can also automatically inject the function call into INSERT queries, so this feature works with ORMs like ActiveRecord, Prisma, etc., out of the box.

Another way to write that query is to use the DEFAULT keyword, which explicitly tells the database to use the configured default for the column. PgDog can handle that scenario as well and will inject its generated primary key into the row.

Configuration¶

This primary key generation feature is still relatively new and disabled by default. To enable it, configure it in pgdog.toml:

pgdog.tomlHelm chart

[rewrite]
enabled = true
primary_key = "rewrite"

rewrite:
  enabled: true
  primaryKey: rewrite

Sharded tables¶

INSERT statements targeting sharded tables will commonly provide the sharding key. A notable exception to this rule is tables that shard on the primary key, which is often database-generated, e.g., using a sequence.

The simplest way to work around this is to use the pgdog.unique_id() function to create a unique identifier on the fly, for example:

INSERT INTO users (id, email, created_at)
VALUES (pgdog.unique_id(), $1, $2)
RETURNING *;

PgDog will inject a unique integer into the query and send it to the corresponding shard as a direct-to-shard statement.

Primary keys¶

For sharded tables that have BIGINT (or INTEGER) primary keys, you can rely on the unique ID generator, as you can with omnisharded tables.

However, since our algorithm produces large numbers, this may not always be suitable for all applications, especially those that pass IDs directly to a JavaScript/TypeScript frontend that can't handle large integers. For this reason, we created sharded sequences.

Sharded sequences¶

Installation required

For sharded sequences to work correctly, they have to be installed into the database first. Read more about this here.

Sharded sequences apply a hashing function to a PostgreSQL sequence, creating unique and monotonically increasing values with small gaps between them. They are useful for generating primary keys for sharded tables, since they create cluster-unique values and require no special handling or query rewriting inside PgDog.

For tables that use sharded sequences for primary key generation and use the primary key for sharding, PgDog sends the query to only one shard (using round-robin routing) and lets the database generate the sharding key automatically, for example:

INSERT INTO users (email, created_at) VALUES ($1, $2)
RETURNING id;

The id column will be generated by the database (and not PgDog), globally unique, and matched to the shard it's generated on, as guaranteed by the sharded sequence implementation.

Sharded tables only

Make sure to never use sharded sequences with omnisharded tables. They are not guaranteed to generate the same value on all shards, even with two-phase commit, and could cause primary key drift across shards.

Composite primary keys¶

Not currently supported

Composite primary keys are not currently supported for primary key generation inside PgDog.

Primary key injection only works for BIGINT primary key columns. Composite primary keys or other data types are not currently supported, but are on the roadmap.

Multiple tuples¶

In order to create multiple rows at once, the PostgreSQL query syntax supports sending multiple tuples in one statement. For example:

INSERT INTO users
    (id, email, created_at)
VALUES
    ($1, $2, $3),
    ($4, $5, $6);

In sharded databases, however, the individual tuples are likely to belong on different shards. To make this work, PgDog can automatically rewrite the statement and send each tuple to the right shard. Using the example above, that operation produces two single-tuple statements:

Statement 1Statement 2

INSERT INTO users
    (id, email, created_at)
VALUES
    ($1, $2, $3)

INSERT INTO users
    (id, email, created_at)
VALUES
    ($1, $2, $3)

This works for all queries, including prepared statements. PgDog will rewrite all Postgres protocol messages (e.g., Bind, Describe, etc.) without the application having to change its queries.

This feature is relatively new and is disabled by default. It can be enabled in pgdog.toml:

pgdog.tomlHelm chart

[rewrite]
enabled = true
split_inserts = "rewrite"

rewrite:
  enabled: true
  splitInserts: rewrite

Transaction required¶

Since multi-tuple inserts will likely write rows to several shards, PgDog requires the application to start a transaction before executing such queries. For example:

BEGIN;
INSERT INTO users (email, created_at) VALUES ($1, $2), ($3, $4);
COMMIT; -- or ROLLBACK;

If a transaction isn't started and a multi-tuple statement is sent by the application, PgDog will return an error and abort the request.

Requiring transactions ensures that if one of the INSERT statements fails, e.g., because of a unique constraint violation, the transaction can be rolled back, leaving the database in a consistent state.

Two-phase commit

Much like omnisharded table inserts, it's best to enable two-phase commit before attempting cross-shard multi-tuple inserts. This feature increases the likelihood that cross-shard transactions are atomic.