Search often starts as a text box.
Then the requirements arrive.
Users want to search by a specific field. Then multiple fields. Then exclusions. Date ranges. Multi-select filters. AND, OR, NOT. Parentheses. Nested conditions.
Eventually, this:
payment approval
turns into this:
(
"payment approval"
OR (
department:finance
AND (status:open OR status:pending)
)
)
AND NOT (
owner:system
OR category:archived
)
At that point, you're no longer building a text box.
You're designing a query language.
I ran into exactly this while designing a global search system for a production application.
From the user's perspective, I wanted the experience to remain simple: one global search surface, similar to the issue-search experience developers are familiar with in tools like GitHub or GitLab.
Underneath, the requirements evolved into a Lucene-style query model supporting:
- free-text search
-
key:valuefilters - quoted phrases
-
AND,OR, andNOT - parentheses and grouping
- recursively nested expressions
- text filters
- date ranges
- multi-select fields
- column-level filtering
But there was one requirement I considered just as important as the query language itself:
Users shouldn't need to understand the query language to use it.
So the system supported two ways of expressing the same search intent.
Power users could write the query directly.
Everyone else could build it through column-level filters. Depending on the field, the UI provided a text input, date-range picker, multi-select dropdown, or Boolean condition and constructed the expression as the user filtered.
Then the production dataset grew, and a second problem emerged:
free-text search performance.
That eventually led to another architectural decision:
Do I introduce a dedicated search engine, or can PostgreSQL continue owning search?
I kept PostgreSQL.
After profiling the expensive path and moving the free-text workload to a GIN-backed indexing strategy, measured search latency dropped from roughly 40ms to 12ms on a production dataset containing more than 20,000 records at the time.
The performance number is useful, but it isn't the most interesting part of the story.
What interested me more was how one search box ended up touching UX design, language parsing, recursive data structures, database performance, and infrastructure trade-offs.
One search box, different levels of precision
I didn't want advanced search to make basic search harder.
Someone should always be able to type:
payment approval
and search normally.
Nothing else should be required.
A user who knows exactly what they're looking for can be more specific:
status:open
or:
owner:"John Doe"
Free text and structured conditions can be combined:
"payment approval" AND status:open
And power users can go much further:
("payment approval" OR refund)
AND (status:open OR status:pending)
AND NOT owner:system
This gave the search experience progressive complexity.
You don't need to understand the language to start searching.
But when you need more precision, the language is there.
The next problem was usability.
Most users shouldn't have to learn that syntax.
So I didn't make them.
The UI could write the query for you
Alongside the global search input, I designed column-level filtering.
The control shown to the user depended on the type of data being filtered.
For text:
Owner
┌────────────────────────┐
│ John │
└────────────────────────┘
For dates:
Created Date
From: 01 Aug 2026
To: 14 Aug 2026
For predefined values:
Status
✓ Open
✓ Pending
Conditions could also include or exclude values:
Status
AND
Open
or:
Status
NOT
Archived
As users interacted with those controls, the application constructed the corresponding search expression.
A user might interact with something like:
Search: payment approval
Status:
✓ Open
✓ Pending
Owner:
NOT System
Created:
Aug 1 → Aug 14
while the system represented the same intent as something equivalent to:
"payment approval"
AND (status:open OR status:pending)
AND NOT owner:system
AND createdDate:[2026-08-01 TO 2026-08-14]
The user didn't need to write that.
The UI did it for them.
That became an important principle in the design:
A system can support a sophisticated query language without requiring users to speak that language.
Two interfaces, one search model
I didn't want the visual filter builder and the advanced query input to become two independent search implementations.
That would eventually create two sets of semantics, two places for bugs, and two implementations that could disagree about what the same filter means.
Instead, both interfaces converged on the same search model.
┌─────────────────────────┐
│ Global Search Box │
│ │
│ Lucene-style syntax │
└────────────┬────────────┘
│
▼
Search Expression
▲
│
┌────────────┴────────────┐
│ Column Filter Builder │
│ │
│ • Text input │
│ • Date range │
│ • Multi-select │
│ • AND / NOT │
└─────────────────────────┘
From there, the expression entered the same processing pipeline:
Search Expression
│
▼
Tokenizer
│
▼
Parser
│
▼
AST
│
▼
Validation
│
▼
Query Compiler
│
▼
Prisma / SQL
│
▼
PostgreSQL
There weren't two search engines.
There were two ways of expressing the same search intent.
That distinction kept the architecture much easier to reason about.
When search became a language
Boolean operators changed the nature of the problem.
Consider:
status:open OR status:pending AND priority:high
With normal Boolean precedence, that means:
status:open
OR
(status:pending AND priority:high)
which is different from:
(status:open OR status:pending)
AND priority:high
Now add NOT:
(status:open OR status:pending)
AND NOT owner:system
Then nested grouping:
(
status:open
OR (
status:pending
AND (
priority:high
OR priority:critical
)
)
)
AND NOT owner:system
At this point, .split("AND") isn't an architecture.
Neither is continuing to grow a regular expression until nobody wants to touch it.
The requirement had crossed an architectural boundary.
I needed a parser.
Treating search like a small compiler
I ended up treating the search input similarly to a small language-processing pipeline:
Raw Query
│
▼
Tokenizer
│
▼
Parser
│
▼
AST
│
▼
Validator
│
▼
Compiler
│
▼
PostgreSQL
Each stage had a narrow responsibility.
The tokenizer identified meaningful pieces of the language.
The parser determined how those pieces related to each other.
The AST represented the user's intent independently of the original string.
The validator ensured that only supported fields and operations could be used.
The compiler translated that structured representation into database conditions.
That separation mattered.
The parser didn't need to understand how PostgreSQL executed search.
PostgreSQL didn't need to understand our user-facing syntax.
And the database layer didn't need to repeatedly reinterpret an arbitrary search string.
From a query string to structured data
Take:
(payment OR refund) AND status:open
The tokenizer can identify units such as:
LPAREN
TEXT(payment)
OR
TEXT(refund)
RPAREN
AND
FIELD(status, open)
Quoted values remain intact:
owner:"John Doe"
as do quoted free-text phrases:
"payment approval"
Parentheses and Boolean operators remain explicit because they determine the structure of the expression.
Now consider:
(payment OR refund) AND NOT status:archived
Instead of carrying that string through the application, the parser can represent its meaning as a tree:
AND
/ \
OR NOT
/ \ \
payment refund FIELD
│
status:archived
A simplified TypeScript representation could look like:
type SearchNode =
| {
type: "text";
value: string;
}
| {
type: "field";
field: string;
value: string;
}
| {
type: "and" | "or";
left: SearchNode;
right: SearchNode;
}
| {
type: "not";
operand: SearchNode;
};
The exact TypeScript isn't the interesting part.
The boundary is.
The user's arbitrary string has become structured data that can be validated, transformed, tested, and compiled.
Supporting N-level nested search
I didn't want the grammar to contain a hard-coded concept of:
level 1
level 2
level 3
Expressions are recursive.
Conceptually:
Expression :=
FreeText
| FieldCondition
| NOT Expression
| (Expression)
| Expression AND Expression
| Expression OR Expression
An expression can contain another expression, which can contain another expression.
So:
A AND (B OR (C AND (D OR (E AND NOT F))))
doesn't require special handling.
It's simply a deeper tree:
AND
/ \
A OR
/ \
B AND
/ \
C OR
/ \
D AND
/ \
E NOT
│
F
The compiler recursively walks the tree.
There is no fixed application-level nesting depth in the search grammar.
That lets users continue composing conditions when they need more precision instead of running into an arbitrary maximum number of filter groups.
Of course, that doesn't mean execution has infinite resources.
Request size, runtime behavior, query complexity, timeouts, and infrastructure still create practical boundaries.
Those are operational constraints rather than artificial limits in the grammar.
Search fields are an API, not database columns
Supporting:
status:open
raises another question.
What happens if someone tries:
someInternalDatabaseField:value
A search language shouldn't automatically become an interface to the underlying database schema.
Instead, searchable fields should be explicitly supported.
A simplified mapping might look like:
const searchableFields = {
status: /* internal mapping */,
owner: /* internal mapping */,
category: /* internal mapping */,
priority: /* internal mapping */,
};
The actual implementation can contain considerably more metadata because different fields require different controls, validation rules, and database behavior.
But the principle is simple:
The search language has its own contract.
A field becomes searchable because the application deliberately exposes it, not because a similarly named PostgreSQL column happens to exist.
That also gives the database schema room to evolve without necessarily breaking the query syntax users already know.
Then the dataset grew
The most complicated-looking part of this architecture wasn't where I encountered the main performance problem.
The parser worked.
Recursive expressions worked.
Structured filters worked.
The problem appeared somewhere much less exotic:
free-text search.
When the dataset was smaller, the existing implementation performed well enough.
Then production data accumulated.
The same kind of free-text search now had increasingly more data to work through.
The code hadn't necessarily regressed.
The workload had changed.
That's an important distinction.
A query that behaves perfectly well at one data volume can have very different characteristics as that volume grows.
So I didn't start by optimizing the parser.
I started by measuring the database path.
Measure before changing the architecture
I wanted to understand what PostgreSQL was actually doing.
EXPLAIN ANALYZE was part of that investigation.
EXPLAIN ANALYZE
SELECT ...
I was looking for answers to concrete questions:
- How many rows are being examined?
- Are we scanning more data than necessary?
- Are the indexes I expect actually being used?
- Which part of the generated condition is expensive?
- Where is execution time being spent?
- How does the execution plan behave as the dataset grows?
This distinction mattered.
The system contained sophisticated application code, but optimizing the most complicated-looking code wouldn't help if PostgreSQL was doing the expensive work.
Optimize what the measurements tell you is expensive, not what looks complicated.
Why GIN fit the workload
Structured filters and free text have different access patterns.
A B-tree index is a natural fit for many structured conditions:
WHERE status = 'OPEN'
Free-text search asks a different kind of question:
Which records contain these searchable terms?
That's where an inverted index becomes useful.
PostgreSQL already provides this capability through GIN — Generalized Inverted Index.
At a high level, an inverted index maintains relationships between searchable terms and the records containing them.
payment ─────► Record 12
├────► Record 48
└────► Record 91
approval ────► Record 12
└───► Record 103
invoice ─────► Record 27
└────► Record 91
A simplified PostgreSQL example might look like:
CREATE INDEX idx_records_search
ON records
USING GIN (
to_tsvector('english', searchable_text)
);
with a corresponding full-text condition:
SELECT id, title
FROM records
WHERE
to_tsvector('english', searchable_text)
@@ plainto_tsquery('english', 'payment approval');
The production implementation was more involved because free text could appear alongside structured conditions and recursively nested Boolean expressions.
Architecturally, though, the responsibilities remained separate:
Search AST
│
┌─────────┴─────────┐
▼ ▼
Structured Free Text
Conditions Search
│ │
▼ ▼
Appropriate GIN
indexes index
│ │
└─────────┬─────────┘
▼
PostgreSQL
The query language remained expressive while the expensive free-text path could be optimized independently.
From roughly 40ms to 12ms
After optimizing the free-text path around the appropriate PostgreSQL indexing strategy, measured search latency moved approximately from:
Before After
~40ms → ~12ms
That's roughly a 70% reduction for the workload we measured.
The production dataset contained more than 20,000 records at the time.
That result needs context.
It does not mean:
Add GIN and PostgreSQL becomes 70% faster.
Database performance depends on schema, data distribution, query shape, hardware, cache state, and workload.
The useful part is the process:
Observe
↓
Measure
↓
Inspect the execution plan
↓
Understand the access pattern
↓
Choose the appropriate index
↓
Measure again
GIN fit our access pattern.
The decision came from the workload, not the other way around.
Why I didn't introduce Elasticsearch
Once free-text search became a performance discussion, a dedicated search engine was an obvious option to consider.
Elasticsearch could handle this kind of search.
But capability alone wasn't enough reason for me to introduce another system.
PostgreSQL was already part of the production architecture and remained the source of truth.
Keeping search there meant the architecture remained roughly:
Application
│
▼
PostgreSQL
┌─────┴─────┐
│ │
Structured Free Text
Search Search
│ │
Appropriate GIN
indexes index
Introducing another search datastore changes that:
Application
│
┌───────┴───────┐
▼ ▼
PostgreSQL Search Engine
│ ▲
└───── Sync ────┘
That synchronization arrow looks small on an architecture diagram.
Operationally, it isn't.
It introduces concerns around:
- data synchronization
- eventual consistency
- failed indexing operations
- retry and replay
- index rebuilding
- mappings
- monitoring
- deployments and upgrades
- infrastructure cost
- another failure domain
None of those make Elasticsearch a bad choice.
They're simply part of the cost of owning another system.
For our workload, PostgreSQL was already there, GIN matched the free-text access pattern, and the resulting performance satisfied the requirements.
Adding another datastore would have increased the operational surface without solving a problem we still had.
So I didn't add one.
GIN was an architectural decision, not just an index
It's easy to summarize the change as:
I added a GIN index and made search faster.
That's technically part of what happened.
But the architectural decision was larger.
I effectively had three directions:
Growing Search Cost
│
┌────────────┼────────────┐
▼ ▼ ▼
Leave it Optimize Introduce
as-is PostgreSQL Search Engine
│
▼
GIN
Doing nothing wasn't going to age well.
A dedicated search engine could solve the problem, but it would introduce another operational component.
Optimizing PostgreSQL gave me a middle path:
Use more of the capabilities of the system we already operate before introducing another one.
That kept PostgreSQL as the source of truth.
No search synchronization pipeline.
No additional datastore.
No additional operational dependency.
And it met the performance requirements.
The question wasn't which technology was more powerful.
The question was which architecture introduced the right amount of complexity for the problem we actually had.
When I would introduce a dedicated search engine
This isn't an argument that PostgreSQL should replace Elasticsearch.
I'd revisit the architecture if the requirements started demanding things such as:
- sophisticated relevance ranking
- advanced fuzzy matching and typo tolerance
- complex language-specific analysis
- significantly larger search workloads
- independent scaling of search
- complex search aggregations
- search-specific availability requirements
At that point, specialized search infrastructure may provide enough value to justify its operational cost.
The useful question isn't:
PostgreSQL or Elasticsearch?
It's:
Have the requirements crossed the point where owning another system is justified?
For us, they hadn't.
Leaving room to evolve without building the future
There was another reason I was comfortable keeping PostgreSQL.
The query language wasn't directly coupled to it.
The boundary looked roughly like:
User Intent
│
▼
Search Expression
│
▼
AST
│
▼
Validation
│
▼
PostgreSQL Compiler
│
▼
PostgreSQL
PostgreSQL was the current execution strategy.
It wasn't the definition of the search language.
If the requirements eventually justify another backend, there's a natural architectural seam:
AST
│
┌───────┴───────┐
▼ ▼
PostgreSQL Dedicated
Compiler Search Compiler
I wouldn't implement that second compiler today.
That's an important distinction.
Designing a boundary is useful. Building hypothetical infrastructure isn't.
What I'd do differently today
If I were starting this feature again, I'd formalize the query language earlier.
Search requirements tend to grow incrementally.
payment
becomes:
status:open
then:
payment AND status:open
then:
payment AND (status:open OR status:pending)
and eventually:
(
payment
OR (
refund
AND (status:open OR status:pending)
)
)
AND NOT owner:system
At some point, you've created a language without explicitly deciding to create one.
Once structured fields, Boolean operators, and grouping start appearing in the requirements, I'd define the boundaries early:
Grammar
↓
Tokenizer
↓
Parser
↓
AST
↓
Validation
↓
Compilation
↓
Execution
That makes everything that follows easier to reason about and test.
What I took away from building it
Looking back, what appeared to be one feature was really three different engineering problems.
Expressiveness
Free text gradually became a query language:
free text
key:value
AND / OR / NOT
parentheses
recursive expressions
That required treating search as structured input:
tokenize → parse → AST → validate → compile
Usability
The query language could be powerful without becoming a prerequisite for using search.
The column-level filter builder exposed controls appropriate to the underlying data—text inputs, date ranges, multi-selects, and Boolean conditions—and generated the same search representation underneath.
Two interfaces. One search model.
The complexity stayed in the system instead of being pushed onto every user.
Scale
The part that looked complicated wasn't the part that eventually became expensive.
As the dataset grew, free-text search became the bottleneck.
The response wasn't to rewrite the parser or immediately introduce another datastore.
It was to measure the query path, inspect what PostgreSQL was doing, and optimize the access pattern that was actually expensive.
For our workload, a GIN-backed strategy brought measured search latency from roughly 40ms to 12ms while allowing PostgreSQL to remain the source of truth.
Could a dedicated search engine become the right architecture later?
Absolutely.
But I didn't want to pay the synchronization, consistency, infrastructure, and operational costs of another system before the requirements justified them.
That's probably the main thing I took away from building this:
Good architecture isn't about eliminating complexity. It's about putting complexity in the right place—and only introducing it when the problem actually requires it.
Top comments (0)