Paul Ramsey: Tags and Postgres Arrays, a Purrrfect Combination

In a previous life, I worked on a CRM system that really loved the idea of tags. Everything could be tagged, users could create new tags, tags were a key organizing principle of searching and filtering. The trouble was, modeled traditionally, tags can really make for some ugly tables and equally ugly queries. Fortunately, and as usual, Postgres has an answer. Today I’m going to walk through working with tags in Postgres with a sample database of 🐈 cats and their attributes First, I’ll look at a traditional relational model Second, I’ll look at using an integer array to store tags Lastly, I’ll test text arrays directly embedding the tags alongside the feline information This post is also available as an interactive tutorial in our Postgres playground. Tags in a relational model For these tests, we will use a very simple table of 🐈 cats, our entity of interest, and tags a short table of ten tags for the cats. In between the two tables, the relationship between tags and cats is stored in the cat_tags table. - Table Creation SQL CREATE TABLE cats ( cat_id serial primary key, cat_name text not null ); CREATE TABLE cat_tags ( cat_id integer not null, tag_id integer not null, unique(cat_id, tag_id) ); CREATE TABLE tags ( tag_id serial primary key, tag_name text not null, unique(tag_name) ); I filled the tables with over 1.7M entries for the cats, ten entries for the tags, and 4.7M entries for the cat/tag relationship. - Data Generation SQL -- Generate cats random names INSERT INTO cats (cat_name) WITH hon AS ( SELECT * FROM unnest(ARRAY['mr', 'ms', 'miss', 'doctor', 'frau', 'fraulein', 'missus', 'governer']) WITH ORDINALITY AS hon(n, i) ), fn AS ( SELECT * FROM unnest(ARRAY['flopsy', 'mopsey', 'whisper', 'fluffer', 'tigger', 'softly']) WITH ORDINALITY AS fn(n, i) ), mn AS ( SELECT * FROM unnest(ARRAY['biggles', 'wiggly', 'mossturn', 'leaflittle', 'flower', 'nonsuch']) WITH ORDINALITY AS mn(n, i) ), ln [...]