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

21–30 of 40 posts

Re: Arrays in Postgres

#21

Post by fnl »

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.

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

Re: Arrays in Postgres

#22

Post by jeffdavis »

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.

"The more you put in the database black box, the more scalability problems you will encounter in the future."

I don't see a obvious relationship between using arrays to store product tags and scalability.

What method of solving this problem are you suggesting, and what are the scalability characteristics of that solution?

Databases don't go out of their way to hurt scalability. Databases are shared state, and scaling that is just hard. Unless the problem is easy, in which case it's easy no matter what you use.

Re: Arrays in Postgres

#23

Post by eckyptang »

jeffdavis wrote: post #22
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.

"The more you put in the database black box, the more scalability problems you will encounter in the future." I don't see a obvious relationship between using arrays to store product tags and scalability. What method of solving this problem are you suggesting, and what are the scalability characteristics of that solution? Databases don't go out of their way to hurt scalability. Databases are shared state, and scaling…

Looking at the way arrays are implemented, they can readily turn operations into O(M*N) rather than O(N). Even the PgSQL documentation suggests cases that will hurt you.

I would suggest that the relational model is maintained as there are no cases in which it isn't valid where arrays are and the optimiser is likely to come up with a better solution.

Relational databases (well all databases) have convenient tools which in the short term look good, but in the long term will hurt you.

Careful testing and feature selection is the alternative I am suggesting (from 20 years of experience with RDBMS platforms).

Re: Arrays in Postgres

#24

Post by eckyptang »

alexro wrote: post #19
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...

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

Re: Arrays in Postgres

#25

Post by eckyptang »

kiwidrew wrote: post #18
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 o…

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

Re: Arrays in Postgres

#26

Post by alexro »

eckyptang wrote: post #24
alexro wrote: post #19

Earlier quoted context omitted.

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

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

The general point still stands - scaling up is often a preferred solution and life proves it every day. Not so many companies enjoy facebook style grows, and even aren't viral at all.

Re: Arrays in Postgres

#27

Post by ocharles »

fnl wrote: post #21
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.

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

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.

Re: Arrays in Postgres

#28

Post by LarryMade »

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.

Re: Arrays in Postgres

#29

Post by EzGraphs »

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.

Re: Arrays in Postgres

#30

Post by guan »

ocharles wrote: post #27
fnl wrote: post #21

Earlier quoted context omitted.

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

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.

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?
Post reply on HN