CODEWITHSUNDEEP
arrayspostgresql
Mihir Shah
Mihir Shah

Reputation: 1809

Storing 10000 integer array value as array column type performance comparing separate table with JOIN query in Postgres

I have product table in Postgres database.. I have to show product based on user's pincode whether product is serviceable in that area or not. so for that I have to store pincode for each product. there can be maximum 12000 pincode per product.

So My question is whether it's efficient to store pincode as array column type in main product table with indexing, or to use separate table which cost JOIN query.

As per my understanding, storing in array column is efficient as have to show products in the list with filter/search with parallel 1000 users(some times).. so join table will be too much heavy compare to this. But by doing, I have to go with native query for all product related things as spring boot JPA does not support integer array type, so can't take advantage of ORM. is there any slowness in query by using integer array column? its 2nd thing whether I store it as string array or integer array..

Need help to make it efficient.

Upvotes: 0

Views: 528

Answers (1)

Akhilesh Mishra
Akhilesh Mishra

Reputation: 6130

Array is the best way in your scenario As far as you will use it for checking whether product is serviceable in that area or not.

Further if you are planning any other regular operations i.e. To get a list of products serviceable in particular PINCODE etc. then you should not adopt this approach. You should go with proper normalization and keep the relation in separate table.

Upvotes: 1

Related Questions