Forem: antoniodipinto

How We Tracked 10,000,000 Spins Per Day with Go and PostgreSQL Partitioning

antoniodipinto — Fri, 20 Feb 2026 17:19:13 +0000

Every spin is a legal record

I worked at an online casino gaming company. Regulated market, multiple jurisdictions, thousands of concurrent players, hundreds of game titles. Every time a player clicked "Spin" on a slot machine, we had to record it. Not just the result. The bet amount, the win amount, the game ID, the player ID, the jurisdiction, the random number generator seed, and a timestamp accurate to the millisecond.

This was not optional. Gaming regulators require a complete, auditable trail of every wager. If a regulator asks "show me every spin player X made in September 2019," you need to produce it within hours. If you lose data, you lose your license.

At peak hours we were recording around 10,000,000 spin events per day. On promotional weekends with free spins campaigns that number doubled. The spins table grew by roughly 300,000,000 rows per month.

For the first year, this worked. Then it didn't.

The table that stopped responding

The spins table looked like this:

CREATE TABLE spins (
    id            bigserial PRIMARY KEY,
    player_id     bigint NOT NULL,
    game_id       int NOT NULL,
    jurisdiction  text NOT NULL,
    bet_amount    numeric(12,2) NOT NULL,
    win_amount    numeric(12,2) NOT NULL,
    rng_seed      text NOT NULL,
    created_at    timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_spins_player_created ON spins (player_id, created_at DESC);
CREATE INDEX idx_spins_game_created ON spins (game_id, created_at);
CREATE INDEX idx_spins_jurisdiction_created ON spins (jurisdiction, created_at);

At 500,000,000 rows, three things broke at the same time.

Queries on recent data got slow. The daily revenue report ran a simple aggregation:

SELECT game_id, sum(bet_amount), sum(win_amount)
FROM spins
WHERE created_at >= now() - interval '24 hours'
GROUP BY game_id;

On 500M rows, the planner chose a sequential scan of the idx_spins_game_created index. The index itself was 18GB. Traversing it to find 24 hours of data in a 2-year table took 14 seconds. The finance team ran this query every morning. Sometimes they ran it three times because they thought it was stuck.

Deleting old data became dangerous. Gaming regulations in most jurisdictions require keeping records for 5 years. But some internal analytics tables derived from spins only needed 90 days. Running:

DELETE FROM spins_analytics WHERE created_at < now() - interval '90 days';

on a 200M-row derived table took 40 minutes, generated gigabytes of WAL, and caused replication lag that triggered alerts. Autovacuum then spent another hour cleaning up the dead tuples.

Index maintenance crushed writes. Every insert updated three indexes. At 10M inserts per day, that is 30M index entry writes per day. The indexes were bloated, and periodic REINDEX operations required downtime or a CONCURRENTLY build that ran for hours.

Range partitioning: one table, many partitions

PostgreSQL declarative partitioning splits a logical table into physical partitions. Each partition is a separate table on disk, but queries against the parent table work transparently. PostgreSQL routes reads and writes to the correct partition automatically.

For time-series data like spins, RANGE partitioning on the timestamp column is the natural fit:

CREATE TABLE spins (
    id            bigserial,
    player_id     bigint NOT NULL,
    game_id       int NOT NULL,
    jurisdiction  text NOT NULL,
    bet_amount    numeric(12,2) NOT NULL,
    win_amount    numeric(12,2) NOT NULL,
    rng_seed      text NOT NULL,
    created_at    timestamptz NOT NULL DEFAULT now()
) PARTITION BY RANGE (created_at);

Then create one partition per month:

CREATE TABLE spins_2025_09 PARTITION OF spins
    FOR VALUES FROM ('2025-09-01') TO ('2025-10-01');

CREATE TABLE spins_2025_10 PARTITION OF spins
    FOR VALUES FROM ('2025-10-01') TO ('2025-11-01');

CREATE TABLE spins_2025_11 PARTITION OF spins
    FOR VALUES FROM ('2025-11-01') TO ('2025-12-01');

When you insert a row with created_at = '2025-10-15 14:32:01', PostgreSQL routes it to spins_2025_10 automatically. No application code changes. The insert statement is the same INSERT INTO spins (...) it always was.

The three problems, solved

Fast queries on recent data

The daily revenue query now only scans the current month's partition:

EXPLAIN ANALYZE
SELECT game_id, sum(bet_amount), sum(win_amount)
FROM spins
WHERE created_at >= now() - interval '24 hours'
GROUP BY game_id;

Append  (cost=0.00..48231.45 rows=112847 width=44)
       (actual time=0.031..82.417 rows=10241847 loops=1)
  ->  Seq Scan on spins_2026_02  (cost=0.00..48231.45 rows=112847 width=44)
        (actual time=0.029..61.340 rows=10241847 loops=1)
        Filter: (created_at >= (now() - '24:00:00'::interval))
Planning Time: 0.412 ms
Execution Time: 83.109 ms

PostgreSQL pruned every partition except the current month. Instead of scanning a 18GB index across 500M rows, it scanned a single partition with ~10M rows. From 14 seconds to 83 milliseconds.

This is called partition pruning. The planner looks at the WHERE clause, compares it against the partition boundaries, and eliminates partitions that cannot contain matching rows. It happens at plan time, before any data is read.

Instant data removal

Dropping old data is no longer a DELETE. It is a DROP TABLE:

DROP TABLE spins_2024_06;

This is instantaneous regardless of how many rows the partition contained. No dead tuples, no vacuum, no WAL bloat, no replication lag. The files are unlinked from disk and the space is reclaimed immediately.

For our compliance requirement, we kept 5 years of monthly partitions (60 partitions). For the analytics-derived tables, we kept 3 months and dropped anything older. The 40-minute DELETE became a sub-second DROP.

Smaller indexes, faster writes

Each partition has its own indexes. Instead of one 18GB B-tree across 500M rows, you have 24 smaller B-trees (one per month), each covering ~20M rows:

CREATE INDEX idx_spins_2025_10_player
    ON spins_2025_10 (player_id, created_at DESC);

CREATE INDEX idx_spins_2025_10_game
    ON spins_2025_10 (game_id, created_at);

CREATE INDEX idx_spins_2025_10_jurisdiction
    ON spins_2025_10 (jurisdiction, created_at);

Smaller indexes mean faster lookups, faster inserts (less tree traversal per write), and faster maintenance. Reindexing a single month's partition takes seconds, not hours.

PostgreSQL can also create indexes on the parent table and propagate them automatically to new partitions:

CREATE INDEX ON spins (player_id, created_at DESC);
CREATE INDEX ON spins (game_id, created_at);
CREATE INDEX ON spins (jurisdiction, created_at);

Every new partition inherits these index definitions. No manual step needed.

BRIN indexes for time-ordered partitions

Inside each monthly partition, rows arrive in roughly chronological order. When data is physically ordered by time, BRIN (Block Range Index) is a good fit.

A BRIN index stores the min/max value for each block of pages instead of indexing every row. It is tiny compared to a B-tree:

CREATE INDEX idx_spins_2025_10_created_brin
    ON spins_2025_10 USING brin (created_at);

A BRIN index on a 20M-row partition is roughly 48KB. The equivalent B-tree would be around 400MB. For time-range scans within a single partition ("show me all spins between 3 PM and 4 PM on October 15"), BRIN is fast enough and uses almost no disk.

We kept B-tree indexes on player_id and game_id (for point lookups and joins) but switched to BRIN for created_at within each partition. The total index footprint dropped from 54GB to under 8GB.

The Go insert pipeline

Recording 10M spins per day means sustaining roughly 115 inserts per second on average, with peaks of 500+/sec during evening hours across European and Latin American markets.

Single-row inserts with database/sql would work at this rate, but we were also shipping data in batches from game providers. Some providers sent results in bulk, a batch of 50,000 spins every 30 seconds. For those, the COPY protocol was essential.

type SpinRecord struct {
    PlayerID     int64
    GameID       int32
    Jurisdiction string
    BetAmount    float64
    WinAmount    float64
    RNGSeed      string
    CreatedAt    time.Time
}

func insertSpinBatch(ctx context.Context, conn *pgx.Conn, spins []SpinRecord) (int64, error) {
    rows := make([][]interface{}, len(spins))
    for i, s := range spins {
        rows[i] = []interface{}{
            s.PlayerID,
            s.GameID,
            s.Jurisdiction,
            s.BetAmount,
            s.WinAmount,
            s.RNGSeed,
            s.CreatedAt,
        }
    }

    count, err := conn.CopyFrom(
        ctx,
        pgx.Identifier{"spins"},
        []string{
            "player_id", "game_id", "jurisdiction",
            "bet_amount", "win_amount", "rng_seed", "created_at",
        },
        pgx.CopyFromRows(rows),
    )
    if err != nil {
        return 0, fmt.Errorf("copy spins: %w", err)
    }
    return count, nil
}

CopyFrom uses PostgreSQL's binary COPY protocol. No SQL parsing per row, no per-statement overhead. A batch of 50,000 rows completes in under 2 seconds. PostgreSQL handles the partition routing automatically -- the COPY targets the parent table spins, and each row lands in the correct monthly partition based on its created_at value.

We ran 4 worker goroutines, each with its own connection from a pgxpool.Pool, consuming batches from a channel:

func runIngestWorkers(ctx context.Context, pool *pgxpool.Pool, batches <-chan []SpinRecord, workers int) error {
    g, ctx := errgroup.WithContext(ctx)

    for i := 0; i < workers; i++ {
        g.Go(func() error {
            for {
                select {
                case <-ctx.Done():
                    return ctx.Err()
                case batch, ok := <-batches:
                    if !ok {
                        return nil
                    }
                    conn, err := pool.Acquire(ctx)
                    if err != nil {
                        return fmt.Errorf("acquire conn: %w", err)
                    }
                    _, err = insertSpinBatch(ctx, conn.Conn(), batch)
                    conn.Release()
                    if err != nil {
                        return fmt.Errorf("insert batch: %w", err)
                    }
                }
            }
        })
    }

    return g.Wait()
}

The producer read from game provider feeds (HTTP webhooks and message queues), buffered spins into slices of 10,000, and pushed them into the channel. The workers drained the channel and COPYed into Postgres. Backpressure was handled by the channel's buffer size. If all workers were busy, the producer blocked until a slot opened.

Automated partition creation

If a row arrives and no matching partition exists, PostgreSQL rejects the insert with an error. You need partitions to exist before data arrives. We automated this with a Go goroutine that ran daily:

func ensurePartitions(ctx context.Context, pool *pgxpool.Pool, monthsAhead int) error {
    now := time.Now().UTC()

    for i := 0; i <= monthsAhead; i++ {
        target := now.AddDate(0, i, 0)
        partName := fmt.Sprintf("spins_%d_%02d", target.Year(), target.Month())
        rangeStart := time.Date(target.Year(), target.Month(), 1, 0, 0, 0, 0, time.UTC)
        rangeEnd := rangeStart.AddDate(0, 1, 0)

        query := fmt.Sprintf(`
            DO $$
            BEGIN
                IF NOT EXISTS (
                    SELECT 1 FROM pg_class WHERE relname = '%s'
                ) THEN
                    EXECUTE format(
                        'CREATE TABLE %s PARTITION OF spins FOR VALUES FROM (%%L) TO (%%L)',
                        '%s', '%s'
                    );
                    RAISE NOTICE 'Created partition %s';
                END IF;
            END $$;
        `, partName, partName, rangeStart.Format(time.RFC3339), rangeEnd.Format(time.RFC3339), partName)

        if _, err := pool.Exec(ctx, query); err != nil {
            return fmt.Errorf("ensure partition %s: %w", partName, err)
        }
    }
    return nil
}

We called this with monthsAhead = 3 so there were always 3 future partitions ready. If the job failed one day, there was a 3-month buffer before any inserts would be rejected. The function is idempotent. Running it twice does nothing the second time.

Partition dropping for old analytics data followed the same pattern:

func dropOldPartitions(ctx context.Context, pool *pgxpool.Pool, table string, keepMonths int) error {
    cutoff := time.Now().UTC().AddDate(0, -keepMonths, 0)
    cutoffName := fmt.Sprintf("%s_%d_%02d", table, cutoff.Year(), cutoff.Month())

    rows, err := pool.Query(ctx, `
        SELECT inhrelid::regclass::text AS partition_name
        FROM pg_inherits
        JOIN pg_class ON pg_class.oid = inhrelid
        WHERE inhparent = $1::regclass
        ORDER BY partition_name
    `, table)
    if err != nil {
        return fmt.Errorf("list partitions: %w", err)
    }
    defer rows.Close()

    for rows.Next() {
        var name string
        if err := rows.Scan(&name); err != nil {
            return fmt.Errorf("scan partition: %w", err)
        }
        if name < cutoffName {
            if _, err := pool.Exec(ctx, fmt.Sprintf("DROP TABLE IF EXISTS %s", name)); err != nil {
                return fmt.Errorf("drop %s: %w", name, err)
            }
            log.Printf("dropped partition %s", name)
        }
    }
    return rows.Err()
}

One function creates future partitions. Another drops expired ones. Both are idempotent and safe to run on any schedule.

The numbers

After migrating to partitioned tables, here is what changed:

Metric	Before (single table)	After (monthly partitions)
Daily revenue query	14 seconds	83ms
Data deletion (90-day cleanup)	40 minutes + vacuum	Sub-second DROP
Total index size	54 GB	8 GB
Insert throughput (COPY, 4 workers)	~45,000 rows/sec	~48,000 rows/sec
Replication lag during deletes	2-5 minutes	None
Reindex time (single month)	Hours (full table)	15 seconds (one partition)

Insert throughput was roughly the same. Partitioning does not slow down writes meaningfully. The gains were entirely on the read side, the maintenance side, and the operational sanity side.

The migration

Moving 500M rows from a non-partitioned table to a partitioned one is its own project. We did it without downtime using this sequence:

Create the new partitioned table (spins_new) with all partitions.
Start writing to both spins (old) and spins_new (new) via dual-write in the application. Both inserts in the same transaction.
Backfill historical data in batches: INSERT INTO spins_new SELECT * FROM spins WHERE created_at BETWEEN $1 AND $2. We processed one month at a time, 30 batches total.
Verify row counts per month between old and new.
Swap the names: ALTER TABLE spins RENAME TO spins_legacy; ALTER TABLE spins_new RENAME TO spins.
Remove the dual-write code path.
Drop spins_legacy after a 2-week safety window.

The backfill took about 4 hours running sequentially (one month at a time to avoid spiking I/O). The rename was instantaneous. Total downtime: zero.

Lessons from running this at scale

Partition by month, not by day. Daily partitions sound appealing for fine-grained control, but at 10M rows/day you end up with 365 partitions per year. The planner has to evaluate all of them during planning. Monthly partitions (12 per year, 60 for 5 years) keep the partition list manageable.

Always create partitions ahead of time. A missing partition at midnight on the first of the month means rejected inserts. Three months of buffer is cheap insurance.

Use BRIN for time, B-tree for everything else. BRIN on created_at within each partition is the best tradeoff between index size and query speed for range scans. B-tree on player_id and game_id is still necessary for point lookups.

DROP is better than DELETE. If your retention policy aligns with partition boundaries (and it should), you never need to DELETE rows. DROP TABLE is O(1) regardless of row count.

Monitor partition sizes. If one month suddenly has 3x the rows of others, something changed in traffic patterns. We tracked pg_total_relation_size() per partition in our monitoring dashboard as an early warning signal.

PostgreSQL as an event store

The gaming industry generates extraordinary volumes of transactional data, and every row matters legally. We evaluated dedicated event stores, time-series databases, and distributed log systems. In the end, PostgreSQL with range partitioning handled 10M events per day, maintained 5 years of history, and answered regulatory queries in milliseconds.

The tooling was straightforward: Go for the ingest pipeline, pgx for the COPY protocol, goroutines for parallelism, and PostgreSQL's built-in partitioning for lifecycle management. No Kafka, no ClickHouse, no Cassandra. One relational database with a feature that has been stable since PostgreSQL 10.

For most applications that generate millions of rows per day, analytics events, IoT telemetry, transaction logs, audit trails, this same pattern applies. Partition by time, index within partitions, drop instead of delete, and automate the lifecycle.

If you want to test partitioning on a real PostgreSQL instance, start with a free database on Noctaploy. Create a partitioned table, load a few million rows with COPY, and run your queries. Extensions like pg_stat_statements are available from the dashboard to profile query performance across partitions.

Full-Text Search in PostgreSQL: When You Can Skip Elasticsearch

antoniodipinto — Thu, 19 Feb 2026 23:13:43 +0000

The search bar that almost became a new service

Someone opens a ticket: "We need a search bar on the articles page." The team huddles. One person suggests Elasticsearch. Another says Algolia. Someone mentions Meilisearch. Within ten minutes the discussion has shifted from "add a search bar" to "provision a new cluster, sync data with a pipeline, handle eventual consistency, and add a new service to the on-call rotation."

Meanwhile, the articles already live in PostgreSQL.

The first attempt is usually ILIKE:

SELECT id, title FROM articles
WHERE title ILIKE '%connection pooling%'
   OR body  ILIKE '%connection pooling%'
LIMIT 20;

It works in development with 500 rows. In production with 2 million rows it takes 3 seconds, returns results in no particular order of relevance, and cannot distinguish "connection pooling" from "the connection to the pooling facility." There is no stemming, no ranking, no concept of how well a document matches.

The fix is not a new service. It is five lines of SQL using features PostgreSQL has shipped since version 8.3.

Why LIKE fails at scale

ILIKE '%term%' forces PostgreSQL to read every row in the table. A B-tree index cannot help because the pattern has a leading wildcard. There is no shortcut -- Postgres must scan the full text of every column in every row, check for a substring match, and discard the rest.

On a table with 2 million articles, that looks like this:

Seq Scan on articles  (cost=0.00..389420.00 rows=1843 width=312)
                      (actual time=12.881..3042.617 rows=347 loops=1)
  Filter: ((title ~~* '%connection pooling%') OR (body ~~* '%connection pooling%'))
  Rows Removed by Filter: 1999653

Nearly 2 million rows scanned, 3 seconds of wall time, all to return 347 results with no ranking. Every time a user types a query and hits enter, the database does the same full scan again.

Beyond performance, ILIKE has no understanding of language. It cannot match "deploying" when the user searches for "deploy." It does not remove stop words like "the" or "is." It returns results in insertion order, not relevance order. For anything beyond a toy prototype, you need something better.

What tsvector and tsquery are

PostgreSQL has a built-in document search engine. It works with two data types: tsvector and tsquery.

A tsvector is a sorted list of normalized words (called lexemes) extracted from text. PostgreSQL tokenizes the input, applies stemming (reducing "running" to "run", "deployed" to "deploy"), and removes stop words:

SELECT to_tsvector('english', 'Deploying PostgreSQL with zero-downtime migrations');

'deploy':1 'migrat':5 'postgresql':2 'zero':4 'zero-downtim':3

Notice: "Deploying" became "deploy", "migrations" became "migrat", and the stop word "with" disappeared. Each lexeme has a position number.

A tsquery is a search expression. It supports boolean operators (& for AND, | for OR, ! for NOT):

SELECT to_tsquery('english', 'deploy & postgres');

'deploy' & 'postgr'

The @@ operator checks whether a tsvector matches a tsquery:

SELECT to_tsvector('english', 'Deploying PostgreSQL with zero-downtime migrations')
    @@ to_tsquery('english', 'deploy & postgres');

true

The user searched for "deploy" and "postgres." The document contains "Deploying" and "PostgreSQL." The stemmer reduced both sides to the same lexemes, and the match succeeds. This is the core of PostgreSQL full-text search.

Making it fast with a GIN index

Without an index, every @@ query still requires a sequential scan. PostgreSQL has to compute to_tsvector for every row and check it against the query. On 2 million rows, this is faster than ILIKE (because the matching is more efficient), but still slow.

A GIN (Generalized Inverted Index) solves this. GIN indexes map each lexeme to the list of rows that contain it. When a query arrives, PostgreSQL looks up the lexemes in the index and intersects the row lists. No table scan needed.

You can create a GIN index directly on an expression:

CREATE INDEX idx_articles_fts ON articles
  USING gin(to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, '')));

But there is a better approach for production. Add a stored generated column that precomputes the tsvector, then index that column:

ALTER TABLE articles ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (
    to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))
  ) STORED;

CREATE INDEX idx_articles_search ON articles USING gin(search_vector);

The generated column updates automatically whenever title or body changes. The GIN index stays in sync. Now a search query hits the index instead of scanning the table:

EXPLAIN ANALYZE
SELECT id, title
FROM articles
WHERE search_vector @@ to_tsquery('english', 'deploy & postgres')
LIMIT 20;

Limit  (cost=52.08..117.34 rows=20 width=268)
       (actual time=0.412..0.931 rows=20 loops=1)
  ->  Bitmap Heap Scan on articles  (cost=52.08..11342.19 rows=3467 width=268)
        (actual time=0.410..0.924 rows=20 loops=1)
        Recheck Cond: (search_vector @@ '''deploy'' & ''postgr'''::tsquery)
        ->  Bitmap Index Scan on idx_articles_search  (cost=0.00..51.21 rows=3467 width=0)
              (actual time=0.287..0.288 rows=347 loops=1)
              Index Cond: (search_vector @@ '''deploy'' & ''postgr'''::tsquery)
Planning Time: 0.134 ms
Execution Time: 0.972 ms

From 3,042ms to under 1ms. The GIN index looked up "deploy" and "postgr" in the inverted index, intersected the results, and returned matching rows without touching the rest of the table.

Ranking results

Finding matching documents is only half the problem. Users expect the most relevant result first.

ts_rank scores each document based on how often the query terms appear:

SELECT id, title, ts_rank(search_vector, q) AS rank
FROM articles, to_tsquery('english', 'deploy & postgres') q
WHERE search_vector @@ q
ORDER BY rank DESC
LIMIT 20;

For longer documents where term proximity matters more than raw frequency, ts_rank_cd (cover density ranking) gives better results. It rewards documents where the search terms appear close together:

SELECT id, title, ts_rank_cd(search_vector, q) AS rank
FROM articles, to_tsquery('english', 'deploy & postgres') q
WHERE search_vector @@ q
ORDER BY rank DESC
LIMIT 20;

You can also highlight matching terms in the output with ts_headline:

SELECT id, title,
       ts_headline('english', body, to_tsquery('english', 'deploy & postgres'),
                   'StartSel=<b>, StopSel=</b>, MaxFragments=2') AS snippet
FROM articles
WHERE search_vector @@ to_tsquery('english', 'deploy & postgres')
ORDER BY ts_rank(search_vector, to_tsquery('english', 'deploy & postgres')) DESC
LIMIT 10;

This returns text fragments with matching terms wrapped in <b> tags, ready to display in a search results page.

Phrase search and advanced queries

Basic to_tsquery splits terms into individual lexemes connected by boolean operators. For phrase search, where word order and proximity matter, PostgreSQL offers more specific functions.

phraseto_tsquery matches terms that appear next to each other in the document:

SELECT * FROM articles
WHERE search_vector @@ phraseto_tsquery('english', 'connection pooling');

This only matches documents where "connection" and "pooling" (after stemming) appear adjacent, not scattered across the text.

websearch_to_tsquery (PostgreSQL 11+) accepts the kind of input users actually type into search bars:

SELECT * FROM articles
WHERE search_vector @@ websearch_to_tsquery('english', '"connection pooling" -pgbouncer');

This matches the exact phrase "connection pooling" but excludes documents containing "pgbouncer." The syntax is close to what Google users expect: quotes for phrases, minus for exclusion.

Prefix matching works with the :* suffix, useful for search-as-you-type:

SELECT * FROM articles
WHERE search_vector @@ to_tsquery('english', 'depl:*');

This matches "deploy", "deployment", "deploying", and any other word that stems to a lexeme starting with "depl."

Fuzzy and typo-tolerant search with pg_trgm

Full-text search requires exact lexeme matches after stemming. If a user types "postgre" instead of "postgres", tsvector search returns nothing. For typo tolerance and fuzzy matching, the pg_trgm extension fills the gap.

pg_trgm breaks strings into trigrams (three-character sequences) and compares the overlap. The similarity function returns a score between 0 and 1:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

SELECT similarity('postgre', 'postgresql');
-- 0.5

A GIN index with trigram operators makes fuzzy search fast on large tables:

CREATE INDEX idx_articles_title_trgm ON articles USING gin(title gin_trgm_ops);

SELECT title, similarity(title, 'postgre') AS sim
FROM articles
WHERE title % 'postgre'
ORDER BY sim DESC
LIMIT 10;

The % operator returns rows where the similarity exceeds the threshold (default 0.3, adjustable with SET pg_trgm.similarity_threshold).

A practical pattern is to combine both approaches. Use pg_trgm for the autocomplete dropdown (matching partial, possibly misspelled input against titles) and tsvector for the full results page (matching stemmed terms across title and body with ranking).

For applications that serve users typing in multiple languages or with accented characters, the unaccent extension normalizes text so that "cafe" matches "cafe":

CREATE EXTENSION IF NOT EXISTS unaccent;

SELECT unaccent('Deploiement rapide du service de base de donnees');
-- 'Deploiement rapide du service de base de donnees'

You can create a custom text search configuration that chains unaccent with the standard dictionary, so accented and unaccented input produce the same lexemes.

When PostgreSQL search is enough

PostgreSQL full-text search is not a toy. It handles real workloads. Here are practical thresholds where it works well:

Corpus size: up to tens of millions of documents. GIN index lookups are fast, and the index stays compact relative to the data.
Query volume: a few hundred queries per second is comfortable for a well-indexed setup. For most applications with a search bar, this is far more than needed.
Language support: PostgreSQL ships with configurations for over 20 languages. If your corpus is in one or a handful of known languages, built-in support covers it.
Feature set: stemming, ranking, phrase search, prefix matching, highlighting, boolean operators, and fuzzy matching with pg_trgm. This covers the needs of most CRUD applications with a search feature.
Operational simplicity: the data already lives in PostgreSQL. There is no sync pipeline, no second cluster, no replication lag between the source of truth and the search index. One database, one connection string, one backup strategy.

When you actually need a dedicated search engine

There are workloads where PostgreSQL is not the right tool:

Faceted navigation with aggregated counts. An e-commerce catalog where users filter by brand, price range, color, and size, and every filter shows a live count of matching products. Elasticsearch was designed for this. PostgreSQL can approximate it with GROUP BY and CTEs, but it is slower and harder to maintain.
Hundreds of millions of documents with sub-10ms p99. At this scale, Elasticsearch's distributed architecture and in-memory segment caching give it an edge that a single PostgreSQL instance cannot match.
Complex scoring pipelines. Learning-to-rank, boosting by recency or popularity, vector-plus-keyword hybrid search. These are possible in PostgreSQL but require significant custom SQL, while dedicated search engines provide them as configuration.
Dynamic multi-language corpora. If documents arrive in dozens of languages and you need automatic language detection per document, a dedicated engine handles this more gracefully.

The key question is not "can PostgreSQL do search?" It can. The question is whether you need capabilities that justify operating a second system. Most applications with a search bar over their own data never reach that threshold. Starting with PostgreSQL and migrating later if needed is a lower-risk path than introducing Elasticsearch on day one.

Getting started

Here is a minimal setup you can run against any PostgreSQL 12+ database:

ALTER TABLE articles ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (
    to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))
  ) STORED;

CREATE INDEX idx_articles_search ON articles USING gin(search_vector);

SELECT id, title, ts_rank(search_vector, q) AS rank
FROM articles, websearch_to_tsquery('english', 'full text search postgres') q
WHERE search_vector @@ q
ORDER BY rank DESC
LIMIT 20;

Three statements: a generated column, a GIN index, and a ranked search query. No external service, no data pipeline, no new dependency.

If you want to test this on a real PostgreSQL instance, start with a free database on Noctaploy. Extensions like pg_trgm, unaccent, and btree_gin are pre-installed and can be enabled from the dashboard with one click. Create your GIN index, run your first ts_rank query, and see if PostgreSQL covers your search needs before reaching for anything else.

This article was originally published on Noctaploy.io.

Full-Text Search in PostgreSQL: When You Can Skip Elasticsearch

antoniodipinto — Thu, 19 Feb 2026 23:13:43 +0000

The search bar that almost became a new service

Meanwhile, the articles already live in PostgreSQL.

The first attempt is usually ILIKE:

SELECT id, title FROM articles
WHERE title ILIKE '%connection pooling%'
   OR body  ILIKE '%connection pooling%'
LIMIT 20;

The fix is not a new service. It is five lines of SQL using features PostgreSQL has shipped since version 8.3.

Why LIKE fails at scale

On a table with 2 million articles, that looks like this:

Seq Scan on articles  (cost=0.00..389420.00 rows=1843 width=312)
                      (actual time=12.881..3042.617 rows=347 loops=1)
  Filter: ((title ~~* '%connection pooling%') OR (body ~~* '%connection pooling%'))
  Rows Removed by Filter: 1999653

Nearly 2 million rows scanned, 3 seconds of wall time, all to return 347 results with no ranking. Every time a user types a query and hits enter, the database does the same full scan again.

What tsvector and tsquery are

PostgreSQL has a built-in document search engine. It works with two data types: tsvector and tsquery.

SELECT to_tsvector('english', 'Deploying PostgreSQL with zero-downtime migrations');

'deploy':1 'migrat':5 'postgresql':2 'zero':4 'zero-downtim':3

Notice: "Deploying" became "deploy", "migrations" became "migrat", and the stop word "with" disappeared. Each lexeme has a position number.

A tsquery is a search expression. It supports boolean operators (& for AND, | for OR, ! for NOT):

SELECT to_tsquery('english', 'deploy & postgres');

'deploy' & 'postgr'

The @@ operator checks whether a tsvector matches a tsquery:

SELECT to_tsvector('english', 'Deploying PostgreSQL with zero-downtime migrations')
    @@ to_tsquery('english', 'deploy & postgres');

true

Making it fast with a GIN index

You can create a GIN index directly on an expression:

CREATE INDEX idx_articles_fts ON articles
  USING gin(to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, '')));

But there is a better approach for production. Add a stored generated column that precomputes the tsvector, then index that column:

ALTER TABLE articles ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (
    to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))
  ) STORED;

CREATE INDEX idx_articles_search ON articles USING gin(search_vector);

The generated column updates automatically whenever title or body changes. The GIN index stays in sync. Now a search query hits the index instead of scanning the table:

EXPLAIN ANALYZE
SELECT id, title
FROM articles
WHERE search_vector @@ to_tsquery('english', 'deploy & postgres')
LIMIT 20;

Limit  (cost=52.08..117.34 rows=20 width=268)
       (actual time=0.412..0.931 rows=20 loops=1)
  ->  Bitmap Heap Scan on articles  (cost=52.08..11342.19 rows=3467 width=268)
        (actual time=0.410..0.924 rows=20 loops=1)
        Recheck Cond: (search_vector @@ '''deploy'' & ''postgr'''::tsquery)
        ->  Bitmap Index Scan on idx_articles_search  (cost=0.00..51.21 rows=3467 width=0)
              (actual time=0.287..0.288 rows=347 loops=1)
              Index Cond: (search_vector @@ '''deploy'' & ''postgr'''::tsquery)
Planning Time: 0.134 ms
Execution Time: 0.972 ms

From 3,042ms to under 1ms. The GIN index looked up "deploy" and "postgr" in the inverted index, intersected the results, and returned matching rows without touching the rest of the table.

Ranking results

Finding matching documents is only half the problem. Users expect the most relevant result first.

ts_rank scores each document based on how often the query terms appear:

SELECT id, title, ts_rank(search_vector, q) AS rank
FROM articles, to_tsquery('english', 'deploy & postgres') q
WHERE search_vector @@ q
ORDER BY rank DESC
LIMIT 20;

SELECT id, title, ts_rank_cd(search_vector, q) AS rank
FROM articles, to_tsquery('english', 'deploy & postgres') q
WHERE search_vector @@ q
ORDER BY rank DESC
LIMIT 20;

You can also highlight matching terms in the output with ts_headline:

SELECT id, title,
       ts_headline('english', body, to_tsquery('english', 'deploy & postgres'),
                   'StartSel=<b>, StopSel=</b>, MaxFragments=2') AS snippet
FROM articles
WHERE search_vector @@ to_tsquery('english', 'deploy & postgres')
ORDER BY ts_rank(search_vector, to_tsquery('english', 'deploy & postgres')) DESC
LIMIT 10;

This returns text fragments with matching terms wrapped in <b> tags, ready to display in a search results page.

Phrase search and advanced queries

Basic to_tsquery splits terms into individual lexemes connected by boolean operators. For phrase search, where word order and proximity matter, PostgreSQL offers more specific functions.

phraseto_tsquery matches terms that appear next to each other in the document:

SELECT * FROM articles
WHERE search_vector @@ phraseto_tsquery('english', 'connection pooling');

This only matches documents where "connection" and "pooling" (after stemming) appear adjacent, not scattered across the text.

websearch_to_tsquery (PostgreSQL 11+) accepts the kind of input users actually type into search bars:

SELECT * FROM articles
WHERE search_vector @@ websearch_to_tsquery('english', '"connection pooling" -pgbouncer');

This matches the exact phrase "connection pooling" but excludes documents containing "pgbouncer." The syntax is close to what Google users expect: quotes for phrases, minus for exclusion.

Prefix matching works with the :* suffix, useful for search-as-you-type:

SELECT * FROM articles
WHERE search_vector @@ to_tsquery('english', 'depl:*');

This matches "deploy", "deployment", "deploying", and any other word that stems to a lexeme starting with "depl."

Fuzzy and typo-tolerant search with pg_trgm

pg_trgm breaks strings into trigrams (three-character sequences) and compares the overlap. The similarity function returns a score between 0 and 1:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

SELECT similarity('postgre', 'postgresql');
-- 0.5

A GIN index with trigram operators makes fuzzy search fast on large tables:

CREATE INDEX idx_articles_title_trgm ON articles USING gin(title gin_trgm_ops);

SELECT title, similarity(title, 'postgre') AS sim
FROM articles
WHERE title % 'postgre'
ORDER BY sim DESC
LIMIT 10;

The % operator returns rows where the similarity exceeds the threshold (default 0.3, adjustable with SET pg_trgm.similarity_threshold).

For applications that serve users typing in multiple languages or with accented characters, the unaccent extension normalizes text so that "cafe" matches "cafe":

CREATE EXTENSION IF NOT EXISTS unaccent;

SELECT unaccent('Deploiement rapide du service de base de donnees');
-- 'Deploiement rapide du service de base de donnees'

You can create a custom text search configuration that chains unaccent with the standard dictionary, so accented and unaccented input produce the same lexemes.

When PostgreSQL search is enough

PostgreSQL full-text search is not a toy. It handles real workloads. Here are practical thresholds where it works well:

Corpus size: up to tens of millions of documents. GIN index lookups are fast, and the index stays compact relative to the data.
Query volume: a few hundred queries per second is comfortable for a well-indexed setup. For most applications with a search bar, this is far more than needed.
Language support: PostgreSQL ships with configurations for over 20 languages. If your corpus is in one or a handful of known languages, built-in support covers it.
Feature set: stemming, ranking, phrase search, prefix matching, highlighting, boolean operators, and fuzzy matching with pg_trgm. This covers the needs of most CRUD applications with a search feature.
Operational simplicity: the data already lives in PostgreSQL. There is no sync pipeline, no second cluster, no replication lag between the source of truth and the search index. One database, one connection string, one backup strategy.

When you actually need a dedicated search engine

There are workloads where PostgreSQL is not the right tool:

Faceted navigation with aggregated counts. An e-commerce catalog where users filter by brand, price range, color, and size, and every filter shows a live count of matching products. Elasticsearch was designed for this. PostgreSQL can approximate it with GROUP BY and CTEs, but it is slower and harder to maintain.
Hundreds of millions of documents with sub-10ms p99. At this scale, Elasticsearch's distributed architecture and in-memory segment caching give it an edge that a single PostgreSQL instance cannot match.
Complex scoring pipelines. Learning-to-rank, boosting by recency or popularity, vector-plus-keyword hybrid search. These are possible in PostgreSQL but require significant custom SQL, while dedicated search engines provide them as configuration.
Dynamic multi-language corpora. If documents arrive in dozens of languages and you need automatic language detection per document, a dedicated engine handles this more gracefully.

Getting started

Here is a minimal setup you can run against any PostgreSQL 12+ database:

ALTER TABLE articles ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (
    to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))
  ) STORED;

CREATE INDEX idx_articles_search ON articles USING gin(search_vector);

SELECT id, title, ts_rank(search_vector, q) AS rank
FROM articles, websearch_to_tsquery('english', 'full text search postgres') q
WHERE search_vector @@ q
ORDER BY rank DESC
LIMIT 20;

Three statements: a generated column, a GIN index, and a ranked search query. No external service, no data pipeline, no new dependency.

This article was originally published on Noctaploy.io.

How I Turned Feedback into Fuel: A Solopreneur’s Journey

antoniodipinto — Mon, 22 Jul 2024 20:24:09 +0000

Hey! Ever felt like networking is the final boss level in the game of solopreneurship? You’re not alone. I’m a developer, and for me, networking was like debugging a never-ending loop — confusing and frustrating. But guess what? I decided to embrace the “sharing is caring” mantra, the social/marketing/whatever-you-want-to-call-it approach. I’m working on feedrika.com, a news API service.

So, about two weeks ago, I gathered my courage, got over my fears, and began posting about my project everywhere, asking for feedback. And that’s when @ayush_saran entered the scene. He dropped some feedback, and we started chatting.

Now, imagine someone giving you advice on the product you built — initially, it felt like being schooled. But Ayush’s suggestions were pure gold.

He didn’t just stop at feedback; he built an MVP for his ideas!

One of his brilliant creations is P.R. War, an app that shows who’s winning the public relations battle between any two topics. A light bulb went off in my head — with the US elections around the corner, what if I use this to check the sentiment between Kamala Harris and Trump? Boom! Instant marketing angle! Check it out: Kamala vs. Trump.

This experience was a revelation. It really made me understand the importance of connections and feedback. Just by asking for feedback, someone ended up using feedrika.com to build something cool. So, if you’re feeling disheartened, remember this: keep connecting, keep pushing. Solopreneurship is a journey that rewards persistence.

Now I see why platforms like Product Hunt and Reddit give badges based on your activity. It’s all about consistency and staying engaged. So, don’t lose hope. Share your journey, seek feedback, and who knows? You might stumble upon the next big thing.

Oh, and a fun side note: I tried to buy the domain kamalavstrump.com but was already too late 😅.

From Angular Advocate to React Enthusiast: My Journey to the Other Side

antoniodipinto — Mon, 22 Jul 2024 08:32:39 +0000

When it comes to front-end development, I’ve always been an Angular advocate. Angular was my go-to for all my apps, especially since I developed a lot of Ionic mobile applications, which were initially based on Angular or pure JavaScript. My comfort zone was solidified by years of Angular development, but the day came when I decided to venture into the world of React.

The Beginning: My Angular Comfort Zone

My journey started with Angular, and I stuck with it through thick and thin. I appreciated its structured approach and how it seamlessly integrated with Ionic for mobile app development.** When Ionic added support for React, I was skeptical and assumed it was just a marketing gimmick**. After all, I’d been building software since 2012 and had tried one of the early versions of React around 2015/2016. My initial experience was less than stellar. Back in the day, React was a mess, props were everywhere, nested within other props, tangled and confusing. The “component” theory was still in its infancy, and JSX felt awkward and clunky. Frustrated, I switched to Angular.js (yes, is not a typo, is with .js at the end, I’m that old) and didn’t look back.

For the next eight years, I built production web apps, mobile apps, and custom dashboards with Angular. It served me well, especially since I always focused more on backend development, treating the frontend as a necessary task rather than a passion.

The React Boom

Over the years, I noticed the React community growing rapidly. People were raving about React being the future of front-end development. Despite the hype, I was content with my Angular setup. I’d given React a shot before and felt no need to revisit it.

However, curiosity eventually got the best of me. Curiosity led me to try Next.js because I needed a server-side rendering (SSR) solution, and Angular was proving to be a pain for this purpose. This is where everything changed. Next.js introduced me to a whole new world.

Typescript, React, SSR, and an endless array of libraries for anything I could think of. The number of pre-made components blew my mind. I whipped up a fully functional app in just one day, with login, authentication, and Stripe integration all set up. The productivity boost was unreal. I was able to build the frontend and the payment logic for Feedrika a News API Service in just 1 day

I found myself being more efficient with a framework that was new to me than with Angular, a framework I’d been using for years. It was a shocking revelation.

Conclusion

This experience completely changed how I see React. I went from being a skeptic to seeing its potential. The flexibility of React’s ecosystem and the incredible community support really impressed me. While I’m not ditching Angular, the productivity boost I got from React was shocking, and I plan to keep exploring this direction. My journey taught me the importance of staying open to new technologies and continuously evolving as a developer.

Building Feedrika A News API Service: The Journey Continues

antoniodipinto — Fri, 12 Jul 2024 18:46:11 +0000

In my first article on Medium, I shared the inception of Feedrika, a news API service designed to simplify access to diverse news sources for developers and businesses. The response has been overwhelmingly positive, and I’m thrilled to continue this journey with you all. Today, I want to dive deeper into the challenges and strategies involved in growing Feedrika from idea to a real startup.

Navigating the Marketing Maze

Launching a tech product is only half the battle, the real challenge lies in marketing it effectively. For Feedrika, this has meant diving headfirst into various online communities and platforms. Engaging with Reddit communities has been particularly impactful. These communities, or subreddits, are treasure troves of niche interests and discussions. By identifying and participating in the right subreddits, I’ve managed to share updates, seek feedback, and participate in discussions that drive traffic and provide invaluable insights into user needs.

Twitter has also been a powerful tool for connecting with potential users. Regularly tweeting about updates, sharing insightful content, I also created a news like profile on X

Platforms like Startupbase.io and Product Hunt are essential for gaining visibility in the startup ecosystem. Listing Feedrika on these sites has driven significant traffic and user engagement. The feedback from these communities has been crucial for refining my product and understanding market demands.

Learning to Sell

Selling a product, especially a tech service like Feedrika and especially for a developer like me, it's hard and is an art that requires constant learning and adaptation. Understanding the audience it’s a big task itself. Knowing who the potential users are and what they need is crucial. For Feedrika, this means understanding the pain points of developers and businesses looking for reliable and affordable news data.

Highlighting Feedrika’s unique features has also been critical. I offer several unique features such as real-time updates, webhooks (soon), easy integration (trust me it’s a feature). Highlighting these strengths in all communications… it’s a challenge.

Overcoming Fear

One of the most significant challenges has been overcoming the fear of doing something wrong and the fear of looking like a pushy seller. It’s easy to get caught up in self-doubt and worry about making mistakes. Embracing these fears and pushing through them has been crucial in building and marketing Feedrika.

Presenting and promoting Feedrika without feeling like a traditional salesman is a delicate balance. Authenticity is key. Sharing genuine stories (like I’m doing here), successes, and even failures helps create a connection with the audience and builds trust. It’s about showing passion for what Feedrika can do rather than just trying to sell a product.

The Road Ahead

The journey of building and marketing Feedrika is far from over. Each day brings new challenges and opportunities. My goal is to continue refining my product, expanding the user base, and ensuring that Feedrika remains a valuable tool for accessing news data.

I am incredibly grateful for the support and feedback from the community. If you haven’t already, I invite you to check out Feedrika at feedrika.com and join me on this exciting journey. Your insights and support are what make this venture possible.

Stay tuned for more updates, and feel free to reach out if you have any questions or feedback. Let’s keep building something amazing together!

Developed a News API Service - Looking for Feedback!

antoniodipinto — Mon, 08 Jul 2024 08:18:41 +0000

Hello everyone,

Hope you're all doing great!

I’ve been struggling to find an affordable news API service for a while, so I decided to build one myself. After a lot of hard work, it’s finally up and running at feedrika.com.

If you're interested in checking it out and giving me some feedback, I’d really appreciate it. Your insights would be super valuable in helping me make it better.

No pressure, though—just thought I'd share something that solved a pain point for me and might help others too.

Thanks a ton in advance!