Understanding database cleaning strategies in tests

Updated . Posted . Visible to the public. Deprecated.

The performance measurements in this card needs to be retaken for PostgreSQL.

TLDR: In tests you need to clean out the database before each example. Use :transaction where possible. Use :deletion for Selenium features or when you have a lot of MyISAM tables.

Understanding database cleaning

You want to clean out your test database after each test, so the next test can start from a blank database. To do so you have three options:

  • Wrap each test in a transaction which is rolled back when you're done (through DatabaseCleaner.strategy = :transaction or config.use_transactional_fixtures = true)
  • Truncate Show archive.org snapshot all tables after each test (through DatabaseCleaner.strategy = :truncation)
  • Delete all rows after each test (through DatabaseCleaner.strategy = :deletion)

Additionally there are some constraints for when you can use transactions:

  • Transactions cannot be used for Selenium features, where Rails and test run in different processes and thus don't see changes within the transaction of another process.
  • Transactions are not available for MyISAM tables (which you might use for FULLTEXT indexes).

Performance of different cleaning strategies

I measured the runtime of different strategies using an average-sized Rails project (with MySQL):

Cucumber RSpec
Transaction 87.14, 86.65 10.20, 10.11
Truncation 90.43, 90.69 26.82, 26.46
Deletion 86.20, 85.61 12.08, 12.12

What we can take away from this:

  • Use transactions where possible. It is the faster option all around, and will scale better than deletion as your number of records grow.
  • Use deletion for Selenium features ( here's why Show archive.org snapshot ) or when you have a lot of MyISAM tables. You can also use truncation, but it's maybe a bit slower.
  • Avoid truncation. In fact you should probably grep your project for :truncation and replace it with :deletion. An exception to this rule is when you are using a lot of foreign key constraints Show archive.org snapshot (you probably don't). In that case truncation might outperform deletion (but I didn't measure that).
  • Take care to set config.use_transactional_fixtures = false when moving away from transactions. Using both transactions and another cleaning strategy will drag down your test performance by quite a bit.
Henning Koch
Last edit
Deleted user #4117
Source code in this card is licensed under the MIT License.
Posted by Henning Koch to makandra dev (2012-12-12 09:55)