17 ms·

Arrays in Postgres

70 points by paratrooper 14y ago

rdegges 14y ago
Excellent article on Arrays. I wish I knew about these before, there have been so many times I should have used this instead of serializing / deserializing JSON myself via TextFields :(
- joevandyk 14y ago
  Postgresql 9.2 supports json, btw! You can store json, or, even better, you can write sql that returns an arbitrary json structure. So you can have one sql query return a nested array of hashes of arrays of hashes... handy if you need a retrieve a lot of different data at once that doesn't fit into a neat set of rows.
  - einhverfr 14y ago
    What we need now is a function that will take an arbitrary JSON element and return a data structure associated with it. This shouldn't be too hard using plv8js..... This could then be used as an input format for object-relational modelling.
jaggederest 14y ago
Ah yes, the venerable array type. I remember trying to normalize applications written in VB6 and Access that used arrays with foreign keys instead of many to many relationships. Good times.
- joevandyk 14y ago
  While I probably wouldn't store things in an array, it's useful to get data back out in array format sometimes. select array_agg(email_address), home_state from users group by home_state Will give you a list of home states and all the email addresses that belong to that state.
  - radiowave 14y ago
    Right. A case I've used them for is a recursive query which returns a set of rows, where each row is some end-point that matches the query criteria, the rows can include arrays representing the list of nodes traversed to reach that end-point, or some notion of the path cost by hop.
- einhverfr 14y ago
  Foreign keys as arrays is just wrong. However, there are many cases where arrays are actually extremely useful, including the ability to be a useful intermediary type for stored procedure interfaces. For example, you can aggregate arrays of rows, and pass those into another stored procedure for processing, or you can use them for pulling info to/from your application. We do this extensively in PostgreSQL in part because DBD::Pg has excellent array support. These are actually remarkably useful in PostgreSQL. Of course like any advanced feature it can be abused.
ksherlock 14y ago
I used Postgres arrays in a project a few years ago -- The university course catalog was so awful I had to create my own. There was a 7-element array of integers (one for each day of the week) where each element was a bitmap indicating if the class was active in that period. To the DB driver, it was just a string -- { 0, 0, 0, 0, 0, 0, 0} -- so I split it into an array manually in client code.
jeffdavis 14y ago
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.
nwenzel 14y ago
The MADlib add-on out of UC Berkeley for Postgres and Greenplum uses arrays for inputs into its algorithms. MADlib.net is worth checking out for some in-database analytic and machine learning goodness. It's not going to replace R or python but it has potential as part of your toolbelt.
eckyptang 14y ago
The more you put in the database black box, the more scalability problems you will encounter in the future. It's more economical to scale out than buy bigger databases servers. Please just don't do it.
- kiwidrew 14y ago
  Happily, Postgres keeps improving performance with each release. According to benchmarks [1], the upcoming version 9.2 will scale up to 64 cores for read-heavy workloads. Unless your application is going to grow really fast -- keeping in mind that the hardware capabilities will continue to improve each year -- then it's just premature optimization to worry about horizontal scaling. Just upgrade your database server once a year and reap the benefits of Moore's law. [1] http://rhaas.blogspot.com/2012/04/did-i-say-32-cores-how-about-64.html http://rhaas.blogspot.com/2012/04/did-i-say-32-cores-how-abo...
  - eckyptang 14y ago
    I love your optimism. The real world doesn't work like that. The real world punishes you for every shitty feature you pick and every bad chunk of code. Our application is 15 years old, and we're on a 64-core machine with 768Gb of RAM and 35Tb of disk. We're running at 80% capacity. Where do we go from here? Yes, we rewrite and scale out for the measly cost of £450k. That cost would have been avoided with the appropriate due diligence. That is not a cost anyone wants to swallow. Then again I don't work disposable CRUD applications...
- alexro 14y ago
  It really depends on the case at hands, sometimes it's more practical to scale up, especially if there is no indefinite grows anticipated. http://www.codinghorror.com/blog/2009/06/scaling-up-vs-scaling-out-hidden-costs.html http://www.codinghorror.com/blog/2009/06/scaling-up-vs-scali...
  - eckyptang 14y ago
    That's hardly a great reference, especially when he immediately shot himself by not checking vendor limits... http://www.codinghorror.com/blog/2009/07/oh-you-wanted-awesome-edition.html http://www.codinghorror.com/blog/2009/07/oh-you-wanted-aweso...
LarryMade 14y ago
Looks like the path to code/data obscurity to me, why not have properly labeled fields for all the elements and save yourself the headache of remembering the fancy trick you implemented years ago.
EzGraphs 14y ago
There are probably some useful applications for this, but I am not really a fan of databases supporting complex data types. It's not because of a commitment to any particular normal form or theoretical construct. It is because on a project that involves multiple developers over a period of time, this sort of special functionality provides "surprises" that are not terribly pleasant. - Nonstandard SQL is required - Database specific functions are used - Later developers can be confused by the use of a non-standard data type - SQL Commenting does not happen much in practice in my experience - Array data can be handled using existing SQL constructs, so it is never an absolute necessity - Other languages are better equipped for handling the data types (in my fuzzy subjective assessment) My experience is mostly with Oracle, which has been adding various data types for years (XML, Objects, Arrays, etc). I can't think of a specific case where their use proved to be a real specific benefit to a project... though the usual argument is improved performance.

70 points by paratrooper 14y ago

Excellent article on Arrays. I wish I knew about these before, there have been so many times I should have used this instead of serializing / deserializing JSON myself via TextFields :(

joevandyk 14y ago
Postgresql 9.2 supports json, btw! You can store json, or, even better, you can write sql that returns an arbitrary json structure. So you can have one sql query return a nested array of hashes of arrays of hashes... handy if you need a retrieve a lot of different data at once that doesn't fit into a neat set of rows.
- einhverfr 14y ago
  What we need now is a function that will take an arbitrary JSON element and return a data structure associated with it. This shouldn't be too hard using plv8js..... This could then be used as an input format for object-relational modelling.

jaggederest 14y ago

Ah yes, the venerable array type. I remember trying to normalize applications written in VB6 and Access that used arrays with foreign keys instead of many to many relationships. Good times.

joevandyk 14y ago
While I probably wouldn't store things in an array, it's useful to get data back out in array format sometimes. select array_agg(email_address), home_state from users group by home_state Will give you a list of home states and all the email addresses that belong to that state.
- radiowave 14y ago
  Right. A case I've used them for is a recursive query which returns a set of rows, where each row is some end-point that matches the query criteria, the rows can include arrays representing the list of nodes traversed to reach that end-point, or some notion of the path cost by hop.
einhverfr 14y ago
Foreign keys as arrays is just wrong. However, there are many cases where arrays are actually extremely useful, including the ability to be a useful intermediary type for stored procedure interfaces. For example, you can aggregate arrays of rows, and pass those into another stored procedure for processing, or you can use them for pulling info to/from your application. We do this extensively in PostgreSQL in part because DBD::Pg has excellent array support. These are actually remarkably useful in PostgreSQL. Of course like any advanced feature it can be abused.

ksherlock 14y ago

I used Postgres arrays in a project a few years ago -- The university course catalog was so awful I had to create my own. There was a 7-element array of integers (one for each day of the week) where each element was a bitmap indicating if the class was active in that period. To the DB driver, it was just a string -- { 0, 0, 0, 0, 0, 0, 0} -- so I split it into an array manually in client code.

jeffdavis 14y ago

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.

nwenzel 14y ago

The MADlib add-on out of UC Berkeley for Postgres and Greenplum uses arrays for inputs into its algorithms. MADlib.net is worth checking out for some in-database analytic and machine learning goodness. It's not going to replace R or python but it has potential as part of your toolbelt.

eckyptang 14y ago

The more you put in the database black box, the more scalability problems you will encounter in the future. It's more economical to scale out than buy bigger databases servers. Please just don't do it.

kiwidrew 14y ago
Happily, Postgres keeps improving performance with each release. According to benchmarks [1], the upcoming version 9.2 will scale up to 64 cores for read-heavy workloads. Unless your application is going to grow really fast -- keeping in mind that the hardware capabilities will continue to improve each year -- then it's just premature optimization to worry about horizontal scaling. Just upgrade your database server once a year and reap the benefits of Moore's law. [1] http://rhaas.blogspot.com/2012/04/did-i-say-32-cores-how-about-64.html http://rhaas.blogspot.com/2012/04/did-i-say-32-cores-how-abo...
- eckyptang 14y ago
  I love your optimism. The real world doesn't work like that. The real world punishes you for every shitty feature you pick and every bad chunk of code. Our application is 15 years old, and we're on a 64-core machine with 768Gb of RAM and 35Tb of disk. We're running at 80% capacity. Where do we go from here? Yes, we rewrite and scale out for the measly cost of £450k. That cost would have been avoided with the appropriate due diligence. That is not a cost anyone wants to swallow. Then again I don't work disposable CRUD applications...
alexro 14y ago
It really depends on the case at hands, sometimes it's more practical to scale up, especially if there is no indefinite grows anticipated. http://www.codinghorror.com/blog/2009/06/scaling-up-vs-scaling-out-hidden-costs.html http://www.codinghorror.com/blog/2009/06/scaling-up-vs-scali...
- eckyptang 14y ago
  That's hardly a great reference, especially when he immediately shot himself by not checking vendor limits... http://www.codinghorror.com/blog/2009/07/oh-you-wanted-awesome-edition.html http://www.codinghorror.com/blog/2009/07/oh-you-wanted-aweso...

LarryMade 14y ago

Looks like the path to code/data obscurity to me, why not have properly labeled fields for all the elements and save yourself the headache of remembering the fancy trick you implemented years ago.

EzGraphs 14y ago

There are probably some useful applications for this, but I am not really a fan of databases supporting complex data types. It's not because of a commitment to any particular normal form or theoretical construct. It is because on a project that involves multiple developers over a period of time, this sort of special functionality provides "surprises" that are not terribly pleasant. - Nonstandard SQL is required - Database specific functions are used - Later developers can be confused by the use of a non-standard data type - SQL Commenting does not happen much in practice in my experience - Array data can be handled using existing SQL constructs, so it is never an absolute necessity - Other languages are better equipped for handling the data types (in my fuzzy subjective assessment) My experience is mostly with Oracle, which has been adding various data types for years (XML, Objects, Arrays, etc). I can't think of a specific case where their use proved to be a real specific benefit to a project... though the usual argument is improved performance.