PostgreSQL 9.2 released

http://www.postgresql.org/about/news/1415/

by lest • 14 years ago

271 77 14 years ago

lest• 14 years ago

"PostgreSQL 9.2 will ship with native JSON support, covering indexes, replication and performance improvements, and many more features. We are eagerly awaiting this release and will make it available in Early Access as soon as it’s released by the PostgreSQL community," said Ines Sombra, Lead Data Engineer, Engine Yard.

r4vik2• 14 years ago

direct link to what's new: http://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9...

mattdeboard4• 14 years ago

I am actually pretty excited about the native JSON support, and overall I am a huge fan of Postgres, but this is the most press-release-y press release ever* . By that I mean that the quotes are way too "perfect", the kind you only see in press releases. Some PR or marketing guy wrote them then showed them to the person to whom they'd be attributed to get their ok. Nothing inherently wrong with it, just struck me as funny.

* having written more than my share of press releases in my time

masklinn6• 14 years ago

> I am actually pretty excited about the native JSON support

There really isn't much to it yet, it's basically a varchar field with JSON validation, it's not like you can query or index it.

I actually find the `row_to_json` and `array_to_json` functions more interesting for now (though strangely enough there's no `hstore_to_json`)

StavrosK• 14 years ago

Eh, if you want to index it you have the hstore. I do wonder why they don't deserialize to an hstore type, though.

porlw• 14 years ago

I think you can index JSON using a function based index, which will probably cover a lot of use-cases.

joevandyk6• 14 years ago

You can query and index json columns using plv8. http://lanyrd.com/coverage/?speakers=leinweber&topics=js... has more details.

jaylevitt• 14 years ago

As one of the main contribs noted in a previous HN article, the important part was to get JSON conversion into core, full stop - because extensions (like hstore) can't declare dependencies on other extensions. Having JSON in/out in core is an enabling feature for all the rest.

EvanAnderson2• 14 years ago

If the proposed HTTP REST API (http://wiki.postgresql.org/wiki/HTTP_API) makes it into the product then the native JSON support is going to be even more useful. (I'm excited to see that there's an experimental attempt at it, too: https://github.com/bjornharrtell/postgresql-http-server)

JoelJacobson• 14 years ago

Here is another attempt, which exposes all your SPs as a JSON-RPC API. http://search.cpan.org/~joeljac/DBIx-Pg-CallFunction-0.012/s...

mattdeboard4• 14 years ago

Very interesting, the rest api would make psql into a total feature-for-feature replacement of mongo for us.

don_draper• 14 years ago

I think Postgres doesn't do enough to toot it's own horn. Look at it's website. Come on. Spice things up a bit. I'm inclined to think hardcore users purposely downplay the product because they see it as their secret sauce.

calinet62• 14 years ago

It actually looks extremely professional. In my experience, when you're trying to convince a large company to use a platform, you want a clean, business-case-focused site without excessive flair. Postgres nails it on those points, I think.

It's reminiscent of IBM, which is a huge compliment in this context.

fdr3• 14 years ago

The last major web site update was in late 2004, according to my bisection of the wayback machine. It's a project, not a company, and there is no marketing or design budget. For example, Magnus, who does some Windows maintenance, is a principal author of the new pg_receivexlog, and knows a lot about the SSL support also worries about the web-based mailing list archive program and the CSS of the manual, if memory serves.

Nobody's holding back anything. It would be wonderful if someone wanted to contribute their design skills to the web page or documentation -- talking to the mailing list pgsql-www is probably the way to do that. I am positive such a person would receive profuse appreciation, and maybe can parlay that into personal advantage if the fulfillment of the impact such improvements would have is not quite material enough by itself.

The project runs its own web site infrastructure (many regimes have come and went in Postgres' history, only semi-recently was pgfoundary decommissioned for the purpose of new projects), and it's a little arcane -- but if someone really wanted to take ownership to move things beyond maintenance into progress, perhaps changes could be made.

wulczer2• 14 years ago

> Some PR or marketing guy wrote them then showed them to the person to whom they'd be attributed to get their ok

I happen to know the guy that AFAIK was in charge of preparing the press release and he's actually a major contributor to the codebase, a geek par excellence and have been nagging people to get him quotes on the development mailing list.

The fact that apart from hacking C code he also knows how to write a catchy press release just makes him all the awesomer :)

carterschonwald• 14 years ago

This. Good devs who are able to better pimp their great work (such as anything in this Postgres release), are the devs that get the coolest job offers. So props to the great work by the Postgres team and their tasteful fact based self promotion. :-)

mattdeboard4• 14 years ago

Hey man, I was PR & marketing for 6 years before I became a programmer. Valuable skillset to have.

conesus• 14 years ago

I'm one of those quotes (talking about NewsBlur) and I just tried to give back to an amazing tool in my tech stack by being effusively honest. Honestly, I think they deserve more credit and was a bit surprised that they chose my quote over the dozens of undoubtedly heavier stacks that also use Postgres. Surprised in the good way.

EvanAnderson2• 14 years ago

I'm always impressed by the PostgreSQL team.

Personally, I'm excited about the range types and I can see immediate usefulness for them. My own applications aside, anything that helps developers create schemas that are better able to handle temporal data is a good thing.

r4vik2• 14 years ago

also overlooked is the massive speed improvement for in memory sorting.. that's what I've been waiting for.

jeltz2• 14 years ago

Range types combined with exclusion constraints solve the problem of not allowing overlapping bookings using a database constraint. The solution is simple, clean, and flexible unlike the workarounds.

Example of adding such a constraint:

  ALTER TABLE reservation ADD EXCLUDE USING gist (room WITH =, during WITH &&);

In this example a room cannot be double booked.

EDIT: This is a feature entirely unique to PostgreSQL.

jeffdavis4• 14 years ago

I'll be speaking on this use case next week at Postgres Open ( http://postgresopen.org ) as part of a larger demo of temporal databases in postgresql.

Jonathan S. Katz will also be presenting on Range Types.

joevandyk6• 14 years ago

For example: if you have a coupon that has a start and end time (but the end time is optional), typically, you'd have a start_at and end_at datetime columns. The query for checking for an active coupon would be:

    select * from coupons 
    where start_at < now() and (end_at is null or end_at > now())

Now, you can have a single column that represents the range of time that the coupon is active.

    select * from coupons where now() <@ duration;

Plus, exclusion contraints. So you could prevent the database from storing a coupon that was active at the same time as another coupon.

metabrew• 14 years ago

Since postgres has basic json type support now, and PL/Javascript exists, it's only a matter of time until an extension appears that lets you deploy javascript applications directly to the database.

Who needs CouchDB or Node.js when you can just say CREATE EXTENSION 'couchnodegres.js'

dotborg2• 14 years ago

yet it will not cease comparsions of postgres to mysql

joevandyk6• 14 years ago

Once the database can start receiving and returning json, then you can treat it as a webservice and not have the client involved in SQL. I think this is huge.

recuter• 14 years ago

Can you elaborate a little bit on how an architecture like that would look like? I had great hopes for CouchDB once.

calinet62• 14 years ago

Just FYI, this is already half-true:

"With PostgreSQL 9.2, query results can be returned as JSON data types."

It also supports the PL/V8 stored procedure format, which is Javascript http://code.google.com/p/plv8js/wiki/PLV8

It's very, very close.

einhverfr8• 14 years ago

My two favorite features are not so high on the PR docs though.

The first is SECURITY BARRIER and LEAKPROOF which gives us an ability to rethink how to multi-tenant applications. This is a game changer and will get even better in future versions I am sure.

The second is NO INHERIT constraints, which I will certainly be making good use of. It is also a complete game changer when it comes to table inheritance and partitioning, and my main use will be things like CHECK (false) NOINHERIT to ensure that a table in fact never has rows of its own.

There is an amazing amount of good stuff going on around Postgres right now. Postgres-XC was recently released, and more. It is an amazing data modelling platform and ORDBMS.

masklinn6• 14 years ago

Could you talk about them further and explain why they're your favorite new features?

jeffdavis4• 14 years ago

Regarding the inheritance features, he's been writing an extensive series on the subject recently:

http://ledgersmbdev.blogspot.com/2012/08/intro-to-postgresql...

(though he should provide forward/backward links in the posts -- as it stands you need to find the other parts in the archive links on the right)

einhverfr8• 14 years ago

The basic thing is this:

One area where Oracle is ahead is in multi-tenant applications. They have an approach where you can build filters on data based on specific criteria. You can do this on PostgreSQL too but the way you have to do it before this either involves add-ons or actual partitions of data, or tweaks to prevent building functions altogether.

Security barriers are important in catching up to Oracle in this regard and they allow us to build more integrated multi-tenant applications with greater access to the db by the tenants.

NO INHERIT constraints open up a fairly large area of PostgreSQL for use or misuse, because you can now partition a primary key between parent and child declaratively. My own use will be to prevent inserts into the parent table directly. Table inheritance is a really neat feature if you use it in non-traditional ways. It is not particularly useful for its advertised use (set/subset stuff outside of partitioning). However what it allows you to do is to compose your tables out of smaller re-usable pieces each of which can have complex derivations of data attached. For example, if you want full text search on comments on a lot of your tables, you can create a consistent, centralized interface for this using table inheritance.

papsosouid• 14 years ago

>The first is SECURITY BARRIER and LEAKPROOF which gives us an ability to rethink how to multi-tenant applications

It doesn't really let us re-think it does it? It just closes a hole in how you would have typically done it anyways. Is there any way to solve the problem of having to create a new connection as company_X_user for every single request?

fdr3• 14 years ago

I think a number of people might find such functionality useful -- every once in a while I run across people with tens or hundreds of thousands of schemas, because a program written to query against a particular schema is more convincing and easy to audit than row-based multi-tenancy.

The problem is that there are performance and tooling issues when you have that many database objects, so those people can end up sad, even though the model was one they basically were happy with.

So I think adding database features for lucid multi-tenant work while reducing the risk of cross-tenant leakage is a movement in a good direction.

y0ghur7_xxx3• 14 years ago

> Is there any way to solve the problem of having to create a new connection as company_X_user for every single request?

Yes you can accomplish that with Veil, but I agree, it would be nice if it was built in, but an add-on is ok too.

rabidsnail• 14 years ago

I was expecting the json support, but SP-GiST is a very welcome surprise. http://www.postgresql.org/docs/9.2/static/spgist-intro.html

User-extensible spacial index types. This makes Postgres perfect for online machine learning.

forgotmyhnlogin• 14 years ago

The absolute best feature of 9.2 is that you can now add \x auto to your psqlrc file and never have to suffer unreadable results again

joevandyk6• 14 years ago

what does '\x auto' do?

jeffdavis4• 14 years ago

It's a display feature in the "psql" client program.

Normal result:

     column1 | column2 | column3 
    ---------+---------+---------
           1 | a       |     9.9
           2 | b       |    19.9
    (2 rows)

Using \x:

    -[ RECORD 1 ]-
    column1 | 1
    column2 | a
    column3 | 9.9
    -[ RECORD 2 ]-
    column1 | 2
    column2 | b
    column3 | 19.9

The first form is tabular and works well for a few columns; but doesn't work well when there are many columns, because the lines start to wrap. So you use \x for wide tables to make the result readable (but, obviously, fewer rows are shown at a time).

Using "\x auto" automatically chooses which format to use based on your terminal width.

willlll• 14 years ago

\x in psql switches if things are like a table or a list. Before you'd have to run a query and see that you cant read it, then hit \x and then run the query again. Now you can just have it figure out which one to show you based on your terminal size

jeltz2• 14 years ago

One thing I love about PostgreSQL development is all the small nice fixes added in every version.

Of the small fixes in 9.1 my personal favorite is probably the cleanup of pg_stat_activity. There are also many other nice small fixes like improved tab completion for some commands and the ability to set environment variables in psql.

craigkerstiens• 14 years ago

We've been pretty excited about this release to come for some time at Heroku as its loaded with great features. In addition to the JSON datatype here's a bit of a longer list of features that are pretty noteworthy in the release:

- Allow libpq connection strings to have the format of a URI

- Add a JSON data type

- Allow the planner to generate custom plans for specific parameter values even when using prepared statements

- Add the SP-GiST (Space-Partitioned GiST) index access method

- Add support for range data types

- Cancel queries if clients get disconnected

- Add CONCURRENTLY option to DROP INDEX

- Add a tcn (triggered change notification) module to generate NOTIFY events on table changes

- Allow pg_stat_statements to aggregate similar queries via SQL

- text normalization. Users with applications that use non-parameterized SQL will now be able to monitor query performance without detailed log analysis.

spitfire• 14 years ago

Now someone please make usable tools for it on OSX. Postgres badly needs front-end tools of the quality of sequel pro.

I am a huge fan of Postgres, it's never let me down. But data exploration, ad hoc querying and such is a pain in psql. These tools are badly needed.

mhurron• 14 years ago

pgAdmin not work on OS X? I'm pretty sure it does.

Just found this: http://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQ...

einhverfr8• 14 years ago

The complaint usually is "it doesn't look like a native app." Mac users are famously picky about only using apps that look native ;-)

hcarvalhoalves• 14 years ago

Navicat[1] works well for me. Plus side, I can use the same app for all databases.

http://www.navicat.com

felideon• 14 years ago

I used Navicat until I finally decided to try M-x sql-postgres in conjunction with a SQL Mode 'scratch' buffer from which I can simply C-c C-c.

I'm not sure about Sequel Pro, but being able to [reverse] incremental search on \d and \dt is a blessing.

If for some reason you want to eyeball sample data for a table with a ton of columns that won't fit in one screen, then a GUI's resizable columns are nice. But usually I just type of the columns of interest — us programmers are usually good typists.

tosivakumar• 14 years ago

We are excited about Cascading Replication because it reduces network data transfer over WAN when we have multiple Read Replicas within and across datacenters.

gtirloni• 14 years ago

mysql only gets mentioned 2 (now 3) times in this thread? oracle seems to be doing a job!

flyinRyan• 14 years ago

Why would anyone bring up mysql?

PostgreSQL 9.2 released

Related Stories