Implementing No Transaction Mode in Postgresql

The other day we were discussing the alter table add foreign key implementation, and Tom Lane made this observation…

We’ll never be able to match the performance of not having transactions, either, but the community has never for a moment considered having a “no transactions” mode.

My first thought was wondering just what removing transactions would mean inside Postgres. The answers actually seemed to be things people **do want** in a number of cases, and some of them are items we have actually given people options for. Thinking about it, I’d say the simplest summary of what you get using transactions is our old friend [http://en.wikipedia.org/wiki/ACID ACID]ity. While everyone (well, everyone on a “real rdbms” anyway) will tell you, there is no [http://en.wikipedia.org/wiki/Monty_Python_and_the_Holy_Grail holier grail] than that of preserving the ideals of ACIDity within your database, but as I think about it, I think a lot of people are willing to trade portions of this contract to get around implementation effects that cause trouble for end users. Durability is something that is highly touted as a huge benefit of Postgres. The whole concept of the CLOG and XLOG being used to guarantee persistence of data once a transaction has committed is something most Postgres advocates point out as a top feature. And yet, in 8.3, Postgres added the handy “[http://www.postgresql.org/docs/8.3/static/runtime-config-wal.html#GUC-SYNCHRONOUS-COMMIT synchronous_commit]” mode, which allows you to trade durability (in the event of a crash, some recently committed transactions may be lost, but at no risk for data corruption) for a significant speed boost. This is one of the things people talk about when they want to do queries outside of transactions. So what about the others? Well, Postgres doesn’t give you other tools to trade off the ACI properties, but I have seen cases where people were willing to trade on them. One example is that of folks who want to implement a true [http://en.wikipedia.org/wiki/Isolation_(computer_science)#READ_UNCOMMITTED read uncommitted] mode, which would allow them to view not only currently committed data, but any data from transactions that have not yet committed as well. This is typically used in high logging / reporting systems, and it’s typically used (in other database products) to work around locking issues that you don’t typically see in Postgres (which might be one reason there isn’t as much demand for it). Another scenario, and one I’ve wished for, is to turn off WAL logging on specific relations (cutting down on i/o overhead, and possibly cutting down on on-disk space as well) at expense of giving up consistency. Now most people might question that, but my specific desire comes from a system where we replicate tables “en masse”, and the whole table is copied every 5 minutes. In the event of a system crash, we’re just going to throw the data away and re-pull it from our target system anyway, so the overhead from the WAL doesn’t buy us anything. Sure, that’s a pretty specific use case, but there are other similar applications, like storing temporary data that needs to be accessible to multiple users, like a session table for instance. Even if you don’t focus on ACIDity, you could find other ways of gaining performance by not supporting transactions (think of the disk space that could be saved if we didn’t need to know x*foo and c*foo values for data, similar to what has been proposed recently as “read-only tables” or its similar proposals of “append-only tables”. I imagine there are other use cases floating out there where people want to make trade offs for performance benefits (and/or implementation work arounds), there are certainly a number of these types of features in other databases, and making these trade-offs in a controlled way is certainly better than doing it with a more brute force approach. So, while the community will probably never agree on a “no-transactions” mode, I think you’re likely to see development pushed in a direction that skirts around them, even if they don’t disappear entirely.