CODEWITHSUNDEEP
sqldatabasepostgresql
Ujjwal Saxena
Ujjwal Saxena

Reputation: 141

Is using UNIQUE in SQL Inefficient?

I am new to SQL and have started learning Postgres. I want to create a database in which one of the column of table in that database is email. Now since I will be using email to uniquely identify the person for sign-up & logging in, I used UNIQUE command while creating the table so that if an account already exists with that email I will pass an error. But will this be efficient in large databases?

Here is the screenshot of the SQL command I have used. Here is the screenshot of the SQL command I have used.

Upvotes: 0

Views: 84

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

What do you mean by efficient? A unique constraint or index adds overhead to prevent duplicates. This overhead is needed on inserts or updates on the affected columns.

How much overhead? Well a uniqueness is validating by using an index. On most databases, looking up a value in an index can be quite fast and usually does not pose a significant performance issue. Of course, such overhead can sometimes be important, particularly on very busy databases with lots of data changes (think dozens or hundreds of transactions per second).

In general, the size of the table is going to have little impact on the performance -- well, indexes do slow down a little bit (but hardly noticeable) as tables get bigger, assuming there is sufficient memory.

On the other hand, the cost of not having data integrity can be much larger -- affecting the performance and quality of queries that run on the data.

Upvotes: 4

Related Questions