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

Question

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.

Akhilesh Mishra · Accepted Answer · 2020-09-10 11:08:56Z

1

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.

answered Sep 10, 2020 at 11:08

Akhilesh Mishra

6,1503 gold badges20 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related