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

61–67 of 67 posts

Re: PostgreSQL Magic

#61

Post by simonpantzare »

rosser wrote: post #3

Quibble: the "now()" function doesn't return the time of statement start; it returns the time of transaction start. $ psql -q rosser=# begin; rosser=# select now(); now ------------------------------- 2015-09-11 02:28:54.262142-07 (1 row) rosser=# select now(); now ------------------------------- 2015-09-11 02:28:54.262142-07 (1 row)

Came here to mention this. Aside from now()/transaction_timestamp() and clock_timestamp() there's also statement_timestamp() that returns the start time of the current statement.

Re: PostgreSQL Magic

#62

Post by threeseed »

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

Sure. But they are few and far between and absolutely do not have the same SLAs as someone like an Oracle, NEC, IBM etc. They are generally mom+pop shops. It doesn't really cut it for enterprises which is generally where you are finding Oracle instances.

Re: PostgreSQL Magic

#63

Post by jeltz »

mhd wrote: post #4

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. Also: If someone has a good version-control wrapper for stored procedures, that would be swell. And while I'm doing the wishful thinking shtick, maybe a Coffeescript-like preprocessor and…

Array are very useful in ad-hoc queries since you can create arrays with the array_agg() aggregate function and then use those arrays for lot's of handy things.

Re: PostgreSQL Magic

#64

Post by threeseed »

davidgerard wrote: post #57
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.

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

AppDynamics which I know very well is used by web shops. Very few of those are using Oracle and it wouldn't surprise me at all if they were moving to something else.

The majority of Oracle instances however are in the back rooms of enterprises.

Re: PostgreSQL Magic

#65

Post by davidgerard »

threeseed wrote: post #64
davidgerard wrote: post #57

Earlier quoted context omitted.

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

AppDynamics which I know very well is used by web shops. Very few of those are using Oracle and it wouldn't surprise me at all if they were moving to something else. The majority of Oracle instances however are in the back rooms of enterprises.

Yeah, that's pretty plausible.

Key point: it's possible. And unlike MySQL, Postgres is actually a proper database that can do what Oracle, MSSQL etc do. (We're also looking askance at our remaining MSSQL and have replaced one instance of the two with PG.)

Re: PostgreSQL Magic

#66

Post by marcosdumay »

snuxoll wrote: post #42
marcosdumay wrote: post #27

Earlier quoted context omitted.

They are incredibly useful in stored procedures. Also at indexes, pg's full text search is completely reliant on array indexes, ditto for the json storage. I think I've never used them as column type, but I can imagine a few uses there too.

> I think I've never used them as column type, but I can imagine a few uses there too. A lot of cases where you would use a one:many are useful to store in an array instead. Tags would be a good example, multi-select lists, etc.

Except that if you'll want to search by those tags, using an array, instead of a one:many means you'll need a bigger and more expensive index.

It's good for some kind of list that you won't search by. That's very limiting, but not an empty set.

Post reply on HN