In a scenario where multiple short strings of text in table2 are to be associated with various rows in table1, is it better to use:
A through-table with 3 columns:
table2_id? This seems like the conventional approach, often preached by "avoid premature optimization" and "that's what RDBMs were built for" sayers who don't pay the AWS bill.
An array of integers corresponding to primary keys in table2? Seems like a naive solution that may be very optimized for sequential reads but a headache for any aggregating queries for daily reports etc.
A few weeks ago I was using the through-table. After some fiddling around in PgAdmin with explain-analyze, I decided to switch to using arrays of integers. The primary reasons were for simpler queries and sequential reads. But after reading this question, I'm now having second thoughts; is an array in Postgres effectively just an abstraction for a table within a row?
These would be used for tags (like on StackExchange but less featured) and for address components (city names, street names, etc). 80% of transactions would be sequential reads (some will hit the cache), 10% reads to filter rows by any number of
table2_id integers, and 10% writes (create/update/delete). Writes would happen in bulk for the most part. I think a through-table would make more sense if they were being added one-by-one at random intervals.