10 ms·

PostgreSQL 9.2 released

271 points by lest 14y ago

einhverfr 14y 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.
- masklinn 14y ago
  Could you talk about them further and explain why they're your favorite new features?
  - jeffdavis 14y 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-as-object.html 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)
    - einhverfr 14y ago
      Managing the forward/backward links in a series this long while it is still coming out is a pain. They will be added as the series draws to a close next week though.
  - einhverfr 14y 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 14y 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?
  - fdr 14y 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_xxx 14y 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.
lest 14y 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.
r4vik 14y ago
direct link to what's new: http://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.2 http://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9...
mattdeboard 14y 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
- masklinn 14y 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 14y 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.
    - pilif 14y ago
      I'm sure something like that will be added. One of the issues there facing is that hstore is a string => string map, so a conversion from json to that will be lossy (i.e. 'that "true" in hstore, is that the string "true" or a converted boolean true from json?'). What you can do right now though is use PL/V8 to query the JSON fields. Then you can use functional indexes to still being able to speed up queries. Yes. You could that before but now there's a guarantee that a field of type json contains just that, meaning that your application logic will get simpler.
      - masklinn 14y ago
        > One of the issues there facing is that hstore is a string => string map, so a conversion from json to that will be lossy Or the insertion/conversion routine can assert that the JSON object is a string:string mapping only.
EvanAnderson 14y 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.
- r4vik 14y ago
  also overlooked is the massive speed improvement for in memory sorting.. that's what I've been waiting for.
- jeltz 14y 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.
  - jeffdavis 14y ago
    I'll be speaking on this use case next week at Postgres Open ( http://postgresopen.org http://postgresopen.org ) as part of a larger demo of temporal databases in postgresql. Jonathan S. Katz will also be presenting on Range Types.
- joevandyk 14y 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 14y 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 14y ago
  yet it will not cease comparsions of postgres to mysql
- joevandyk 14y 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 14y ago
    Can you elaborate a little bit on how an architecture like that would look like? I had great hopes for CouchDB once.
    - y0ghur7_xxx 14y ago
      HTML/JS frontend calls simple node server that does nothing but call a stored procedure like spc_handle_request(req_headers, req_body, http_method, ...) stored procedure does simple routing and does something like insert into table (a, b, c) values (to_json(req_body).a, to_json(req_body).b, to_json(req_body).c); or to_json(select * from emp); Time to implement simple crud app: 10 minutes.
  - calinet6 14y 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 http://code.google.com/p/plv8js/wiki/PLV8 It's very, very close.
rabidsnail 14y 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 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 14y 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
- joevandyk 14y ago
  what does '\x auto' do?
  - jeffdavis 14y 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.
    - joevandyk 14y ago
      Nice! I tried using it for a result that contains really wide columns. I'm seeing a screen full of hyphens separating the rows. You'd think that the hyphens would stretch across just one line of the screen, instead of across the whole result set. See https://img.skitch.com/20120910-fn1abpp3w94yhg63hc8yemt4a4.png https://img.skitch.com/20120910-fn1abpp3w94yhg63hc8yemt4a4.p...
      - jeffdavis 14y ago
        Oh, interesting. That's a problem for very wide fields, which aren't going to be handled very well even using \x. \x was meant to handle large numbers of fields, or slightly wider fields. But you're right, maybe that could be cleaned up a little more.
jeltz 14y 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 14y 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 14y 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 14y ago
  pgAdmin not work on OS X? I'm pretty sure it does. Just found this: http://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools http://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQ...
  - einhverfr 14y 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 14y ago
  Navicat[1] works well for me. Plus side, I can use the same app for all databases. http://www.navicat.com http://www.navicat.com
- felideon 14y 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.
tubbo 14y ago
That JSON data type looks pretty awesome!
tosivakumar 14y 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 14y ago
mysql only gets mentioned 2 (now 3) times in this thread? oracle seems to be doing a job!
- flyinRyan 14y ago
  Why would anyone bring up mysql?

271 points by lest 14y 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.

masklinn 14y ago
Could you talk about them further and explain why they're your favorite new features?
- jeffdavis 14y 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-as-object.html 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)
  - einhverfr 14y ago
    Managing the forward/backward links in a series this long while it is still coming out is a pain. They will be added as the series draws to a close next week though.
- einhverfr 14y 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 14y 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?
- fdr 14y 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_xxx 14y 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.

lest 14y 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.

r4vik 14y ago

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

mattdeboard 14y 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

masklinn 14y 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 14y 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.
  - pilif 14y ago
    I'm sure something like that will be added. One of the issues there facing is that hstore is a string => string map, so a conversion from json to that will be lossy (i.e. 'that "true" in hstore, is that the string "true" or a converted boolean true from json?'). What you can do right now though is use PL/V8 to query the JSON fields. Then you can use functional indexes to still being able to speed up queries. Yes. You could that before but now there's a guarantee that a field of type json contains just that, meaning that your application logic will get simpler.
    - masklinn 14y ago
      > One of the issues there facing is that hstore is a string => string map, so a conversion from json to that will be lossy Or the insertion/conversion routine can assert that the JSON object is a string:string mapping only.

EvanAnderson 14y 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.

r4vik 14y ago
also overlooked is the massive speed improvement for in memory sorting.. that's what I've been waiting for.
jeltz 14y 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.
- jeffdavis 14y ago
  I'll be speaking on this use case next week at Postgres Open ( http://postgresopen.org http://postgresopen.org ) as part of a larger demo of temporal databases in postgresql. Jonathan S. Katz will also be presenting on Range Types.
joevandyk 14y 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 14y 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 14y ago
yet it will not cease comparsions of postgres to mysql
joevandyk 14y 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 14y ago
  Can you elaborate a little bit on how an architecture like that would look like? I had great hopes for CouchDB once.
  - y0ghur7_xxx 14y ago
    HTML/JS frontend calls simple node server that does nothing but call a stored procedure like spc_handle_request(req_headers, req_body, http_method, ...) stored procedure does simple routing and does something like insert into table (a, b, c) values (to_json(req_body).a, to_json(req_body).b, to_json(req_body).c); or to_json(select * from emp); Time to implement simple crud app: 10 minutes.
- calinet6 14y 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 http://code.google.com/p/plv8js/wiki/PLV8 It's very, very close.

rabidsnail 14y 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 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 14y 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

joevandyk 14y ago
what does '\x auto' do?
- jeffdavis 14y 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.
  - joevandyk 14y ago
    Nice! I tried using it for a result that contains really wide columns. I'm seeing a screen full of hyphens separating the rows. You'd think that the hyphens would stretch across just one line of the screen, instead of across the whole result set. See https://img.skitch.com/20120910-fn1abpp3w94yhg63hc8yemt4a4.png https://img.skitch.com/20120910-fn1abpp3w94yhg63hc8yemt4a4.p...
    - jeffdavis 14y ago
      Oh, interesting. That's a problem for very wide fields, which aren't going to be handled very well even using \x. \x was meant to handle large numbers of fields, or slightly wider fields. But you're right, maybe that could be cleaned up a little more.

jeltz 14y 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 14y 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 14y 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 14y ago
pgAdmin not work on OS X? I'm pretty sure it does. Just found this: http://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools http://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQ...
- einhverfr 14y 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 14y ago
Navicat[1] works well for me. Plus side, I can use the same app for all databases. http://www.navicat.com http://www.navicat.com
felideon 14y 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.

tubbo 14y ago

That JSON data type looks pretty awesome!

tosivakumar 14y 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 14y ago

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

flyinRyan 14y ago
Why would anyone bring up mysql?