4 ms·

One of the differences with postgres is that it's OK to use interesting data types. Other systems treat it as though it were somehow wrong. See my post here: h

One of the differences with postgres is that it's OK to use interesting data types. Other systems treat it as though it were somehow wrong. See my post here: http://thoughts.davisjeff.com/2009/09/30/choosing-data-types/ http://thoughts.davisjeff.com/2009/09/30/choosing-data-types... And no, using arrays is not an automatic violation of first normal form.

einhverfr 14y ago
Arrays are actually pretty cool in PostgreSQL and keep getting cooler. Unnest() turns arrays into relations for example. One of my primary uses for arrays is for passing data to/from the application. Data may not be stored in the db as an array, but it really makes passing complex data structures to/from stored procedures a lot easier.
johnrob 14y ago
Arrays aren't really in the spirit of relational design. This means that using them will change the ACID characteristics compared to a relational design. In the article's first example, it would seem like adding another item to a purchase would cause the entire purchase row to lock - this wouldn't happen if items were stored in their own table. That's not to say they aren't useful though; read performance would be much better with all the data living in a single row somewhere on disk. SQL queries would also be easier to write.
- kiwidrew 14y ago
  It's a slightly contrived example, because in a real-world schema you would want enough information about each line item that an array isn't practical. But I don't think locks are an issue here, because no sane business process would need different transactions that were updating the same purchase's line items in conflicting ways.
- jeffdavis 14y ago
  "Arrays aren't really in the spirit of relational design." Why do you say that? To a certain extent I agree in broad terms; but I don't believe that it is somehow "unclean" or un-relational just because you are using an array (or any other complex data type). "This means that using them will change the ACID characteristics compared to a relational design." No, arrays are protected by ACID as is any other data in postgres. "it would seem like adding another item to a purchase would cause the entire purchase row to lock" It will still allow concurrent reads of the row in postgres (MVCC). In order for the write lock to be a practical problem, there would have to be a lot of concurrent updates to the very same purchase. I think we can all agree that the first example is "hacky" (which the author says in the article), so let's ignore that one. How about the second example? I think it's pretty reasonable to store tags that way, if for no other reason than it could save a lot of space.
  - fusiongyro 14y ago
    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.
    - jeffdavis 14y ago
taligent 14y ago
There is nothing that unique about the inline support of other data structures. Many databases have done it in the past e.g Oracle and do so today especially in NoSQL land e.g. MongoDB, Cassandra.
- fdr 14y ago
  I think Jeff was principally referring to relational dogma that is so prevalent in other SQL database system culture, even though they might implement such types to get a check-box (or not: MSSQL doesn't support arrays, AFAIK) Clearly nested and record tagged structures are popular in other database systems, but in the universe of SQL systems Postgres has always broken rank with tradition with most of its relational brethren (exception, due to similar parentage: Illustra and then Informix) when it came to data types -- for example, the long-standing inclusion of polygons, lines, and points.
fnl 14y ago
> And no, using arrays is not an automatic violation of first normal form. One of the conditions of the 1NF is that each row and column contain one and only one value. While a NULL might be disputable, using multiple values as shown in the two examples (tags, items/price/quantity) are a clear violation of the 1NF. Only if the array data were truly "one value" (e.g., a vector) it would not be a violation. (I.e., I am not trying to contradict your statement, but I think it helps to add a bit of clarification.)
- ocharles 14y ago
  If you never need to query 'inside' the array, then I don't think that's a violation of 1NF at all. If you treat the entire array as an atomic value, then I would think that's still 1NF.
  - guan 14y ago
    Even if you do need to query inside the array, how is this different from something like WHERE x like 'prefix%'? Is that a violation of 1NF too?
    - ocharles 14y ago
      Arguably, yes, because you haven't really broken your data down as much as you need to; but this is a fairly contentious issue - just see http://en.wikipedia.org/wiki/First_normal_form#Atomicity http://en.wikipedia.org/wiki/First_normal_form#Atomicity. If you are frequently needing to query inside data, you may well gain from moving that 'prefix' into a separate column, or more drastically rethinking your data model if that's appropriate.
      - guan 14y ago
        In this example prefix is something the user typed in. So textcolumn LIKE '%blahblah%'.
- jeffdavis 14y ago
  "One of the conditions of the 1NF is that each row and column contain one and only one value." There is no useful differentiation between atomic and non-atomic values. A timestamp is made up of a date and a time, a string is an array of characters, a real number has a sign and magnitude, etc. I suppose you could just use boolean only, which could be regarded as "atomic".

einhverfr 14y ago

Arrays are actually pretty cool in PostgreSQL and keep getting cooler. Unnest() turns arrays into relations for example. One of my primary uses for arrays is for passing data to/from the application. Data may not be stored in the db as an array, but it really makes passing complex data structures to/from stored procedures a lot easier.

johnrob 14y ago

Arrays aren't really in the spirit of relational design. This means that using them will change the ACID characteristics compared to a relational design. In the article's first example, it would seem like adding another item to a purchase would cause the entire purchase row to lock - this wouldn't happen if items were stored in their own table. That's not to say they aren't useful though; read performance would be much better with all the data living in a single row somewhere on disk. SQL queries would also be easier to write.

kiwidrew 14y ago
It's a slightly contrived example, because in a real-world schema you would want enough information about each line item that an array isn't practical. But I don't think locks are an issue here, because no sane business process would need different transactions that were updating the same purchase's line items in conflicting ways.
jeffdavis 14y ago
"Arrays aren't really in the spirit of relational design." Why do you say that? To a certain extent I agree in broad terms; but I don't believe that it is somehow "unclean" or un-relational just because you are using an array (or any other complex data type). "This means that using them will change the ACID characteristics compared to a relational design." No, arrays are protected by ACID as is any other data in postgres. "it would seem like adding another item to a purchase would cause the entire purchase row to lock" It will still allow concurrent reads of the row in postgres (MVCC). In order for the write lock to be a practical problem, there would have to be a lot of concurrent updates to the very same purchase. I think we can all agree that the first example is "hacky" (which the author says in the article), so let's ignore that one. How about the second example? I think it's pretty reasonable to store tags that way, if for no other reason than it could save a lot of space.
- fusiongyro 14y ago
  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.
  - jeffdavis 14y ago

taligent 14y ago

There is nothing that unique about the inline support of other data structures. Many databases have done it in the past e.g Oracle and do so today especially in NoSQL land e.g. MongoDB, Cassandra.

fdr 14y ago
I think Jeff was principally referring to relational dogma that is so prevalent in other SQL database system culture, even though they might implement such types to get a check-box (or not: MSSQL doesn't support arrays, AFAIK) Clearly nested and record tagged structures are popular in other database systems, but in the universe of SQL systems Postgres has always broken rank with tradition with most of its relational brethren (exception, due to similar parentage: Illustra and then Informix) when it came to data types -- for example, the long-standing inclusion of polygons, lines, and points.

fnl 14y ago

> And no, using arrays is not an automatic violation of first normal form. One of the conditions of the 1NF is that each row and column contain one and only one value. While a NULL might be disputable, using multiple values as shown in the two examples (tags, items/price/quantity) are a clear violation of the 1NF. Only if the array data were truly "one value" (e.g., a vector) it would not be a violation. (I.e., I am not trying to contradict your statement, but I think it helps to add a bit of clarification.)

ocharles 14y ago
If you never need to query 'inside' the array, then I don't think that's a violation of 1NF at all. If you treat the entire array as an atomic value, then I would think that's still 1NF.
- guan 14y ago
  Even if you do need to query inside the array, how is this different from something like WHERE x like 'prefix%'? Is that a violation of 1NF too?
  - ocharles 14y ago
    Arguably, yes, because you haven't really broken your data down as much as you need to; but this is a fairly contentious issue - just see http://en.wikipedia.org/wiki/First_normal_form#Atomicity http://en.wikipedia.org/wiki/First_normal_form#Atomicity. If you are frequently needing to query inside data, you may well gain from moving that 'prefix' into a separate column, or more drastically rethinking your data model if that's appropriate.
    - guan 14y ago
      In this example prefix is something the user typed in. So textcolumn LIKE '%blahblah%'.
jeffdavis 14y ago
"One of the conditions of the 1NF is that each row and column contain one and only one value." There is no useful differentiation between atomic and non-atomic values. A timestamp is made up of a date and a time, a string is an array of characters, a real number has a sign and magnitude, etc. I suppose you could just use boolean only, which could be regarded as "atomic".