§ Seeds / 2014 / Fall


Fall 2014 an OmniTI publication

Year:

Season:

Using Writeable CTEs to Improve Performance

Transactions are Great

Transactions are a wonderful tool for web/application developers. If several things in the database need to be modified together, simply wrap them in a transaction. This will guarantee that either all of the changes succeed together, or they all fail together. This helps to remove complicated error conditions of partially successful updates and avoid being stuck in a situation that may no longer possible for the application to clean up.

The following is an example of a simple web transaction in Perl.*

# Turn off AutoCommit so we're in a transaction
local $dbh->{AutoCommit} = 0;
eval {
   my $sth = $dbh->prepare('insert into users (name, email) 
            values (?,?) returning userid');
   $sth->execute( $name, $email );
   $sth->fetch( \$userid ); 
   $sth = $dbh->prepare('insert into addresses (...) 
            values (?,?,?,?,?) returning addressid');
   $sth->execute( $userid, $addr, $city, $state, $zip );
   $sth->fetch( \$addressid ); 
   $sth = $dbh->prepare('insert into user_history (...) 
            values (?,?,?) returning historyid');
   $sth->execute( $userid, $addressid, 'User Created’ );
   $sth->fetch( \$userid ); 
};
if($@) {
    $dbh->rollback();
} else {
    $dbh->commit();
}
# Return AutoCommit back on
$dbh->{AutoCommit} = 1;

The user’s data is inserted into all three tables that this business requires. Without the transaction, it would be possible for the first two statements to execute successfully but have the third fail leaving the user in an incomplete state! Thankfully a transaction provides a guarantee that all statements will succeed or get rolled back to how they were initially.

* Careful readers may have noticed this example isn’t handling errors properly.

Transactions are Hard

While providing great functionality, as transactions become more complex, mistakes are increasingly likely to be made. Error handling is a difficult topic and it is easy for developers to make simple mistakes that can have unpleasant side effects. Modifying the earlier example, a number of common errors that find their way into transactional code, can be illustrated.

Breaking “autocommit” state

In Perl, the DBI module regulates transactions with the “AutoCommit” flag. Normally AutoCommit is set to true, and each query is implicitly wrapped in a “BEGIN; ; COMMIT;” sandwich. To create multi-statement transactions, the AutoCommit flag is turned off. This tells DBI not to send an implicit COMMIT. The application is then responsible for issuing a commit or rollback statement. In the earlier example "local" was used to limit the variable change to the current block scope. Once the block completes, the variable is automatically returned to its previous value. But imagine instead that local had not been used and simply changed that as:

# Turn off AutoCommit so we're in a transaction
$dbh->{AutoCommit} = 0;

If anything unexpected happened, that value would never get reset. This changes that database handle for all future database queries that use it and can cause very long transactions, or unexpectedly large volumes of SQL queries to get rolled back when an error does happen. It’s always important to maintain the state of the AutoCommit flag, and return it back to its previous state.

Including outside operations in a transaction

Especially in larger operations, a developer may not even realize they are in a transaction and put other calls into the execution path. These calls are not part of the transaction, and will not get rolled back in the case of an error. Consider the following change:

eval {
   my $sth = $dbh->prepare('insert into users (name, email) 
            values (?,?) returning userid');
   $sth->execute( $name, $email );
   $sth->fetch( \$userid ); 
   $PARTNER->API->acknowledge_signup( $userid );
   $sth = $dbh->prepare('insert into addresses (...) 
            values (?,?,?,?,?) returning addressid');
   $sth->execute( $userid, $addr, $city, $state, $zip );
   $sth->fetch( \$addressid ); 
};

If an error occurs during the second execute() statement, the first insert will be rolled back. However the Partner has already been notified of the acknowledgement via its API. This API call will not get rolled back causing the Partner to have an acknowledgement of something that never occurred. These acknowledgements should not occur until after the transaction has been successfully committed.

Slowing down other transactions

Updating a value in an MVCC database such as PostgreSQL causes a lock to be taken. Other clients may also want to update that value or table, but until the first transaction is committed or rolled back, the database cannot handle their request. These other clients will have to wait until that lock is released. It is not uncommon to see a thoughtless and expensive operation put in the middle of a transaction block causing other clients to wait needlessly. The API call in the previous example could have a network issue causing the transaction to block until its timeout is reached.

Error handling can have errors

It’s quite difficult to anticipate all of the ways errors can occur. Consider this piece of code:

eval {
   my $sth = $dbh->prepare('insert into users (name, email) 
            values (?,?) returning userid');
   $sth->execute( $name, $email );
   $sth->fetch( \$userid ); 
   $sth = $dbh->prepare('insert into addresses (...) 
            values (?,?,?,?,?) returning addressid');
   $sth->execute( $userid, $addr, $city, $state, $zip );
   $sth->fetch( \$addressid );
};
if($@) {
    $dbh->rollback();
}
$response->send_success();
$dbh->commit();

Here the code does not take into consideration that successful completion of the eval does not mean that the transaction has completed. The commit command itself can return an error if the database connection was broken. In this case it is possible to have sent a success message, but the transaction would still get rolled back by the database.

While only a few are reviewed here, there are many ways that transactions can be done incorrectly. While there are solutions to all of these problems, it would be great for developers if there was a way to avoid most of the issues in the first place by not using transactions, yet still atomically doing the changes.

Enter Writeable CTEs

Common Table Expressions (CTEs) can be thought of like temporary tables that exist only for the life of that query. These are created by using the WITH keyword and are supported by most major databases. These can be quite powerful on their own and I’ve written about the recursive versions of these before. A simple example of a CTE would be:

with naughty_users as (
  select * from users where banned = 1
)
select * from naughty_users;

Writeable CTEs were added to PostgreSQL in version 9.1. These took CTEs and super charged them by allowing the modification of data inside of these expressions. For example, inserting, updating, and deleting. This allows many powerful expressions to be chained together in a manner not possible before. For example being able to delete a set of rows, while being able to insert a subset of those rows into a log table.

with deletions as (
  delete from users where expired is true
  returning *
)
insert into deleted_even_user_archive
select * from deletions where userid % 2 = 0;

It is an important side note that CTEs are “optimization fences” to the query planner. The query planner will not attempt to re-arrange queries across table expression for faster execution plans. For more advanced users, this can be used to force the query planner to use certain indexes. However for the novice, it means the query planner cannot try to optimize a poorly written CTE whereas it may have been able to if it was written in a more traditional manner.

Replacing Insert Transactions with CTEs

This example consists of three separate insert queries:

insert into users (name, email) 
values (?,?) returning userid

insert into addresses (userid, address, city, state, zip) 
values (?,?,?,?,?) returning addressid

insert into user_history (userid, addressid, action) 
values (?,?,?) returning historyid

These three queries could instead be expressed as a single CTE query that returns the ids of all three inserted rows.

with userdata as (
  insert into users (name, email) values (?,?)
  returning userid
), addressdata as (
  insert into addresses (userid, address, city, state, zip)
  select userid,?,?,?,?
  from userdata
  returning addressid 
), historydata as (
  insert into user_history (userid, addressid, action)
  select userid, addressid,?
  from userdata, addressdata 
  returning historyid
)
select userid, addressid, historyid 
from userdata, addressdata, historydata;

Doing this as a single query avoids the transaction issue completely. The entire query is executed and succeeds or fails atomically. If part of the query throws an error the entire query is rolled back, like can be expected of any other query. Wrapping these inserts into the single CTE query allows:

Performance Impact

In theory, this should provide a significant amount of back-and-forth savings on the network by running all three queries at once instead of serially. But nothing beats real world metrics. To test this, a free Postgres instance on Amazon RDS was created with the following settings:

Free Tier Postgres RDS instance of Postgres 9.3.3
Multi-AZ Deployment : No
db.t2.micro - 1 vCPU, 1GB RAM
General Purpose SSD storage, 20 GB

A free t1.micro instance running OmniOS r151012 AMI was created to test with. To reproduce these results instructions and scripts are available at https://github.com/bdunavant/cte_demo

The ‘demo_web_inserts.pl’ script runs three different scenarios 100,000 times to get the average time spent for each set of inserts. The results were pretty close to expected.

Traditional without transaction took 758 seconds to run.
758 / 100000 = 7.58ms per set.
Traditional with transaction took 737 seconds to run.
737 / 100000 = 7.37ms per set.
CTE took 289 seconds to run.
289 / 100000 = 2.89ms per set.

It is interesting to note that the transaction version of the 3 inserts actually runs slightly faster than the non-transactional version. This has to do with Postgres not having to write the WAL logs to disk until the commit is issued. Less disk access means faster run times. The CTE version of the query gets to take advantage of this same fact since all three inserts are in the same commit.

Overall the CTE version of the inserts give an approximately 60% decrease in run time; which is to be expected if 1/3rd of the number of queries are being run.

To further explore the level of savings, a simple user insert is run for multiple iterations; 2 inserts vs CTE, 3 inserts vs CTE, and so on up til 10 inserts, to verify the savings scale upwards. This test is in demo_variable_inserts.pl. Each set is run with 100,000 iterations on the same setup as the previous test. Towards the end the test cases take significant time to complete (hours per iteration set). This is simply total time / iterations to determine an average (including outliers). EC2’s variable performance throws off the data but the trends appear pretty clearly.

Caveats

There’s no such thing as a free lunch. So there are reasons to not suddenly go in and update all of a project’s inserts statements to wrap them in CTEs.

Conclusion

Common Table Expressions are a great way to simplify some common database cases helping to reduce mistakes in transaction handling by avoiding transactions entirely. In addition they can improve performance out of a series of queries by merging them into a single query.