Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Arrays in Postgres

craigkerstiens.com

Post reply on HN
Print view View original

11–20 of 40 posts

Re: Arrays in Postgres

#11

Post by einhverfr »

joevandyk wrote: post #3
rdegges wrote: post #2

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 :(

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.

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.

Re: Arrays in Postgres

#12

Post by radiowave »

joevandyk wrote: post #5
jaggederest wrote: post #4

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.

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.

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.

Re: Arrays in Postgres

#13

Post by johnrob »

jeffdavis wrote: post #7

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... And no, using arrays is not an automatic violation of first normal form.

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.

Re: Arrays in Postgres

#14

Post by taligent »

jeffdavis wrote: post #7

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... And no, using arrays is not an automatic violation of first normal form.

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.

Re: Arrays in Postgres

#15

Post by fdr »

taligent wrote: post #14
jeffdavis wrote: post #7

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... And no, using arrays is not an automatic violation of first normal form.

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.

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.

Re: Arrays in Postgres

#16

Post by eckyptang »

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.

Re: Arrays in Postgres

#17

Post by kiwidrew »

johnrob wrote: post #13
jeffdavis wrote: post #7

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... And no, using arrays is not an automatic violation of first normal form.

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 mu…

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.

Re: Arrays in Postgres

#18

Post by kiwidrew »

eckyptang wrote: post #16

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.

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-abo...

Re: Arrays in Postgres

#19

Post by alexro »

eckyptang wrote: post #16

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.

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-scali...

Re: Arrays in Postgres

#20

Post by jeffdavis »

johnrob wrote: post #13
jeffdavis wrote: post #7

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... And no, using arrays is not an automatic violation of first normal form.

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 mu…

"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.

Post reply on HN