PostgresSQL: How to Make it Faster

I usually cross the line and post about RDBMS when there are interesting things that can be learned. Robert Hass explains some PostgreSQL knobs that can be turned to make things faster. But more interesting is what these knobs are doing: some are disabling fsync, others are disabling the write-ahead-log, others are making the commits asynchronous. Even more interesting is that all these knobs are in the end trading off durability for speed.

I think that, in the future, we may be able to provide more options to allow people to relax the data integrity guarantees that PostgreSQL provides in controlled ways. For example, I can imagine a “dirty read” table, where transactions are not used; instead, rows become visible as soon as they’re inserted, and disappear as soon as they’re deleted. Such a table would be unsuitable for many business applications, but if your application only does single-row operations indexed by primary key, it might work just fine; and it would open up a number of interesting optimization opportunities that aren’t available for ordinary tables. Or, you might have a “no snapshot” table, where rows don’t become visible until the inserting transaction commits, but we make no attempt to guarantee serializability: rows appear pop into existence the instant they’re committed, and disappear out from under you if a deleting transaction commits.

Original title and link: PostgresSQL: How to Make it Faster (NoSQL databases © myNoSQL)

via: http://rhaas.blogspot.com/2010/11/when-your-data-isnt-made-of-gold.html