Tools: Speeding Up PostgreSQL in Containers

The Problem ## Catching PostgreSQL in the Act ## The Solution ## 1. --nosync Flag ## 2. Increased Shared Buffers ## 3. tmpfs for Data Directory (The Game Changer) ## The Impact ## Real Test Performance Examples ## Where the Time Was Going ## The Math ## Why This Works for CI ## Key Takeaways ## Implementation in Woodpecker CI ## Why Not Just Tune PostgreSQL Settings Instead of tmpfs? ## Tested Aggressive Disk-Based Tuning ## The Results: tmpfs Still Wins ## Bonus: Other PostgreSQL CI Optimizations to Consider Running a test suite on an older CI machine with slow disks revealed PostgreSQL as a major bottleneck. Each test run was taking over 1 hour to complete. The culprit? Tests performing numerous database operations, with TRUNCATE commands cleaning up data after each test. With slow disk I/O, PostgreSQL was spending most of its time syncing data to disk - operations that were completely unnecessary in a ephemeral CI environment where data persistence doesn't matter. Running top during test execution revealed the smoking gun: PostgreSQL was consuming 81.7% CPU just to truncate a table! This single TRUNCATE operation ran for over 15 seconds. On a machine with slow disks, PostgreSQL was spending enormous amounts of time on fsync operations, waiting for the kernel to confirm data was written to physical storage - even though we were just emptying tables between tests. Three simple PostgreSQL configuration tweaks made a dramatic difference: The --nosync flag tells PostgreSQL to skip fsync() calls during database initialization. In a CI environment, we don't care about data durability - if the container crashes, we'll just start over. This eliminates expensive disk sync operations that were slowing down database setup. Setting POSTGRES_SHARED_BUFFERS: 256MB (up from the default ~128MB) gives PostgreSQL more memory to cache frequently accessed data. This is especially helpful when running tests that repeatedly access the same tables. The biggest performance win came from mounting PostgreSQL's data directory on tmpfs - an in-memory filesystem. This completely eliminates disk I/O for database operations: With tmpfs, all database operations happen in RAM. This is especially impactful for: The 1GB size limit is generous for most test databases. Adjust based on your test data volume. Before: ~60 minutes per test run After: ~10 minutes per test run Improvement: 6x faster! 🚀 You should have seen my surprise when I first saw a single test taking 30 seconds in containers. I knew something was terribly wrong. But when I applied the in-memory optimization and saw the numbers drop to what you'd expect on a normal machine - I literally got tears in my eyes. Before tmpfs optimization: Each test was taking 25-30 seconds even though the actual test logic was minimal! Most of this time was spent waiting for PostgreSQL to sync data to disk. After tmpfs optimization: These same tests now complete in 0.4-0.5 seconds - a 50-60x improvement per test! 🎉 The biggest gains came from reducing disk I/O during: With slow disks, even simple operations like creating a test user or truncating a table would take seconds instead of milliseconds. The top output above shows a single TRUNCATE TABLE operation taking 15+ seconds and consuming 81.7% CPU - most of that was PostgreSQL waiting for disk I/O. Multiply that across hundreds of tests, and you get hour-long CI runs. With dozens of test files, the cumulative time savings are massive. In production, you absolutely want fsync() enabled and conservative settings to ensure data durability. But in CI: By telling PostgreSQL "don't worry about crashes, we don't need this data forever," we eliminated unnecessary overhead. Here's our complete PostgreSQL service configuration: Note: The tmpfs field is officially supported in Woodpecker CI's backend (defined in pipeline/backend/types/step.go). If you see schema validation warnings, they may be from outdated documentation - the feature works perfectly. Lucky us! Not all CI platforms support tmpfs configuration this easily. Woodpecker CI makes it trivial with native Docker support - just add a tmpfs: field and you're done. If you're on GitHub Actions, GitLab CI, or other platforms, you might need workarounds like docker run with --tmpfs flags or custom runner configurations. Simple, effective, and no code changes required - just smarter configuration for the CI environment. TL;DR: I tried. tmpfs is still faster AND simpler. After seeing the dramatic improvements with tmpfs, I wondered: "Could we achieve similar performance by aggressively tuning PostgreSQL settings instead?" This would be useful for environments where tmpfs isn't available or RAM is limited. Experimenting with disabling all durability features: Even with all these aggressive settings, tmpfs was still faster. Disk-based (even with fsync=off): If you're still looking for more speed improvements: Templates let you quickly answer FAQs or store snippets for re-use. Are you sure you want to hide this comment? It will become hidden in your post, but will still be visible via the comment's permalink. Hide child comments as well For further actions, you may consider blocking this person and/or reporting abuse CODE_BLOCK: 242503 postgres 20 0 184592 49420 39944 R 81.7 0.3 0:15.66 postgres: postgres api_test 10.89.5.6(43216) TRUNCATE TABLE Enter fullscreen mode Exit fullscreen mode CODE_BLOCK: 242503 postgres 20 0 184592 49420 39944 R 81.7 0.3 0:15.66 postgres: postgres api_test 10.89.5.6(43216) TRUNCATE TABLE CODE_BLOCK: 242503 postgres 20 0 184592 49420 39944 R 81.7 0.3 0:15.66 postgres: postgres api_test 10.89.5.6(43216) TRUNCATE TABLE CODE_BLOCK: services: postgres: image: postgres:16.11-alpine environment: POSTGRES_INITDB_ARGS: "--nosync" POSTGRES_SHARED_BUFFERS: 256MB tmpfs: - /var/lib/postgresql/data:size=1g Enter fullscreen mode Exit fullscreen mode CODE_BLOCK: services: postgres: image: postgres:16.11-alpine environment: POSTGRES_INITDB_ARGS: "--nosync" POSTGRES_SHARED_BUFFERS: 256MB tmpfs: - /var/lib/postgresql/data:size=1g CODE_BLOCK: services: postgres: image: postgres:16.11-alpine environment: POSTGRES_INITDB_ARGS: "--nosync" POSTGRES_SHARED_BUFFERS: 256MB tmpfs: - /var/lib/postgresql/data:size=1g CODE_BLOCK: tmpfs: - /var/lib/postgresql/data:size=1g Enter fullscreen mode Exit fullscreen mode CODE_BLOCK: tmpfs: - /var/lib/postgresql/data:size=1g CODE_BLOCK: tmpfs: - /var/lib/postgresql/data:size=1g CODE_BLOCK: API::FilamentSupplierAssortmentsTest#test_create_validation_negative_price = 25.536s API::FilamentSupplierAssortmentsTest#test_list_with_a_single_assortment = 29.996s API::FilamentSupplierAssortmentsTest#test_list_missing_token = 25.952s Enter fullscreen mode Exit fullscreen mode CODE_BLOCK: API::FilamentSupplierAssortmentsTest#test_create_validation_negative_price = 25.536s API::FilamentSupplierAssortmentsTest#test_list_with_a_single_assortment = 29.996s API::FilamentSupplierAssortmentsTest#test_list_missing_token = 25.952s CODE_BLOCK: API::FilamentSupplierAssortmentsTest#test_create_validation_negative_price = 25.536s API::FilamentSupplierAssortmentsTest#test_list_with_a_single_assortment = 29.996s API::FilamentSupplierAssortmentsTest#test_list_missing_token = 25.952s CODE_BLOCK: API::FilamentSupplierAssortmentsTest#test_list_as_uber_curator = 0.474s API::FilamentSupplierAssortmentsTest#test_list_as_assistant = 0.466s API::FilamentSupplierAssortmentsTest#test_for_pressman_without_filament_supplier = 0.420s Enter fullscreen mode Exit fullscreen mode CODE_BLOCK: API::FilamentSupplierAssortmentsTest#test_list_as_uber_curator = 0.474s API::FilamentSupplierAssortmentsTest#test_list_as_assistant = 0.466s API::FilamentSupplierAssortmentsTest#test_for_pressman_without_filament_supplier = 0.420s CODE_BLOCK: API::FilamentSupplierAssortmentsTest#test_list_as_uber_curator = 0.474s API::FilamentSupplierAssortmentsTest#test_list_as_assistant = 0.466s API::FilamentSupplierAssortmentsTest#test_for_pressman_without_filament_supplier = 0.420s CODE_BLOCK: services: postgres: image: postgres:16.11-alpine environment: POSTGRES_USER: postgres POSTGRES_PASSWORD: dbpgpassword POSTGRES_DB: api_test POSTGRES_INITDB_ARGS: "--nosync" POSTGRES_SHARED_BUFFERS: 256MB ports: - 5432 tmpfs: - /var/lib/postgresql/data:size=1g Enter fullscreen mode Exit fullscreen mode CODE_BLOCK: services: postgres: image: postgres:16.11-alpine environment: POSTGRES_USER: postgres POSTGRES_PASSWORD: dbpgpassword POSTGRES_DB: api_test POSTGRES_INITDB_ARGS: "--nosync" POSTGRES_SHARED_BUFFERS: 256MB ports: - 5432 tmpfs: - /var/lib/postgresql/data:size=1g CODE_BLOCK: services: postgres: image: postgres:16.11-alpine environment: POSTGRES_USER: postgres POSTGRES_PASSWORD: dbpgpassword POSTGRES_DB: api_test POSTGRES_INITDB_ARGS: "--nosync" POSTGRES_SHARED_BUFFERS: 256MB ports: - 5432 tmpfs: - /var/lib/postgresql/data:size=1g COMMAND_BLOCK: services: postgres: command: - postgres - -c - fsync=off # Skip forced disk syncs - -c - synchronous_commit=off # Async WAL writes - -c - wal_level=minimal # Minimal WAL overhead - -c - full_page_writes=off # Less WAL volume - -c - autovacuum=off # No background vacuum - -c - max_wal_size=1GB # Fewer checkpoints - -c - shared_buffers=256MB # More memory cache Enter fullscreen mode Exit fullscreen mode COMMAND_BLOCK: services: postgres: command: - postgres - -c - fsync=off # Skip forced disk syncs - -c - synchronous_commit=off # Async WAL writes - -c - wal_level=minimal # Minimal WAL overhead - -c - full_page_writes=off # Less WAL volume - -c - autovacuum=off # No background vacuum - -c - max_wal_size=1GB # Fewer checkpoints - -c - shared_buffers=256MB # More memory cache COMMAND_BLOCK: services: postgres: command: - postgres - -c - fsync=off # Skip forced disk syncs - -c - synchronous_commit=off # Async WAL writes - -c - wal_level=minimal # Minimal WAL overhead - -c - full_page_writes=off # Less WAL volume - -c - autovacuum=off # No background vacuum - -c - max_wal_size=1GB # Fewer checkpoints - -c - shared_buffers=256MB # More memory cache COMMAND_BLOCK: command: - postgres - -c - log_statement=none # Don't log any statements - -c - log_min_duration_statement=-1 # Don't log slow queries Enter fullscreen mode Exit fullscreen mode COMMAND_BLOCK: command: - postgres - -c - log_statement=none # Don't log any statements - -c - log_min_duration_statement=-1 # Don't log slow queries COMMAND_BLOCK: command: - postgres - -c - log_statement=none # Don't log any statements - -c - log_min_duration_statement=-1 # Don't log slow queries - TRUNCATE operations - instant cleanup between tests - Index updates - no disk seeks required - WAL (Write-Ahead Log) writes - purely memory operations - Checkpoint operations - no waiting for disk flushes - TRUNCATE operations between tests - PostgreSQL was syncing empty table states to disk - Database initialization at the start of each CI run - INSERT operations during test setup - creating test fixtures (users, roles, ...) - Transaction commits - each test runs in a transaction that gets rolled back - Frequent small writes during test execution - 24 tests in this file alone - Before: ~27 seconds average per test = ~648 seconds (10.8 minutes) for one test file - After: ~0.45 seconds average per test = ~11 seconds for the same file - Per-file speedup: 59x faster! - Data is ephemeral - containers are destroyed after each run - Speed matters more than durability - faster feedback loops improve developer productivity - Disk I/O is often the bottleneck - especially on older/slower CI machines - Profile your CI pipeline - we discovered disk I/O was the bottleneck, not CPU or memory - CI databases don't need production settings - optimize for speed, not durability - tmpfs is the ultimate disk I/O eliminator - everything in RAM means zero disk bottleneck - Small configuration changes can have big impacts - three settings saved us 50 minutes per run - Consider your hardware - these optimizations were especially important on older machines with slow disks - Watch your memory usage - tmpfs consumes RAM; ensure your CI runners have enough (1GB+ for the database) - ❌ File system overhead - ext4/xfs metadata operations - ❌ Disk seeks - mechanical latency on HDDs, limited IOPS on SSDs - ❌ Kernel buffer cache - memory copies between user/kernel space - ❌ Docker overlay2 - additional storage driver overhead - ❌ Complexity - 7+ settings to manage and understand - ✅ Pure RAM operations - no physical storage involved - ✅ Zero disk I/O - everything happens in memory - ✅ Simple configuration - just one tmpfs line - ✅ Maximum performance - nothing faster than RAM - Disable query logging - reduces I/O overhead: - Use fsync=off in postgresql.conf - similar to --nosync but for runtime (redundant with tmpfs) - Increase work_mem - helps with complex queries in tests