serviceParser: ~70% of SELECTs in one parse repeat an earlier query with the same values (845 of 1188 on the appwrite template)

[alex-frolov]

What happens

One Service::parse() of the stock templates/compose/appwrite.yaml (14 compose services) issues 1,750 queries, 1,188 of them SELECTs. 845 of those SELECTs repeat a query that already ran in the same parse, with the same SQL and the same bound values. The second parse (the one StartService does after Create) has fewer writes but the same 845 repeats: nothing is left to create, and the lookups still run.

Template	Queries per parse (1st / 2nd)	SELECTs	Repeated SELECTs
appwrite	1,750 / 1,281	1,188	845 (71%)
appflowy	442 / 341	282	169 (60%)
hoppscotch	150 / 116	90	39 (43%)

Where the repeats come from (appwrite, first parse):

Repeats	Call site	What it does
243	bootstrap/helpers/parsers.php:3179	environment_variables()->firstOrCreate(['key' => $varName, …]) for ${VAR:-default}
241	bootstrap/helpers/parsers.php:3114	firstOrCreate for each reference found in a value
177	bootstrap/helpers/parsers.php:3148	firstOrCreate for a plain ${VAR}
25	bootstrap/helpers/parsers.php:2758	firstOrCreate for generated SERVICE_* values
24	bootstrap/helpers/parsers.php:3130	updateOrCreate for SERVICE_* values

The cause is the same at every site. The environment variables belong to the Service (resourceable_type = App\Models\Service). But the loop runs once per compose service, and appwrite's services share most of their variables. So _APP_ENV is looked up 14 times, _APP_DB_HOST 12 times, and so on. One variable can also be looked up twice in the same iteration: once as a reference (:3114), then again as the primary variable (:3148/:3179).

A smaller instance of the same pattern: findServiceApplicationForEnvName() (bootstrap/helpers/domains.php:584) runs $resource->applications()->pluck('name') for every SERVICE_FQDN_*/SERVICE_URL_* variable it resolves. That is 8 identical queries per appwrite parse.

parse() runs on create, on every start/deploy and on every domain save (updateCompose). The measurement uses sqlite in memory, so it shows no network time. A real instance pays one round-trip to Postgres for every one of those queries.

Expected

Each environment variable row of the service is read at most once per parse. For example, load $resource->environment_variables()->get()->keyBy('key') once before the loop, then look keys up in that collection, and create only the missing ones. In findServiceApplicationForEnvName(), read the application names once per parse. The output of the parse stays the same. The golden test (ServiceParserReferenceTest) already pins it, so the change can be checked against that test.

How to reproduce

Drop this file into tests/Feature/ and run php artisan test tests/Feature/ServiceParseQueryCountReproTest.php. It uses only DB::listen:

<?php

use App\Models\Environment;
use App\Models\InstanceSettings;
use App\Models\Project;
use App\Models\Server;
use App\Models\Service;
use App\Models\StandaloneDocker;
use App\Models\Team;
use Illuminate\Foundation\Testing\RefreshDatabase;
use Illuminate\Support\Facades\Bus;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Queue;

uses(RefreshDatabase::class);

beforeEach(function () {
    Bus::fake();
    Queue::fake();
    InstanceSettings::forceCreate(['id' => 0]);
});

it('counts the queries of one parse', function (string $template) {
    $team = Team::factory()->create();
    $server = Server::factory()->create(['team_id' => $team->id]);
    $destination = StandaloneDocker::where('server_id', $server->id)->first()
        ?? StandaloneDocker::factory()->create(['server_id' => $server->id]);
    $project = Project::factory()->create(['team_id' => $team->id]);
    $environment = Environment::where('project_id', $project->id)->first()
        ?? Environment::factory()->create(['project_id' => $project->id]);
    $service = Service::factory()->create([
        'environment_id' => $environment->id,
        'server_id' => $server->id,
        'destination_id' => $destination->id,
        'destination_type' => $destination->getMorphClass(),
        'docker_compose_raw' => file_get_contents(base_path("templates/compose/{$template}.yaml")),
    ]);

    $queries = null;
    DB::listen(function ($query) use (&$queries) {
        if (! is_array($queries)) {
            return;
        }
        $site = '';
        foreach (debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS) as $frame) {
            $file = $frame['file'] ?? '';
            if ($file !== '' && ! str_contains($file, '/vendor/') && ! str_contains($file, '/tests/')) {
                $site = str_replace(base_path().'/', '', $file).':'.$frame['line'];
                break;
            }
        }
        $queries[] = ['sql' => $query->sql, 'bindings' => json_encode($query->bindings), 'site' => $site];
    });

    foreach (['first parse', 'second parse'] as $label) {
        $queries = [];
        $service->fresh()->parse();
        $recorded = $queries;
        $queries = null;

        $selects = array_values(array_filter($recorded, fn ($q) => stripos($q['sql'], 'select') === 0));
        $seen = [];
        $repeats = [];
        foreach ($selects as $q) {
            $key = $q['sql'].$q['bindings'];
            if (isset($seen[$key])) {
                $repeats[$q['site']] = ($repeats[$q['site']] ?? 0) + 1;
            }
            $seen[$key] = true;
        }
        arsort($repeats);

        fwrite(STDERR, sprintf("\n%s, %s: %d queries (%d SELECT), %d repeated\n",
            $template, $label, count($recorded), count($selects), array_sum($repeats)));
        foreach (array_slice($repeats, 0, 5, true) as $site => $count) {
            fwrite(STDERR, "  {$count}  {$site}\n");
        }
    }

    expect(true)->toBeTrue();
})->with(['appwrite', 'appflowy', 'hoppscotch']);

Output at 8ed31bb (the parser files are unchanged on main 0eeb630):

appwrite, first parse: 1750 queries (1188 SELECT), 845 repeated
  243  bootstrap/helpers/parsers.php:3179
  241  bootstrap/helpers/parsers.php:3114
  177  bootstrap/helpers/parsers.php:3148
  25  bootstrap/helpers/parsers.php:2758
  24  bootstrap/helpers/parsers.php:3130
appwrite, second parse: 1281 queries (1188 SELECT), 845 repeated

How this was found

Found with query-guard, a PHPUnit extension that traces the SQL of an ordinary test run. It groups queries by call site and shape, and flags a call site when the same query with the same bound values runs 5+ times inside one test. It was run over Coolify's own suite, unchanged. ServiceParserReferenceTest was the loudest test.

You do not need that tool to check any of this. The reproduction above uses only DB::listen, and every number quoted comes from it. DB::enableQueryLog() around $service->parse() in Tinker gives the same counts.