Arrays in Postgres

I assume by complex data type you mean something like (say) complex numbers, IP addresses, etc. The key difference is that the components of a complex data type don't stand on their own, or it's a violation of 1NF. I wouldn't mind storing a complex number in one column instead of two largely because I'd rather math with complex numbers "just work" and I'm unlikely to go updating the database for just the imaginary part. Likewise with IP addresses, it is useful to query them with netmasks and the like, but you're unlikely to query or update some middle component of them.

The interesting thing about arrays is depending on the work you're doing with them, they could either be a violation of 1NF or not. For example, if you're storing vectors in the physics sense, you may not be querying or updating their contents, in which case it's not a violation of 1NF because it's an atomic value. The danger of encouraging people to use Postgres's arrays is a fair number of the people reading are developers and will take this as carte blanch to store repeated values or sets as arrays, and miss out on a bunch of the power and elegance of relational databases (and probably cause themselves a lot of pain dealing with a type that has no direct and helpful JDBC mapping, for example).

> if for no other reason than it could save a lot of space

I discourage people from worrying too much up-front about the space utilization of their database design for several reasons. Intuitions about database storage and performance tend to be incorrect. Indexes usually matter a lot more in terms of disk use than the exact types of the columns. It's also a bad idea to spend a lot of energy optimizing the database before you have a significant amount of real-world data. Query performance alone varies dramatically between fake and real-world data sets, in part because the query planner will choose different strategies based on the sizes of tables and indexes and also because you usually don't know ahead of time what your access patterns will look like.

When it comes to databases, there really aren't very many hard and fast rules compared to rules of thumb. The rule of thumb on arrays is don't use them, and there are good reasons for that. It could be that you see massive performance and disk savings with tags and the loss of relational correctness doesn't bite you in a particular application, but you should investigate before choosing. All optimizations are trading flexibility for performance. You should make sure you don't need the flexibility and you do need the performance before making the trade.

"can readily turn operations"

Which operations, specifically? We have the product tags example to work from, so it would help to be more concrete.

"into O(M*N) rather than O(N)"

I assume here that your target is "scalability to higher numbers of tags for a given product"? If so, please state that explicitly. Again, M and N aren't necessary, because we have an example to work from.

Are the operations still susceptible to worse scalability on this axis if you employ a GIN index?

"PgSQL documentation suggests cases that will hurt you."

Please elaborate.

"I would suggest that the relational model is maintained"

I don't see how your solution is relational and the other one is not. Can you please explain? How is using an array different from using, say, a string, which is essentially an array of characters? How is it different from using a date, the components of which you can access (e.g. grouping by the month)?

"the optimiser is likely to come up with a better solution."

Please elaborate.

"The key difference is that the components of a complex data type don't stand on their own, or it's a violation of 1NF."

The characters or substrings that make up a string stand on their own. In fact, it's pretty hard to differentiate a string from an array of characters.

Similarly, the date component of a timestamp stands on its own, and timestamps are routinely broken up in that fashion.

"The interesting thing about arrays is depending on the work you're doing with them, they could either be a violation of 1NF or not."

In other words, you're saying that queries are a violation of 1NF, rather than the schema?

What about integers and modulo division? Is modulo division considered to be accessing a component of an integer?

"people reading are developers and will take this as carte blanch to store repeated values or sets as arrays"

There's no substitute for careful thought when designing a schema.

"Intuitions about database storage and performance tend to be incorrect."

Good point.

"loss of relational correctness"

I still don't think you've justified this. Remember that the normal forms are formally defined, so you should be able to show a clear distinction, which I've not seen yet.

"All optimizations are trading flexibility for performance."

What particular kind of flexibility is lost in this example (product tags)?

"The rule of thumb on arrays is don't use them, and there are good reasons for that."

This really sums up the situation. It's easy to use arrays for the wrong things, so the rule of thumb is to never use them. But they are still useful often enough for developers to get frustrated, and the rule of thumb may cause them to invent a worse solution elsewhere.

We should make the good reasons known, rather than just the rule of thumb.

"To access an element in an array"

I guess that's the thing: why would you want to access array elements? What business problem is that solving?

"there is a condition there if you want to reference by index which is the primary point of arrays"

In this example, he's using an array more like a set. I don't think he intends for a tag at index 17 to mean something different than a tag of the same value at index 3.

I agree that postgresql should probably have a good "set" datatype, but at this time it does not and arrays are a reasonable way to implement sets in a lot of cases.

For instance, GIN allows fast searches on queries like "find the arrays that contain element 'X'". That's more like a set operation.

"The optimiser will most likely be better at retrieving and caching data based on a join condition versus column contents."

There are probably some cases where it's better and some where it's worse. Postgres does keep track of stats for array elements in addition to the arrays themselves, so it's not obvious to me that using a separate table would improve matters.

"The characters or substrings that make up a string stand on their own. In fact, it's pretty hard to differentiate a string from an array of characters."

It's difficult to differentiate a number from an ordered n-tuple of bits, but that doesn't make storing the number 123 storing an array.

I took the parent post's point to be clear: it isn't whether you can look at something as, in some sense, an array, it's whether you treat it as an atomic value. That's generally what you do with numbers: you update the whole number, not the third bit. And it's likely what you do with strings---even if you just want to change one character, you update the whole string, not the third character.

Likewise, if you want to store a complex number as a 2-tuple, you could treat that as an atomic data type even though it consists of two elements by always operating on the pair*. That's the respect in which it "stands on its own"---you don't peer inside.