check-database-scaling

Database Scaling Check

Analyze PHP code for database access patterns that prevent horizontal scaling, overload the primary database, and miss read replica offloading opportunities.

Detection Patterns

1. Single DB Connection for All Queries

<?php

declare(strict_types=1);

// BAD: All queries use the same connection
final readonly class DatabaseConnection
{
    private PDO $pdo;

    public function __construct()
    {
        $this->pdo = new PDO(
            'mysql:host=db-primary;dbname=app',
            'root',
            'password',
        );
        // All reads AND writes go through this single connection
    }

    public function query(string $sql): array
    {
        return $this->pdo->query($sql)->fetchAll();
    }
}

// GOOD: Separate read/write connections
final readonly class ReadWriteConnection
{
    public function __construct(
        private PDO $writeConnection,
        private PDO $readConnection,
    ) {}

    public static function fromConfig(DatabaseConfig $config): self
    {
        return new self(
            writeConnection: new PDO($config->writeDsn(), $config->user(), $config->password()),
            readConnection: new PDO($config->readDsn(), $config->user(), $config->password()),
        );
    }

    public function forWrite(): PDO
    {
        return $this->writeConnection;
    }

    public function forRead(): PDO
    {
        return $this->readConnection;
    }
}

2. Missing Read Replica Configuration

<?php

declare(strict_types=1);

// BAD: Only one database host configured
// .env:
// DATABASE_URL=mysql://root:pass@primary-db:3306/app
// No DB_READ_HOST, no replica DSN

// BAD: Doctrine with single connection
// doctrine.yaml:
// doctrine:
//     dbal:
//         url: '%env(DATABASE_URL)%'

// GOOD: Doctrine with read replica(s)
// doctrine.yaml:
// doctrine:
//     dbal:
//         default_connection: default
//         connections:
//             default:
//                 wrapper_class: Doctrine\DBAL\Connections\PrimaryReadReplicaConnection
//                 primary:
//                     url: '%env(DATABASE_PRIMARY_URL)%'
//                 replica:
//                     replica1:
//                         url: '%env(DATABASE_REPLICA1_URL)%'
//                     replica2:
//                         url: '%env(DATABASE_REPLICA2_URL)%'

// GOOD: Environment with read replica
// .env:
// DATABASE_PRIMARY_URL=mysql://root:pass@primary-db:3306/app
// DATABASE_REPLICA1_URL=mysql://root:pass@replica1-db:3306/app
// DATABASE_REPLICA2_URL=mysql://root:pass@replica2-db:3306/app

3. SELECT Queries Hitting Primary

<?php

declare(strict_types=1);

// BAD: Read queries go to primary database
final readonly class ProductRepository
{
    public function __construct(
        private EntityManagerInterface $em,
    ) {}

    public function findAll(): array
    {
        // Uses default (primary) connection for reads
        return $this->em->getRepository(Product::class)->findAll();
    }

    public function findById(ProductId $id): ?Product
    {
        // Every SELECT hits the primary -- wasting write capacity
        return $this->em->find(Product::class, $id->toString());
    }
}

// GOOD: Explicit read connection for queries
final readonly class ProductRepository
{
    public function __construct(
        private EntityManagerInterface $em,
    ) {}

    public function findAll(): array
    {
        $connection = $this->em->getConnection();
        if ($connection instanceof PrimaryReadReplicaConnection) {
            $connection->ensureConnectedToReplica();
        }

        return $this->em->getRepository(Product::class)->findAll();
    }
}

// GOOD: CQRS -- separate read model with read-only connection
final readonly class ProductReadRepository
{
    public function __construct(
        private Connection $readConnection, // Injected read-only connection
    ) {}

    public function findAll(): array
    {
        return $this->readConnection
            ->executeQuery('SELECT id, name, price FROM products WHERE active = 1')
            ->fetchAllAssociative();
    }
}

4. Missing Connection Pooling

<?php

declare(strict_types=1);

// BAD: New connection per request without pooling
final readonly class LegacyDatabase
{
    public function query(string $sql): array
    {
        // Each PHP-FPM request opens a new connection
        $pdo = new PDO('mysql:host=db;dbname=app', 'root', 'pass');
        $result = $pdo->query($sql)->fetchAll();
        // Connection closed at end of request
        // Under 1000 RPS = 1000 connections to DB
        return $result;
    }
}

// GOOD: Persistent connections + external pooler (PgBouncer/ProxySQL)
// Connection through PgBouncer/ProxySQL
final readonly class PooledDatabase
{
    public function __construct(
        private PDO $pdo, // Injected once, persistent
    ) {}

    // With PDO persistent connections
    public static function createPersistent(string $dsn, string $user, string $pass): self
    {
        return new self(new PDO($dsn, $user, $pass, [
            PDO::ATTR_PERSISTENT => true,
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        ]));
    }
}

// docker-compose.yml or infrastructure:
// pgbouncer:
//     image: edoburu/pgbouncer
//     environment:
//         DATABASE_URL: postgres://user:pass@postgres:5432/app
//         POOL_MODE: transaction
//         MAX_CLIENT_CONN: 1000
//         DEFAULT_POOL_SIZE: 20

5. Heavy Queries on Primary

<?php

declare(strict_types=1);

// BAD: Reporting/analytics queries on write database
final readonly class ReportService
{
    public function generateMonthlyReport(DateTimeImmutable $month): Report
    {
        // Heavy aggregation on primary -- blocks writes!
        $data = $this->em->createQuery(
            'SELECT SUM(o.total), COUNT(o) FROM Order o
             WHERE o.createdAt BETWEEN :start AND :end
             GROUP BY o.status'
        )
            ->setParameter('start', $month->modify('first day of this month'))
            ->setParameter('end', $month->modify('last day of this month'))
            ->getResult();

        return new Report($data);
    }
}

// GOOD: Heavy queries on dedicated read replica or analytics database
final readonly class ReportService
{
    public function __construct(
        private Connection $analyticsConnection, // Separate replica for analytics
    ) {}

    public function generateMonthlyReport(DateTimeImmutable $month): Report
    {
        $data = $this->analyticsConnection->executeQuery(
            'SELECT SUM(total) as revenue, COUNT(*) as order_count, status
             FROM orders
             WHERE created_at BETWEEN :start AND :end
             GROUP BY status',
            [
                'start' => $month->modify('first day of this month')->format('Y-m-d'),
                'end' => $month->modify('last day of this month')->format('Y-m-d 23:59:59'),
            ],
        )->fetchAllAssociative();

        return new Report($data);
    }
}

Grep Patterns

# Single database connection
Grep: "new PDO\(|DriverManager::getConnection" --glob "**/*.php"

# Read replica configuration
Grep: "DB_READ_HOST|DATABASE_REPLICA|replica|PrimaryReadReplicaConnection" --glob "**/*.php"
Grep: "DB_READ_HOST|DATABASE_REPLICA|replica" --glob "**/.env*"

# Connection pooling indicators
Grep: "ATTR_PERSISTENT|pgbouncer|ProxySQL|pool_size" --glob "**/*.php"
Grep: "pgbouncer|proxysql" --glob "**/docker-compose*.yml"

# Heavy queries (GROUP BY, SUM, COUNT, subqueries)
Grep: "GROUP BY|SUM\(|COUNT\(|AVG\(|HAVING" --glob "**/*.php"

# Report/analytics queries
Grep: "class.*Report|generateReport|analytics" --glob "**/*.php"

# Doctrine read/write split
Grep: "ensureConnectedToReplica|ensureConnectedToPrimary" --glob "**/*.php"

# All database connections in config
Grep: "DATABASE_URL|DB_HOST|DB_CONNECTION" --glob "**/.env*"

Severity Classification

Pattern	Severity
All queries on single primary connection	🔴 Critical
Reporting/analytics on primary database	🔴 Critical
No read replica configuration	🟠 Major
No connection pooling under high load	🟠 Major
SELECT queries not routed to replica	🟠 Major
New PDO connection per query	🟡 Minor
Missing persistent connections	🟡 Minor

Output Format

### Database Scaling Issue: [Brief Description]

**Severity:** 🔴/🟠/🟡
**Location:** `file.php:line`
**Type:** [Single Connection|No Replica|SELECT on Primary|No Pooling|Heavy Query]

**Issue:**
[Description of the database scaling problem]

**Impact:**
- Primary database overloaded with reads
- Write latency increases under read load
- Cannot scale reads independently

**Code:**
```php
// Non-scalable database access

Fix:

// With read/write split and pooling

## When This Is Acceptable

- **Low-traffic applications** -- Under 100 QPS, a single database connection is sufficient
- **Single-server deployment** -- When horizontal scaling is not a requirement
- **Development/staging** -- Non-production environments don't need read replicas
- **Write-heavy workloads** -- If 90%+ of operations are writes, read replicas add complexity without benefit

### False Positive Indicators
- Application is a CLI tool or batch processor (not serving HTTP traffic)
- Database is already behind a managed proxy (AWS RDS Proxy, Cloud SQL Proxy)
- Read/write split is configured at the ORM level but not visible in code
- PDO is created once in a service container and reused across requests