database - Does PostgreSQL quickly search for columns with arrays of strings? -

according can postgresql index array columns?, postgresql can index array columns.

can searches on array column efficiently non array types?

for example, suppose have row questions table (like so):

title: ... content:... tags: [ 'postgresql', 'indexing', 'arrays' ] 

and want find questions tag 'postgresql'. storing relationship in join table faster searching?

and yes, each column have index.

gin , gist indexes bigger simple b-tree, , take longer scan. gin faster gist @ cost of expensive updates.

if store tags in array column update row require update index on array. under circumstances hot permit skipped, it's not can rely on. you'll have more index updates , more index bloat.

on other hand, you're avoiding need scan b-tree ids of desired objects fetch them main table via join. you're saving fair bit of space using array instead of paying 28 byte per row overhead each tag in join table.

if insert , update rate on main table low - including changes tags - gin might suitable choice. otherwise i'd go typical b-tree on join table, secondary index on (tag, object_id) index-only scans can used object(s) have given tag.

in end best thing benchmark simulation of workload.