CODEWITHSUNDEEP
postgresqldatabase-indexes
elizabeth lail
elizabeth lail

Reputation: 17

Index in postgresql

Firstly, I have a table in database USERS with almost 30 Million records in it. I have different indices for each column. But some of the column have only 2 to 3 non null values while others are Null but still their index size is 847 MB a little less than the one index that contain unique value for each row.

Can anyone know why is it like this?

Secondly, in PostgreSQL we have a index for primary key index for each column by default what if we delete that index what will be the consequences? What that index is really use for? As i'm searching based on values in other columns only will it be safe to delete index for primary key?

Upvotes: 0

Views: 34

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 246053

NULL values are stored in indexes just like all other values, so the first part is not surprising.

You cannot delete the primary key index, what you could do is drop the primary key constraint. But then you cannot be certain that no duplicate rows get added to the table. If you think that is no problem, look at the many questions asking for help with exactly that problem.

Every table should have a primary key.

But it might be a good idea to get rid of some other indexes if you don't need them.

Upvotes: 1

Jim Macaulay
Jim Macaulay

Reputation: 5141

There is nothing called primary key index, seems to be you are talking about unique index. First of all you need to understand the difference between primary key and index. You can have only one primary key in a table. Primary key would be your unique identifier of each column and does not allow nulls. Index is used to speed up your fetching process on particular column and you can have one null if it is unique index. Deleting unique index in your table will not impact any thing apart from performance. Its your way of design to have index or not

Upvotes: 0

Related Questions