PostgreSQL Performance Tuning for Symfony Applications: The Configuration Changes That Actually Matter

Most Symfony applications run on a PostgreSQL instance whose configuration was never touched after installation. The defaults postgresql.conf ships with assume a machine with 128MB of RAM, because that used to be a safe assumption for a generic install. It is not a safe assumption for a dedicated database server running a production SaaS application, and the gap between the default settings and the actual hardware is where a lot of avoidable slowness comes from.

This is a practical postgresql performance tuning symfony walkthrough: the specific configuration changes, the reasoning behind each one, and the query pattern it helps. None of this requires touching application code. All of it requires understanding what the setting actually controls, because copying numbers from a blog post without understanding them is how you end up with a database that runs out of memory under load instead of one that runs faster.

PostgreSQL performance tuning for Symfony: memory settings first

Three settings control how PostgreSQL uses the RAM on its host, and they interact with each other, so changing one without the others rarely helps.

shared_buffers is PostgreSQL's own cache for table and index data. The default is 128MB regardless of how much RAM the server has. On a dedicated database server, set it to 25% of total RAM. A server with 16GB of RAM gets shared_buffers = 4GB. Going much higher than 25% tends to backfire, because PostgreSQL also relies on the operating system's page cache, and giving PostgreSQL too much of the RAM starves that second layer of caching.

effective_cache_size does not allocate any memory. It tells the query planner how much cache (PostgreSQL's shared buffers plus the OS page cache combined) it can assume is available when deciding whether an index scan will be cheap. Set this to 75% of total RAM. On the same 16GB server, that is effective_cache_size = 12GB. Getting this number too low is a common cause of a planner choosing a sequential scan over an index scan on a table that should clearly use the index. If you have ever looked at an EXPLAIN ANALYZE output and wondered why Postgres ignored a perfectly good index, this setting is usually the reason.

work_mem controls the memory available per sort or hash operation, and this is the one that needs the most care, because it is multiplied by concurrent activity rather than set once globally. Each query can use work_mem multiple times if it has several sort or hash steps, and each concurrent connection running such a query uses its own allocation. The formula that keeps you safe:

work_mem = (RAM * 0.25) / max_connections

On a 16GB server with max_connections = 100, that is roughly 40MB. Setting work_mem too high across the board is how a burst of concurrent reporting queries takes down a database server that had plenty of RAM on paper. If a specific query needs more (an export job doing a large sort, for example), set it for that session with SET work_mem = '256MB' rather than raising the global default.

Impact: correctly sized memory settings are usually the single biggest jump in query latency you will see from configuration alone, often cutting the time for read-heavy dashboard and listing endpoints by half or more, because the data those queries need is actually staying in memory between requests instead of getting evicted and re-read from disk.

Write-ahead log settings: durability against performance

The write-ahead log (WAL) is how PostgreSQL guarantees that committed data survives a crash, and it is also where a lot of write latency comes from if it is not configured for the actual durability requirements of the workload.

synchronous_commit is the setting to look at first. The default, on, means PostgreSQL waits for the WAL record to be flushed to disk before returning a successful commit to the application. That is the safest setting, and it should stay on for anything involving financial transactions, billing records, or data you cannot afford to lose even in a rare crash scenario.

For workloads where losing the last few hundred milliseconds of writes in a crash is acceptable (activity logs, analytics events, non-critical background job records), synchronous_commit = off removes that wait and can meaningfully reduce write latency under load. This is a per-transaction setting in PostgreSQL, so you do not have to choose one policy for the whole database:

BEGIN;
SET LOCAL synchronous_commit = OFF;
INSERT INTO activity_log (...) VALUES (...);
COMMIT;

Keep the global default at on and opt specific transactions out, rather than the reverse.

wal_buffers should be set explicitly rather than left on -1 (autotune), which under-sizes it on larger servers. A value of 16MB covers most workloads and avoids the WAL becoming a bottleneck during write-heavy bursts.

Impact: this affects write latency specifically, not read performance. If your slow endpoints are the ones doing inserts and updates rather than the ones doing reads, this is the setting group to check first.

Autovacuum for write-heavy multi-tenant SaaS

Autovacuum is the process that reclaims space from updated and deleted rows and keeps table statistics current for the query planner. The default configuration assumes a relatively low write rate, and a multi-tenant SaaS application with frequent updates to the same rows (subscription status, usage counters, session state) will outpace it.

The default trigger is autovacuum_vacuum_scale_factor = 0.2, meaning a table is vacuumed once 20% of its rows have changed. On a 10-million-row table, that is 2 million changed rows before vacuum runs, by which point query performance has usually already degraded and the vacuum itself takes longer because there is more work to do.

For high-write tables specifically, override this at the table level instead of changing the global default, which would trigger unnecessary vacuum work on tables that barely change:

ALTER TABLE usage_counters SET (autovacuum_vacuum_scale_factor = 0.02);
ALTER TABLE subscription_status SET (autovacuum_vacuum_scale_factor = 0.02);

This drops the trigger threshold to 2% of rows changed, so vacuum runs more often on the tables that need it, while doing smaller amounts of work each time.

Also worth checking: autovacuum_max_workers (default 3) can be a bottleneck if you have more than three tables that all need frequent vacuuming at the same time, since they will queue behind each other. Raising it to 5 or 6 on a server with enough CPU headroom lets vacuum keep pace across more tables simultaneously.

Impact: under-vacuumed tables show up as gradually slowing queries on tables that used to be fast, plus table bloat that inflates storage and backup size over time. Table-level autovacuum tuning targets exactly the tables causing the problem without adding vacuum overhead everywhere else.

Connection configuration and PgBouncer for Symfony

max_connections in postgresql.conf sets a hard ceiling, and the number is easy to get wrong in both directions. Each connection reserves memory whether or not it is actively doing anything (roughly related to your work_mem setting, as shown above), so setting this too high wastes RAM that could otherwise be used for caching. Setting it too low means Symfony workers start getting connection errors under load.

A reasonable starting point is max_connections = 100 to 200 for a mid-size application, calculated from your actual concurrency: PHP-FPM or worker pool size, times the number of app servers, plus background workers and any admin or reporting connections, with some headroom.

Symfony's default Doctrine connection behavior opens a new database connection per request and closes it at the end. Under real traffic, this creates far more connection churn than PostgreSQL handles efficiently, and it is the reason most production Symfony deployments put PgBouncer in front of PostgreSQL rather than connecting directly.

PgBouncer pool sizing for this pattern:

[databases]
app_db = host=127.0.0.1 port=5432 dbname=app_db

[pgbouncer]
pool_mode = transaction
default_pool_size = 25
max_client_conn = 500

pool_mode = transaction is the setting that matters most for Symfony: it returns the underlying PostgreSQL connection to the pool as soon as a transaction commits, rather than holding it for the life of the client connection. This lets 500 concurrent PHP-FPM workers share a much smaller pool of actual PostgreSQL connections (default_pool_size = 25 here), keeping PostgreSQL's own max_connections low while still serving high request concurrency at the application layer.

One caveat with transaction pooling: session-level features like prepared statements and advisory locks that span multiple transactions do not work reliably across connections. Doctrine's default behavior is compatible with transaction pooling, but if you use raw prepared statements or session-scoped features elsewhere in the codebase, check them against this pooling mode before switching.

Impact: this is the change that prevents "too many connections" errors during traffic spikes and lets a single PostgreSQL server support far more concurrent Symfony workers than direct connections would allow.

Index strategy for common Symfony query patterns

Configuration changes help every query proportionally, but the biggest single-query wins usually come from indexes matched to how Doctrine actually generates queries.

Composite indexes for multi-column WHERE clauses matter because a single-column index does not help a query filtering on two or three columns together. If a query filters on tenant_id and status, a composite index on (tenant_id, status) serves that filter directly, while separate indexes on each column force PostgreSQL to either pick one and filter the rest in memory or do a slower bitmap merge:

CREATE INDEX idx_orders_tenant_status ON orders (tenant_id, status);

Column order matters. Put the column used in equality filters first, and the column used in range filters or sorting second.

Partial indexes for soft-deleted rows help in codebases using Doctrine's soft-delete pattern (a deleted_at column checked on every query). A regular index on a frequently queried column still includes the soft-deleted rows, which bloats the index with rows that will never actually be returned. A partial index excludes them:

CREATE INDEX idx_orders_active_tenant ON orders (tenant_id) WHERE deleted_at IS NULL;

This keeps the index smaller and faster to scan, and it matches the WHERE deleted_at IS NULL clause that Doctrine's soft-delete filter adds to nearly every query automatically.

Covering indexes address a specific version of the N+1 problem: a query that needs a few extra columns beyond what it filters on, forcing PostgreSQL to look up the full row in the table even though an index already matched the filter. Adding the needed columns with INCLUDE lets PostgreSQL answer the query from the index alone:

CREATE INDEX idx_orders_tenant_lookup ON orders (tenant_id, status) INCLUDE (total_amount, created_at);

This does not replace fixing genuine N+1 query patterns in Doctrine (eager loading with JOIN or fetch: EAGER where appropriate is still the first fix). What it does is reduce the cost of the individual lookups that remain, which matters once the query count itself is already under control.

Impact: index changes are the most workload-specific tuning in this list. Run EXPLAIN ANALYZE on your slowest actual queries before and after adding an index. A composite or covering index can turn a sequential scan over a multi-million-row table into an index-only scan that returns in milliseconds, but only when it matches the queries your application actually runs.

Putting it together

None of these changes are dramatic on their own, but they compound. Memory settings determine how much of your working data stays cached. WAL settings determine your write latency floor. Autovacuum determines whether tables stay fast or slowly degrade. Connection pooling determines how much concurrency your server can absorb without falling over. Indexes determine whether the query planner has a fast path available at all.

Test each change under a realistic load rather than assuming the numbers above are correct for your exact hardware and traffic pattern. pgbench or a replay of production query logs against a staging copy of the database will show you the actual before-and-after difference, which is worth having before you change production settings.

If this reads like more database work than your team has time for on top of shipping product, that is a common position to be in, and it is exactly the kind of performance work we take on for clients through code quality consulting, including production PostgreSQL and Symfony configuration reviews. For custom Symfony application builds where this tuning is designed in from the start, see custom software development.

Questions about a specific slow query or a configuration decision for your own setup are welcome at hello@wolf-tech.io, or take a look at what else we cover at wolf-tech.io.