-1

I have a table called listings. For each listing, the user can upload 5 images. In the table listings, I have a multidimensional array images text[][]. Each multidimensional element will hold [ a base64 string, filename of image].

The only time I would do a search on the images[][] array is if the user removes a image, I would search for the filename of image in the images[][] array and remove it.

I would really like to avoid having a table just for images. No image will ever belong to more than a single listing I can not justify the cost of foreign key looks ups for what there is really no benefit.

I read in PostgreSQL documentation:

Arrays are not sets; searching for specific array elements can be a sign of database misdesign.

Since the only time I am searching for a specific array element is when a user deletes a image... does this constitute a database misdesign? I was really hoping to use denormalization for the images of a listing.

1

If you want to avoid any overhead for a fixed maximum of images, and keep the disk footprint to a minimum, don't use an array, that adds 24 bytes of overhead for the array type, similar to a the overhead of a row.

Add 5 individual columns to the row. Some empty columns with NULL values cost almost nothing. That's even more awkward to search for (you'll have to look into each column), but you say that's irrelevant for the use case.

If the number of images is dynamic I would suggest a normalized design with a 1:n table images, though (which wouldn't be a bad idea anyway).

If you need to search occasionally after all, a bloom index would be an option. The manual:

This type of index is most useful when a table has many attributes and queries test arbitrary combinations of them. A traditional btree index is faster than a bloom index, but it can require many btree indexes to support all possible queries where one needs only a single bloom index. Note however that bloom indexes only support equality queries,

Or a GIN index on an ARRAY expression covering the 5 columns.

Related answers concerning storage:

  • What about using a jsonb column instead of a multidimensional array? Then I wouldn't have the overhead of normalization for data that isn't repeated. Would that be a good fit? – dman Apr 20 '17 at 2:09
  • 1
    @dman: It's an option, especially if you work with JSON in your application anyway (simple I/O). Else I would use 5 plain columns. – Erwin Brandstetter Apr 20 '17 at 2:12
  • I will never be querying on the images column. When querying listings table, I would like to avoid the overhead of relational look up on a separate images table(since this data is never repeated). Would having images a jsonb column be less overhead than having the separate images table when retrieving listings? – dman Apr 20 '17 at 2:21
  • 1
    @dman: It depends on the details. Either is simple to handle (json, jsonb, hstore, array, plain columns, normalized table). Plain columns have the smallest disk footprint. – Erwin Brandstetter Apr 20 '17 at 2:37

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.