0

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.

Improve this question

1 Answer 1

Reset to default
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:

Improve this answer
4
  • 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
    Commented Apr 20, 2017 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
    Commented Apr 20, 2017 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
    Commented Apr 20, 2017 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
    Commented Apr 20, 2017 at 2:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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