8 ms·

PostgreSQL 9.1 released

284 points by wulczer 15y ago

pilif 15y ago
(Mostly) like a clockwork: A new year, a new release. And like every year before we find a beautiful collection of new stuff to play with. Even better this time around: It's looking as if the next release of Ubuntu will get 9.1 packaged which spares me from manually packaging or using a PPA this time around. The new features each release introduces are too sweet to skip just because a distribution is lagging. And ever since I began using PostgreSQL at the 7.1 days I have _never_ experienced a bug that really affected me. No byte of data has ever been lost, no single time did it crash on me due to circumstances beyond my control (cough free disk space cough). Congratulations to everybody responsible for yet another awesome release! Yes. I am a fanboy. Sorry.
saurik 15y ago
The only thing I feel is wrong with this (really: the /only thing/, which is fricken awesome... I love PostgreSQL from the bottom of my heart, and have been using it for almost all of my database needs since the late 90s) is "per-column collation": collation is not a problem "per-column", it is a problem "per-index", which means you really want it "per-operator class". Here's the use case: you have a website, and you have users using it in English and French. With per-column collation, you are being advocated to have two fields, one english_name, and one french_name, that have /the same content/, but are defined using a different collation, so that the ordering condition on them becomes language-dependent. The effect that has is actually terrible: it means that the size of your row (and yes, this may end up in TOAST, but there is still a massive penalty to going that route) ends up becoming ginormous, and the size of your row will just get larger the more languages you want to support as first-class citizens in your app. Instead, what you /want/ is to just have an index english_ordered and an index french_ordered, and you want to be able to select which index you use for any specific query. If you "do it right", you'd also want to be able to support ordering the data using German collation, but it would "just be irritating slower". Now, if you don't use PostgreSQL much, this may seem like a pipe dream of extra standards and complex interactions ("how will you specify that?!", etc.). However, it turns out there is already a feature that does 99% of this: "operator classes", which is how PostgreSQL lets you define custom collations for user-defined types. Only, PostgreSQL operator classes are slightly more general than that, as you can specify an operator class to be used when performing order operations for your index; and, even more importantly: they are already being used to work around a specific case of operator-specific collation. Here's the example: let's say that your database is set up for UTF-8 collation, and yet you have this one field you want to do a "prefix-match" on: WHERE a LIKE 'B%'. The problem with this is that you cannot use a Unicode collation to index this search: it might be that 'B' and 'b' and even 'Q' all index "exactly the same" for purposes of this collation (and there are some other corner cases with the other mapping direction as well). So, to get index performance for this field, without changing your entire database to collate using "C" collation (which works out to "binary ordering"), you have a few choices, with one of them being to create a index that uses the special "operator class" called text_pattern_ops ("text" in this case as the field is likely a "text" field: there is also varchar_pattern_ops, etc.). Once specified in your index, PostgreSQL knows to use it for purposes of the aforementioned LIKE clause. You specify this while making your index by specifying the operator class after the column. CREATE INDEX my_index ON my_table (a text_pattern_ops); The next piece of the puzzle is that an ORDER BY clause can take a USING parameter to pass it a custom operator, and you can always (obviously) use a custom operator for purposes of comparison. So, you now are in the position where you should be able to do this: CREATE INDEX my_index ON my_table (a english_collation_ops); CREATE INDEX my_index ON my_table (a french_collation_ops); SELECT * FROM my_table WHERE english_collation_less(a, 'Bob') ORDER BY a USING english_collation_less; So, really, the only thing that needs to be specified, is we need the ability to have "parameterized operator classes": as in, we really need a "meta operator class" that takes itself an argument, the string name of the collation, and then returns an operator class. With this one general technique defined, we not only drastically increase PostgreSQL's user-defined type abilities, but we better solve this whole class of collation problem. (Unfortunately, I suck at e-mail, or I'd get on the PostgreSQL mailing list and try to argue for this in a more well-defined way; maybe someone else who cares will eventually see it and become this feature's champion; or, of course, come up with an even better solution than mine ;P.)
- saurik 15y ago
  (I make this a reply, as it is kind of a separate thought; also, I just came up with this in the shower, and a ton of people have already read the other comment and might not notice a new edit even if they cared about the post. ;P) As some people may balk at the "english_collation_less" operator usage (after all: using < is so much more convenient, especially if you have multiple of these operators strewn throughout every single one of your statements), all we really need to specify is a single new data type: collated_text. This, combined with a "client_collation" session variable, would allow clients to use < and ORDER BY on fields of all types, and get the behavior they would expect. It is only while constructing an index that you would need to specify the collation using a more complex syntax. CREATE TABLE my_table (a collated_text); CREATE INDEX my_index ON my_table (a collation_ops('en_US')); SET client_collation = 'en_US'; SELECT * FROM my_table WHERE a < 'Bob' ORDER BY a; (Given that they already seemed willing to add special syntax for per-column collation, if you did not believe in the dream of "per-operator class parameters" (or "parameterized operator classes", or "meta operator classes", or whatever), you could imagine "collation_ops('en_US')" being replaced by an index-specific syntax COLLATED BY 'en_US'). Just saying there are tons of options here, and they all seem better than forcing me to have 7x as much data in each of my rows, all identical.)
- jvdongen 15y ago
  For my multi-lingual-content-needs I work with a main table which only has the non-translatable data items and a subtable which contains the translatable content, one row per language per row from the main table. Then I do a join to get a single full 'object' in the language of choice. And per the postgres docs I can choose collation at query time. Thus the following query would do the trick if I'm correct: select * from main_table join sub_table on main_id=sub_id where sub_table.language='fr_FR' ORDER BY my_translatable_col COLLATE "fr_FR.utf8"; No bloating tables, just a join and correct collation.
  - saurik 15y ago
    Most content is not "translated", it just needs to be differently collated. If you have a company directory, you have the names of every user in the company in a table, and you need to display that information in a collation based on the locale of the viewing user. Having to have a new table for every language that contains the same data as the main table is just pointless overhead. Also, while you can choose the collation at query time, the point of per-column collation is to let you have an index over that collation. Can you please demonstrate how the use case of "company directory" would be cleanly and efficiently implemented using per-column collation? EDIT: I've been looking more into this COLLATE keyword that they have added, and I'm actually somewhat curious to see if I can make this work (where the optimizer manages to choose the right index) by something like the following, in which case I'm going to be seriously happy... ;P. CREATE TABLE my_table (a text); CREATE INDEX my_index ON my_table ((a COLLATE "en_US")); CREATE INDEX my_index ON my_table ((a COLLATE "de_DE")); SELECT * FROM my_table ORDER BY a COLLATE "de_DE";
hyperrail 15y ago
Finally! True serializability! Never let anyone (including the docs for older postgres versions) tell you that predicate locking is too hard to implement.
- clarkevans 15y ago
  Kevin Grittner will be talking on Serialization this Friday, September 16th 11:30 a.m. – 12:30 p.m. His talk will be recorded... so those who aren't in Chicago can see/hear it later next week. http://postgresopen.org/2011/schedule/presentations/61/ http://postgresopen.org/2011/schedule/presentations/61/
ConceitedCode 15y ago
I recently switched to PostgreSQL from MySQL and I couldn't be happier about it.
- pbreit 15y ago
  It seems that in general this decision should be made more frequently than it is, at least for new projects. Why do not more people select Postgres? Is it comfort level with MySQL? Tools and extensions?
  - sixtofour 15y ago
    It may be that tutorials for Tool X use MySQL in their examples.
  - xorglorb 15y ago
    Nearly all of the introductory PHP tutorials use MySQL, so many beginning web developers automatically go to MySQL because it is the only thing they have played around with.
  - masklinn 15y ago
    > Why do not more people select Postgres? Is it comfort level with MySQL? * Cheap (let alone free/ISP) hosting does not provide Postgres * Most introductory tutorials, especially in the PHP world (but also Ruby I believe) use MySQL * AMP packages/installers (is there any out there using Postgres as its db?) and a history of punitively hard installation Following that, a combination of habit (MySQL works for me, why would I care?) and BLUB (what could it even bring to the table?), as well as drawbacks/issues whose fixes are recent (replication, master/slave, ...)
  - lhnn 15y ago
    Because I spent $60 on an awesome MySQL reference guide and tutorial. http://www.amazon.com/MySQL-4th-Paul-DuBois/dp/0672329387/ref=sr_1_1?ie=UTF8&qid=1315855515&sr=8-1 http://www.amazon.com/MySQL-4th-Paul-DuBois/dp/0672329387/re...
    - 15y ago
clarkevans 15y ago
If you happen to be in Chicago, or near Chicago this week, be sure to come to PostgreSQL Open from Wednesday to Friday this week(Sept 14th-16th). http://postgresopen.org/2011/ http://postgresopen.org/2011/
jgavris 15y ago
KNN indexing!
- samstokes 15y ago
  Can anyone give an example use case for this? I'm not sure I fully understand what it means.
  - netghost 15y ago
    In spatial queries, find the 5 restaurants closest to me. In full text search, find the 5 words closest to "fuschia" so you can spell check, or try to search for what you __think__ the user meant to type.
    - deleted 15y ago
      [deleted]
  - wulczer 15y ago
    Here's the presentation from last year's PGCon, it's not exactly easy to follow, but has some hints on how this can be used: http://www.pgcon.org/2010/schedule/events/227.en.html http://www.pgcon.org/2010/schedule/events/227.en.html
  - rbranson 15y ago
    Without it, SELECT * FROM table WHERE text_column LIKE '%word%' involves a full table scan. With KNN + pg_trgm, it's an index scan. That's huge.
  - jgavris 15y ago
    take a look at these query plans - http://www.depesz.com/index.php/2010/12/11/waiting-for-9-1-knngist/ http://www.depesz.com/index.php/2010/12/11/waiting-for-9-1-k...
  - samstokes 15y ago
    Nice, thanks for all the examples and explanations! Anyone know if it's possible to define a custom distance metric for use with this? We don't currently use full-text or spatial indices, but I can think of some cool things we could do with a generalised notion of "distance".
pvh 15y ago
Congrats to the team for a really exciting release. We can't wait to ship it here at Heroku.
socratic 15y ago
Should I as a web app developer at a startup be looking for an RDBMS beyond PostgreSQL, probably a commercial one? I have somewhat of a database background, so I see the obvious advantages of PostgreSQL over MySQL. In particular, things like more procedural language support, a better query optimizer, better concurrency control, etc. (Though things like Amazon RDS are compelling from a deployment perspective.) I fundamentally believe that using an RDBMS, rather than a NoSQL data store is the right approach for rapid development of web apps. (Though, I primarily mean this as an attack on, e.g., MongoDB, since I think Redis is great, just not a replacement for an RDBMS.) However, I have almost no experience with the more advanced end of the RDBMS spectrum, primarily because they tend to cost money (for the real, non-free versions). Should I be learning/looking at DB2? Should I be learning/looking at Oracle? Or do the additional features of these more advanced RDBMS options require such specialized scenarios (a bank, a big enterprise) or such specialized hardware (weird clustered setups) that MySQL/PostgreSQL will always be just as good?
- wulczer 15y ago
  A short, probably biased and not 100% precise answer would be: no, you don't need to look for a commercial RDBMS. The real answer is: always evaluate options. Make sure the solution you choose supports everything you need and that you will be able to learn how to use it or hire people who can. Without knowing what your requirements are it's impossible to say, but in the vast majority of cases, PostgreSQL will be as good as Oracle, MS SQL Server or DB2. If your evaluation indicates that you need to pay for one of these, double and triple check it... and only if you're 100% sure that's the case, shell out for a commercial RDBMS.
  - socratic 15y ago
    I think that's probably true, though I do wonder from a purely engineering standpoint what features Oracle/DB2/MS have at this point that PostgreSQL does not. Special index types? Query hints? Suggestions for physical layout on table creation?
    - megaman821 15y ago
rbranson 15y ago
For a trivial, synthetic write benchmark that I usually use to benchmark hardware and/or config changes, I'm seeing slight slowdowns for non-concurrent loads, and solid improvements for concurrent loads with. For 1-2 clients, I'm seeing ~8% slower. For 4 clients, 7.2% faster. 8 clients, 15% faster. 16 clients, 16.4% faster. 32 clients, 11% faster. 64 clients, 10% faster. Aggregate performance starts to level off here, so I stopped. These are just cycling super simple INSERTs/DELETEs against the same table, columns data is 1K string, 100 byte string, then a concatenation of the pid and current iterator count. No indexes or primary keys. Each client is just a fork that performs 10,000 INSERTs, then 10,000 DELETEs in a loop of 10,000 iterations. For the record, that's around 6,729 writes per second with 32 clients. If I set synchronous_commit = OFF in each client before running the benchmark, it's 27,157/sec. Then, if I reduce the first column size to 100 bytes, it's 50,592/sec. Impressive. I'm sure the synchronous_commit improvement would be much more drastic on disks without BBU write caches. Database server is a 4-core Nehalem-based Xeon with 16GB RAM and a SAS disk array. PostgreSQL configuration has been decently tuned and full write durability is retained all the way down to the disks.

284 points by wulczer 15y ago

pilif 15y ago

(Mostly) like a clockwork: A new year, a new release. And like every year before we find a beautiful collection of new stuff to play with. Even better this time around: It's looking as if the next release of Ubuntu will get 9.1 packaged which spares me from manually packaging or using a PPA this time around. The new features each release introduces are too sweet to skip just because a distribution is lagging. And ever since I began using PostgreSQL at the 7.1 days I have _never_ experienced a bug that really affected me. No byte of data has ever been lost, no single time did it crash on me due to circumstances beyond my control (cough free disk space cough). Congratulations to everybody responsible for yet another awesome release! Yes. I am a fanboy. Sorry.

saurik 15y ago

The only thing I feel is wrong with this (really: the /only thing/, which is fricken awesome... I love PostgreSQL from the bottom of my heart, and have been using it for almost all of my database needs since the late 90s) is "per-column collation": collation is not a problem "per-column", it is a problem "per-index", which means you really want it "per-operator class". Here's the use case: you have a website, and you have users using it in English and French. With per-column collation, you are being advocated to have two fields, one english_name, and one french_name, that have /the same content/, but are defined using a different collation, so that the ordering condition on them becomes language-dependent. The effect that has is actually terrible: it means that the size of your row (and yes, this may end up in TOAST, but there is still a massive penalty to going that route) ends up becoming ginormous, and the size of your row will just get larger the more languages you want to support as first-class citizens in your app. Instead, what you /want/ is to just have an index english_ordered and an index french_ordered, and you want to be able to select which index you use for any specific query. If you "do it right", you'd also want to be able to support ordering the data using German collation, but it would "just be irritating slower". Now, if you don't use PostgreSQL much, this may seem like a pipe dream of extra standards and complex interactions ("how will you specify that?!", etc.). However, it turns out there is already a feature that does 99% of this: "operator classes", which is how PostgreSQL lets you define custom collations for user-defined types. Only, PostgreSQL operator classes are slightly more general than that, as you can specify an operator class to be used when performing order operations for your index; and, even more importantly: they are already being used to work around a specific case of operator-specific collation. Here's the example: let's say that your database is set up for UTF-8 collation, and yet you have this one field you want to do a "prefix-match" on: WHERE a LIKE 'B%'. The problem with this is that you cannot use a Unicode collation to index this search: it might be that 'B' and 'b' and even 'Q' all index "exactly the same" for purposes of this collation (and there are some other corner cases with the other mapping direction as well). So, to get index performance for this field, without changing your entire database to collate using "C" collation (which works out to "binary ordering"), you have a few choices, with one of them being to create a index that uses the special "operator class" called text_pattern_ops ("text" in this case as the field is likely a "text" field: there is also varchar_pattern_ops, etc.). Once specified in your index, PostgreSQL knows to use it for purposes of the aforementioned LIKE clause. You specify this while making your index by specifying the operator class after the column. CREATE INDEX my_index ON my_table (a text_pattern_ops); The next piece of the puzzle is that an ORDER BY clause can take a USING parameter to pass it a custom operator, and you can always (obviously) use a custom operator for purposes of comparison. So, you now are in the position where you should be able to do this: CREATE INDEX my_index ON my_table (a english_collation_ops); CREATE INDEX my_index ON my_table (a french_collation_ops); SELECT * FROM my_table WHERE english_collation_less(a, 'Bob') ORDER BY a USING english_collation_less; So, really, the only thing that needs to be specified, is we need the ability to have "parameterized operator classes": as in, we really need a "meta operator class" that takes itself an argument, the string name of the collation, and then returns an operator class. With this one general technique defined, we not only drastically increase PostgreSQL's user-defined type abilities, but we better solve this whole class of collation problem. (Unfortunately, I suck at e-mail, or I'd get on the PostgreSQL mailing list and try to argue for this in a more well-defined way; maybe someone else who cares will eventually see it and become this feature's champion; or, of course, come up with an even better solution than mine ;P.)

saurik 15y ago
(I make this a reply, as it is kind of a separate thought; also, I just came up with this in the shower, and a ton of people have already read the other comment and might not notice a new edit even if they cared about the post. ;P) As some people may balk at the "english_collation_less" operator usage (after all: using < is so much more convenient, especially if you have multiple of these operators strewn throughout every single one of your statements), all we really need to specify is a single new data type: collated_text. This, combined with a "client_collation" session variable, would allow clients to use < and ORDER BY on fields of all types, and get the behavior they would expect. It is only while constructing an index that you would need to specify the collation using a more complex syntax. CREATE TABLE my_table (a collated_text); CREATE INDEX my_index ON my_table (a collation_ops('en_US')); SET client_collation = 'en_US'; SELECT * FROM my_table WHERE a < 'Bob' ORDER BY a; (Given that they already seemed willing to add special syntax for per-column collation, if you did not believe in the dream of "per-operator class parameters" (or "parameterized operator classes", or "meta operator classes", or whatever), you could imagine "collation_ops('en_US')" being replaced by an index-specific syntax COLLATED BY 'en_US'). Just saying there are tons of options here, and they all seem better than forcing me to have 7x as much data in each of my rows, all identical.)
jvdongen 15y ago
For my multi-lingual-content-needs I work with a main table which only has the non-translatable data items and a subtable which contains the translatable content, one row per language per row from the main table. Then I do a join to get a single full 'object' in the language of choice. And per the postgres docs I can choose collation at query time. Thus the following query would do the trick if I'm correct: select * from main_table join sub_table on main_id=sub_id where sub_table.language='fr_FR' ORDER BY my_translatable_col COLLATE "fr_FR.utf8"; No bloating tables, just a join and correct collation.
- saurik 15y ago
  Most content is not "translated", it just needs to be differently collated. If you have a company directory, you have the names of every user in the company in a table, and you need to display that information in a collation based on the locale of the viewing user. Having to have a new table for every language that contains the same data as the main table is just pointless overhead. Also, while you can choose the collation at query time, the point of per-column collation is to let you have an index over that collation. Can you please demonstrate how the use case of "company directory" would be cleanly and efficiently implemented using per-column collation? EDIT: I've been looking more into this COLLATE keyword that they have added, and I'm actually somewhat curious to see if I can make this work (where the optimizer manages to choose the right index) by something like the following, in which case I'm going to be seriously happy... ;P. CREATE TABLE my_table (a text); CREATE INDEX my_index ON my_table ((a COLLATE "en_US")); CREATE INDEX my_index ON my_table ((a COLLATE "de_DE")); SELECT * FROM my_table ORDER BY a COLLATE "de_DE";

hyperrail 15y ago

Finally! True serializability! Never let anyone (including the docs for older postgres versions) tell you that predicate locking is too hard to implement.

clarkevans 15y ago
Kevin Grittner will be talking on Serialization this Friday, September 16th 11:30 a.m. – 12:30 p.m. His talk will be recorded... so those who aren't in Chicago can see/hear it later next week. http://postgresopen.org/2011/schedule/presentations/61/ http://postgresopen.org/2011/schedule/presentations/61/

ConceitedCode 15y ago

I recently switched to PostgreSQL from MySQL and I couldn't be happier about it.

pbreit 15y ago
It seems that in general this decision should be made more frequently than it is, at least for new projects. Why do not more people select Postgres? Is it comfort level with MySQL? Tools and extensions?
- sixtofour 15y ago
  It may be that tutorials for Tool X use MySQL in their examples.
- xorglorb 15y ago
  Nearly all of the introductory PHP tutorials use MySQL, so many beginning web developers automatically go to MySQL because it is the only thing they have played around with.
- masklinn 15y ago
  > Why do not more people select Postgres? Is it comfort level with MySQL? * Cheap (let alone free/ISP) hosting does not provide Postgres * Most introductory tutorials, especially in the PHP world (but also Ruby I believe) use MySQL * AMP packages/installers (is there any out there using Postgres as its db?) and a history of punitively hard installation Following that, a combination of habit (MySQL works for me, why would I care?) and BLUB (what could it even bring to the table?), as well as drawbacks/issues whose fixes are recent (replication, master/slave, ...)
- lhnn 15y ago
  Because I spent $60 on an awesome MySQL reference guide and tutorial. http://www.amazon.com/MySQL-4th-Paul-DuBois/dp/0672329387/ref=sr_1_1?ie=UTF8&qid=1315855515&sr=8-1 http://www.amazon.com/MySQL-4th-Paul-DuBois/dp/0672329387/re...
  - 15y ago

clarkevans 15y ago

If you happen to be in Chicago, or near Chicago this week, be sure to come to PostgreSQL Open from Wednesday to Friday this week(Sept 14th-16th). http://postgresopen.org/2011/ http://postgresopen.org/2011/

jgavris 15y ago

KNN indexing!

samstokes 15y ago
Can anyone give an example use case for this? I'm not sure I fully understand what it means.
- netghost 15y ago
  In spatial queries, find the 5 restaurants closest to me. In full text search, find the 5 words closest to "fuschia" so you can spell check, or try to search for what you __think__ the user meant to type.
  - deleted 15y ago
    [deleted]
- wulczer 15y ago
  Here's the presentation from last year's PGCon, it's not exactly easy to follow, but has some hints on how this can be used: http://www.pgcon.org/2010/schedule/events/227.en.html http://www.pgcon.org/2010/schedule/events/227.en.html
- rbranson 15y ago
  Without it, SELECT * FROM table WHERE text_column LIKE '%word%' involves a full table scan. With KNN + pg_trgm, it's an index scan. That's huge.
- jgavris 15y ago
  take a look at these query plans - http://www.depesz.com/index.php/2010/12/11/waiting-for-9-1-knngist/ http://www.depesz.com/index.php/2010/12/11/waiting-for-9-1-k...
- samstokes 15y ago
  Nice, thanks for all the examples and explanations! Anyone know if it's possible to define a custom distance metric for use with this? We don't currently use full-text or spatial indices, but I can think of some cool things we could do with a generalised notion of "distance".

pvh 15y ago

Congrats to the team for a really exciting release. We can't wait to ship it here at Heroku.

socratic 15y ago

Should I as a web app developer at a startup be looking for an RDBMS beyond PostgreSQL, probably a commercial one? I have somewhat of a database background, so I see the obvious advantages of PostgreSQL over MySQL. In particular, things like more procedural language support, a better query optimizer, better concurrency control, etc. (Though things like Amazon RDS are compelling from a deployment perspective.) I fundamentally believe that using an RDBMS, rather than a NoSQL data store is the right approach for rapid development of web apps. (Though, I primarily mean this as an attack on, e.g., MongoDB, since I think Redis is great, just not a replacement for an RDBMS.) However, I have almost no experience with the more advanced end of the RDBMS spectrum, primarily because they tend to cost money (for the real, non-free versions). Should I be learning/looking at DB2? Should I be learning/looking at Oracle? Or do the additional features of these more advanced RDBMS options require such specialized scenarios (a bank, a big enterprise) or such specialized hardware (weird clustered setups) that MySQL/PostgreSQL will always be just as good?

wulczer 15y ago
A short, probably biased and not 100% precise answer would be: no, you don't need to look for a commercial RDBMS. The real answer is: always evaluate options. Make sure the solution you choose supports everything you need and that you will be able to learn how to use it or hire people who can. Without knowing what your requirements are it's impossible to say, but in the vast majority of cases, PostgreSQL will be as good as Oracle, MS SQL Server or DB2. If your evaluation indicates that you need to pay for one of these, double and triple check it... and only if you're 100% sure that's the case, shell out for a commercial RDBMS.
- socratic 15y ago
  I think that's probably true, though I do wonder from a purely engineering standpoint what features Oracle/DB2/MS have at this point that PostgreSQL does not. Special index types? Query hints? Suggestions for physical layout on table creation?
  - megaman821 15y ago

rbranson 15y ago

For a trivial, synthetic write benchmark that I usually use to benchmark hardware and/or config changes, I'm seeing slight slowdowns for non-concurrent loads, and solid improvements for concurrent loads with. For 1-2 clients, I'm seeing ~8% slower. For 4 clients, 7.2% faster. 8 clients, 15% faster. 16 clients, 16.4% faster. 32 clients, 11% faster. 64 clients, 10% faster. Aggregate performance starts to level off here, so I stopped. These are just cycling super simple INSERTs/DELETEs against the same table, columns data is 1K string, 100 byte string, then a concatenation of the pid and current iterator count. No indexes or primary keys. Each client is just a fork that performs 10,000 INSERTs, then 10,000 DELETEs in a loop of 10,000 iterations. For the record, that's around 6,729 writes per second with 32 clients. If I set synchronous_commit = OFF in each client before running the benchmark, it's 27,157/sec. Then, if I reduce the first column size to 100 bytes, it's 50,592/sec. Impressive. I'm sure the synchronous_commit improvement would be much more drastic on disks without BBU write caches. Database server is a 4-core Nehalem-based Xeon with 16GB RAM and a SAS disk array. PostgreSQL configuration has been decently tuned and full write durability is retained all the way down to the disks.