Michael Nikitochkin

Posted on Jan 24

From 4 Minutes to 3 Seconds: How Database Transaction Rollback Revolutionized Test Suite

#testing #database #performance #crystal

Executive Summary

In a single afternoon, I transformed my Crystal/Marten test suite from a 4-minute ordeal into a 3-second sprint by replacing expensive database TRUNCATE with lightning-fast transaction rollback. This 98.8% performance improvement didn't just make developers happier—it fundamentally changed how I approach testing.

The Bottom Line: 447 tests now run in 2.84 seconds instead of 245.87 seconds—a 86.5x speedup that makes test-driven development (TDD) practical again.

The Problem: When Tests Become a Bottleneck

The Performance Crisis

The test suite was destroying developer productivity. Every pull request meant waiting, every bug fix meant coffee breaks. With truncation-based test isolation, the team was hemorrhaging time:

Individual test example: UserTest#test_email_validation - 0.527 seconds

The Root Cause: Truncation Hell

The culprit was my test isolation strategy: database truncation. Before each test, I was:

Dropping and recreating data across 20+ tables
Performing expensive I/O operations that scaled with data size

Each test was paying a ~500ms tax just to clean up after itself. With 447 tests, that's over 3 minutes of pure overhead.

The Solution: Transaction Rollback Strategy

Instead of physically deleting data and resetting sequences, I could simply wrap each test in a database transaction and always roll back. PostgreSQL transactions are designed for exactly this—atomic operations that can be discarded instantly.

How It Works

# The core insight: Wrap ONLY the test execution
def run_one(name : String, proc : Test ->) : Nil
  # 1. Setup runs OUTSIDE transaction
  before_setup
  setup
  after_setup

  # 2. Test runs INSIDE transaction
  Marten::DB::Connection.default.transaction do
    proc.call(self)  # Run the actual test
    raise Marten::DB::Errors::Rollback.new("Test cleanup")
  end

  # 3. Teardown runs AFTER rollback
  before_teardown
  teardown
  after_teardown
end

Why This Is Magical:

Setup/teardown run once - Database schema loading, migrations, etc.
Only test data is transactional - All changes disappear instantly
No I/O overhead - rollback is just a memory operation
Clean isolation - Each test gets a fresh slate automatically

Performance Transformation

Dramatic Individual Test Speedup

Example: User::ValidateTest#test_email_validation

Metric	Before	After	Improvement
Test Duration	0.527s	0.002s	263x faster

Test Suite Revolution

Metric	Before (Truncation)	After (Transaction)	Improvement
Total Duration	00:04:05.872s (245.87s)	00:00:02.841s (2.84s)	86.5x faster
Runs per Second	0.00407 runs/s	0.352 runs/s	86.5x improvement
Test Count	447 tests	447 tests	Same coverage

What This Means for Developers

Before (Truncation Hell):

"I'll run tests while grabbing coffee" - waiting kills productivity
1.8 tests per minute - glacial feedback
4+ minute feedback loop - context switching inevitable
TDD feels painful - testing becomes optional

After (Transaction Magic):

"I'll run tests before every commit" - instant gratification
9,450 tests per minute - blazing speed
3-second feedback loop - you're still thinking about the code
TDD feels effortless - testing becomes second nature

Technical Deep Dive

The Problem: Expensive Database Surgery

Each test paid ~40-60ms of truncation overhead:

TRUNCATE operations: 20-30ms (disk I/O)
Sequence resets: 10-15ms (catalog updates)
Connection overhead: 5-10ms

With 447 tests, that's over 3 minutes of pure cleanup time.

The Solution: Instant Rollback

Transaction rollback costs ~2-4ms per test (50-75% reduction):

Transaction start: 1-2ms (memory allocation)
Rollback: 1-2ms (memory discard)
No disk I/O: Pure memory operation

![IMAGE PLACEHOLDER: Prompt: Performance cost breakdown comparison chart. Left bar showing truncation costs: "TRUNCATE ops (20-30ms)" in red, "Sequence resets (10-15ms)" in orange, "Connection overhead (5-10ms)" in yellow, totaling 40-60ms per test. Right bar showing rollback costs: "Transaction start (1-2ms)" in light blue, "Rollback (1-2ms)" in dark blue, totaling 2-4ms per test. Include annotations: "50-75% reduction" and "Pure memory operation" for rollback. Style: modern bar chart with gradient colors, clear cost labels, benchmark comparison.]

Implementation Blueprint

Override Minitest's run_one method to wrap test execution in database transactions:

Setup OUTSIDE transaction - Database schema and reference data loads once per test suite
Test INSIDE transaction - Each test runs atomically and always rolls back
Teardown AFTER rollback - Clear caches (Marten::Cache, converted models, email collectors, WebMock)

Why Override run_one Instead of Using Standard Hooks?

The core issue is how Marten's DB.transaction works¹:

Marten uses yield to execute code inside a transaction block
It sets thread-local variables to track the active transaction connection
Individual database operations check these thread variables to reuse the transaction connection
This pattern requires wrapping the entire test execution from the outside

Standard Minitest lifecycle hooks have a fatal limitation², as there is no around_run or wrapper mechanism.

By overriding run_one, we control the order of operations and can properly use yield:

1. Setup (OUTSIDE transaction)     ← Reference data persists
2. Begin transaction with yield
3. Test code (INSIDE transaction)  ← Thread variables track connection
4. Rollback transaction
5. Teardown (OUTSIDE transaction)  ← Clear caches, email collectors

DEV Community