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

PostgreSQL Magic

goto.project-a.com

Post reply on HN
Print view View original

51–60 of 67 posts

Re: PostgreSQL Magic

#51

Post by ahachete »

hobs wrote: post #21
ahachete wrote: post #17

Earlier quoted context omitted.

> I have to admit that I'm still not quite sure about arrays in relational databases. Don't get me wrong, I use them all the time, but it kinda feels like when you've got tables that you just know could be normalized more thoroughly. Why is that? There are many use cases for data (like vector data) which really needs an array. And it would be unwise to store it as columns. Think of, for example, matrix data or a prac…

Why wouldnt an unbounded set of values related to a sensor just fit in a one to many model of data? (Instead of a constantly updating row, simply inserting more rows) It seems like you would be trading one fat row for something the database does well (unless you always want all of the sensor data every time)

An array is basically a set of homogeneous fields indexed by number, which are probably accessed in constant-time. A relational table is a completely different beast. Even after disregarding performance (which might be an abysmal difference in many use cases) it's not obvious how to access by index without "hacks". And if you consider multidimensional data, things get tougher without arrays...

Re: PostgreSQL Magic

#52

Post by danmaz74 »

asolove wrote: post #32
greggyb wrote: post #22

Earlier quoted context omitted.

Window functions are not a Postgres thing, but part of the SQL standard with (varying, of course) support across most of the major RDBMS's. I've seen comments like this several times that seem to imply that Postgres is exceptional either due to having window functions, or like in this one where the tone sounds as if Postgres does them particularly better. I'm curious, as I do almost 100% of my production work in MS S…

I think it's largely a question of community. Many users of MS SQL or Oracle learned advanced SQL or platform-specific features which they take for granted. They work at companies that spend lots of money for DBs and hire people who use them very well. Postgres also has a core community of very knowledgeable folks. But most of its user base, and especially those new to using it, would otherwise use MySQL. They only k…

> so the fact that a free database has these cool features seems exciting to them

It IS exciting to them. And to anybody who can't afford Oracle or other expensive enterprise solutions.

Re: PostgreSQL Magic

#53

Post by dragonwriter »

greggyb wrote: post #31
yread wrote: post #28

Earlier quoted context omitted.

There are window functions in sql server, too https://www.simple-talk.com/sql/learn-sql-server/window-func...

I am well aware. My confusion is, therefore, why do window functions get called out specifically, fairly commonly, as a Postgres-specific piece of awesomeness. Window functions are awesome, and they are a part of the SQL standard, thus my confusion. As a sibling to your post pointed out, Postgres is often being compared to MySQL, which leads to highlighting the differences between these two, ignoring the features of…

> As a sibling to your post pointed out, Postgres is often being compared to MySQL, which leads to highlighting the differences between these two, ignoring the features of DB2, MS SQL Server, and Oracle.

License issues (and not just licensing costs, though that's often a factor) often mean that DB2, MS SQL, and Oracle are excluded options for non-technical reasons when MySQL and/or Postgres are under considerations.

Re: PostgreSQL Magic

#54

Post by hessenwolf »

timonv wrote: post #2

As a fellow Postgres amateur wizard, love the positive attention that postgres seems to be getting more and more, and some half databases less and less (unless you actually need map-reduce, ofcourse. You probably don't. /trollface) What I miss though in this article, and where I think postgres shines majorly compared to other rel dbs, are window functions. It allows you to apply a partition to a set. You can do some…

Do you mean Postgres's implementation of Excel pivot tables? ;)

Re: PostgreSQL Magic

#55

Post by Erwin »

pjungwir wrote: post #44
timonv wrote: post #6

Earlier quoted context omitted.

We use them a lot where the join table could be over a million rows. Saves a ton of performance and a join. Were talking seconds here on a 3-4s query before, ~1s after.

Yes, it can make a huge difference. I did this once when I had arrays of ~1 million floats and had to compute statistics on them. I wound up implementing a bunch of stats functions as C stored procedures that operate on arrays, and brought query times down from ~12 seconds to ~20 milliseconds: https://github.com/pjungwir/aggs_for_arrays/ Another time arrays are handy is when you don't know how many "columns" you need…

Thanks -- I had a a query on 9.1 using MEDIAN (implemented in pl/psql) going from 12 seconds to 0.2 seconds by switching to array_to_median(array_agg(the_column)). I found it was faster to actually query a million values and calculate the median in Python than to use that pl/psql MEDIAN so it's nice this can be done easier with array_agg + your thing.

Re: PostgreSQL Magic

#56

Post by threeseed »

davidgerard wrote: post #49
crc32 wrote: post #23

Earlier quoted context omitted.

I think because Postgres is commonly being considered as an alternative to MySQL rather than MSSQL or Oracle.

I dunno, getting the hell off Oracle to Postgres is fashionable as hell. We're doing it and EVERYTHING IS BETTER.

Half the reason you go with solutions like Oracle is because of (a) enterprise support and (b) easy access to talent pool. PostgreSQL has neither of these. So it may be fashionable but I don't know anyone who is doing it.

Re: PostgreSQL Magic

#57

Post by davidgerard »

threeseed wrote: post #56
davidgerard wrote: post #49

Earlier quoted context omitted.

I dunno, getting the hell off Oracle to Postgres is fashionable as hell. We're doing it and EVERYTHING IS BETTER.

Half the reason you go with solutions like Oracle is because of (a) enterprise support and (b) easy access to talent pool. PostgreSQL has neither of these. So it may be fashionable but I don't know anyone who is doing it.

That's half the justification. Aaand it turns out the justification doesn't hold against practice.

We're discovering that literally everything is better with Postgres. Mostly because instead of a single expensive point of failure, every app gets its own clustered PG pair. Because we can, because we don't have to think about licensing ever again.

Just everything not having to play nicely with anything else makes a huge difference.

The other nice thing is that PG is administerable by clear-thinking (and understand relational databases) non-specialists who can read a manual. You don't actually need big-ticket support unless you do.

And, guess what? Our Oracle support was most keen to offer Postgres support, because they too can tell which way the wind is blowing.

(PG 9.3 out of Ubuntu 14.04 repos. Failover pair with a primary and standby. Primary streams write-ahead log records to standby as they’re generated. Some script gaffer-tape to watch for primary failure and fail over (I think we haven’t ever yet actually had to invoke this). Conversions done by hand with ora2pg then faff and twiddling and unit tests. Gotchas: malformed sql that Oracle accepts but PG chokes on. All cobbled together just following the docs, almost certainly better ways to do all this.)

As for anyone else doing it ... we were buying AppDynamics (which is frickin' awesome btw) and talking to them about our plans to move from Oracle to PG. They said quite a few of their customers were thinking similarly. So maybe it's our own personal bubbles differing, but I think it's happening in at least some quarters.

Re: PostgreSQL Magic

#58

Post by dragonwriter »

threeseed wrote: post #56
davidgerard wrote: post #49

Earlier quoted context omitted.

I dunno, getting the hell off Oracle to Postgres is fashionable as hell. We're doing it and EVERYTHING IS BETTER.

Half the reason you go with solutions like Oracle is because of (a) enterprise support and (b) easy access to talent pool. PostgreSQL has neither of these. So it may be fashionable but I don't know anyone who is doing it.

> Half the reason you go with solutions like Oracle is because of (a) enterprise support and (b) easy access to talent pool. PostgreSQL has neither of these.

If you need enterprise support for Postgres, there are vendors that offer it (EnterpriseDB is probably the closest to a "first party" equivalent.)

Re: PostgreSQL Magic

#59

Post by davidgerard »

dragonwriter wrote: post #58
threeseed wrote: post #56

Earlier quoted context omitted.

Half the reason you go with solutions like Oracle is because of (a) enterprise support and (b) easy access to talent pool. PostgreSQL has neither of these. So it may be fashionable but I don't know anyone who is doing it.

> Half the reason you go with solutions like Oracle is because of (a) enterprise support and (b) easy access to talent pool. PostgreSQL has neither of these. If you need enterprise support for Postgres, there are vendors that offer it (EnterpriseDB is probably the closest to a "first party" equivalent.)

Our third-party Oracle support were most keen to offer PG support. They can tell which way the wind's blowing.

(We haven't taken them up on it. We seriously can't see how we'll need it. But if you do need it, it's there.)

Re: PostgreSQL Magic

#60

Post by lcswi »

hessenwolf wrote: post #54
timonv wrote: post #2

As a fellow Postgres amateur wizard, love the positive attention that postgres seems to be getting more and more, and some half databases less and less (unless you actually need map-reduce, ofcourse. You probably don't. /trollface) What I miss though in this article, and where I think postgres shines majorly compared to other rel dbs, are window functions. It allows you to apply a partition to a set. You can do some…

Do you mean Postgres's implementation of Excel pivot tables? ;)

tablefunc() :-)
Post reply on HN