Stage 7 · Master
Phase 11 — Notice Service
Search
Teach the notice feed to answer resident-style queries with PostgreSQL full-text search that balances relevance against freshness.
How does PostgreSQL tokenize notice text?
Residents do not search with exact SQL terms; they type phrases like 'water shutdown block c' or 'election date'. PostgreSQL full-text search is a good fit because it normalizes terms, supports multi-word free-text queries through websearch_to_tsquery, and can keep the index close to the relational data instead of sending one more domain to a separate search stack before the platform needs it.
The generated search_vector column stores weighted tokens from title and body so title hits count more. A stale but exact title match should score well, but not so well that it always outranks a more recent operational notice residents are more likely to care about today.
ALTER TABLE notices
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 notices_search_vector_idx
ON notices USING GIN (search_vector);
CREATE INDEX notices_org_state_published_idx
ON notices (org_id, state, published_at DESC, id DESC)
WHERE state = 'published';A stored generated column keeps tokenization logic in one place and ensures every INSERT or UPDATE refreshes the tsvector consistently.
DROP INDEX IF EXISTS notices_org_state_published_idx;
DROP INDEX IF EXISTS notices_search_vector_idx;
ALTER TABLE notices DROP COLUMN IF EXISTS search_vector;What ranking formula balances exact matches with freshness?
Pure lexical ranking can overvalue historical notices. If a resident searches for 'election' during this year's nomination window, a two-year-old AGM notice with the exact phrase in the title should not necessarily outrank today's announcement that uses slightly different wording. That is why ranking combines ts_rank_cd with a recency decay term.
ts_rank_cd(n.search_vector, q.query) * 0.85
+ 0.15 * exp(
-GREATEST(EXTRACT(EPOCH FROM ($4::timestamptz - n.published_at)), 0) / 604800.0
) AS scoreThe recency term decays over seven-day units. A newly published close match keeps a noticeable boost without completely swamping true relevance.
Freeze ranking against one reference timestamp per request
The ranking formula should use a request-scoped reference time, not raw now() in every page query, because search pagination later depends on a stable ordering. If page 1 and page 2 compute freshness against different moments, the rank of borderline items can drift between requests and break seek pagination.
package notice
import (
"context"
"fmt"
"time"
"github.com/google/uuid"
"github.com/jackc/pgx/v5/pgxpool"
)
type SearchResult struct {
ID uuid.UUID
Title string
Body string
PublishedAt time.Time
Score float64
}
type SearchService struct {
pool *pgxpool.Pool
}
func NewSearchService(pool *pgxpool.Pool) *SearchService {
return &SearchService{pool: pool}
}
func (s *SearchService) Search(ctx context.Context, orgID uuid.UUID, rawQuery string, limit int, referenceTime time.Time) ([]SearchResult, error) {
if limit <= 0 || limit > 50 {
limit = 20
}
const q = "WITH input AS (" +
" SELECT websearch_to_tsquery('english', $2) AS query" +
"), ranked AS (" +
" SELECT n.id, n.title, n.body, n.published_at," +
" ts_rank_cd(n.search_vector, input.query) * 0.85" +
" + 0.15 * exp(-GREATEST(EXTRACT(EPOCH FROM ($4 - n.published_at)), 0) / 604800.0) AS score" +
" FROM notices AS n" +
" CROSS JOIN input" +
" WHERE n.org_id = $1" +
" AND n.state = 'published'" +
" AND n.search_vector @@ input.query" +
")" +
" SELECT id, title, body, published_at, score" +
" FROM ranked" +
" ORDER BY score DESC, id DESC" +
" LIMIT $3"
rows, err := s.pool.Query(ctx, q, orgID, rawQuery, limit, referenceTime.UTC())
if err != nil {
return nil, fmt.Errorf("search notices: %w", err)
}
defer rows.Close()
results := make([]SearchResult, 0, limit)
for rows.Next() {
var item SearchResult
if err := rows.Scan(&item.ID, &item.Title, &item.Body, &item.PublishedAt, &item.Score); err != nil {
return nil, fmt.Errorf("scan search result: %w", err)
}
results = append(results, item)
}
if err := rows.Err(); err != nil {
return nil, fmt.Errorf("iterate search results: %w", err)
}
return results, nil
}The WHERE clause always applies org_id before returning any result, which is the difference between one shared search table and a tenant-isolated query over it.
Why keep one shared search index instead of per-tenant tables?
One notices table with a shared GIN index is the right design here because the query still scopes by org_id and PostgreSQL can combine the GIN match with the org/state/published btree filter. A per-tenant search table explodes operational complexity: migrations multiply, autovacuum statistics fragment, and small tenants pay the cost of many tiny underutilized indexes.
| Design | Operational cost | Why it wins or loses |
|---|---|---|
| Shared notices table + org-scoped WHERE clause | One schema, one GIN index family, straightforward migrations | Best fit because tenant isolation is enforced by row filtering, not by duplicating every table per tenant |
| Per-tenant search tables | Many tables, many indexes, many migrations and vacuum targets | Anti-pattern here because it turns a query-isolation problem into a schema-management problem |
A shared index is an internal implementation detail. The API contract is still org-scoped, so every search path must keep WHERE org_id = $1 and published-only visibility rules in place.
How do you verify search quality and isolation?
Test both ranking and isolation. Seed two organizations with similar notice text, then search from one org and confirm the other org's rows never appear. Also seed one old exact-title match and one recent close match so you can see the freshness boost doing real work instead of treating lexical rank as the only signal.
Applied exercise
Rank a stale exact match against a fresh close match
Your fixture set has two published notices in the same organization. Notice A is two years old, has title 'Annual election notice', and matches the query exactly. Notice B is three days old, titled 'Election nominations open', and matches only partially in the title but strongly in the body. Use the combined score to decide which one should lead the results.
- Identify which parts of the score come from lexical relevance and which come from recency.
- Explain why websearch_to_tsquery is a better fit for resident-entered phrases than hand-building tsquery syntax in the client.
- Decide which notice should rank first under the provided formula and justify it in one sentence about resident usefulness.
- State the exact WHERE clause that keeps similarly worded notices from another organization out of the result set.
Deliverable
A ranking note that names the winning notice, breaks down the score components qualitatively, and includes the tenant-scoping predicate verbatim.
Completion checks
- The answer recognizes that freshness is a boost, not a replacement for lexical rank.
- The winning result is justified from the combined formula rather than from intuition alone.
- The org_id filter is present alongside the full-text predicate.
Why use websearch_to_tsquery for the notice search box?
Mark this lesson complete to store local progress and unlock a cleaner resume path the next time you visit.