CODEWITHSUNDEEP
amazon-web-servicesamazon-redshift
Gautam
Gautam

Reputation: 5

Unique key / Primary key in aws redshift table

I have created primary key and unique constraint seperately on a redshift table, and I noticed the uniqueness is not being enforced at all

According to the documentation, they say uniqueness or primary key is not enforced, but by creating a unique or primary key there will be better query plans.

Just wanted to confirm the above understanding is true, that there will be duplicates even though I create a primary key or unique key, its not enforced, but it may help in generating better query plans ?

Upvotes: 0

Views: 2608

Answers (1)

Bill Weiner
Bill Weiner

Reputation: 11032

Yes, both statements are correct - Redshift does not enforce uniqueness and identifying primary / foreign keys can speed up queries. The catch is that if you define primary / foreign keys and the data is not unique the results be invalid / inconsistent. The speed up gained is by assuming uniqueness (which RS doesn't enforce) so it is the responsibility of the DBA to ensure uniqueness for correct results to be produced. In Redshift if you cannot enforce uniqueness don't define columns as unique.

The reason for this is based on the cost of enforcing uniqueness in a distributed data cluster. Because there are network hops between data rows in the same table the cost to enforce uniqueness is very high and the benefits of defining uniqueness don't come close to outweighing these costs.

Upvotes: 4

Related Questions