PostgreSQL 9.3 Beta 1 Released • Hacker News Board

Re: PostgreSQL 9.3 Beta 1 Released

#51

Post by jeffdavis » Mon, May 13, 2013, 4:39 PM UTC

masklinn wrote: post #23

jeltz wrote: post #19

Earlier quoted context omitted.

PostgreSQL 9.3 adds functions for accessing JSON data without having to use plv8js.

Yeah but since it's just being added, it precludes "NoSQL guys" having built upon it, which is the original complaint.

Another intepretation of zapov's comment is that it's a shame the NoSQL projects didn't try achieving their goals by building postgres extensions (or even adding new features).

A reasonable question. MongoDB could have focused their energies on really good JSON support in postgres and making postgres sharding easier.

Re: PostgreSQL 9.3 Beta 1 Released

#52

Post by twerquie » Mon, May 13, 2013, 4:43 PM UTC

jeffdavis wrote: post #48

So, what's still missing in postgres? To make the responses as constructive as possible, please also specify: * Whether you currently use postgres, and what other systems you use (presumably ones that do have the feature that is missing in postgres) * Whether you currently need the feature, or whether you anticipate the need in the future, or whether you expect that other people will need it * Whether the missing fea…

Multi-master in the core distribution.

Re: PostgreSQL 9.3 Beta 1 Released

#53

Post by pjungwir » Mon, May 13, 2013, 4:48 PM UTC

jeffdavis wrote: post #48

So, what's still missing in postgres? To make the responses as constructive as possible, please also specify: * Whether you currently use postgres, and what other systems you use (presumably ones that do have the feature that is missing in postgres) * Whether you currently need the feature, or whether you anticipate the need in the future, or whether you expect that other people will need it * Whether the missing fea…

I'd love to see on-disk bitmap indexes. I noticed there was an attempt to add these around 2009 but it was abandoned after being (apparently) nearly completed. [1,2] I'd like to see these because it would make Postgres better at data warehouse applications. I've been thinking about volunteering to add them myself, but I've never looked at the Postgres codebase before, so it seems a bit ambitious.

A minor feature I'd like to see is to allow ORDER BY on UPDATE statements to avoid deadlocks. If you have two multi-row UPDATEs that hit the same rows but in different order, you've got a classic deadlock setup. Apparently you can sometimes control UPDATE ordering by persuading Postgres to use certain query plans if you really know what you're doing, but that seems like an undesirable requirement for most users.

To answer your bullet points, Postgres is my #1 database choice, but I've also used Oracle and MySQL many times and sometimes still do. Neither of these features are things that are preventing me from using Postgres, but I've sure wished they were there. In particular bitmap indexes seem useful to many people.

[1] http://www.postgresql.org/message-id/20090111104442.GA3503@e... [2] http://www.postgresql.org/message-id/20081101000154.GO27872@...

Re: PostgreSQL 9.3 Beta 1 Released

#54

Post by sheff » Mon, May 13, 2013, 4:48 PM UTC

jeffdavis wrote: post #48

So, what's still missing in postgres? To make the responses as constructive as possible, please also specify: * Whether you currently use postgres, and what other systems you use (presumably ones that do have the feature that is missing in postgres) * Whether you currently need the feature, or whether you anticipate the need in the future, or whether you expect that other people will need it * Whether the missing fea…

I use Postgres for pretty much every project possible, as well as using Oracle for consulting clients.

The main feature which I'd like the PG development team to look at next is implementing a more comprehensive partitioning solution.

As far as remaining competitive with the commercial database vendors, the one thing that is the primary reason I have seen for enterprises choosing to reluctantly pay for Oracle over using Postgres for free is RAC (Real Application Clusters). Active/active clustering of DB nodes seems to hit a sweet spot for businesses wanting uptime and scalability without some of the downsides of physical replication.

Re: PostgreSQL 9.3 Beta 1 Released

#55

Post by papsosouid » Mon, May 13, 2013, 4:49 PM UTC

masklinn wrote: post #9

The short log is missing one of the most important and awesome new feature to application writers: a bunch of new PQresultErrorFields[0] on constraint failure, providing access to (context-dependent) the raw schema name, table name, column name, constraint name and datatype involved. Previously these remained locked into the database and to get programmatically-useable info (for logging, better error messages or to t…

Wow, that is awesome and is something I've been worrying about recently. However, there is a second part that is still missing that would allow for actual postgresql application development. There needs to be a way to check all constraints and report all failures, rather than just stopping at the first error. Without that, users submit data, get an error for one field, fix it and resubmit, get an error for the next field, etc, etc. Reporting all the errors the first time is really important for usability. So right now you have to duplicate all the database constraints in your app, which is a huge source of code duplication (and thus bugs).

Re: PostgreSQL 9.3 Beta 1 Released

#56

Post by jeffdavis » Mon, May 13, 2013, 4:50 PM UTC

orf wrote: post #26

The "User-defined background workers for automating database tasks" feature sounds very interesting: http://www.postgresql.org/docs/9.3/static/bgworker.html I wonder if it would be possible to plug a python/node interpreter into that.

"I wonder if it would be possible to plug a python/node interpreter into that."

Sure, why not?

This is a great feature because it is a significant extensibility improvement. And that's what postgres is really about: the idea that any user/developer with an idea has many powerful APIs that can help them achieve that without waiting for anyone else to agree that the idea is good.

Re: PostgreSQL 9.3 Beta 1 Released

#57

Post by joevandyk » Mon, May 13, 2013, 4:52 PM UTC

jeffdavis wrote: post #48

So, what's still missing in postgres? To make the responses as constructive as possible, please also specify: * Whether you currently use postgres, and what other systems you use (presumably ones that do have the feature that is missing in postgres) * Whether you currently need the feature, or whether you anticipate the need in the future, or whether you expect that other people will need it * Whether the missing fea…

https://news.ycombinator.com/item?id=5699748 brings up a good point.

Re: PostgreSQL 9.3 Beta 1 Released

#58

Post by pjungwir » Mon, May 13, 2013, 4:55 PM UTC

effn wrote: post #50

btown wrote: post #27

Earlier quoted context omitted.

Where would one find out if anyone is working on a patch for ActiveRecord?

ActiveRecord doesn't even support constraints in the first place.

Shameless plug: here is a gem I wrote to manage foreign keys and CHECK constraints (and also indexes) in a Rails project:

https://github.com/pjungwir/db_leftovers

Re: PostgreSQL 9.3 Beta 1 Released

#59

Post by jkbyc » Mon, May 13, 2013, 4:59 PM UTC

Refefer wrote: post #20

Materialized Views! Well, sort of; it appears they don't automatically refresh when data has been updated in the underlying tables ala Oracle's implementation, but this gets you rather close.

Implementing efficient updates of materialized views is difficult. They have to support recursive queries, aggregation, and probably even recursive views that they list as new in "Additional Features."

Algorithms for updates of materialized views exist since at least 20 years but maybe they don't cover all these cases efficiently.

Re: PostgreSQL 9.3 Beta 1 Released

#60

Post by jl6 » Mon, May 13, 2013, 5:08 PM UTC

sickpig wrote: post #35

mintyfresh wrote: post #12

Earlier quoted context omitted.

In that spirit, what are the best resources for getting started and diving in to working with PostgreSQL?

Postgresql documentation is terrific, really [1]. Then you can also use pgsql wiki [2]. Other useful resources are the searchable mailing lists archive [3]. Have a look also to pgcon.org web site, it's fully packed of presentation material (mainly pdf slides) gathered since 2007, just to give you an example look at Tom Lane's " http://www.pgcon.org/2011/schedule/events/350.en.html [4] Speaking of books a few of the b…

The docs really are great. I once downloaded the whole lot as a PDF and read it from the beginning on my phone during downtime (like 15 mins on the train). Read all the way through to the parts that discuss internals then stopped. I now appreciate it as a relational database, not just as a storage backend.