DEV Community: Andrew Huntingdon

Performance Testing Restaurant Software: Simulating Rush Hour Traffic

Andrew Huntingdon — Tue, 28 Oct 2025 20:13:26 +0000

How we test BistroBee's performance under real-world restaurant conditions, and why generic load testing misses the point

At 6:47 PM on a Friday evening, BistroBee's response times suddenly spiked. Our monitoring showed database query times jumping from 50ms to 3 seconds. API endpoints that normally returned in under 200ms were taking 5+ seconds.

The cause wasn't a code bug or infrastructure failure. It was success.

We'd just onboarded a large restaurant group with 30 locations. That Friday evening, all 30 restaurants hit their dinner rush simultaneously. Hundreds of staff members were clocking in, managers were checking schedules, servers were swapping shifts, and the system was experiencing exactly the kind of concentrated, bursty traffic pattern that restaurants create.

Our standard load tests had shown BistroBee could handle 10,000 concurrent users. But those tests simulated steady, distributed traffic, not the reality of restaurant operations where everyone in an entire city tries to clock in at 5:00 PM sharp.

That evening taught us that performance testing restaurant software requires understanding restaurant operations, not just running generic load tests. This post shares what we learned about simulating realistic restaurant traffic patterns and building performance tests that actually matter.

Why Restaurant Traffic Is Different

Before diving into our testing approach, it's worth understanding why restaurant software creates unique performance challenges.

The Rush Hour Problem

Most web applications experience gradual traffic increases and decreases throughout the day. Restaurant software experiences sharp, predictable spikes:

Typical SaaS traffic pattern:

Traffic throughout day: ~~~~∿~~~∿~~~~∿~~~∿~~~~
Gradual peaks and valleys, relatively smooth

Restaurant software traffic pattern:

Traffic throughout day: _____|‾‾‾‾|_____|‾‾‾‾|___
Sharp spikes at meal services, nearly idle between

These spikes correspond to shift changes, meal service starts, and other predictable restaurant events.

The Thundering Herd Effect

When a restaurant's dinner shift starts at 5:00 PM, it's not just one person logging in, it's the entire serving staff, kitchen crew, and management team all accessing the system within a 5-minute window.

Multiply this across dozens or hundreds of restaurant locations in the same time zone, and you get concentrated load spikes that dwarf the steady-state traffic levels.

The Zero-Tolerance Reality

Performance degradation in restaurant software has immediate, visible impacts:

Staff can't clock in, backing up at the time clock
Managers can't access schedules during shift changes
Real-time updates lag during busy service periods
Mobile apps become unusable exactly when most needed

Unlike many SaaS applications where slight slowdowns are annoying but tolerable, restaurant software performance issues create operational chaos during the business's most critical moments.

Our Performance Testing Philosophy

Based on these realities, we developed performance testing principles specific to restaurant operations:

Test Real Patterns, Not Uniform Load

Generic load testing tools simulate uniform traffic, X users steadily making Y requests. This doesn't match restaurant reality.

We simulate actual restaurant behavior:

Shift change login spikes
Hourly schedule checks during service
End-of-shift activity bursts
Late-night schedule adjustments

Test the Right Metrics

Response time averages can be misleading. We focus on:

P95 and P99 response times: The worst 5% and 1% of requests matter most. These are what users actually experience during peak load.

Error rates under load: When load spikes, do requests fail or just slow down? Failures are worse than slowness.

Recovery time: How quickly does the system return to normal after a spike passes?

Database connection pool saturation: Are we running out of database connections during peaks?

Test Failure Scenarios

Restaurant software can't just perform well under ideal conditions, it needs to handle:

Database slowdowns
Network issues
Dependency failures
Partial outages

Our testing includes deliberate failure injection to verify graceful degradation.

Building Realistic Test Scenarios

Here's how we model actual restaurant traffic patterns in our performance tests.

Scenario 1: The Dinner Shift Login Storm

Real-world pattern: At 5:00 PM, 50 staff members need to clock in for dinner service at a single location.

Test implementation:

// k6 load testing script
import http from 'k6/http';
import { check, sleep } from 'k6';
import { Rate } from 'k6/metrics';

const failureRate = new Rate('failed_requests');

export const options = {
  scenarios: {
    dinner_shift_login: {
      executor: 'ramping-arrival-rate',
      startRate: 0,
      timeUnit: '1m',
      preAllocatedVUs: 100,
      stages: [
        // Ramp from 0 to 50 logins per minute over 2 minutes
        { target: 50, duration: '2m' },
        // Hold at 50 per minute for 3 minutes (peak)
        { target: 50, duration: '3m' },
        // Ramp down over 5 minutes (stragglers)
        { target: 5, duration: '5m' },
      ],
    },
  },
  thresholds: {
    'http_req_duration{scenario:dinner_shift_login}': [
      'p(95)<500', // 95% of requests complete in under 500ms
      'p(99)<1000', // 99% complete in under 1s
    ],
    'failed_requests': ['rate<0.01'], // Less than 1% error rate
  },
};

export default function () {
  // Simulate staff login
  const loginResponse = http.post(
    'https://api.bistrobee.com/auth/login',
    JSON.stringify({
      email: `staff${__VU}@testrestaurant.com`,
      password: 'testpass123',
      restaurantId: 'test-restaurant-1',
    }),
    { headers: { 'Content-Type': 'application/json' } }
  );

  const loginSuccess = check(loginResponse, {
    'login successful': (r) => r.status === 200,
    'has auth token': (r) => r.json('token') !== undefined,
  });

  failureRate.add(!loginSuccess);

  if (loginSuccess) {
    const token = loginResponse.json('token');

    // Immediately check today's schedule (typical behavior)
    const scheduleResponse = http.get(
      'https://api.bistrobee.com/schedule/today',
      {
        headers: { Authorization: `Bearer ${token}` },
      }
    );

    check(scheduleResponse, {
      'schedule loaded': (r) => r.status === 200,
      'schedule response time acceptable': (r) => r.timings.duration < 500,
    });
  }

  // Random think time between 5-15 seconds
  sleep(Math.random() * 10 + 5);
}

This test simulates the concentrated login spike that happens at shift changes, measuring how the system handles authentication and immediate schedule lookups under peak load.

Scenario 2: Multi-Location Rush Hour

Real-world pattern: 50 restaurant locations across a region all hit dinner rush between 5:00-7:00 PM.

Test implementation:

export const options = {
  scenarios: {
    // East Coast restaurants (5 PM ET = 2 PM PT)
    east_coast_rush: {
      executor: 'ramping-arrival-rate',
      startTime: '0s',
      startRate: 0,
      timeUnit: '1m',
      preAllocatedVUs: 500,
      stages: [
        { target: 250, duration: '5m' }, // Rush builds
        { target: 250, duration: '15m' }, // Peak
        { target: 50, duration: '10m' }, // Dies down
      ],
    },
    // Central restaurants (6 PM CT = 4 PM PT)
    central_rush: {
      executor: 'ramping-arrival-rate',
      startTime: '60m', // Start 1 hour after east coast
      startRate: 0,
      timeUnit: '1m',
      preAllocatedVUs: 300,
      stages: [
        { target: 150, duration: '5m' },
        { target: 150, duration: '15m' },
        { target: 30, duration: '10m' },
      ],
    },
    // West Coast restaurants (5 PM PT)
    west_coast_rush: {
      executor: 'ramping-arrival-rate',
      startTime: '180m', // Start 3 hours after east coast
      startRate: 0,
      timeUnit: '1m',
      preAllocatedVUs: 400,
      stages: [
        { target: 200, duration: '5m' },
        { target: 200, duration: '15m' },
        { target: 40, duration: '10m' },
      ],
    },
  },
};

export default function () {
  const location = __ENV.SCENARIO; // east_coast_rush, central_rush, west_coast_rush

  // Different behaviors based on time zone
  const restaurantId = `test-restaurant-${location}-${__VU % 50}`;

  // Simulate typical rush hour activities
  performLoginAndScheduleCheck(restaurantId);
  sleep(Math.random() * 30);

  performScheduleUpdates(restaurantId);
  sleep(Math.random() * 60);

  performEndOfShiftActivities(restaurantId);
}

This test simulates the reality of serving customers across multiple time zones, each creating load spikes at their local peak hours.

Scenario 3: The Weekend Chaos Pattern

Real-world pattern: Weekend dinner service is 2-3x busier than weekdays, with more concurrent activity.

Test implementation:

export const options = {
  scenarios: {
    weekend_peak: {
      executor: 'ramping-vus',
      startVUs: 0,
      stages: [
        // Friday happy hour (4-7 PM)
        { duration: '30m', target: 500 },
        { duration: '3h', target: 500 },
        // Late night wind down
        { duration: '2h', target: 100 },
        // Saturday brunch build (9 AM - 2 PM)
        { duration: '1h', target: 400 },
        { duration: '5h', target: 400 },
        // Brief afternoon lull
        { duration: '2h', target: 150 },
        // Saturday dinner rush (5-10 PM)
        { duration: '1h', target: 800 },
        { duration: '5h', target: 800 },
        // Late night
        { duration: '2h', target: 200 },
      ],
      gracefulRampDown: '30m',
    },
  },
};

// Mix of activities during peak hours
export default function () {
  const activities = [
    scheduleChecks,
    shiftSwapRequests,
    timeClockPunches,
    breakScheduling,
    staffMessaging,
    managerDashboardViews,
  ];

  // Randomly select activities weighted by real usage patterns
  const activity = weightedRandom(activities, [30, 15, 25, 10, 15, 5]);
  activity();

  sleep(Math.random() * 45 + 15); // 15-60 second think time
}

This marathon test runs for hours, simulating sustained weekend traffic patterns including multiple peak periods.

Scenario 4: The Emergency Scenario

Real-world pattern: A key staff member calls out 30 minutes before shift, triggering frantic scheduling activity.

Test implementation:

export const options = {
  scenarios: {
    normal_operations: {
      executor: 'constant-arrival-rate',
      rate: 50,
      timeUnit: '1m',
      duration: '30m',
      preAllocatedVUs: 100,
    },
    emergency_spike: {
      executor: 'ramping-arrival-rate',
      startTime: '15m', // Emergency happens 15 minutes in
      startRate: 0,
      timeUnit: '1m',
      preAllocatedVUs: 200,
      stages: [
        { target: 150, duration: '2m' }, // Managers check coverage
        { target: 150, duration: '5m' }, // Frantically contacting staff
        { target: 50, duration: '5m' }, // Resolution
      ],
    },
  },
};

export default function () {
  if (__ENV.SCENARIO === 'emergency_spike') {
    // Emergency behavior: rapid-fire schedule checks and updates
    checkStaffAvailability();
    sendCoverageRequests();
    checkForResponses();
    updateSchedule();
    // Minimal think time during emergencies
    sleep(Math.random() * 5 + 2);
  } else {
    // Normal operations
    normalSchedulingActivity();
    sleep(Math.random() * 60 + 30);
  }
}

This test verifies the system can handle sudden load spikes on top of baseline traffic, a critical real-world scenario.

Database Performance Under Load

Restaurant software is database-intensive. Much of our performance testing focuses on database behavior under load.

Connection Pool Monitoring

We monitor database connection pool utilization during load tests:

// Custom k6 extension to check database metrics
import { Counter, Gauge } from 'k6/metrics';

const dbConnections = new Gauge('db_connections_active');
const dbQueuedRequests = new Counter('db_requests_queued');

export function setup() {
  // Start monitoring database connection pool
  return {
    dbMonitoringEndpoint: 'https://api.bistrobee.com/internal/metrics',
  };
}

export default function (data) {
  // Normal test activities
  performTestActions();

  // Periodically check database health
  if (Math.random() < 0.1) { // 10% of iterations
    const metrics = http.get(data.dbMonitoringEndpoint).json();
    dbConnections.add(metrics.database.connectionsActive);
    if (metrics.database.requestsQueued > 0) {
      dbQueuedRequests.add(metrics.database.requestsQueued);
    }
  }
}

If we see connection pool saturation (queued requests), we know we need to optimize queries or scale database resources.

Query Performance Analysis

During load tests, we capture slow query logs:

-- PostgreSQL slow query logging
ALTER SYSTEM SET log_min_duration_statement = 100; -- Log queries > 100ms
ALTER SYSTEM SET log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h ';
SELECT pg_reload_conf();

Then analyze which queries degrade under load:

# Analyze slow queries during load test
pg_badger postgresql.log --outfile=/tmp/report.html --jobs 4

# Common issues we find:
# - Missing indexes on frequently joined tables
# - N+1 queries in ORM code
# - Inefficient aggregations
# - Lock contention on hot tables

Database Optimization Example

Here's a real optimization we made based on load testing:

Problem: During shift changes, fetching available staff for a shift was slow.

Original query:

-- Took 2-3 seconds under load
SELECT s.* FROM staff s
WHERE s.restaurant_id = $1
AND s.active = true
AND NOT EXISTS (
  SELECT 1 FROM shifts sh
  WHERE sh.staff_id = s.id
  AND sh.start_time <= $3
  AND sh.end_time >= $2
);

Optimized query:

-- Takes 50-100ms under load
SELECT s.* FROM staff s
LEFT JOIN shifts sh ON (
  sh.staff_id = s.id
  AND sh.start_time <= $3
  AND sh.end_time >= $2
)
WHERE s.restaurant_id = $1
AND s.active = true
AND sh.id IS NULL;

-- Added index:
CREATE INDEX idx_shifts_staff_time ON shifts(staff_id, start_time, end_time)
WHERE end_time >= CURRENT_DATE;

This 40x improvement came from load testing revealing the bottleneck.

Caching Strategy Testing

Caching is crucial for handling rush hour load. We test our caching strategy's effectiveness:

Redis Cache Hit Rate Monitoring

import { Trend } from 'k6/metrics';

const cacheHitRate = new Trend('cache_hit_rate');

export default function () {
  const scheduleResponse = http.get(
    'https://api.bistrobee.com/schedule/today',
    { headers: { Authorization: `Bearer ${token}` } }
  );

  // Check if response came from cache
  const fromCache = scheduleResponse.headers['X-Cache-Status'] === 'HIT';
  cacheHitRate.add(fromCache ? 100 : 0);

  check(scheduleResponse, {
    'cached response fast': (r) =>
      r.headers['X-Cache-Status'] === 'HIT' ? r.timings.duration < 100 : true,
  });
}

We aim for 80%+ cache hit rates during steady-state operations.

Cache Invalidation Testing

The tricky part of caching is invalidation. We test scenarios that should clear cached data:

// Test cache invalidation behavior
export function testCacheInvalidation() {
  const token = login();

  // Fetch schedule (should cache)
  const schedule1 = getSchedule(token);
  check(schedule1.headers, { 'initial fetch cached': (h) => h['X-Cache-Status'] === 'MISS' });

  // Fetch again (should hit cache)
  const schedule2 = getSchedule(token);
  check(schedule2.headers, { 'second fetch from cache': (h) => h['X-Cache-Status'] === 'HIT' });

  // Update schedule
  updateSchedule(token, { shiftId: 'shift-123', startTime: '18:00' });

  // Fetch again (cache should be invalidated)
  const schedule3 = getSchedule(token);
  check(schedule3.headers, { 'cache invalidated after update': (h) => h['X-Cache-Status'] === 'MISS' });

  // Verify schedule reflects update
  check(schedule3.json(), {
    'updated data returned': (data) => 
      data.shifts.find(s => s.id === 'shift-123').startTime === '18:00',
  });
}

Stale cache data is worse than no cache, so we extensively test invalidation logic.

Failure Mode Testing

Performance isn't just about speed, it's about graceful degradation when things go wrong.

Database Slowdown Simulation

We use Toxiproxy to inject latency into database connections:

# Add 500ms latency to database connections
toxiproxy-cli toxic add bistrobee-db -t latency -a latency=500

# Run load test while database is slow
k6 run --vus 200 --duration 10m load-test.js

# Remove latency
toxiproxy-cli toxic remove bistrobee-db latency

We verify that:

Request timeout handling works correctly
Connection pools don't exhaust
Users see appropriate error messages
System recovers when database performance returns

Dependency Failure Testing

BistroBee integrates with external services (email, SMS, payment processors). We test what happens when they fail during peak load:

export const options = {
  scenarios: {
    normal_load: {
      executor: 'constant-vus',
      vus: 100,
      duration: '20m',
    },
  },
  thresholds: {
    // Core operations should succeed even if dependencies fail
    'http_req_duration{endpoint:schedule}': ['p(95)<500'],
    'http_req_duration{endpoint:shifts}': ['p(95)<500'],
    // Dependent operations may degrade but shouldn't bring down core
    'http_req_duration{endpoint:notifications}': ['p(95)<2000'],
  },
};

export default function () {
  // Core operations should always work
  const schedule = getSchedule();
  check(schedule, { 'schedule loads': (r) => r.status === 200 });

  // Try to send notification (might fail if SMS service down)
  const notification = sendShiftReminder();
  // Don't fail test if notification fails - just log it
  if (notification.status !== 200) {
    console.warn('Notification failed - acceptable during dependency outage');
  }
}

Circuit breakers should prevent cascading failures:

// Circuit breaker implementation
class CircuitBreaker {
  constructor(service, threshold = 5, timeout = 60000) {
    this.service = service;
    this.failureThreshold = threshold;
    this.timeout = timeout;
    this.failureCount = 0;
    this.state = 'CLOSED'; // CLOSED, OPEN, HALF_OPEN
    this.nextAttempt = Date.now();
  }

  async execute(operation) {
    if (this.state === 'OPEN') {
      if (Date.now() < this.nextAttempt) {
        throw new Error(`Circuit breaker OPEN for ${this.service}`);
      }
      // Try half-open
      this.state = 'HALF_OPEN';
    }

    try {
      const result = await operation();
      this.onSuccess();
      return result;
    } catch (error) {
      this.onFailure();
      throw error;
    }
  }

  onSuccess() {
    this.failureCount = 0;
    this.state = 'CLOSED';
  }

  onFailure() {
    this.failureCount++;
    if (this.failureCount >= this.failureThreshold) {
      this.state = 'OPEN';
      this.nextAttempt = Date.now() + this.timeout;
    }
  }
}

Continuous Performance Monitoring

Load testing isn't just a pre-release activity, we run tests continuously:

Scheduled Performance Tests

# GitHub Actions workflow
name: Performance Tests
on:
  schedule:
    - cron: '0 2 * * *' # 2 AM daily
  workflow_dispatch: # Manual trigger

jobs:
  load-test:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v2

      - name: Run load tests
        run: |
          k6 run --out json=results.json load-tests/dinner-rush.js

      - name: Check performance thresholds
        run: |
          python scripts/analyze-performance.py results.json

      - name: Alert on degradation
        if: failure()
        uses: slackapi/slack-github-action@v1
        with:
          payload: |
            {
              "text": "Performance regression detected in nightly tests"
            }

Production Performance Monitoring

We monitor real production traffic to catch performance issues early:

// Application performance monitoring
const apm = require('elastic-apm-node').start({
  serviceName: 'bistrobee-api',
  serverUrl: process.env.APM_SERVER_URL,
  transactionSampleRate: 0.1, // Sample 10% of transactions
});

// Track custom metrics
app.use((req, res, next) => {
  const transaction = apm.currentTransaction;
  if (transaction) {
    transaction.setLabel('restaurant_id', req.restaurantId);
    transaction.setLabel('user_role', req.user.role);
    transaction.setLabel('time_of_day', new Date().getHours());
  }
  next();
});

This lets us see performance patterns by restaurant, user role, and time of day, identifying issues before customers complain.

Performance Budgets

We maintain performance budgets for critical operations:

Operation	P95 Target	P99 Limit	Under Load
Login	200ms	500ms	500ms
Schedule fetch	150ms	300ms	400ms
Schedule update	250ms	500ms	750ms
Shift swap	300ms	600ms	1000ms
Time clock punch	100ms	200ms	300ms

Automated tests fail if any operation exceeds its budget during load testing.

Lessons Learned

Building effective performance tests for BistroBee taught us:

Generic Load Tests Are Insufficient

Tools like Apache Bench or wrk can generate traffic, but they don't simulate realistic restaurant usage patterns. We needed custom test scenarios based on actual behavior.

Peak Performance Matters Most

Average performance during low load is irrelevant. What matters is how the system performs during Friday dinner rush when 500 restaurants are simultaneously active.

Database Is Usually the Bottleneck

Almost every performance problem we've found traces back to database queries, connection pools, or locking issues. Database optimization is continuous work.

Caching Is Essential but Tricky

Aggressive caching handles load spikes effectively, but cache invalidation bugs create data consistency issues. Testing both cache performance and correctness is critical.

Real-World Failure Testing Matters

Perfect conditions don't exist in production. Testing how the system degrades when dependencies fail prevents cascading outages during actual incidents.

The Bottom Line

Performance testing restaurant software requires understanding restaurant operations, not just running generic load tests.

Rush hour traffic patterns, thundering herd effects during shift changes, and zero-tolerance for slowdowns during service create unique requirements that standard load testing approaches miss.

By simulating realistic traffic patterns, focusing on peak performance rather than averages, extensively testing database performance under load, and validating graceful degradation during failures, we ensure BistroBee performs when it matters most, during the dinner rush when restaurants can't afford slowdowns.

The Friday evening incident that started this post? It led to database query optimizations, improved connection pooling, and more aggressive caching. Those changes came from performance tests that simulated actual restaurant rush hour patterns, not from generic load testing.

Sometimes the best performance tests aren't the ones that generate the most traffic, they're the ones that generate the right kind of traffic.

Questions about performance testing patterns for domain-specific applications? Want to share your own approaches to realistic load testing? Drop a comment , we love talking about performance engineering.

Database Design for Multi-Tenant Restaurant Software: Lessons Learned

Andrew Huntingdon — Tue, 14 Oct 2025 19:55:01 +0000

How we evolved from a simple shared schema to a hybrid approach that balances performance, isolation, and operational complexity

"We need to completely rebuild the database layer."

I was staring at our production database, watching a single poorly-optimized query from one restaurant slow down operations for hundreds of others. It was 2 AM, we'd just onboarded our largest customer yet, and their first day of real usage was bringing our entire platform to its knees.

The problem wasn't the query itself, it was our fundamental architectural decision to share database tables across all tenants with simple tenant_id filtering. This approach had worked fine when we had 20 customers. At 200 customers, with one processing thousands of transactions per hour, we were discovering its limitations the hard way.

This is the story of how we evolved BistroBee's database architecture, the painful lessons we learned about multi-tenancy in restaurant software, and the hybrid approach we eventually landed on that balances performance, data isolation, and operational complexity.

The Three Multi-Tenancy Approaches

For developers building SaaS applications, the multi-tenancy question is fundamental: how do you store data for multiple customers in a way that's secure, performant, and maintainable?

The three standard approaches each have clear tradeoffs:

Approach 1: Shared Database, Shared Schema

Every tenant's data lives in the same tables, distinguished by a tenant_id column:

CREATE TABLE restaurants (
  id UUID PRIMARY KEY,
  tenant_id UUID NOT NULL,
  name VARCHAR(255),
  address TEXT,
  -- ... other fields
);

CREATE TABLE shifts (
  id UUID PRIMARY KEY,
  tenant_id UUID NOT NULL,
  restaurant_id UUID REFERENCES restaurants(id),
  staff_member_id UUID,
  start_time TIMESTAMP,
  end_time TIMESTAMP,
  -- ... other fields
);

CREATE INDEX idx_shifts_tenant ON shifts(tenant_id);

Advantages:

Simple to implement initially
Easy to add new tenants—no infrastructure changes needed
Straightforward database migrations affect all tenants at once
Cross-tenant analytics and reporting are trivial

Disadvantages:

One tenant's bad query affects everyone
Data isolation depends entirely on application logic
Difficult to tune performance for tenants with vastly different usage patterns
Database backups and restores affect all tenants
Index bloat as total data grows

This is where we started with BistroBee, and it served us well initially.

Approach 2: Shared Database, Separate Schemas

Each tenant gets their own schema within a shared database:

-- Tenant 1
CREATE SCHEMA tenant_abc123;
CREATE TABLE tenant_abc123.restaurants (...);
CREATE TABLE tenant_abc123.shifts (...);

-- Tenant 2  
CREATE SCHEMA tenant_xyz789;
CREATE TABLE tenant_xyz789.restaurants (...);
CREATE TABLE tenant_xyz789.shifts (...);

Advantages:

Better logical isolation than shared schema
Per-tenant query performance tuning
Easier to understand data boundaries
Can restore individual tenant data more easily

Disadvantages:

Database migrations require running against every schema
Cross-tenant queries become more complex
Schema proliferation can cause operational challenges
Still subject to noisy neighbor problems at the database level

Approach 3: Database Per Tenant

Each tenant gets their own dedicated database:

// Connection routing logic
function getDatabaseConnection(tenantId) {
  const dbConfig = {
    'tenant-abc123': { host: 'db1.example.com', database: 'tenant_abc123' },
    'tenant-xyz789': { host: 'db2.example.com', database: 'tenant_xyz789' }
  };
  return createConnection(dbConfig[tenantId]);
}

Advantages:

Complete performance isolation
Simplified backup and restore per tenant
Easy to move individual tenants between servers
Natural data boundaries

Disadvantages:

Complex connection management
Database migrations across hundreds of databases
Higher infrastructure costs
Cross-tenant analytics require complex aggregation
Operational overhead scales linearly with tenant count

Each approach has advocates and detractors. The right choice depends heavily on your specific requirements, scale, and operational capabilities.

Our Journey: What Actually Happened

Phase 1: Shared Schema Success (Months 1-8)

We launched BistroBee with a straightforward shared schema approach. Every table had a tenant_id column, and our ORM automatically injected tenant filters into every query:

// Sequelize model with automatic tenant scoping
class Shift extends Model {}
Shift.init({
  id: { type: DataTypes.UUID, primaryKey: true },
  tenantId: { type: DataTypes.UUID, allowNull: false },
  restaurantId: DataTypes.UUID,
  staffMemberId: DataTypes.UUID,
  startTime: DataTypes.DATE,
  endTime: DataTypes.DATE
}, {
  sequelize,
  modelName: 'Shift',
  defaultScope: {
    // Automatically filter by tenant
    where: {
      tenantId: sequelize.literal('current_tenant_id()')
    }
  }
});

This worked beautifully for our first dozen customers. Development velocity was high, and database management was straightforward.

Phase 2: The Performance Wall (Months 9-12)

As we grew past 50 customers, problems emerged:

Problem 1: Index bloat
Our shifts table grew to millions of rows. Even with tenant_id indexes, queries were slowing down because the database had to scan through massive amounts of irrelevant data.

-- This query had to scan through all tenants' data
SELECT * FROM shifts 
WHERE tenant_id = 'abc-123' 
  AND start_time > '2024-01-01'
  AND staff_member_id = 'xyz-789';

-- Even with indexes, the query planner struggled
EXPLAIN showed sequential scans despite proper indexing

Problem 2: The noisy neighbor
One restaurant chain with 50 locations was processing vastly more data than our typical 1-2 location customers. Their bulk operations would slow the entire system.

Problem 3: Data skew
Our largest tenant had 100x more data than our smallest. Query optimization that worked well for small tenants performed terribly for large ones, and vice versa.

Problem 4: Connection pool exhaustion
During peak hours, we'd hit PostgreSQL connection limits because every request needed a connection regardless of which tenant it served.

Phase 3: The Failed Attempt (Months 13-15)

We decided to move to database-per-tenant. In hindsight, this was an overreaction to performance problems that could have been solved other ways.

Implementation nightmare:

// Connection management became a nightmare
class ConnectionPool {
  constructor() {
    this.pools = new Map();
  }

  getConnection(tenantId) {
    if (!this.pools.has(tenantId)) {
      // Create new connection pool on-demand
      const config = this.getTenantConfig(tenantId);
      this.pools.set(tenantId, new Pool(config));
    }
    return this.pools.get(tenantId);
  }

  // Memory leaks lurked here - we never properly cleaned up pools
}

Migration hell:
Running database migrations across 100+ databases was complex and error-prone. We built tooling to manage it, but the operational overhead was substantial:

# Our migration script
for tenant_db in $(list_tenant_databases); do
  echo "Migrating $tenant_db..."
  DATABASE_URL=$tenant_db npm run migrate
  if [ $? -ne 0 ]; then
    echo "FAILED: $tenant_db"
    # Now what? Rollback? Continue? 
  fi
done

Cost explosion:
Infrastructure costs increased dramatically. We needed connection pools for each database, backup storage multiplied, and monitoring became exponentially more complex.

After three months of struggling with this approach, we made the difficult decision to abandon it and find a hybrid solution.

Phase 4: The Hybrid Approach (Months 16-present)

We landed on an approach that combines the best elements of each strategy:

Tier-based architecture:

Small tenants (1-3 locations): Shared database, shared schema with partitioning
Medium tenants (4-20 locations): Shared database, dedicated schema
Large tenants (20+ locations): Dedicated database

This lets us optimize for the characteristics of each tenant segment.

Technical Implementation: What We Actually Built

Schema Design with Partitioning

For shared schema tenants, we implemented PostgreSQL table partitioning:

-- Parent table
CREATE TABLE shifts (
  id UUID NOT NULL,
  tenant_id UUID NOT NULL,
  restaurant_id UUID,
  staff_member_id UUID,
  start_time TIMESTAMP,
  end_time TIMESTAMP,
  created_at TIMESTAMP DEFAULT NOW()
) PARTITION BY LIST (tenant_id);

-- Create partition for each tenant
CREATE TABLE shifts_tenant_abc123 
PARTITION OF shifts 
FOR VALUES IN ('abc-123');

CREATE TABLE shifts_tenant_xyz789
PARTITION OF shifts
FOR VALUES IN ('xyz-789');

-- Indexes on partitions, not parent
CREATE INDEX idx_shifts_abc123_start_time 
ON shifts_tenant_abc123(start_time);

This gave us the operational simplicity of shared schema with much better query performance—queries only scan relevant partitions.

Intelligent Connection Routing

We built a connection router that directs queries to the appropriate database:

class DatabaseRouter {
  constructor() {
    this.sharedPool = new Pool(sharedDbConfig);
    this.dedicatedPools = new Map();
  }

  async getConnection(tenantId) {
    const tenant = await this.getTenantConfig(tenantId);

    if (tenant.tier === 'dedicated') {
      if (!this.dedicatedPools.has(tenantId)) {
        this.dedicatedPools.set(
          tenantId, 
          new Pool(tenant.dbConfig)
        );
      }
      return this.dedicatedPools.get(tenantId);
    }

    // Use shared pool for small/medium tenants
    return this.sharedPool;
  }

  // Cleanup unused connections periodically
  async cleanup() {
    for (const [tenantId, pool] of this.dedicatedPools) {
      const lastUsed = this.getLastUsedTime(tenantId);
      if (Date.now() - lastUsed > IDLE_TIMEOUT) {
        await pool.end();
        this.dedicatedPools.delete(tenantId);
      }
    }
  }
}

Migration Framework

We built tooling to handle migrations across our heterogeneous database setup:

class MigrationRunner {
  async runMigration(migration) {
    // Run on shared database
    await this.runOnDatabase(sharedDb, migration);

    // Run on each dedicated database
    for (const tenantId of this.getDedicatedTenants()) {
      try {
        const db = await this.getConnection(tenantId);
        await this.runOnDatabase(db, migration);
        await this.recordSuccess(tenantId, migration);
      } catch (error) {
        await this.recordFailure(tenantId, migration, error);
        // Continue with other tenants
      }
    }

    // Generate report of migration status
    return this.getMigrationReport(migration);
  }
}

Tenant Migration Strategy

We needed a way to migrate tenants between tiers as they grew:

class TenantMigrator {
  async promoteToDedicated(tenantId) {
    // 1. Provision new dedicated database
    const newDb = await this.provisionDatabase(tenantId);

    // 2. Copy data from shared to dedicated
    await this.copyTenantData(tenantId, sharedDb, newDb);

    // 3. Enable dual-writes (to both databases)
    await this.enableDualWrites(tenantId);

    // 4. Wait for replication to catch up
    await this.waitForSync(tenantId);

    // 5. Switch reads to new database
    await this.cutoverReads(tenantId);

    // 6. Disable writes to old database
    await this.disableDualWrites(tenantId);

    // 7. Clean up old data after safety period
    await this.scheduleCleanup(tenantId, sharedDb);
  }
}

Restaurant-Specific Database Challenges

Beyond general multi-tenancy concerns, restaurant software has domain-specific challenges:

Time Zone Complexity

Restaurants operate in specific time zones, but corporate users might view data across multiple time zones:

-- Bad: storing local times without timezone awareness
CREATE TABLE shifts (
  start_time TIMESTAMP,  -- Which timezone?
  end_time TIMESTAMP
);

-- Better: always use timezone-aware timestamps
CREATE TABLE shifts (
  start_time TIMESTAMPTZ,
  end_time TIMESTAMPTZ,
  timezone VARCHAR(50)  -- Store restaurant's timezone
);

-- Query helper
SELECT 
  start_time AT TIME ZONE restaurants.timezone AS local_start_time,
  start_time AT TIME ZONE 'UTC' AS utc_start_time
FROM shifts
JOIN restaurants ON shifts.restaurant_id = restaurants.id;

High-Frequency Transaction Data

Point-of-sale transactions generate enormous data volumes:

// Separate hot and cold data
const OrderSchema = {
  // Hot: current/recent orders
  orders: {
    tableName: 'orders',
    // Partition by created_at for efficient archival
  },

  // Cold: historical orders
  orders_archive: {
    tableName: 'orders_archive',
    // Moved here after 90 days
  }
};

// Automatic archival process
async function archiveOldOrders() {
  await db.query(`
    INSERT INTO orders_archive 
    SELECT * FROM orders 
    WHERE created_at < NOW() - INTERVAL '90 days'
  `);

  await db.query(`
    DELETE FROM orders 
    WHERE created_at < NOW() - INTERVAL '90 days'
  `);
}

Menu and Pricing Historization

Restaurants need to track menu changes over time for accurate historical reporting:

-- Naive approach: updates lose history
CREATE TABLE menu_items (
  id UUID PRIMARY KEY,
  name VARCHAR(255),
  price DECIMAL(10,2),
  updated_at TIMESTAMP
);

-- Better: maintain price history
CREATE TABLE menu_items (
  id UUID PRIMARY KEY,
  name VARCHAR(255),
  current_price_id UUID REFERENCES prices(id)
);

CREATE TABLE prices (
  id UUID PRIMARY KEY,
  menu_item_id UUID REFERENCES menu_items(id),
  price DECIMAL(10,2),
  valid_from TIMESTAMP,
  valid_until TIMESTAMP,  -- NULL for current price
  created_by UUID
);

-- Query current price
SELECT p.price FROM menu_items m
JOIN prices p ON m.current_price_id = p.id
WHERE m.id = 'item-id';

-- Query historical price
SELECT p.price FROM prices p
WHERE p.menu_item_id = 'item-id'
  AND p.valid_from <= '2024-01-15'
  AND (p.valid_until IS NULL OR p.valid_until > '2024-01-15');

Reservation and Booking Complexity

Table management requires handling complex constraints:

CREATE TABLE table_bookings (
  id UUID PRIMARY KEY,
  tenant_id UUID,
  table_id UUID,
  booking_time TIMESTAMPTZ,
  duration_minutes INTEGER,
  party_size INTEGER,
  status VARCHAR(50),

  -- Constraint: prevent double-booking
  EXCLUDE USING gist (
    table_id WITH =,
    tstzrange(
      booking_time, 
      booking_time + (duration_minutes || ' minutes')::INTERVAL
    ) WITH &&
  ) WHERE (status != 'cancelled')
);

Performance Lessons Learned

Lesson 1: Index Strategy Matters More Than You Think

We initially added indexes liberally. This caused problems:

-- Bad: too many indexes slow down writes
CREATE INDEX idx_shifts_tenant ON shifts(tenant_id);
CREATE INDEX idx_shifts_restaurant ON shifts(restaurant_id);
CREATE INDEX idx_shifts_staff ON shifts(staff_member_id);
CREATE INDEX idx_shifts_start ON shifts(start_time);
CREATE INDEX idx_shifts_end ON shifts(end_time);
CREATE INDEX idx_shifts_created ON shifts(created_at);

-- Better: composite indexes for common query patterns
CREATE INDEX idx_shifts_tenant_start 
ON shifts(tenant_id, start_time) 
INCLUDE (restaurant_id, staff_member_id);

CREATE INDEX idx_shifts_staff_date 
ON shifts(staff_member_id, start_time)
WHERE end_time IS NULL;  -- Partial index for active shifts

Lesson 2: Connection Pooling is Critical

We learned this the hard way:

// Bad: new connection per request
app.use(async (req, res, next) => {
  req.db = await createConnection(dbConfig);
  next();
});

// Better: shared pool with limits
const pool = new Pool({
  max: 20,  // Maximum connections
  min: 5,   // Minimum connections
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,

  // This saved us during traffic spikes
  maxWaitingClients: 100
});

Lesson 3: Query Timeouts Prevent Cascading Failures

One slow query shouldn't take down the whole system:

// Set statement timeout for all queries
await client.query('SET statement_timeout = 5000');  // 5 seconds

// Or per-query timeout
const result = await client.query({
  text: 'SELECT * FROM complex_report WHERE...',
  timeout: 10000  // 10 seconds for this specific query
});

Security and Data Isolation

Multi-tenant databases require extra security vigilance:

Row-Level Security

PostgreSQL's RLS provides an extra safety layer:

-- Enable RLS
ALTER TABLE shifts ENABLE ROW LEVEL SECURITY;

-- Policy: users can only see their tenant's data
CREATE POLICY tenant_isolation ON shifts
FOR ALL
USING (tenant_id = current_setting('app.current_tenant_id')::UUID);

-- Set tenant context at connection time
SET app.current_tenant_id = 'abc-123';

Application-Level Safeguards

Defense in depth requires application-level checks too:

class TenantAwareModel {
  static async find(id, tenantId) {
    // Always include tenant_id in where clause
    const result = await db.query(
      'SELECT * FROM shifts WHERE id = $1 AND tenant_id = $2',
      [id, tenantId]
    );

    if (!result.rows.length) {
      throw new NotFoundError('Shift not found');
    }

    return result.rows[0];
  }
}

Audit Logging

Track all cross-tenant access attempts:

async function queryWithAudit(sql, params, tenantId) {
  const startTime = Date.now();

  try {
    const result = await db.query(sql, params);

    // Log successful queries
    await auditLog.record({
      tenantId,
      sql: sql.substring(0, 1000),  // Truncate long queries
      duration: Date.now() - startTime,
      rowCount: result.rows.length
    });

    return result;
  } catch (error) {
    // Log failures too
    await auditLog.record({
      tenantId,
      sql: sql.substring(0, 1000),
      error: error.message,
      duration: Date.now() - startTime
    });
    throw error;
  }
}

Operational Realities

The technical design is only part of the story. Operations matter just as much:

Backup Strategy

Different tenants need different backup strategies:

// Small tenants: shared database backup
await backupDatabase(sharedDb, {
  frequency: 'daily',
  retention: '30 days'
});

// Large tenants: individual backups
for (const tenantId of largeTenants) {
  await backupDatabase(getTenantDb(tenantId), {
    frequency: 'hourly',
    retention: '90 days',
    pointInTimeRecovery: true
  });
}

Monitoring and Alerting

We monitor different metrics based on tenant tier:

// Shared database: watch for slow queries affecting multiple tenants
monitorSharedDb({
  slowQueryThreshold: 500,  // ms
  alertOn: (query) => {
    return query.scansMultiplePartitions;
  }
});

// Dedicated databases: watch for individual tenant issues
for (const tenantId of dedicatedTenants) {
  monitorTenantDb(tenantId, {
    connectionPoolUtilization: 0.8,
    queryTimeoutRate: 0.01,
    diskUtilization: 0.75
  });
}

Conclusion: There Is No Perfect Solution

After two years of evolution, our hybrid approach works well for BistroBee, but it's not perfect. We still face challenges:

Database migrations are complex
Connection management requires vigilance
Deciding when to migrate tenants between tiers involves judgment calls
Operational overhead is higher than a pure shared-schema approach

But the tradeoffs are worth it. We can offer appropriate isolation and performance to each customer segment without the operational burden of treating every tenant identically.

The biggest lesson: multi-tenant database design is about understanding your customers' needs and usage patterns, then choosing architecture that serves those needs—not about finding the theoretically "best" approach.

For BistroBee, that meant accepting complexity in exchange for flexibility. For your application, the right tradeoffs might be different.

The key is making those decisions deliberately, understanding the consequences, and building systems that can evolve as your understanding improves.

What's your experience with multi-tenant database design? What approaches have worked well for your applications, and what unexpected challenges have you encountered? Share your experiences in the comments.