Read more

Understanding database cleaning strategies in tests

Henning Koch
December 12, 2012Software engineer at makandra GmbH

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

Illustration web development

Do you need DevOps-experts?

Your development team has a full backlog? No time for infrastructure architecture? Our DevOps team is ready to support you!

  • We build reliable cloud solutions with Infrastructure as code
  • We are experts in security, Linux and databases
  • We support your dev team to perform
Read more Show archive.org snapshot

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
December 12, 2012Software engineer at makandra GmbH
Posted by Henning Koch to makandra dev (2012-12-12 10:55)