Setting the scene

I have a PostgreSQL 11 database running on a very fast 64core, 10 SSD HW RAID 10 server. In the database I need to create a table with a couple of million rows, let's call it cars. The rows will see very frequent updates and even more frequent read. Very heavy usage. The cars have many different equipment attributes like has_electrical_side_mirrors, has_automatic_gearbox, has_gps_navigation etc. etc. About 150 of them, all boolean. On top of this there is gonna be something like 40 other columns of strings and integers. I want a design specifically for storing those 150 boolean values.

My ideas

I can basically see a couple of designs of which I think two will be superior from a performance and storage overhead point of view:

  1. Add 150 boolean columns
  2. Add a join table with equipment
  3. Add an intarray column with equipment ids (23=has_gps_navigation etc.)
  4. Add a JSONb column with key/value equipment ({'has_gps_navigation' => true} etc.)

Of those above I think the choice stands between option 1. and 3.

Why I don't think option 2. and 4. is a good idea

Option 2. in my experience is gonna give too much performance overhead on queries (I need < 200ms response time on queries on cars that has this or that equipment). I think option 4. is very similar to 3. but 4. will also have a larger disk space overhead than 3. if I store all values like {'has_gps_navigation' => true} etc. because I would have the key names stored in the db.

So down to option 1. and 3. I guess - any strong opinions otherwise?

My concerns about option 1. and 3.

My main concern with option 1. is that I would have to create 150 indexes or somewhere around that, to make queries performant. That sounds like the table, to my knowledge, will see a huge overhead on write performance. The other concern I have with option 1. is that it's gonna be a pain adding new equipment, having to create a column and an index every time.

My main concern with option 2. is that I have little experience with using postgresql intarrays. How performant are they in comparison in this situation and what kind of indexes should I use? As far as I can read, I can cover indexing of all equipment by just having a single index on that equipment_item_ids-column. I can easily create some logic in my application that maps the strings of has_gps_navigation to an integer of 23, my idea is then, if the car has a specific piece of equipment, add the id of it to the array.

  • 1
    How many rows are you expecting to store? – George.Palacios Mar 20 at 22:39
  • "create a table with a couple of million rows" :-) – Niels Kristian Mar 20 at 22:40
  • Work out the common fields selected and use compound indexes for those, starting with common fields. – danblack Mar 21 at 0:52
  • 1
    Can you show us some example queries? What type of conditions are you expecting? – a_horse_with_no_name Mar 21 at 7:00
  • 1
    Do the queries only test for simple containment, or do you also have tests for non-containment, or complex booleans expressions including parentheses, NOT, OR? – jjanes Mar 23 at 14:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.