3

This one is difficult to summarize in the title.

I have a set of data such that a single table can contain all information in a single row per item except for one column. That column will contain a URL or many URLs. So I have three ideas:

  1. Add another table to store all URLs with a foreign key back to the main table for each
  2. Use a single table and match the URLs with a regular expression
  3. Use a single table and store the URLs in a jsonb column as an array

For case #2, I would be storing the regular expression in the column and matching values against it, e.g.:

select * 
from foo 
where 'http://someting.example.com/foo' ~ url

The url column has a row with the value: http(s)?://.+\.example\.com/foo.

Let's assume the row set for foo would number in the 100s (1000 would be a very large set), but most likely in the 10s. Let's further assume that the URL count could be double or triple in number.

Would the performance of each of these three possible designs be inline with each other? In other words, would the choice of design be a matter of preference? Or would designs #2 and #3 be so much worse than #1 that it would never be a good idea to choose them over #1?

I need to match rows by the URL. But I'd also like to keep maintenance to a minimum.

closed as unclear what you're asking by dezso, mustaccio, James Anderson, Philᵀᴹ, Mike Walsh Dec 5 '16 at 20:33

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

2

Options

  1. Add another table to store all URLs with a foreign key back to the main table for each
  2. Use a single table and match the URLs with a regular expression
  3. Use a single table and store the URLs in a jsonb column as an array

Overview

  1. Good idea. Totally ok.
  2. If 2 means storing the URLs as a compound text field and processing with a regex, than that's utterly insane. Worst idea of the day insane. ;)
  3. 3 is also a very poor idea. Using JSONB to store an array is way overkill.

Conclusion

  1. Store them in a separate table, or
  2. Store them on the same table using a PostgreSQL Native Array Type

Querying

The 1nf-form method.

SELECT t1.*, t2.*
FROM t1
JOIN t2 USING (id)
WHERE t2.url = 'http://someting.example.com/foo';

This is how you query the table with an array type.

SELECT t1.*
FROM t1
WHERE urls @> ARRAY['http://someting.example.com/foo']

Performance implications

Separate-table (1nf) are that

  • adding URL's, and updating URLs are faster (you're writing less data).
  • joining slows things down a bit.

Performance implications for the column-array

  • Depending on the query, indexing can be more complex.
  • Adding or updating URLs requires writing the whole row.
  • Not having to join makes this potentially faster.
  • Arrays are less supported with ORMs and libraries.

Maintenance

I need to match rows by the URL. But I'd also like to keep maintenance to a minimum.

Neither of these approaches is less-maintenance. However, for what it's worth, this is an ideal case for using an array. But make sure you normalize urls before you store them.

  • Awesome. I did not know Postgres has a native array type. The jsonb idea was definitely a "hack" to implement one. Writes are going to be far fewer than reads, so the array design is very appealing. – James Sumners Dec 5 '16 at 19:42

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