9 ms·

PostgreSQL 8.4 Released

62 points by xenoterracide 17y ago

After many years of development, PostgreSQL has become feature-complete in many areas. This release shows a targeted approach to adding features (e.g., authentication, monitoring, space reuse), and adds capabilities defined in the later SQL standards. The major areas of enhancement are: * Windowing Functions * Common Table Expressions and Recursive Queries * Default and variadic parameters for functions * Parallel Restore * Column Permissions * Per-database locale settings * Improved hash indexes * Improved join performance for EXISTS and NOT EXISTS queries * Easier-to-use Warm Standby * Automatic sizing of the Free Space Map * Visibility Map (greatly reduces vacuum overhead for slowly-changing tables) * Version-aware psql (backslash commands work against older servers) * Support SSL certificates for user authentication * Per-function runtime statistics * Easy editing of functions in psql * New contrib modules: pg_stat_statements, auto_explain, citext, btree_gin

mattyb 17y ago
tsally, don't look! I was hoping to see simple built-in replication in 8.4 (didn't make it into 8.3, see here: http://it.toolbox.com/blogs/database-soup/postgresql-development-priorities-31886 http://it.toolbox.com/blogs/database-soup/postgresql-develop...), so I guess it's on the 8.5 wishlist.
- tdavis 17y ago
  Still, some awesome enhancements nonetheless! Congratulations are in order for all contributors and the community :)
- xenoterracide 17y ago
  I agree this is the main feature I was hoping would make it that didn't, too...
- mdasen 17y ago
  Part of the issue is that there is no such thing as "simple" replication unless one means "unreliable" replication. Simple replication is just shooting off the same SQL insert/update query on two servers. In the best scenario, that means that if you use a time function, you're likely to get two different results. In the worst case, it fails on one server and data gets out of sync. Really, it's not that PostgreSQL needs more replication or built-in replication. It needs better replication than any of the current solutions. pgpool, while great as a connection pooler, is a terrible replication solution. Slony-I is based off triggers and its communication costs grow in quadratically (O(n^2) - yuck!). Plus, Slony-I requires an immense amount of setup for every table and every key. Mammoth replicator, which seems to be the closest to the right track, has a website that doesn't show much life and only a beta release for download. Plus, it still looks like there's a good amount of setup. What we all really want is for PostgreSQL to implement a log-shipping based replication system where we can say "replicate this database" or "replicate these tables" to another server and have it just work. But there's nothing simple about that.
  - mattyb 17y ago
    What do you think about Londiste (https://developer.skype.com/SkypeGarage/DbProjects/SkyTools https://developer.skype.com/SkypeGarage/DbProjects/SkyTools)?
  - emmett 17y ago
    I completely agree there's nothing simple about that, but ever since we switched from Slony-I to Londiste at Justin.tv I couldn't be happier. It's been completely bulletproof, recovers from errors easily, and makes schema upgrades a breeze.
  - russss 17y ago
    Postgres already has a asynchronous log shipping replication system similar to MySQL's, but the rub is that you can't read from the slave, so it's only good as a "warm standby" solution. Work was happening to make it possible to read from the slave, and this was meant to go into 8.4. However, there were some issues and it got pulled from the release. Hopefully we'll see it in 8.5. This is the only real way MySQL is beating Postgres at the moment.
nradov 17y ago
It's a shame that PostgreSQL still lacks meaningful XML support. That's one critical area where the commercial relational database vendors are way ahead.
- sho 17y ago
  Why on earth would you store XML data in a database? XML is for when you have structured data but no database. Any structure you can store in XML is already catered for in conventional RDBMS design. And if you just want to store the string, there is nothing stopping you from doing that.
  - daveungerer 17y ago
    Because some database designs can be vastly simplified if they don't need to contain tables to map to every possible type of XML document your system handles. You can extract some of the data into a tables and store the whole document in a column to refer to later. MS SQL Server, since version 2005, has had the ability to do XPath queries on columns containing XML data. In an optimised manner. That's very handy in the average enterprise system that contains mounds of XML data in different schemas - you'd have an explosion of tables if you tried to store all that using conventional RDBMS design.
  - fizx 17y ago
    I have a table that stores xml documents. The document schema has evolved over time, and is a representation of a print-on-demand(able) brochure. In my documents table, I have a field called "title". I have an after save hook in my application code to update this field. If I had an XML-aware database, I could: "SELECT id, //node[@type='title'] AS title, /@version AS version FROM documents WHERE version=2;" This is more elegant than creating extra columns and hooks to support future queries, and mutilating your table with tens of extra columns.
    - sethg 17y ago
      PostgreSQL 8.3 (maybe earlier versions too, I haven't checked) has an xml data type. I haven't used it personally, but if I read the documentation correctly, you can do something like this: SELECT id, xpath('//node[@type=''title'']', doc) AS title, xpath('/@version', doc) AS version FROM documents WHERE version=2; (assuming your "documents" table has a "doc" field of type "xml") Documentation here: http://www.postgresql.org/docs/8.3/interactive/functions-xml.html http://www.postgresql.org/docs/8.3/interactive/functions-xml...
drusenko 17y ago
"A dump/restore using pg_dump is required for those wishing to migrate data from any previous release." Don't know much about postgresql, but that sounds fun...
- jawngee 17y ago
  It's one command to dump and one command to restore: pg_dump -Fc database > database.backup upgrade postgresql pg_restore -d database database.backup
  - neilc 17y ago
    You should use pg_dumpall rather than pg_dump, typically. Also, remember to use the pg_dump implementation from the new version of PostgreSQL (e.g. 8.4), not the old one (e.g. 8.3). There's also the new (beta) pg_migrator tool which allows upgrades to be performed without a dump + reload. http://pgfoundry.org/projects/pg-migrator/ http://pgfoundry.org/projects/pg-migrator/
sethg 17y ago
I am salivating over the new WITH RECURSIVE ... SELECT ... syntax (http://www.postgresql.org/docs/8.4/static/sql-select.html http://www.postgresql.org/docs/8.4/static/sql-select.html). There's a very hairy piece of C++ code in my group's codebase that grinds over a certain schema to perform a certain transitive closure, and if I can redo that operation in SQL, we might be able to change its status from "only godlike programmers may dare touch this code" to "only demigodlike programmers may dare touch this code".

62 points by xenoterracide 17y ago

mattyb 17y ago

tsally, don't look! I was hoping to see simple built-in replication in 8.4 (didn't make it into 8.3, see here: http://it.toolbox.com/blogs/database-soup/postgresql-development-priorities-31886 http://it.toolbox.com/blogs/database-soup/postgresql-develop...), so I guess it's on the 8.5 wishlist.

tdavis 17y ago
Still, some awesome enhancements nonetheless! Congratulations are in order for all contributors and the community :)
xenoterracide 17y ago
I agree this is the main feature I was hoping would make it that didn't, too...
mdasen 17y ago
Part of the issue is that there is no such thing as "simple" replication unless one means "unreliable" replication. Simple replication is just shooting off the same SQL insert/update query on two servers. In the best scenario, that means that if you use a time function, you're likely to get two different results. In the worst case, it fails on one server and data gets out of sync. Really, it's not that PostgreSQL needs more replication or built-in replication. It needs better replication than any of the current solutions. pgpool, while great as a connection pooler, is a terrible replication solution. Slony-I is based off triggers and its communication costs grow in quadratically (O(n^2) - yuck!). Plus, Slony-I requires an immense amount of setup for every table and every key. Mammoth replicator, which seems to be the closest to the right track, has a website that doesn't show much life and only a beta release for download. Plus, it still looks like there's a good amount of setup. What we all really want is for PostgreSQL to implement a log-shipping based replication system where we can say "replicate this database" or "replicate these tables" to another server and have it just work. But there's nothing simple about that.
- mattyb 17y ago
  What do you think about Londiste (https://developer.skype.com/SkypeGarage/DbProjects/SkyTools https://developer.skype.com/SkypeGarage/DbProjects/SkyTools)?
- emmett 17y ago
  I completely agree there's nothing simple about that, but ever since we switched from Slony-I to Londiste at Justin.tv I couldn't be happier. It's been completely bulletproof, recovers from errors easily, and makes schema upgrades a breeze.
- russss 17y ago
  Postgres already has a asynchronous log shipping replication system similar to MySQL's, but the rub is that you can't read from the slave, so it's only good as a "warm standby" solution. Work was happening to make it possible to read from the slave, and this was meant to go into 8.4. However, there were some issues and it got pulled from the release. Hopefully we'll see it in 8.5. This is the only real way MySQL is beating Postgres at the moment.

nradov 17y ago

It's a shame that PostgreSQL still lacks meaningful XML support. That's one critical area where the commercial relational database vendors are way ahead.

sho 17y ago
Why on earth would you store XML data in a database? XML is for when you have structured data but no database. Any structure you can store in XML is already catered for in conventional RDBMS design. And if you just want to store the string, there is nothing stopping you from doing that.
- daveungerer 17y ago
  Because some database designs can be vastly simplified if they don't need to contain tables to map to every possible type of XML document your system handles. You can extract some of the data into a tables and store the whole document in a column to refer to later. MS SQL Server, since version 2005, has had the ability to do XPath queries on columns containing XML data. In an optimised manner. That's very handy in the average enterprise system that contains mounds of XML data in different schemas - you'd have an explosion of tables if you tried to store all that using conventional RDBMS design.
- fizx 17y ago
  I have a table that stores xml documents. The document schema has evolved over time, and is a representation of a print-on-demand(able) brochure. In my documents table, I have a field called "title". I have an after save hook in my application code to update this field. If I had an XML-aware database, I could: "SELECT id, //node[@type='title'] AS title, /@version AS version FROM documents WHERE version=2;" This is more elegant than creating extra columns and hooks to support future queries, and mutilating your table with tens of extra columns.
  - sethg 17y ago
    PostgreSQL 8.3 (maybe earlier versions too, I haven't checked) has an xml data type. I haven't used it personally, but if I read the documentation correctly, you can do something like this: SELECT id, xpath('//node[@type=''title'']', doc) AS title, xpath('/@version', doc) AS version FROM documents WHERE version=2; (assuming your "documents" table has a "doc" field of type "xml") Documentation here: http://www.postgresql.org/docs/8.3/interactive/functions-xml.html http://www.postgresql.org/docs/8.3/interactive/functions-xml...

drusenko 17y ago

"A dump/restore using pg_dump is required for those wishing to migrate data from any previous release." Don't know much about postgresql, but that sounds fun...

jawngee 17y ago
It's one command to dump and one command to restore: pg_dump -Fc database > database.backup upgrade postgresql pg_restore -d database database.backup
- neilc 17y ago
  You should use pg_dumpall rather than pg_dump, typically. Also, remember to use the pg_dump implementation from the new version of PostgreSQL (e.g. 8.4), not the old one (e.g. 8.3). There's also the new (beta) pg_migrator tool which allows upgrades to be performed without a dump + reload. http://pgfoundry.org/projects/pg-migrator/ http://pgfoundry.org/projects/pg-migrator/

sethg 17y ago

I am salivating over the new WITH RECURSIVE ... SELECT ... syntax (http://www.postgresql.org/docs/8.4/static/sql-select.html http://www.postgresql.org/docs/8.4/static/sql-select.html). There's a very hairy piece of C++ code in my group's codebase that grinds over a certain schema to perform a certain transitive closure, and if I can redo that operation in SQL, we might be able to change its status from "only godlike programmers may dare touch this code" to "only demigodlike programmers may dare touch this code".