PostgreSQL-Specific DQL in Symfony: ILIKE, Arrays, and Full-Text Search Without Leaving Doctrine

Good evening. Your DQL is leaving performance on the table.

Doctrine's DQL is a remarkably capable abstraction. It maps object queries to SQL, handles joins across entity relationships, hydrates result sets into proper objects. For standard CRUD against a relational schema, it does its job with quiet competence. I have no quarrel with DQL. In its domain, it serves well.

PostgreSQL, however, is not a standard relational database. It offers ILIKE for case-insensitive matching. Native array types with containment operators. Full-text search with tsvector and tsquery. JSONB path queries. Range types with overlap detection. These are not obscure extensions gathering dust in the documentation appendix — they are the features that make teams choose PostgreSQL over MySQL in the first place.

DQL supports none of them.

The result is predictable, and I have seen it in enough Symfony codebases that the pattern has become familiar to me in the way a certain creak in the floorboards becomes familiar. Developers reach for workarounds. LOWER(column) LIKE instead of ILIKE. Serialized strings instead of native arrays. Separate Elasticsearch clusters instead of built-in full-text search. Each workaround carries a performance cost, an operational cost, or both. Some carry all three, if you count the cognitive burden on the team maintaining them.

This guide covers three things: how to register PostgreSQL-specific DQL functions in Symfony using the postgresql-for-doctrine package, what performance difference the native operators actually make (with EXPLAIN ANALYZE output, because claims without evidence are merely opinions), and where DQL reaches its limits and DBAL becomes the correct tool. I shall also address the migration path — how to move an existing codebase from workarounds to native operators without disrupting production.

The LOWER()+LIKE problem: why your case-insensitive search is slow

This is the single most common PostgreSQL performance mistake in Symfony applications. I do not say that lightly. Across every Doctrine codebase I have examined, this pattern appears more reliably than any other anti-pattern. The reason is simple: DQL does not have ILIKE, so developers reach for the next available tool.

<?php
// The workaround everyone uses: LOWER() + LIKE
$qb = $em->createQueryBuilder();
$qb->select('u')
   ->from(User::class, 'u')
   ->where('LOWER(u.email) LIKE :search')
   ->setParameter('search', '%' . strtolower($term) . '%');

// Doctrine generates:
// SELECT u0_.id, u0_.email, u0_.name
// FROM users u0_
// WHERE LOWER(u0_.email) LIKE '%gibson%'

It works. It returns correct results. It is also a sequential scan on every execution.

The issue is structural, not incidental. When you wrap a column in LOWER(), PostgreSQL cannot use a standard B-tree index on that column. The index stores raw values; the query asks for transformed values. The planner sees a function call and falls back to scanning every row. This is not a deficiency in the planner. It is a mathematical certainty — the planner cannot guarantee that the function's output matches the index's sort order without evaluating it on every row.

-- EXPLAIN ANALYZE on LOWER(email) LIKE '%gibson%'
-- Table: users, 500,000 rows, B-tree index on email

Seq Scan on users  (cost=0.00..12841.00 rows=2500 width=64)
  Filter: (lower((email)::text) ~~ '%gibson%'::text)
  Rows Removed by Filter: 497500
  Planning Time: 0.11 ms
  Execution Time: 89.4 ms

-- The B-tree index on email is useless here.
-- LOWER() wraps the column, so PostgreSQL cannot use the index.
-- Every row in the table is read and evaluated.

89 milliseconds to search 500,000 rows. For a search box. On every keystroke, if your frontend is debouncing to the API. And this is with the data cached in shared_buffers. A cold cache adds disk I/O on top, and the numbers become rather less polite.

It compounds with multiple columns

Most search features do not search a single column. They search name, email, bio, company — whatever fields the user might reasonably type into. The LOWER()+LIKE pattern scales linearly in the worst possible direction:

<?php
// It gets worse with multiple columns.
// "Search across name, email, and bio" — a common requirement.
$qb = $em->createQueryBuilder();
$qb->select('u')
   ->from(User::class, 'u')
   ->where('LOWER(u.email) LIKE :search OR LOWER(u.name) LIKE :search OR LOWER(u.bio) LIKE :search')
   ->setParameter('search', '%' . strtolower($term) . '%');

// Doctrine generates:
// SELECT u0_.id, u0_.email, u0_.name, u0_.bio
// FROM users u0_
// WHERE LOWER(u0_.email) LIKE '%gibson%'
//    OR LOWER(u0_.name) LIKE '%gibson%'
//    OR LOWER(u0_.bio) LIKE '%gibson%'
//
// Three LOWER() calls per row. Three columns the planner
// cannot use indexes for. The sequential scan reads every row
// and evaluates all three predicates on each one.

-- EXPLAIN ANALYZE on multi-column LOWER()+LIKE
-- Table: users, 500,000 rows

Seq Scan on users  (cost=0.00..18953.00 rows=7450 width=128)
  Filter: ((lower((email)::text) ~~ '%gibson%'::text)
        OR (lower((name)::text) ~~ '%gibson%'::text)
        OR (lower((bio)::text) ~~ '%gibson%'::text))
  Rows Removed by Filter: 492550
  Planning Time: 0.15 ms
  Execution Time: 147.3 ms

-- 147ms. For a search box. On a modest table.
-- And this is with the data entirely in shared_buffers.
-- Cold cache adds disk I/O on top.

147 milliseconds. Three LOWER() calls evaluated on every row, none of which can use an index. The planner has no choice but to read the entire table and evaluate all three predicates sequentially. If you are wondering why your search endpoint is the slowest route in your application, this is likely the reason.

The ILIKE comparison

Now compare the same query intent expressed as ILIKE with a trigram index:

-- EXPLAIN ANALYZE on email ILIKE '%gibson%'
-- Table: users, 500,000 rows, GIN trigram index on email

Bitmap Heap Scan on users  (cost=28.77..1284.50 rows=2500 width=64)
  Recheck Cond: ((email)::text ~~* '%gibson%'::text)
  Heap Blocks: exact=1847
  ->  Bitmap Index Scan on idx_users_email_trgm
        (cost=0.00..28.14 rows=2500 width=0)
        Index Cond: ((email)::text ~~* '%gibson%'::text)
Planning Time: 0.18 ms
Execution Time: 4.7 ms

-- 19x faster. Same query intent. Different operator.

4.7 milliseconds. A 19x improvement. Same table, same data, same search intent. The difference is the operator and the index. ILIKE is not magic — without an appropriate index, it performs a sequential scan too. The improvement comes from the combination: an operator that PostgreSQL's planner recognizes natively, paired with an index type (GIN trigram) designed specifically for pattern matching.

The full benchmark

I ran each approach against a users table with 500,000 rows, searching for a substring pattern. Five approaches, measured with EXPLAIN (ANALYZE, BUFFERS), best of five runs after cache warmup.

Three things stand out. First, without an index, LOWER()+LIKE and ILIKE perform almost identically — the function overhead is negligible compared to the sequential scan. The scan is the expensive part, not the function call. Second, the GIN trigram index is the clear winner for substring patterns, and it is not close. Third, the GiST trigram index is slightly slower for exact matching but supports nearest-neighbor searches (<-> operator), which matters for fuzzy matching and autocomplete — a distinction I shall return to shortly.

The expression index on LOWER(email) helps with prefix patterns (LIKE 'gibson%') but cannot accelerate substring patterns (LIKE '%gibson%'). For substring search — which is what most search boxes actually need — trigram indexes are the only option. A B-tree, no matter how cleverly applied, cannot index arbitrary substrings.

A note on locale: the hidden LOWER() inconsistency

There is a subtlety to the LOWER()+LIKE pattern that I would be remiss not to mention, because it affects correctness — not just performance.

-- The collation trap: LOWER() is locale-dependent.
-- Different results on different servers.

-- Server A (en_US.UTF-8):
SELECT LOWER('STRASSE');
-- Result: 'strasse'

-- Server B (de_DE.UTF-8):
SELECT LOWER('STRASSE');
-- Result: 'strasse'
-- (Same here, but for Turkish dotless-i, it differs.)

-- Server C (tr_TR.UTF-8):
SELECT LOWER('INFO');
-- Result: 'ınfo'  (dotless i, not 'info')

-- ILIKE uses PostgreSQL's internal case-folding,
-- which is consistent regardless of server locale.
-- LOWER() uses the OS locale. Same query, different
-- results depending on which server runs it.

LOWER() delegates to the operating system's locale settings for case folding. Different servers with different locale configurations can produce different results for the same input. This is most visible with Turkish locale rules, where LOWER('I') produces a dotless ı instead of i — but it affects other languages as well.

ILIKE, by contrast, uses PostgreSQL's internal case-folding logic, which is deterministic regardless of the server's OS locale. Same operator, same behavior, every server, every time. It is a small thing, but small inconsistencies in data matching have a way of producing support tickets that are extraordinarily tedious to debug. I find it best to avoid creating them in the first place.

Setting up postgresql-for-doctrine

The postgresql-for-doctrine package by Martin Georgiev registers PostgreSQL-specific types and DQL functions. It has been actively maintained since 2017, supports Doctrine ORM 2.x and 3.x, and covers ILIKE, array operations, JSONB operators, and full-text search functions. The maintenance record matters — I would not recommend a package for production use without it.

# Install the postgresql-for-doctrine package
composer require martin-georgiev/postgresql-for-doctrine

# config/packages/doctrine.yaml
doctrine:
    dbal:
        types:
            jsonb: MartinGeorgiev\Doctrine\DBAL\Types\Jsonb
            text_array: MartinGeorgiev\Doctrine\DBAL\Types\TextArray
            integer_array: MartinGeorgiev\Doctrine\DBAL\Types\IntegerArray
        mapping_types:
            jsonb: jsonb
            _text: text_array
            _int4: integer_array
    orm:
        dql:
            string_functions:
                ILIKE: MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\Ilike
                ALL_OF: MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\AllOf
                ANY_OF: MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\AnyOf
                ARRAY_APPEND: MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ArrayAppend
                ARRAY_LENGTH: MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ArrayLength
                TO_TSVECTOR: MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ToTsvector
                TO_TSQUERY: MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ToTsquery
                TSMATCH: MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\Tsmatch

Once registered, these functions are available in any DQL query or QueryBuilder call. No custom AST walkers, no string SQL injection, no leaving the Doctrine ecosystem. Your existing QueryBuilder patterns continue to work — you are adding vocabulary, not changing grammar.

Register only what you use

<?php
// If using Symfony Flex, the package auto-discovers.
// If not, or if you need to register functions selectively:

// config/packages/doctrine.yaml — minimal registration
doctrine:
    orm:
        dql:
            string_functions:
                ILIKE: MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\Ilike

// You do not need to register all 40+ functions.
// Register only what you use. Each registration adds
// a class to the DQL parser's function map — no runtime
// cost for unused functions, but a cleaner configuration
// makes intent obvious to the next developer who reads it.

The package registers approximately 40 functions. I am covering the four categories that have the most significant performance impact: ILIKE, arrays, full-text search, and the boundary where DQL stops and DBAL starts. You are welcome to register all 40 — the unused ones carry no runtime cost — but a focused configuration communicates intent more clearly to the next developer who inherits the codebase. And one does try to leave things better than one found them.

ILIKE in DQL: the correct way to do case-insensitive search

With the package registered, case-insensitive search becomes a one-line change:

<?php
// With postgresql-for-doctrine installed:
$qb = $em->createQueryBuilder();
$qb->select('u')
   ->from(User::class, 'u')
   ->where('ILIKE(u.email, :search) = true')
   ->setParameter('search', '%gibson%');

// Doctrine generates:
// SELECT u0_.id, u0_.email, u0_.name
// FROM users u0_
// WHERE u0_.email ILIKE '%gibson%'
//
// No LOWER(). No strtolower(). No function wrapping.
// PostgreSQL can use the trigram index directly.

The generated SQL uses PostgreSQL's native ILIKE operator directly. No LOWER() wrapper. No strtolower() in PHP. The column reference is bare, which means PostgreSQL can use an index on it.

The multi-column case improves proportionally:

<?php
// Multi-column search with ILIKE — each column indexed independently
$qb = $em->createQueryBuilder();
$qb->select('u')
   ->from(User::class, 'u')
   ->where('ILIKE(u.email, :search) = true')
   ->orWhere('ILIKE(u.name, :search) = true')
   ->orWhere('ILIKE(u.bio, :search) = true')
   ->setParameter('search', '%gibson%');

// Doctrine generates:
// SELECT u0_.id, u0_.email, u0_.name, u0_.bio
// FROM users u0_
// WHERE u0_.email ILIKE '%gibson%'
//    OR u0_.name ILIKE '%gibson%'
//    OR u0_.bio ILIKE '%gibson%'
//
// With GIN trigram indexes on each column, PostgreSQL
// uses BitmapOr to combine three fast index scans.
// 12ms vs 147ms for the LOWER()+LIKE equivalent.

With GIN trigram indexes on each column, PostgreSQL uses a BitmapOr to combine three fast index scans into a single result set. 12 milliseconds instead of 147. The planner does the work that the application developer used to do manually — and does it considerably better.

To get the full performance benefit, you need a trigram index:

-- Enable the pg_trgm extension (required for trigram indexes)
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- GIN trigram index for ILIKE and LIKE pattern matching
CREATE INDEX idx_users_email_trgm ON users USING gin (email gin_trgm_ops);

-- This index supports:
--   email ILIKE '%gibson%'     (case-insensitive substring)
--   email LIKE '%gibson%'      (case-sensitive substring)
--   email ~ 'gib.*son'         (regex)
--
-- Without pg_trgm, PostgreSQL cannot index substring patterns.
-- LIKE 'gibson%' (prefix only) can use a standard B-tree.
-- LIKE '%gibson%' (substring) needs a trigram index.

The pg_trgm extension ships with PostgreSQL — it is not a third-party add-on. It splits strings into three-character sequences (trigrams) and indexes them. This allows PostgreSQL to find substring matches without scanning every row.

A single GIN trigram index supports ILIKE, LIKE, and regex patterns on the same column. If your application has a search feature — and it does — this is the index it needs.

The minimum pattern length constraint

Trigram indexes have a property that occasionally catches developers off guard, and I should rather mention it now than leave you to discover it in production:

-- Trigram indexes have a minimum pattern length.
-- Trigrams are 3-character sequences:
SELECT show_trgm('gibson');
--  {"  g"," gi","bso","gib","ibs","on ","son"}

-- A search term shorter than 3 characters produces
-- no useful trigrams. The planner falls back to a
-- sequential scan even with a GIN trigram index.

SELECT * FROM users WHERE email ILIKE '%ab%';
-- Seq Scan. 'ab' is only 2 characters.

SELECT * FROM users WHERE email ILIKE '%abc%';
-- Bitmap Index Scan. 3 characters, trigrams available.

-- This is not a bug. It is a mathematical constraint.
-- If your UI allows 1-2 character searches, consider
-- requiring a minimum of 3 characters, or using a
-- prefix B-tree index for short patterns.

A trigram is, by definition, a sequence of three characters. A search term of one or two characters cannot produce useful trigrams, so the planner correctly ignores the index and falls back to a sequential scan. This is not a bug — it is a mathematical constraint of the data structure. If your search UI allows users to type a single character and fire a query, either enforce a minimum of three characters on the frontend or accept that very short patterns will scan the table. For most applications, requiring three characters is the correct answer.

Beyond ILIKE: similarity search and fuzzy matching

ILIKE handles the case where the user types a correct substring and you need to find it regardless of case. But users misspell things. They type "gibsen" when they mean "gibson." They type "postgre" when they mean "postgres." A search feature that only matches exact substrings is correct but unkind.

The pg_trgm extension — the same one powering your ILIKE indexes — also provides similarity functions:

<?php
// Fuzzy matching with pg_trgm similarity — typo tolerance
// This goes beyond exact ILIKE and handles misspellings.

// In DBAL (no DQL equivalent for similarity functions):
$conn = $em->getConnection();

$sql = "SELECT id, name, similarity(name, :term) AS sim
        FROM users
        WHERE name % :term
        ORDER BY sim DESC
        LIMIT 10";

$results = $conn->executeQuery($sql, ['term' => 'gibsen']);

// The % operator returns true when similarity exceeds
// the pg_trgm.similarity_threshold (default 0.3).
// 'gibsen' matches 'gibson' with ~0.5 similarity.
//
// GiST trigram indexes support the % operator and
// the <-> distance operator for ORDER BY similarity.
// GIN trigram indexes do not support distance ordering.

The similarity() function computes a value between 0 and 1 based on the proportion of shared trigrams between two strings. The % operator returns true when that similarity exceeds a configurable threshold. This gives your search feature typo tolerance at the database level — no external fuzzy matching library, no application-side Levenshtein distance computation, no Elasticsearch.

However, this is where the choice between GIN and GiST trigram indexes becomes important:

-- GIN trigram: faster lookups, no distance ordering
CREATE INDEX idx_users_name_trgm_gin ON users USING gin (name gin_trgm_ops);
-- Supports: ILIKE, LIKE, ~, ~*
-- Does NOT support: % (similarity), <-> (distance)

-- GiST trigram: slightly slower lookups, supports fuzzy ordering
CREATE INDEX idx_users_name_trgm_gist ON users USING gist (name gist_trgm_ops);
-- Supports: ILIKE, LIKE, ~, ~*, % (similarity), <-> (distance)

-- If you need autocomplete with typo tolerance:
SELECT name FROM users
WHERE name % 'gibsen'
ORDER BY name <-> 'gibsen'
LIMIT 10;
-- This requires GiST, not GIN.

-- If you only need exact substring matching (ILIKE):
-- GIN is faster. Use GIN.

If you need only ILIKE — exact substring matching, case-insensitive — GIN is faster and should be your default. If you also need fuzzy matching with % or distance ordering with <-> for autocomplete, you need GiST. Some applications maintain both: GIN on the columns used in search filters, GiST on the column used in the autocomplete dropdown. The indexes serve different query patterns and do not conflict.

I should note that similarity search via pg_trgm does not currently have a DQL function in postgresql-for-doctrine. The similarity() function and % operator require DBAL or NativeQuery. This is one of those boundaries worth knowing about before you design the feature, not after.

Native array queries: containment, overlap, and length

PostgreSQL's array types are one of its most underused features in the Symfony ecosystem. Developers serialize arrays to JSON strings, or — more commonly — normalize them into join tables with a many-to-many relationship, because DQL does not support array operators. Both approaches work. Neither is optimal for the query patterns that arrays naturally serve.

<?php
// Entity definition
#[ORM\Column(type: 'text_array')]
private array $tags = [];

// Query: find products tagged with BOTH 'organic' AND 'local'
$qb = $em->createQueryBuilder();
$qb->select('p')
   ->from(Product::class, 'p')
   ->where('ALL_OF(p.tags, :required) = true')
   ->setParameter('required', '{organic,local}');

// Generates:
// SELECT p0_.id, p0_.name, p0_.tags
// FROM products p0_
// WHERE p0_.tags @> '{organic,local}'
//
// With a GIN index on the tags column, this is a fast
// inverted index lookup — not a sequential scan.

The ALL_OF function maps to the @> (contains) operator. ANY_OF maps to && (overlap). The distinction is AND vs OR semantics:

<?php
// Query: find products tagged with ANY of these tags (OR semantics)
$qb = $em->createQueryBuilder();
$qb->select('p')
   ->from(Product::class, 'p')
   ->where('ANY_OF(p.tags, :desired) = true')
   ->setParameter('desired', '{organic,local,seasonal}');

// Generates:
// SELECT p0_.id, p0_.name, p0_.tags
// FROM products p0_
// WHERE p0_.tags && '{organic,local,seasonal}'
//
// The && operator checks for overlap — any element in common.
// Same GIN index, same fast lookup, different semantics.

Both are indexable with GIN:

-- Create a GIN index for array containment queries
CREATE INDEX idx_products_tags ON products USING gin (tags);

-- Powers @> (contains), <@ (contained by), && (overlap)
-- These are the operators that ALL_OF and ANY_OF map to.

EXPLAIN ANALYZE
SELECT * FROM products WHERE tags @> '{organic,local}';

Bitmap Heap Scan on products  (cost=16.08..420.50 rows=100 width=96)
  Recheck Cond: (tags @> '{organic,local}'::text[])
  Heap Blocks: exact=95
  ->  Bitmap Index Scan on idx_products_tags
        (cost=0.00..16.05 rows=100 width=0)
        Index Cond: (tags @> '{organic,local}'::text[])
Planning Time: 0.14 ms
Execution Time: 0.9 ms

0.9 milliseconds for a containment query across 100,000 products. The GIN index stores each distinct array element as an index entry, so the @> lookup intersects the posting lists for 'organic' and 'local' directly. No table scan, no row evaluation, no GROUP BY.

Arrays vs join tables: the honest comparison

The traditional approach in Symfony is a many-to-many relationship with a join table. It works. It is well-understood. And it is significantly slower for containment queries:

-- The join table approach: what most Symfony apps do instead
-- (three tables, two joins, more I/O)

SELECT p.id, p.name
FROM products p
JOIN product_tags pt ON pt.product_id = p.id
JOIN tags t ON t.id = pt.tag_id
WHERE t.name IN ('organic', 'local')
GROUP BY p.id, p.name
HAVING COUNT(DISTINCT t.name) = 2;

-- Execution time: 23.4 ms (with indexes on join columns)

-- The native array approach:
SELECT id, name FROM products
WHERE tags @> '{organic,local}';

-- Execution time: 0.9 ms (with GIN index on tags)

-- 26x faster. No joins. No GROUP BY. No HAVING.
-- One column. One operator. One index scan.

26x faster. But I should be forthright about the trade-offs, because a waiter who recommends native arrays without mentioning the costs would be doing you a disservice.

Native arrays are excellent when: you query by containment or overlap, the array elements are simple values (strings, integers), the array size is bounded and modest (tags, roles, categories — not unbounded lists), and you do not need to join on the array elements to fetch related data.

Native arrays are the wrong choice when: you need rich metadata on the association (a join table with additional columns), the "tags" are entities in their own right with properties you query independently, you need referential integrity constraints (arrays cannot have foreign keys), or the array size is unbounded and can grow to thousands of elements (GIN index maintenance becomes expensive).

For a tags column with 5-20 elements per row, queried by containment? Native arrays. For a full-featured tagging system with tag hierarchies, tag metadata, and per-user tag counts? A join table. The data model should match the query pattern, not the other way around.

Native arrays also support operations that are awkward or impossible with serialized strings: ARRAY_LENGTH for filtering by tag count, ARRAY_APPEND for atomic additions without read-modify-write cycles, and unnest() for expanding arrays into rows for aggregation. The postgresql-for-doctrine package exposes most of these as DQL functions. For a deeper look at array and JSONB indexing strategies, see the JSONB guide.

Full-text search without Elasticsearch

This is the feature that saves entire infrastructure components. I do not say that for dramatic effect — I say it because I have watched teams deploy, configure, monitor, and troubleshoot Elasticsearch clusters for search requirements that PostgreSQL handles natively. PostgreSQL's full-text search is not a toy. It supports language-aware stemming, ranking, phrase matching, prefix queries, and weighted search across multiple columns.

<?php
// Full-text search in DQL with postgresql-for-doctrine
$qb = $em->createQueryBuilder();
$qb->select('a')
   ->from(Article::class, 'a')
   ->where('TSMATCH(TO_TSVECTOR(a.body), TO_TSQUERY(:query)) = true')
   ->setParameter('query', 'postgresql & performance');

// Generates:
// SELECT a0_.id, a0_.title, a0_.body
// FROM articles a0_
// WHERE to_tsvector(a0_.body) @@ to_tsquery('postgresql & performance')

TO_TSVECTOR converts text to a searchable vector. TO_TSQUERY parses the search query. TSMATCH maps to the @@ match operator. All three are DQL functions registered by the package.

The indexing strategy matters considerably:

-- GIN index for full-text search
CREATE INDEX idx_articles_body_fts ON articles
  USING gin (to_tsvector('english', body));

-- For multi-column search, use a generated column or expression index:
ALTER TABLE articles ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(body, '')), 'B')
  ) STORED;

CREATE INDEX idx_articles_search ON articles USING gin (search_vector);

-- This gives title matches higher ranking than body matches.

The generated column approach is the most efficient for production use. The tsvector is computed once on write and stored, rather than recomputed on every query. The GIN index on the stored vector makes @@ queries fast. The setweight() calls allow you to rank title matches above body matches in results.

Without the index, to_tsvector() @@ to_tsquery() triggers a sequential scan that recomputes the vector for every row. On a 100,000-row articles table, that is the difference between 2ms and 400ms. A 200x difference. The index is not optional.

Ranked results with websearch_to_tsquery

The DQL functions cover basic matching, but ranking requires dropping to NativeQuery. This is a reasonable boundary — ranking is inherently a presentation concern, and the SQL for it benefits from being explicit:

<?php
// Full-text search WITH ranking — requires NativeQuery
// DQL cannot express ts_rank() or ts_rank_cd()
use Doctrine\ORM\Query\ResultSetMappingBuilder;

$rsm = new ResultSetMappingBuilder($em);
$rsm->addRootEntityFromClassMetadata(Article::class, 'a');
$rsm->addScalarResult('rank', 'rank');

$sql = "SELECT a.*,
               ts_rank(a.search_vector,
                       websearch_to_tsquery('english', :query)) AS rank
        FROM articles a
        WHERE a.search_vector @@ websearch_to_tsquery('english', :query)
        ORDER BY rank DESC
        LIMIT :limit";

$query = $em->createNativeQuery($sql, $rsm);
$query->setParameter('query', 'postgres performance tuning');
$query->setParameter('limit', 20);

// websearch_to_tsquery() accepts natural search syntax:
//   'postgres performance' -> 'postgres' & 'performance'
//   'postgres -mysql'      -> 'postgres' & !'mysql'
//   '"exact phrase"'       -> 'exact' <-> 'phrase'
// More forgiving than to_tsquery() for user-facing search.

Note the use of websearch_to_tsquery() instead of to_tsquery(). The former accepts natural search syntax — quotes for phrases, minus for exclusion — while the latter requires the & and | operators. For user-facing search, websearch_to_tsquery() is considerably more forgiving. A user typing "postgres performance" -mysql gets the results they expect without your application parsing their input.

The honest counterpoint: where Elasticsearch earns its keep

-- PostgreSQL full-text search: what it handles well
-- (and where Elasticsearch earns its keep)

-- PostgreSQL strengths:
--   Language-aware stemming (31 built-in configurations)
--   Weighted multi-column search with ranking
--   Phrase matching and prefix queries
--   Highlight/snippet generation via ts_headline()
--   Transactional consistency — search results match writes
--   Zero additional infrastructure

-- Where Elasticsearch is the better tool:
--   Distributed search across sharded datasets (TB+)
--   Complex aggregations (faceted search, histograms)
--   Fuzzy matching with edit-distance tuning
--   Multilingual analysis with custom tokenizers
--   Near-real-time across replicated clusters

-- For a Symfony app with <10M searchable rows and
-- standard search requirements: PostgreSQL is sufficient.
-- For an e-commerce catalog with faceted navigation
-- across 50M products: Elasticsearch earns its place.

I should be forthcoming about the boundaries, because pretending they do not exist would be a disservice to you and an embarrassment to me. PostgreSQL's full-text search is excellent for applications with moderate search requirements — most web applications, in my experience. It is transactionally consistent, requires no additional infrastructure, and performs well up to several million rows.

But if your search requirements include complex aggregations (faceted navigation with counts per category, price range histograms), fuzzy matching with tunable edit distances, or distributed search across datasets measured in terabytes — Elasticsearch is the better tool. The question is not "which is superior" but "which matches your actual requirements." For a Symfony application with a few million searchable records and standard search needs, PostgreSQL eliminates an entire service from your stack. For an e-commerce platform with 50 million products and faceted navigation, Elasticsearch is worth the operational cost. Most applications, in my observation, fall into the first category. I have explored this distinction further in the closing chapter of You Don't Need Redis.

When DQL is not enough: the DBAL boundary

There is a boundary, and it is worth knowing where it sits before you encounter it at midnight with a production issue. DQL — even with postgresql-for-doctrine — cannot express every PostgreSQL feature. Range types, ltree hierarchies, JSONB path queries with @?, lateral joins, recursive CTEs, window functions with custom frames: these require SQL that DQL's AST cannot represent.

This is not a limitation to work around. It is a signal to use the right tool.

<?php
// When DQL is not enough: drop to DBAL for complex PostgreSQL features
$conn = $em->getConnection();

// Range containment with daterange
$sql = "SELECT * FROM reservations
        WHERE daterange(check_in, check_out) && daterange(:start, :end)";

$result = $conn->executeQuery($sql, [
    'start' => '2026-03-01',
    'end'   => '2026-03-15',
]);

// JSONB path queries with jsonpath
$sql = "SELECT * FROM events
        WHERE payload @? '$.items[*] ? (@.price > 100)'";

// ltree hierarchy queries
$sql = "SELECT * FROM categories
        WHERE path <@ 'electronics.audio'";

// These operators have no DQL equivalent — even with
// postgresql-for-doctrine. DBAL is the correct choice here.

DBAL gives you the full PostgreSQL feature set with parameterized queries and connection management. The trade-off is that you lose Doctrine's entity hydration — you get arrays of data, not objects. For many use cases, particularly reporting queries, dashboard aggregations, and complex search results, this is perfectly acceptable. Not every query needs to return a fully hydrated entity graph.

If you need raw SQL and entity hydration, NativeQuery with a ResultSetMappingBuilder provides both:

<?php
// NativeQuery: use raw SQL but map results to entities
use Doctrine\ORM\Query\ResultSetMappingBuilder;

$rsm = new ResultSetMappingBuilder($em);
$rsm->addRootEntityFromClassMetadata(Article::class, 'a');

$sql = "SELECT a.* FROM articles a
        WHERE a.search_vector @@ plainto_tsquery('english', :query)
        ORDER BY ts_rank(a.search_vector, plainto_tsquery('english', :query)) DESC
        LIMIT :limit";

$query = $em->createNativeQuery($sql, $rsm);
$query->setParameter('query', 'postgres performance');
$query->setParameter('limit', 20);

$articles = $query->getResult();
// Returns Article entities, hydrated by Doctrine.
// Full PostgreSQL feature set. No DQL limitations.

The decision tree is straightforward:

• Standard queries (WHERE, JOIN, ORDER BY, GROUP BY): use DQL. It is what Doctrine was built for.
• PostgreSQL operators (ILIKE, @>, @@, array functions): use DQL with postgresql-for-doctrine.
• Advanced PostgreSQL features (ranges, ltree, jsonpath, CTEs, window functions): use DBAL or NativeQuery.
• Performance-critical paths where you need exact control over the SQL: use DBAL or NativeQuery.

Mixing DQL and DBAL in the same application is normal. I would go further — it is desirable. Reaching for DBAL is not an admission of defeat. It is a recognition that you chose PostgreSQL for its features, and you should be able to use them. The application that insists on expressing everything in DQL is the application that re-implements PostgreSQL features in PHP. I have seen this. The results are invariably slower and harder to maintain than the SQL they were avoiding. For an overview of all the index types mentioned here — B-tree, GIN, GiST, and how they apply to different query patterns — allow me to direct you to the index types guide, which covers each type's strengths.

"The features you are not using — full-text search, trigram matching, array containment, JSONB path queries — are not advanced. They are simply undiscovered. And once discovered, they eliminate entire categories of application-layer complexity."

— from You Don't Need Redis, Chapter 19: The Case for Simplicity

The expression index fallback

Not every application can install postgresql-for-doctrine immediately. Legacy codebases, shared Doctrine configurations, organizational inertia, teams with a strict "no new dependencies" policy during a release freeze — the reasons are real, and I do not dismiss them. If you are stuck with LOWER()+LIKE for now, an expression index reduces the damage:

-- If you cannot switch from LOWER()+LIKE to ILIKE,
-- an expression index is your fallback:
CREATE INDEX idx_users_email_lower ON users (LOWER(email));

-- This allows PostgreSQL to use the index for:
--   WHERE LOWER(email) = 'gibson@example.com'
--   WHERE LOWER(email) LIKE 'gibson%'
--
-- But NOT for:
--   WHERE LOWER(email) LIKE '%gibson%'
--
-- For substring patterns with LOWER(), you need:
CREATE INDEX idx_users_email_lower_trgm
  ON users USING gin (LOWER(email) gin_trgm_ops);

-- At this point you are maintaining an expression index
-- to work around a function call that ILIKE eliminates entirely.

A plain expression index on LOWER(email) handles equality and prefix patterns. For substring patterns, you need a trigram expression index on LOWER(email) — at which point you are maintaining a two-layer workaround (function wrapper in PHP, expression index in PostgreSQL) to avoid using ILIKE.

The math on this is simple. One composer require and a YAML change gives you native ILIKE in DQL with a standard trigram index. The expression index approach requires a custom index on every column, is harder to explain to new team members, and performs identically or worse. The workaround has a higher total cost than the fix. I say this not to be dismissive of the constraints that prevent you from adopting the package — those constraints are real — but to make the case that resolving those constraints should be a priority, because the current workaround is more expensive than it appears.

Migration strategy: moving a live codebase

Adopting postgresql-for-doctrine in an existing Symfony application is straightforward, but doing it without disrupting production requires attention to the correct sequence. Allow me to suggest a path.

Step 1: Add the indexes first

<?php
// Doctrine migration to add pg_trgm and trigram indexes
// (generated via: php bin/console make:migration)

use Doctrine\DBAL\Schema\Schema;
use Doctrine\Migrations\AbstractMigration;

final class Version20260311000000 extends AbstractMigration
{
    public function getDescription(): string
    {
        return 'Add pg_trgm extension and trigram indexes for ILIKE';
    }

    public function up(Schema $schema): void
    {
        // pg_trgm ships with PostgreSQL — this is safe
        $this->addSql('CREATE EXTENSION IF NOT EXISTS pg_trgm');

        // CONCURRENTLY avoids locking the table during index creation.
        // Doctrine migrations do not run in a transaction by default,
        // so CONCURRENTLY works here. If your migration framework
        // wraps in a transaction, remove CONCURRENTLY and schedule
        // index creation during a maintenance window.
        $this->addSql(
            'CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_email_trgm '
            . 'ON users USING gin (email gin_trgm_ops)'
        );
        $this->addSql(
            'CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_name_trgm '
            . 'ON users USING gin (name gin_trgm_ops)'
        );
    }

    public function down(Schema $schema): void
    {
        $this->addSql('DROP INDEX IF EXISTS idx_users_name_trgm');
        $this->addSql('DROP INDEX IF EXISTS idx_users_email_trgm');
    }
}

Create the trigram indexes before you change any queries. The indexes support both LOWER()+LIKE and ILIKE, so your existing queries will benefit immediately. The CONCURRENTLY keyword is essential for production — it builds the index without locking the table for writes. On a 500,000-row table, a GIN trigram index takes approximately 8-12 seconds to build concurrently. Plan accordingly, but it is not a maintenance-window event.

Step 2: Install the package and register functions

Install postgresql-for-doctrine and register the functions you need. This changes no runtime behavior — it only makes new DQL vocabulary available. Your existing queries continue to work identically.

Step 3: Migrate queries one repository at a time

<?php
// A clean repository pattern for PostgreSQL-specific queries
// Keeps the ILIKE/array logic in one place.

class UserRepository extends ServiceEntityRepository
{
    public function __construct(ManagerRegistry $registry)
    {
        parent::__construct($registry, User::class);
    }

    /**
     * Case-insensitive substring search across multiple fields.
     * Requires: postgresql-for-doctrine, GIN trigram indexes.
     */
    public function search(string $term, int $limit = 20): array
    {
        $pattern = '%' . $term . '%';

        return $this->createQueryBuilder('u')
            ->where('ILIKE(u.email, :search) = true')
            ->orWhere('ILIKE(u.name, :search) = true')
            ->setParameter('search', $pattern)
            ->setMaxResults($limit)
            ->getQuery()
            ->getResult();
    }

    /**
     * Find users with any of the specified roles.
     * Uses native PostgreSQL array overlap (&&).
     */
    public function findByAnyRole(array $roles): array
    {
        $pgArray = '{' . implode(',', $roles) . '}';

        return $this->createQueryBuilder('u')
            ->where('ANY_OF(u.roles, :roles) = true')
            ->setParameter('roles', $pgArray)
            ->getQuery()
            ->getResult();
    }
}

Change one repository method at a time. Each change is small, testable, and independently deployable. The generated SQL changes from WHERE LOWER(email) LIKE '%gibson%' to WHERE email ILIKE '%gibson%'. Same results, better performance, same API surface. Your controllers, services, and tests do not change — only the repository internals.

Step 4: Remove the expression indexes

Once all queries have been migrated from LOWER()+LIKE to ILIKE, the expression indexes on LOWER(column) are no longer needed. Drop them. They were workarounds, and the workaround is no longer necessary. The trigram indexes remain and serve the ILIKE queries directly.

Testing PostgreSQL-specific queries

There is a trade-off to using database-specific features, and I shall not pretend otherwise. Your tests must use PostgreSQL. An SQLite test database — common in Symfony projects for speed — does not support ILIKE, array containment, or full-text search. A test that cannot exercise the query it is testing is not a test. It is a decoration.

<?php
// Testing PostgreSQL-specific queries requires PostgreSQL.
// SQLite test databases will not work for ILIKE, arrays,
// or full-text search. This is the trade-off of using
// database-specific features.

// phpunit.xml — use a real PostgreSQL database for tests
// DATABASE_URL=postgresql://test:test@localhost:5432/app_test

// Functional test for ILIKE search
class UserRepositoryTest extends KernelTestCase
{
    public function testSearchIsCaseInsensitive(): void
    {
        self::bootKernel();
        $repo = static::getContainer()
            ->get('doctrine')
            ->getRepository(User::class);

        // Fixture: user with email 'Stephen.Gibson@example.com'
        $results = $repo->search('gibson');

        $this->assertCount(1, $results);
        $this->assertSame(
            'Stephen.Gibson@example.com',
            $results[0]->getEmail()
        );
    }

    public function testSearchByArrayContainment(): void
    {
        $repo = static::getContainer()
            ->get('doctrine')
            ->getRepository(Product::class);

        // Fixture: product with tags ['organic', 'local', 'seasonal']
        $results = $repo->findByAllTags(['organic', 'local']);

        $this->assertCount(1, $results);
    }
}

The practical solution is a PostgreSQL test database running locally. With Docker, this is a single line: docker run -d --name pg-test -e POSTGRES_PASSWORD=test -p 5433:5432 postgres:16. The database starts in under a second and can be reset between test suites with DROP SCHEMA public CASCADE; CREATE SCHEMA public;.

If test speed is a concern — and it is a legitimate one — isolate the PostgreSQL-specific repository tests into a separate test suite that runs against a real database, while keeping your unit tests on the fast path. The repository tests are integration tests by nature. Treating them as such is not a compromise; it is an accurate classification.

Symfony's KernelTestCase provides the container, and Doctrine's test utilities handle schema creation. The overhead per test is roughly 2-5 milliseconds for the database round-trip — not the bottleneck you might expect. The bottleneck in test suites is usually fixture setup, not query execution.

An honest accounting of the trade-offs

I have spent the majority of this guide recommending PostgreSQL-specific features and the tools that expose them through DQL. It would be discourteous of me not to address the costs, because every architectural choice has them.

Database portability. The moment you use ILIKE, @>, or @@ in your DQL, your application is bound to PostgreSQL. If there is a realistic possibility of switching to MySQL, MariaDB, or SQL Server, these features create a migration cost. In my experience, the number of production applications that actually switch database engines mid-lifetime is vanishingly small — but if yours is one of them, this is a real constraint. The postgresql-for-doctrine functions will not generate valid MySQL. They will fail at parse time.

Team knowledge. PostgreSQL arrays, trigram indexes, and full-text search are not as widely understood as standard SQL. A junior developer joining the team will need to learn these concepts. This is an investment, not a waste — the knowledge transfers to any PostgreSQL project — but it is time that must be allocated.

Index maintenance overhead. GIN trigram indexes are larger than B-tree indexes and take longer to update on writes. For a read-heavy application (most web applications), this trade-off is overwhelmingly favorable. For a write-heavy application — bulk imports, high-frequency inserts — the index maintenance cost should be measured. On the 500,000-row table in our benchmarks, the GIN trigram index adds approximately 0.1ms per insert. Noticeable in aggregate during bulk operations; invisible during normal application traffic.

The testing cost. As discussed above, you need PostgreSQL for tests. This is a minor inconvenience for most teams and a significant workflow change for teams that have built their entire test infrastructure around SQLite. Plan for this.

These costs are real. They are also, in my assessment, modest compared to the benefits. The alternative — wrapping every column in LOWER(), serializing arrays to strings, deploying Elasticsearch for basic search — carries its own costs, and they are larger. But I would rather you make that determination with full knowledge of both sides than discover the costs after the migration is complete.

What Gold Lapel does with LOWER()+LIKE queries

Gold Lapel is a self-optimizing PostgreSQL proxy. It sits between your Symfony application and PostgreSQL, observing query patterns and rewriting them for better performance. One of its rewrite rules targets exactly the pattern this article addresses.

-- What your Symfony application sends (via LOWER()+LIKE):
SELECT u0_.id, u0_.email, u0_.name
FROM users u0_
WHERE LOWER(u0_.email) LIKE '%gibson%';

-- What Gold Lapel rewrites it to:
SELECT u0_.id, u0_.email, u0_.name
FROM users u0_
WHERE u0_.email ILIKE '%gibson%';

-- Same results. 19x faster with a trigram index.
-- No query changes. Just composer require goldlapel/goldlapel.

When Gold Lapel sees WHERE LOWER(column) LIKE pattern, it rewrites it to WHERE column ILIKE pattern. The semantics are identical — both perform case-insensitive matching. But the rewritten form can use a standard trigram index, while the original form requires an expression index or falls back to a sequential scan.

The rewriting is not limited to the simple case:

-- Gold Lapel recognizes several LOWER()+LIKE variants:

-- Pattern 1: LOWER(col) LIKE '%term%'
-- Rewritten to: col ILIKE '%term%'

-- Pattern 2: LOWER(col) = 'exact'
-- Rewritten to: col ILIKE 'exact'

-- Pattern 3: LOWER(col) LIKE 'prefix%'
-- Rewritten to: col ILIKE 'prefix%'

-- Pattern 4: LOWER(col) IN ('a', 'b', 'c')
-- Rewritten to: col ILIKE ANY(ARRAY['a', 'b', 'c'])

-- All four produce identical results with less CPU work
-- and better index utilization. The rewriting happens at
-- the wire protocol level — Doctrine never knows.

Gold Lapel also handles the indexing side. If it sees ILIKE queries hitting a column without a trigram index, it recommends (or creates, depending on your configuration) the gin_trgm_ops index automatically. The same applies to full-text search patterns without GIN indexes on tsvector columns, and array containment queries without GIN indexes on array columns.

This matters for Symfony applications specifically because DQL generates SQL that the application developer does not write directly. The LOWER()+LIKE pattern is a Doctrine convention, not a developer choice — it exists because DQL lacks ILIKE, and every Doctrine tutorial and Stack Overflow answer teaches the workaround. Gold Lapel corrects it at the wire protocol level. No query changes. Just composer require goldlapel/goldlapel and your existing QueryBuilder calls continue to work. The SQL that reaches PostgreSQL is simply better.

I should note: the approaches are not mutually exclusive. Installing postgresql-for-doctrine and switching your DQL to use ILIKE is the correct long-term solution — it makes your code express what it actually means, and it gives you access to arrays, full-text search, and the other features discussed here. Gold Lapel handles the queries you have not migrated yet, and the queries generated by third-party bundles whose DQL you do not control. The PHP integration guide covers the setup in detail.

The brief inventory

If you will permit me a summary of the household — not as a recitation, but as a checklist to take with you.

For case-insensitive search: use ILIKE via postgresql-for-doctrine with GIN trigram indexes. Replace LOWER()+LIKE wherever it appears. The performance improvement is 10-20x for indexed queries, and the generated SQL is cleaner, more portable within the PostgreSQL ecosystem, and locale-independent.

For array queries: use native PostgreSQL arrays with ALL_OF and ANY_OF when the data model fits — bounded, simple-valued arrays queried by containment or overlap. Use join tables when you need referential integrity, association metadata, or independent querying of the related entities.

For full-text search: use PostgreSQL's built-in tsvector/tsquery with stored generated columns and GIN indexes. Consider Elasticsearch only when you need distributed search, complex aggregations, or fuzzy matching beyond what pg_trgm provides.

For everything else PostgreSQL offers: use DBAL or NativeQuery without guilt. DQL's boundaries are not failures — they are the natural limit of a database-agnostic query language encountering a database with capabilities beyond the common denominator.

The household is better kept when the tools match the task. A trigram index for substring search. A GIN index for array containment. A tsvector column for full-text. Each purpose-built. Each performing exactly the duty it was designed for. One does find that rather satisfying.