How to Enforce Global Uniqueness in a Partitioned Table

You’re using partitioning — maybe for performance, maybe for easier data management. But now you hit a common wall:

You must use partitioning (e.g., by user ID, date, region)
But you also need global uniqueness on a column not part of the partition key (like email, username, SKU...)

The Gotcha

PostgreSQL (and most databases) cannot enforce a UNIQUE constraint on a column unless it includes the partition key.

So this will fail:

ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);

Why? Because users is partitioned by, say, user_id—and Postgres doesn’t know how to check uniqueness across multiple partitions.

If you do this instead:

ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (user_id, email);

That works, but it only ensures uniqueness within each partition, not globally.

Workarounds for Global Uniqueness

1. Separate Table + Trigger

Create a small side table just to enforce uniqueness.

CREATE TABLE unique_emails (
    email TEXT PRIMARY KEY
);

Then use a trigger to insert into this table before inserting into your partitioned table:

CREATE OR REPLACE FUNCTION enforce_unique_email()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO unique_emails(email) VALUES (NEW.email);
    RETURN NEW;
EXCEPTION
    WHEN unique_violation THEN
        RAISE EXCEPTION 'Duplicate email: %', NEW.email;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER users_email_unique_trigger
BEFORE INSERT ON users
FOR EACH ROW EXECUTE FUNCTION enforce_unique_email();

Pros:

• Enforces true global uniqueness
• Keeps partitioning on users

Cons:

• Slight overhead (2 inserts per row)
• Adds transaction complexity (must handle rollbacks properly)

2. Periodic Uniqueness Check

If you can live with eventual consistency:

• Allow inserts
• Periodically run a query like:

SELECT email FROM users GROUP BY email HAVING COUNT(*) > 1;

• Or build a materialized view to flag duplicates

Good for reporting or background validation, but not for real-time enforcement.

3. Don’t Partition That Table

If the table is not too large and partitioning isn’t essential for performance:

• Skip partitioning
• Add regular primary/unique constraints as needed

This simplifies constraints, indexing, and queries.

Use partitioning only where it truly helps — such as for large logs, metrics, or historical data.

4. Partition by a Column Tied to Uniqueness

Instead of partitioning by user_id, you can partition by a hash of the column you want unique, like email.

CREATE TABLE users (
    email TEXT,
    user_id UUID
) PARTITION BY HASH (email);

That way, the same email always goes into the same partition, so now you can enforce a UNIQUE constraint on email within each partition.

Pros:

• Enforces uniqueness per email (almost globally)
• Simple and scalable

Cons:

• Not perfect — hash collisions are possible (but rare with good hashing)
• Less intuitive than range/list partitioning

Bonus: Postgres EXCLUDE Constraints (Advanced)

Postgres supports EXCLUDE constraints via btree_gist, allowing enforcement of custom rules (e.g. no overlapping ranges).

However:

• They don’t work on partitioned parent tables
• More complex to set up
• Not ideal for standard uniqueness constraints

Useful in special cases, but probably not the best tool for simple uniqueness enforcement.

Summary

Partitioning is powerful — but it limits how you enforce global constraints. If you need both performance and strict uniqueness, the separate table + trigger pattern is the most reliable.