This is my first post here and I am quite new to PostgreSQL - many apologies if my question may appear a bit unclear.

We set up a database for storing a large volume of photo-metadata with attached Hashtags/tags (about 500 Million photos and about 1 to two Billion Hashtags, about 50 Million of those distinct/unique).

We just started to experiment and we're unclear which path to follow for storing hashtags. Two possible options we examined:

  • Many-to-Many relationship

    • as suggested here and here: this would mean creating 3 Tables

    Table: Photos
    Columns: Photo_ID, Photo_Title, Content

    Table: Tags
    Columns: Tag_ID, Tag_Title

    Table: Photo_Tags
    Columns: Photo_ID, Tag_ID

  • Use an Array-Field and create a GIN index over it, as suggested here, here and here:
--adapted from the link above
CREATE TABLE "Photos"("Column1" text[]);
INSERT INTO "Photos" VALUES ('{tree, street, green}');
INSERT INTO "Photos" VALUES ('{tree, house, red}');

CREATE INDEX idx_photos on "Photos" USING GIN ("Column1");

-- To enforce index usage because we have only 2 records for this test... 
SET enable_seqscan TO off;

SELECT * FROM "Photos" WHERE "Column1" @> ARRAY[tree];

Which one of the two approaches would be faster for our structure/volume? We will need to query both - either for for a single hashtag giving a list of (thousands of) photos - or querying for thousands of photos with their respective tags attached

Thanks a lot for experience/suggestions!

  • 1
    You might be interested in this: databasesoup.com/2015/01/tag-all-things.html – a_horse_with_no_name Nov 23 '17 at 7:33
  • 2
    Unrelated, but: you should really avoid those dreaded quoted identifiers. They are much more trouble in the long run, then they are worth – a_horse_with_no_name Nov 23 '17 at 7:34
  • Many thanks to both of you! The article confirms what I was feeling: arrays are much better for our approach, but my colleagues argued against me. – Alex Nov 23 '17 at 7:49
  • What I don't understand, why do I read everywhere then that normalization is better? (see here for example) – Alex Nov 23 '17 at 9:29
  • As always, it all depends on the usage pattern. Sometimes arrays make sense, sometimes they don't. E.g. if you would like to ensure that you only store pre-defined tags, then an array wouldn't work as you can't have a proper foreign key. Also arrays aren't efficient if you store thousands or even millions of elements in them (which is not the case for tags). – a_horse_with_no_name Nov 23 '17 at 9:44

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.