DEV Community: Team Tiger Data

How TimescaleDB Outperforms ClickHouse and MongoDB for LogTide's Observability Platform

Team Tiger Data — Wed, 15 Apr 2026 12:24:18 +0000

Giuseppe “Polliog” Pollio started writing code for LogTide in September 2025. By early 2026, the platform was handling five million logs per day for alpha users, compressing 220GB of production data down to 25GB.

LogTide

Most enterprise log management tools are built for enterprises. Datadog and Splunk far exceed small operation budgets. For developers running a self-hosted stack, there is no clear alternative for affordable log observability.

LogTide addresses this gap as an open-source log management and SIEM platform built specifically for teams who need serious observability without serious hardware. Sigma rule-based detection, structured log search, alerting, and notifications, the same capabilities that make Datadog and Splunk useful, run in two gigabytes of RAM with Logtide.

"That's because our target is small agencies and home labs," Giuseppe explains. "I wanted to create an ecosystem with low impact on RAM, something you can host on a really old machine."

LogTide launched its cloud alpha in early 2026, with around 100 companies stress-testing the platform for free. One of them sends five million logs per day.

The Challenge

When Giuseppe set out to build LogTide, he targeted home labs and small businesses who cannot afford enterprise infrastructure, let alone enterprise pricing.

ELK - Elasticsearch, Logstash, Kibana typically require multiple nodes and significant RAM. Grafana Loki is lighter but still has indexing and query limitations that make full-text log search painful at scale. ClickHouse is fast and compresses well, but is built for analytics clusters, not Raspberry Pis. Datadog and Splunk simply cost too much.

LogTide needed a reliable database to underpin its OSS log observability that could scale to production without split architecture or excessive budget spend.

Why TimescaleDB

Giuseppe found TimescaleDB while searching for Postgres with additional support for high ingest of event data.

"There are lots of alternatives, but most are too resource-intensive," Giuseppe explains. "TimescaleDB was a perfect choice."

There are lots of alternatives, but most are too resource-intensive. TimescaleDB was a perfect choice. - Giuseppe Pollio, Founder, LogTide

The appeal was both technical and practical. TimescaleDB is Postgres. It uses the same wire protocol, the same SQL syntax, the same tooling, and the same extension ecosystem. For a solo developer building a platform that has to run on minimal hardware, that meant no operational surprises, no vendor-specific APIs, and no migration work if users already had Postgres running.

“If Postgres can run on your machine, TimescaleDB can run,” notes Giuseppe,”and you can deploy LogTide for inexpensive observability at scale.”

The LogTide Stack

LogTide's architecture is simple by design. “Simple architecture means it's easier to manage, easier to maintain,” said Giuseppe.

Simple architecture means it’s easier to manage, easier to maintain. - Giuseppe Pollio

Logs enter the system from one of three client sources: OpenTelemetry-instrumented services, Fluent Bit agents, or one of LogTide's native SDKs. All three routes converge on a single ingest endpoint. The endpoint handles format variations including OTEL format and a handful of special-case adapters so the ingestion path stays unified regardless of how the log was generated.

From the ingest endpoint, log payloads enter a job queue backed by Redis. Redis is optional: if it is not available, the ingestion path routes directly to the worker. The worker is where the platform earns its SIEM designation. It evaluates Sigma rules against incoming logs, generates alerts, dispatches notifications, and runs the full analysis pipeline.

After processing, logs pass through what Giuseppe calls the LogTide Reservoir: a storage abstraction layer that keeps the backend pluggable. In practice, only one backend is truly necessary.

"TimescaleDB is our unique persistent database," Giuseppe explains. "All the aggregation that populates our dashboards is powered by TimescaleDB."

TimescaleDB is our unique persistent database. All the aggregation that populates our dashboards is powered by TimescaleDB. - Giuseppe Pollio

Inside TimescaleDB, LogTide maintains three hypertable families: raw logs, distributed traces (spans), and detection events. Retention policies run automatically with no manual intervention or cron jobs. Continuous aggregates sit on top of the raw log hypertable and are what make the platform fast at scale.

From packages/backend/src/modules/retention/service.ts:

/**
 * Execute retention cleanup for all organizations.
 *
 * Strategy (scales with number of distinct retention values, not orgs):
 * 1. drop_chunks for max retention — instant, drops entire files
 * 2. Group orgs by retention_days, collect all project_ids per group
 * 3. For each group with retention < max: batch-delete their logs
 */
async executeRetentionForAllOrganizations(): Promise<RetentionExecutionSummary> {
  const startTime = Date.now();
  const logging = isInternalLoggingEnabled();

  // Get all organizations with their retention + projects
  const organizations = await db
    .selectFrom('organizations')
    .select(['id', 'name', 'retention_days'])
    .execute();

  const orgProjects = await db
    .selectFrom('projects')
    .select(['id', 'organization_id'])
    .execute();

  // Build org -> projectIds map
  const projectsByOrg = new Map<string, string[]>();
  for (const p of orgProjects) {
    const list = projectsByOrg.get(p.organization_id) || [];
    list.push(p.id);
    projectsByOrg.set(p.organization_id, list);
  }

  // Find max retention (used for drop_chunks)
  const maxRetention = Math.max(...organizations.map(o => o.retention_days));
  const maxCutoff = new Date(Date.now() - maxRetention * 24 * 60 * 60 * 1000);

  // Step 1: drop_chunks older than max retention (TimescaleDB only — instant, no decompression)
  // For ClickHouse, TTL policies handle this natively or deleteByTimeRange in step 3
  let chunksDropped = 0;
  if (reservoir.getEngineType() === 'timescale') {
    try {
      const dropResult = await sql`
        SELECT drop_chunks('logs', older_than => ${maxCutoff}::timestamptz)
      `.execute(db);
      chunksDropped = dropResult.rows.length;

      /* v8 ignore next 6 -- telemetry, disabled in tests */
      if (chunksDropped > 0 && logging) {
        hub.captureLog('info', `Dropped ${chunksDropped} chunks older than ${maxRetention} days`, {
          maxRetentionDays: maxRetention,
          cutoffDate: maxCutoff.toISOString(),
          chunksDropped,
        });
      }
    } catch (err) {
      // drop_chunks may fail if no chunks to drop — that's fine
      /* v8 ignore next 4 -- telemetry, disabled in tests */
      if (logging) {
        const msg = err instanceof Error ? err.message : String(err);
        hub.captureLog('debug', `drop_chunks: ${msg}`);
      }
    }
  }

  // Step 2: Group orgs by retention_days (only those with retention < max need per-row deletes)
  const retentionGroups = new Map<number, { orgs: typeof organizations; projectIds: string[] }>();
  for (const org of organizations) {
    if (org.retention_days >= maxRetention) continue; // already handled by drop_chunks

    const group = retentionGroups.get(org.retention_days) || { orgs: [], projectIds: [] };
    group.orgs.push(org);
    const orgProjectIds = projectsByOrg.get(org.id) || [];
    group.projectIds.push(...orgProjectIds);
    retentionGroups.set(org.retention_days, group);
  }

  // Step 3: Batch-delete per retention group
  const results: RetentionExecutionResult[] = [];
  let totalDeleted = 0;
  let failedCount = 0;

  for (const [retentionDays, group] of retentionGroups) {
    if (group.projectIds.length === 0) {
      for (const org of group.orgs) {
        results.push({
          organizationId: org.id,
          organizationName: org.name,
          retentionDays,
          logsDeleted: 0,
          executionTimeMs: 0,
        });
      }
      continue;
    }

    const groupStart = Date.now();
    const cutoffDate = new Date(Date.now() - retentionDays * 24 * 60 * 60 * 1000);

    try {
      const oldestResult = await reservoir.query({
        projectId: group.projectIds,
        from: new Date(0),
        to: cutoffDate,
        limit: 1,
        sortOrder: 'asc',
      });

      if (oldestResult.logs.length === 0) {
        for (const org of group.orgs) {
          results.push({
            organizationId: org.id,
            organizationName: org.name,
            retentionDays,
            logsDeleted: 0,
            executionTimeMs: Date.now() - groupStart,
          });
        }
        continue;
      }

      const deleted = await this.batchDeleteLogs(
        group.projectIds,
        cutoffDate,
        new Date(oldestResult.logs[0].time)
      );
      totalDeleted += deleted;
    } catch (error) {
      failedCount += group.orgs.length;
    }
  }
}

"The aggregates are necessary," said Giuseppe. "If you have five million, ten million logs every day, and you need to see how many logs you received every hour, you can't run that query on 10 million logs. The aggregates give you query results in milliseconds instead of 30 or 40 seconds."

Continuous aggregate definition, from packages/backend/migrations/004_performance_optimization.sql:

CREATE MATERIALIZED VIEW logs_hourly_stats
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 hour', time) AS bucket,
  project_id,
  level,
  service,
  COUNT(*) AS log_count
FROM logs
GROUP BY bucket, project_id, level, service
WITH NO DATA;

-- Refreshes automatically every hour
SELECT add_continuous_aggregate_policy('logs_hourly_stats',
  start_offset => INTERVAL '3 hours',
  end_offset => INTERVAL '1 hour',
  schedule_interval => INTERVAL '1 hour',
  if_not_exists => TRUE
);

CREATE INDEX IF NOT EXISTS idx_logs_hourly_stats_project_bucket
  ON logs_hourly_stats (project_id, bucket DESC);

Hybrid query at runtime, from packages/backend/src/modules/dashboard/service.ts

const [todayAggregateStats, recentTotal, recentErrors, recentServices, yesterdayAggregateStats, prevHourCount] = await Promise.all([
  // Today's historical stats from aggregate (today start to 1 hour ago)
  db
    .selectFrom('logs_hourly_stats')
    .select([
      sql<string>`COALESCE(SUM(log_count), 0)`.as('total'),
      sql<string>`COALESCE(SUM(log_count) FILTER (WHERE level IN ('error', 'critical')), 0)`.as('errors'),
      sql<string>`COUNT(DISTINCT service)`.as('services'),
    ])
    .where('project_id', 'in', projectIds)
    .where('bucket', '>=', todayStart)
    .where('bucket', '<', lastHourStart)
    .executeTakeFirst(),

  // Recent stats from reservoir (last hour)
  reservoir.count({ projectId: projectIds, from: lastHourStart, to: new Date() }),
  reservoir.count({ projectId: projectIds, from: lastHourStart, to: new Date(), level: ['error', 'critical'] }),
  reservoir.distinct({ field: 'service', projectId: projectIds, from: lastHourStart, to: new Date() }),

  // Yesterday's stats from aggregate
  db
    .selectFrom('logs_hourly_stats')
    .select([
      sql<string>`COALESCE(SUM(log_count), 0)`.as('total'),
      sql<string>`COALESCE(SUM(log_count) FILTER (WHERE level IN ('error', 'critical')), 0)`.as('errors'),
      sql<string>`COUNT(DISTINCT service)`.as('services'),
    ])
    .where('project_id', 'in', projectIds)
    .where('bucket', '>=', yesterdayStart)
    .where('bucket', '<', todayStart)
    .executeTakeFirst(),

  // Previous hour from reservoir (for throughput trend)
  reservoir.count({ projectId: projectIds, from: prevHourStart, to: lastHourStart }),
]);

LogTide's architecture. Logs flow from client SDKs and agents through a single ingest endpoint, into a processing worker, and into TimescaleDB hypertables via the LogTide Reservoir storage abstraction.

What We've Seen

220GB Down to 25GB

In production, LogTide's TimescaleDB deployment compressed 220GB of raw log data, 135GB of row data plus 85GB of indexes, down to 25GB. That is an 88.6% reduction, achieved using TimescaleDB's native columnar compression with a segmentby configuration on project_id and log level, ordered by timestamp descending. Chunks older than seven days compress automatically.

From packages/backend/migrations/001_initial_schema.sql:

-- Enable compression on logs hypertable
ALTER TABLE logs SET (
  timescaledb.compress,
  timescaledb.compress_segmentby = 'project_id',
  timescaledb.compress_orderby = 'time DESC'
);

-- Add compression policy for logs (compress chunks older than 7 days)
SELECT add_compression_policy('logs', INTERVAL '7 days', if_not_exists => TRUE);

-- Global retention safety net
SELECT add_retention_policy('logs', INTERVAL '90 days', if_not_exists => TRUE);

Query performance did not degrade. Time-range filtering got 33% faster after compression. Aggregations got 41% faster. Only30 full-text search slowed slightly, by about 12%, because columnar storage requires scanning additional columns to reconstruct text fields. For a log management platform where engineers are far more likely to query a time window than to search a raw string, the tradeoff strongly favors compression.

In practice, 30 million logs stored in 15GB on a single 4-vCPU, 8GB RAM node, with a P95 query latency of 50ms. Learn more in Giuseppe’s dev.to post on TimescaleDB compression.

TimescaleDB Bested MongoDB and ClickHouse in Head-to-Head Performance Benchmarks

Giuseppe built an open benchmark suite and ran it across 1K to 1M records, as outlined in his AWS Builder Center article benchmarking ClickHouse and MongoDB vs TimescaleDB. The ingestion story is straightforward: at batch sizes typical of real-world observability (100 events per call), TimescaleDB handles 14,200 inserts per second. ClickHouse handles 250 at the same batch size. The gap exists because ClickHouse buffers small writes and flushes on a 400ms timer, the right design for bulk analytics, the wrong design when a dozen microservices are logging in real time.

The query results are the main story. At 100,000 log records, TimescaleDB answers a filtered service query in 0.47ms. MongoDB answers the same query in 304ms, a 650x difference. Under 50 concurrent queries, TimescaleDB holds at 6.2ms whether the dataset is 1,000 or 1,000,000 records. The mechanism is hypertable partitioning: queries filter by time range and service, TimescaleDB routes them to the active chunk instead of scanning the full table, and continuous aggregates make count and dashboard queries nearly free because the work is already done at write time.

A 2GB RAM Requirement Keeps Operations Lean

The most important number is not the compression ratio or the write throughput. It is the 2GB RAM figure that defines where LogTide can actually run.

"If you have log management that can work with 2GB of RAM, it's really magic," Giuseppe says. "Because you can't do that with Datadog or Splunk or the other self-hosted programs and containers."

If you have log management that can work with 2GB of RAM, it's really magic. You can't do that with Datadog or Splunk or the other self-hosted programs and containers. - Giuseppe Pollio

That 2GB ceiling is what makes LogTide viable for home labs running NAS, small businesses on shared hosting, or a developer who wants to know when their Raspberry Pi's services throw errors. The entire LogTide platform, including API, worker, dashboard, and TimescaleDB storage, runs on the same hardware that already runs Postgres.

Looking Ahead

The LogTide Cloud Platform alpha prototype is now open to trial users. Meanwhile, LogTide’s open-source project is growing fast. Hundreds of GitHub stars and 1k+ clones per day signal a developer community that has found the project and is actively building with it. The next phase is expanding SDK coverage and continuing to stress-test the storage layer. TimescaleDB runs anywhere Postgres runs. The goal is to make sure LogTide does too.

pg_textsearch 1.0: How We Built a BM25 Search Engine on Postgres Pages

Team Tiger Data — Tue, 31 Mar 2026 13:09:03 +0000

Design, implementation, and benchmarks of a native BM25 index for Postgres. Now generally available to all Tiger Cloud customers and freely available via open source.

If you have used Postgres's built-in ts_rank for full-text search at any meaningful scale, you already know the limitations. Ranking quality degrades as your corpus grows. There is no inverse document frequency, so common words carry the same weight as rare ones. There is no term frequency saturation, so a document that mentions "database" 50 times outranks one that mentions it once. There is no efficient top-k path: scoring requires touching every matching row.

Most teams work around this by bolting on Elasticsearch or Typesense as a sidecar. That works, but now you are syncing data between two systems, operating two clusters, and debugging consistency issues when they diverge.

pg_textsearch takes a different approach: real BM25 scoring, built from scratch in C on top of Postgres's own storage layer. You create an index, write a query, and get results ranked by relevance:

CREATE INDEX ON articles USING bm25(content) WITH (text_config = 'english');

SELECT title, content <@> 'database ranking' AS score
FROM articles
ORDER BY content <@> 'database ranking'
LIMIT 10;

The <@> operator returns a BM25 relevance score. Scores are negated so that Postgres's default ascending ORDER BY returns the most relevant results first. The index is stored entirely in standard Postgres pages managed by the buffer cache. It participates in WAL, works with pg_dump and streaming replication, and requires no external storage or special backup procedures.

What shipped in 1.0
From preview to production. In October 2025, we released a preview that held the entire inverted index in shared memory, rebuilt from the heap on restart (preview blog). In the five months and 180+ commits since, the extension has been substantially rewritten:
• Disk-based segments replaced the memory-only architecture
• Block-Max WAND + WAND optimization for fast top-k queries
• Posting list compression with SIMD-accelerated decoding (41% smaller indexes)
• Parallel index builds (138M documents in under 18 minutes)
• 2.4x to 6.5x faster than ParadeDB/Tantivy for 2-4 term queries at 138M scale
• 8.7x higher concurrent throughput
This post covers the architecture, query optimization strategy, and benchmark results. We include a candid discussion of where ParadeDB is faster and a full accounting of current limitations.

Background: Why BM25 in Postgres?

Postgres ships tsvector/tsquery with ts_rank for full-text ranking. ts_rank uses an ad-hoc scoring function that lacks the three properties that make BM25 effective:

Inverse document frequency (IDF): downweights common terms so that rarer, more informative terms drive the ranking.
Term frequency saturation: prevents a document from scoring arbitrarily high by repeating a term many times. A document mentioning "database" 50 times is not 50 times more relevant than one mentioning it once.
Document length normalization: accounts for the fact that a term match in a short document is more informative than the same match in a long one [1].

For applications where ranking quality matters (RAG pipelines, search-driven UIs, hybrid retrieval), this is a material limitation. At scale, ts_rank also has no top-k optimization path: ranking by relevance requires scoring every matching row.

The primary existing BM25 extension for Postgres is ParadeDB/pg_search, which wraps the Tantivy search library written in Rust. Early versions stored the index in auxiliary files outside the WAL; current versions use Postgres pages.

pg_textsearch takes a different approach: rather than wrapping an external search library, the entire search engine (tokenization, compression, query optimization) is built from scratch in C on top of Postgres's storage layer.

Architecture

Fig. 1: pg_textsearch Architecture diagram

The hybrid memtable + segment design

pg_textsearch uses an LSM-tree-inspired architecture [4]. Incoming writes go to an in-memory inverted index (the memtable), which periodically spills to immutable on-disk segments. Segments compact in levels: when a level accumulates enough segments (default 8), they merge into the next level. Fewer segments means fewer posting lists to consult per query term, which directly reduces query latency. This is the same write-optimized-memtable / read-optimized-segment pattern used in RocksDB [5] and other LSM-based engines, adapted here for Postgres's page-based storage.

The write path: memtable

The memtable lives in Postgres shared memory, one per index, accessible to all backends. It contains a string-interning hash table that stores each unique term exactly once; per-term posting lists recording document IDs and term frequencies; and corpus statistics (document count and average document length) maintained incrementally so that BM25 scores can be computed without a separate pass over the index.

When the memtable exceeds a configurable threshold (default: 32M posting entries), it spills to a Level-0 disk segment at transaction commit. A secondary trigger (default: 100K unique terms per transaction) handles large single-transaction loads like bulk imports.

The memtable is rebuilt from the heap on startup. Since the heap is WAL-logged, no data is lost if Postgres crashes before a spill completes. This is analogous to how a write-ahead log protects an LSM memtable, except here the WAL is Postgres's own. The rebuild cost is proportional to the amount of data not yet spilled to segments; for indexes where most data has been spilled, startup is fast.

Fig. 2: pg_textsearch memtable write path

The read path: segments

Segments are immutable and stored in standard Postgres pages. Each segment contains:

A term dictionary: a sorted array of offsets into a string pool, binary-searchable for O(log n) term lookup.
Posting blocks of up to 128 documents each, containing delta-encoded doc IDs, packed term frequencies, and quantized document lengths (fieldnorms). A separate skip index stores one entry per posting block with upper-bound score metadata used by Block-Max WAND optimization (described below).
A fieldnorm table mapping document lengths to 1-byte quantized values using Lucene/Tantivy's SmallFloat encoding [6]. This encoding is exact for lengths 0-39 (covering most short documents); for longer documents, quantization error increases from ~5% to ~11%. In practice, the impact on ranking is smaller than these numbers suggest: BM25 scores depend on the ratio of document length to average document length, which dampens quantization error, and the b parameter (default 0.75) further reduces length's influence.
A doc ID to CTID mapping that translates internal document IDs to Postgres tuple identifiers for heap fetches.

Fig. 3: pg_textsearch segment internal structure

Minimizing page access

Storing data in Postgres pages means every access goes through the buffer manager. Even for pages already in cache, each access involves a buffer table lookup, pin acquisition, and lock handling. That overhead adds up in a scoring loop processing millions of postings. This constraint shaped several design decisions.

Each segment assigns compact 4-byte, segment-local document IDs (0 to N-1), which map to Postgres's 6-byte CTIDs (heap tuple identifiers). After collecting all documents for a segment, doc IDs are reassigned so that doc_id order matches CTID order. Sequential iteration through posting lists then produces sequential access to the CTID mapping, maximizing cache locality. CTIDs themselves are stored as two separate arrays (4-byte page numbers and 2-byte offsets) rather than interleaved 6-byte records, doubling cache line utilization.

The scoring loop works entirely with doc IDs, term frequencies, and fieldnorms. It never touches the CTID arrays. CTIDs are resolved only for the final top-k results in a single batched pass. A top-10 query that scores thousands of candidates resolves ten CTIDs, not thousands.

Postgres integration

Because the index is stored in standard buffer-managed pages, pg_textsearch participates in Postgres infrastructure without special handling: MVCC visibility, proper rollback on abort, WAL and physical replication, pg_dump / pg_upgrade, VACUUM with correct dead-entry removal, and planner hooks that detect the <@> operator and select index scans automatically. Logical replication works in the usual way: row changes are replicated and the index is rebuilt on the subscriber.

Query Optimization: Block-Max WAND

The top-k problem

Naive BM25 evaluation scores every document matching any query term. For a 3-term query on MS-MARCO v2 (138M documents), this means decoding and scoring posting lists with tens of millions of entries. Most applications need only the top 10 or 100 results. The challenge is finding them without scoring everything.

Block-Max WAND

pg_textsearch implements Block-Max WAND (BMW) [2], which uses block-level upper bounds to skip non-contributing posting blocks during top-k evaluation. Lucene adopted a similar approach in version 8.0 [7]. The core idea: maintain the score of the k-th best result seen so far as a threshold, and skip any posting block whose upper-bound score cannot exceed it.

Each 128-document posting block has a corresponding skip entry storing the maximum term frequency in the block and the minimum fieldnorm (the shortest document, which would score highest for a given term frequency). From these two values, BMW can compute a tight upper bound on the block's BM25 contribution without decompressing it. If the upper bound falls below the current threshold, the entire block (all 128 documents) is skipped.

To illustrate: consider a single-term top-10 query on a large corpus. After scanning a few thousand postings, the algorithm has accumulated 10 results with a minimum score of, say, 12.3. It now encounters a block where the upper-bound BM25 score (computed from the block's stored metadata) is 9.1. Since 9.1 < 12.3, no document in this block can enter the top 10, and the entire block is skipped without decompression. For short queries on large corpora, the vast majority of blocks are skipped this way.

Fig. 4: pg_textsearch Block-Max WAND visualization

WAND pivot selection

For multi-term queries, pg_textsearch adds the WAND algorithm [3] for cross-term skipping. Terms are ordered by their current document ID, and the algorithm identifies a pivot term: the first term whose cumulative maximum score exceeds the current threshold. All terms before the pivot advance to at least the pivot's current doc ID, skipping entire ranges of documents across multiple posting lists simultaneously, before block-level BMW bounds are even checked. For multi-term queries, BMW compares the sum of per-term block upper bounds against the threshold, extending the single-term logic described above.

The combination of WAND (cross-term skipping) and BMW (within-list block skipping) is most effective for short queries (1-4 terms), which account for the majority of real-world search traffic. In the full MS-MARCO v1 query set (1,010,916 queries from Bing), 72.6% have 2-4 lexemes after English stemming and stopword removal, with a mean of 3.7 and a mode of 3. The speedup narrows for longer queries, where more blocks contain at least one term with a potentially high-scoring document. Grand et al. [7] observe the same pattern in Lucene's BMW implementation.

Compression and Storage

Posting blocks use a compression scheme designed for fast random-access decoding. Doc IDs are delta-encoded (storing differences between consecutive IDs rather than absolute values), then packed with variable-width bitpacking: the maximum delta in the block determines the bit width, and all deltas use that width. Term frequencies are packed separately with their own bit width. Fieldnorms are the 1-byte SmallFloat values described above.

The bitpack decode path uses branchless direct-indexed uint64 loads rather than a byte-at-a-time accumulator, eliminating branch misprediction in the inner decode loop. Where available, SIMD intrinsics (SSE2 on x86-64, NEON on ARM64) accelerate the mask-and-store step. A scalar fallback handles other platforms.

Compression reduces index size by 41% compared to uncompressed storage. Decode overhead is approximately 6% of query time (measured by profiling), which is more than offset by reduced buffer cache pressure. The scheme prioritizes decode speed over compression ratio.

A note on index size comparisons: pg_textsearch does not store term positions, so it cannot support phrase queries natively (see Limitations). This makes its indexes inherently smaller than engines like Tantivy that store positions by default. The 19-26% size advantage reported in our benchmarks reflects both compression and this feature difference.

Parallel Index Build

For large tables, serial index construction can take hours. pg_textsearch uses Postgres's built-in parallel worker infrastructure to distribute the work.

The leader launches workers and assigns each a range of heap blocks. Workers scan their assigned blocks, tokenize documents via to_tsvector, build local in-memory indexes, and write intermediate segments to temporary BufFiles. The leader then performs an N-way merge of all worker output, writing a single merged segment directly to index pages.

Fig. 5: pg_textsearch Parallel Index Build

Workers run concurrently in the scan/tokenize/build phase; the leader merges sequentially. The expensive part (heap scanning, tokenization, posting list assembly) is CPU-bound and parallelizes well. The merge/write phase is comparatively cheap, so a serial merge captures most of the speedup with minimal complexity. It also produces a single fully-compacted segment that is optimal for query performance.

On MS-MARCO v2 (138M passages), 15 workers complete the build in 17 minutes 37 seconds:

SET max_parallel_maintenance_workers = 15;
SET maintenance_work_mem = '256MB';
CREATE INDEX ON passages USING bm25(content) WITH (text_config = 'english');

Benchmarks

Methodology

All benchmarks use the MS-MARCO passage ranking dataset [8], a standard information retrieval benchmark drawn from real Bing search queries. We compare pg_textsearch against ParadeDB v0.21.6 (which wraps Tantivy). Both extensions use their default configurations; Postgres tuning is specified per experiment. Both systems configure English stemming and stopword removal.

Queries are drawn uniformly from 8 token-count buckets (100 queries per bucket on v1; up to 100 per bucket on v2). Weighted-average metrics use the MS-MARCO v1 lexeme distribution as weights, reflecting real search traffic.

Cache state. All query benchmarks are warm-cache: a warmup pass runs before timing begins, and the working set fits in the OS page cache and shared_buffers for all configurations tested. Results reflect CPU and algorithmic efficiency, not I/O. We have not benchmarked memory-constrained configurations where the index exceeds available cache.

Ranking. Both systems produce BM25 rankings using the same tokenization (English stemming and stopwords). We have not performed a systematic ranking equivalence comparison; both implement standard BM25 with the same default parameters (k1 = 1.2, b = 0.75), but differences in IDF computation and tokenization edge cases may produce different orderings for some queries.

MS-MARCO query length distribution

The following histogram shows the distribution of query lengths in the full MS-MARCO v1 query set (1,010,916 queries), measured in lexemes after English stopword removal and stemming via Postgres to_tsvector('english'):

Fig. 6: MS-MARCO query length histogram

This distribution is broadly consistent with web search query length studies [9, 10]. The MS-MARCO mean of 3.7 lexemes (after stemming/stopword removal) corresponds to roughly 5–6 raw words, consistent with the corpus statistics reported by Nguyen et al. [8]. We use the v1 distribution for weighting throughout as it provides the largest sample.

Results: MS-MARCO v2 (138M passages)

Environment. Dedicated c6i.4xlarge EC2 instance: Intel Xeon Platinum 8375C, 8 cores / 16 threads, 123 GB RAM, NVMe SSD. Postgres 17.4 with shared_buffers = 31 GB. Both indexes fit in the buffer cache.

Index build:

Metric	pg_textsearch	ParadeDB
Index size	17 GB	23 GB
Build time	17 min 37 sec	8 min 55 sec
Documents	138,364,158	138,364,158
Parallel workers	15	14

pg_textsearch index is 26% smaller. ParadeDB builds approximately 2x faster.

Single-client query latency (p50 median, top-10 queries):

Lexemes	pg_textsearch (ms)	ParadeDB (ms)	Speedup
1	5.11	59.83	11.7x
2	9.14	59.65	6.5x
3	20.04	77.62	3.9x
4	41.92	98.89	2.4x
5	67.76	125.38	1.9x
6	102.82	148.78	1.4x
7	159.37	169.65	1.1x
8+	177.95	190.47	1.1x

The same pattern holds: pg_textsearch is fastest on short queries and the systems converge at longer lengths. Weighted by the MS-MARCO v1 query length distribution, the overall p50 is 40.6 ms for pg_textsearch vs. 94.4 ms for ParadeDB, a 2.3x advantage.

Concurrent throughput. We ran pgbench with 16 parallel clients for 60 seconds (after a 5-second warmup). Each client repeatedly executes a query drawn at random from a weighted pool of 1,000 queries:

Metric	pg_textsearch	ParadeDB
Transactions/sec	198.7	22.8
Average latency	81 ms	701 ms
Total transactions (60s)	11,969	1,387

pg_textsearch sustains 8.7x higher throughput under concurrent load.

Results: MS-MARCO v1 (8.8M passages)

On the smaller dataset (GitHub Actions runner, 7 GB RAM, Postgres 17), the advantages are more pronounced: 26x speedup for single-token queries, 14x for 2-token, 7.3x for 4-token. Total sequential execution time for all 800 queries: 6.5 seconds for pg_textsearch vs. 25.2 seconds for ParadeDB. Full results and methodology are available at the benchmarks page.

Discussion

Latency vs. query length

The speedup correlates strongly with query length: 11.7x for single-token queries on v2, narrowing to 1.1x at 8+ tokens. This is the expected behavior of dynamic pruning algorithms like BMW and WAND. Grand et al. [7] observe the same pattern in Lucene's BMW implementation.

The practical significance depends on the workload's query length distribution. 72.6% of MS-MARCO queries have 2-4 lexemes, the range where pg_textsearch shows its largest advantage (6.5x to 2.4x on v2). Weighted by this distribution, the overall speedup is 2.3x on v2 and 3.9x on v1.

Concurrent throughput

The concurrent throughput advantage (8.7x) substantially exceeds the single-client advantage (2.3x weighted p50). pg_textsearch queries execute as C code operating on Postgres buffer pages, with all memory management handled by Postgres's buffer cache. ParadeDB routes queries through Rust/C FFI into Tantivy, which manages its own memory and I/O outside the buffer pool. We have not profiled ParadeDB's internals, so we cannot attribute the concurrency gap to specific causes, but the architectural difference (shared buffer cache vs. separate memory management) is a plausible contributor. ParadeDB's concurrent performance may also improve in future versions.

Where ParadeDB is faster

Index build time. ParadeDB builds indexes 1.6-2x faster across both datasets. Tantivy's indexer is highly optimized Rust code with its own I/O management, not constrained by Postgres's page-based storage. Build time is a one-time cost per index (or per REINDEX); it does not affect query performance.

Long queries. At 7+ lexemes, the two systems converge. On v2, the 8+ lexeme p50 is 178 ms for pg_textsearch vs. 190 ms for ParadeDB. These long queries represent ~3.7% of the MS-MARCO distribution.

Index size caveat. pg_textsearch indexes are 19-26% smaller, but this comparison is not apples-to-apples: pg_textsearch does not store term positions, while ParadeDB stores positions to support phrase queries.

Benchmark limitations

All measurements are warm-cache on datasets that fit in memory. The 100-query sample per bucket provides directional results but limited statistical power for tail latencies. ParadeDB v0.21.6 was current at time of testing; future versions may improve. We compare against ParadeDB because it is the primary Postgres-native BM25 alternative; standalone engines like Elasticsearch operate in a different deployment model. We have not benchmarked write-heavy workloads with concurrent queries.

Limitations

We want to be clear about what pg_textsearch does not support in 1.0.

No phrase queries. The index stores term frequencies but not term positions, so it cannot natively evaluate queries like "database system" as a phrase. Phrase matching can be done with a post-filter:

SELECT * FROM (
  SELECT * FROM documents
  ORDER BY content <@> 'database system'
  LIMIT 100 -- over-fetch to compensate for post-filter
) sub
WHERE content ILIKE '%database system%'
LIMIT 10;

OR-only query semantics. All query terms are implicitly OR'd. A query for "database system" matches documents containing either term. We plan to add AND/OR/NOT operators via a dedicated boolean query syntax in a post-1.0 release.

No highlighting or snippet generation. Use Postgres's ts_headline() on the result set for highlighting.

No expression indexing. Each BM25 index covers a single text column. Workaround: create a generated column concatenating multiple fields.

Partition-local statistics. Each partition maintains its own IDF and average document length. Cross-partition queries return scores computed independently per partition.

No background compaction. Segment compaction runs synchronously during memtable spill. Write-heavy workloads may observe compaction latency. Background compaction is planned.

PL/pgSQL requires explicit index names. The implicit text <@> 'query' syntax relies on planner hooks that do not fire inside PL/pgSQL, DO blocks, or stored procedures. Use to_bm25query('query', 'index_name') explicitly. This is a practical limitation many developers will hit.

shared_preload_libraries required. pg_textsearch must be listed in shared_preload_libraries, requiring a server restart to install. On Tiger Cloud, this is handled automatically.

No fuzzy matching or typo tolerance. pg_textsearch uses Postgres's standard text search configurations for tokenization and stemming but does not provide built-in fuzzy matching. Typo-tolerant search requires a separate approach (e.g., pg_trgm).

What's Next

Planned work for post-1.0 releases:

Boolean query operators: AND, OR, NOT via a dedicated query syntax
Background compaction: decouple compaction from the write path
Expression index support: index computed expressions, not just bare columns
Dictionary compression: front-coding for terms, reducing dictionary size
Improved write concurrency: better throughput for sustained insert-heavy workloads

Try It

pg_textsearch requires Postgres 17 or 18. The fastest way to try it is on Tiger Cloud, where it is already installed and configured. No setup, no shared_preload_libraries. Create a service and run the example below.

For self-hosted installations, pre-built binaries for Linux and macOS (amd64, arm64) are available on the GitHub Releases page. Add it to shared_preload_libraries and restart:

shared_preload_libraries = 'pg_textsearch'

Source code and full documentation: github.com/timescale/pg_textsearch

Part 2 of this series covers getting started with pg_textsearch, hybrid search with pgvectorscale, and production patterns.

References

[1] Robertson et al. "Okapi at TREC-3." 1994. See also: Robertson, Zaragoza. "The Probabilistic Relevance Framework: BM25 and Beyond." Foundations and Trends in IR, 3(4):333-389, 2009.

[2] Ding, Suel. "Faster top-k document retrieval using block-max indexes." SIGIR 2011, pp. 993-1002.

[3] Broder et al. "Efficient query evaluation using a two-level retrieval process." CIKM 2003, pp. 426-434.

[4] O'Neil et al. "The log-structured merge-tree (LSM-tree)." Acta Informatica, 33(4):351-385, 1996.

[5] Facebook. "RocksDB: A Persistent Key-Value Store for Fast Storage Environments." https://rocksdb.org/

[6] SmallFloat encoding: Apache Lucene SmallFloat.java. Tantivy uses an equivalent implementation.

[7] Grand et al. "From MAXSCORE to Block-Max Wand: The Story of How Lucene Significantly Improved Query Evaluation Performance." ECIR 2020.

[8] Nguyen et al. "MS MARCO: A Human Generated MAchine Reading COmprehension Dataset." 2016.

[9] Statista. "Distribution of online search queries in the US, February 2020, by number of search terms."

[10] Dean. "We Analyzed 306M Keywords." Backlinko, 2024.

How to Break Your PostgreSQL IIoT Database and Learn Something in the Process

Team Tiger Data — Mon, 30 Mar 2026 17:42:43 +0000

As engineers, we're taught to design for reliability. We do design calculations, run simulations, build and test prototypes, and even then we recognize that these are imperfect, so we include safety factors. When it comes to the Industrial Internet of Things (IIoT) though, we rarely give the same level of scrutiny to the components that we rely on.

What if we treated our IIoT database the same way we treated the physical things we produce? We build and design a prototype database, and then put it through some serious testing, even to failure.

The Value (and Perils) of Stress Testing

Think of database stress testing as a destructive materials test for your data storage. You wouldn't trust a bridge made of untested steel, so don’t trust your database until you know its limits.

The Value:

Identify Bottlenecks: Stress testing reveals the weak links—what is likely to fail first? Will you run out of storage? Will your queries get bogged down? Or will you hit the dreaded ingest wall (when data comes in faster than it can be stored)?
Determine Real-World Behaviour: You'll find out exactly how your database performance changes as the amount of data increases. What issues are future-you going to struggle with?
Optimize Configuration: Just like you might build a few different prototypes and see how it affects failure modes, changing your database configuration, especially when it comes to indices, can dramatically affect how it behaves. Building a rigorous stress testing framework provides a safe way to optimize your design.

I hope it goes without saying, but please, please don’t run this on your production environment. Even if it’s technically a different database but the same hardware, this test can wreak havoc on your resources and crash your system. You’ve been warned.

What to Measure?

There’s no point going through all the effort to break your system if you don’t learn anything. Assuming you’re using a PostgreSQL database (It’s 2026, Just Use PostgreSQL), here is a decent set of metrics to keep track of while you’re putting your database through its paces.

Table Size

The size of a Postgresql table is generally measured by number of rows, but the actual space on disk that it occupies is a sum of the heap (the main relational table), the indices, and the TOAST (storage for large objects).

The following query will give the number or rows as well as the size of each component of the table in bytes.

SELECT
      reltuples::bigint AS row_count,
      pg_relation_size('iiot_history') AS heap_size,
      pg_indexes_size('iiot_history') AS indices_size,
      pg_table_size('iiot_history') -
            pg_relation_size('iiot_history') AS toast_size
FROM pg_class WHERE relname = 'iiot_history';

The reason for the odd row_count is that counting rows the standard way, with COUNT(*), requires scanning the whole table, which is going to be painfully slow when we’re building a table big enough to break things.

Table Performance

The best way to measure table performance is to use the actual queries that your production system will use. At a minimum, this should include your batched INSERT (you always batch, right?) and at least one common SELECT. Keep in mind that for a table with N rows, the timing for queries tend to be either constant, log(N), N or worse depending on how the indices are structured.

You can get very accurate timing info from running your queries with the prefix EXPLAIN ANALYZE, and it’s worth doing this at least once to see what the database is doing under the hood. However, I recommend running the whole test with a scripting language and then just timing the execution of that particular step.

Server Performance

Don’t forget the engine that’s driving all this machinery. You’ll need to watch the CPU, Memory, Storage, and Network Bandwidth. People in the IT world tend to talk about headroom for a server, and that’s what you’re really looking at: how much spare capacity do you have? Your CPU and Memory usage might spike at times, but the important thing is that it’s not always running at max capacity.

There are a lot of free and paid tools to monitor these variables. I almost always do this type of test in a VM (easier to clean up the mess when it all breaks) and I like to use Prometheus but honestly Perfmon in Windows or Top in Linux gives you all you really need.

Setting Limits

It’s helpful to set some limits on these parameters so you know when to stop the test. For database size, it might be some measurement like a year's worth of data, or when the drive is 80% full. For ingest timing, I suggest stopping when inserting takes longer than the desired ingest frequency—this is the ingest bottleneck and something you really want to avoid in production. Scan times can be limited by the time it takes for a specific query. Maybe calculating the average value from one tag over the past hour must be less than 10s.

How to Simulate Data?

There are lots of ways to insert data, but it’s usually a tradeoff between how well the data represents real scenarios and how long it takes to run the test.

The following is one of my favourite methods for injecting large amounts of data into an IIoT database:

Say you have a classic IIoT history table like the following:

CREATE TABLE iiot_history(
    time TIMESTAMPZ NOT NULL,
    tag_id INT NOT NULL,
    value DOUBLE PRECISION,
    PRIMARY KEY (tag_id, time)
);

If you expect to ingest 10,000 tags at 1s intervals, you can use the following INSERT query to add a day’s worth of history to the back end of your table.

INSERT INTO iiot_history(time, tag_id, value)
    SELECT *, random() as value 
FROM(
        SELECT generate_series(
            min_date-INTERVAL '1day',
            min_date-INTERVAL '1s',
            INTERVAL '1s') as time
        FROM (SELECT LEAST(NOW(),MIN(time)) AS min_date 
FROM iiot_history)
),
        generate_series(1,10000) as tag_id;

This will generate random data values for every second during a day and for every tag_id from 1 to 10,000. Not exactly as interesting as real data, but enough to fill up your table.

The nice thing about this query is that you should be able to run it in parallel to your real-time data pipeline and it won’t mess with your data (aside from potentially locking your table while it runs). It’s also easy to modify this query to inject more or less tags as well as change the time interval if you’re playing around with different configurations.

If you use this query, or whichever one you prefer, in a script (I usually use Python), then you can automate the whole test. Something along the lines of:

Get database size
Run select queries, measure execution time
Run insert queries several times, measure and average execution time
Artificially grow database size
Repeat 1-3 until one of the failure conditions is reached.

How to Interpret Results and What to Expect in the Real World?

Your test results will give you some clear data points, but you still need to do some interpreting.

Identify the Limiting Component: Where did the database fail? If it’s a query that took too long, you might be able to speed things up with a clever index. If it’s an insert that took too long, you might be able to speed things up by removing that clever index you added earlier.
Optimize: There’s a lot you can do to improve table performance before throwing the whole thing out in frustration:
1. Proper Indexing: Choosing an index is almost always a tradeoff, for example: Indexing the tag_id column before the time column will speed up most queries, at the cost of slower inserts as the table grows. Indexing the time column first will avoid the ‘ingest wall’ at the cost of slower queries. Figure out which solution is best.
2. Plan for the future: Will you need more hardware in a few months or a few years? Being able to estimate the life of your existing architecture means you won’t be caught unawares when it no longer suffices.
3. Partitioning/Chunking: For very large tables, you may need to partition appropriately (see PostgreSQL extensions like TimescaleDB). How great would it be to learn you’ll need this before you actually need this.
Add a Safety Factor: If your test showed a maximum reliable throughput of 15,000 rows/sec, set your operational limit to 10,000 rows/sec. The real world has peaks, unexpected queries, and background maintenance tasks that will steal resources. Like we do with all engineering products, design with margin.

If you treat your database like a prototype and really put it through its paces, you’ll get a preview of how it’ll behave in the future and make good, proactive design decisions instead of struggling in the future. Now, go break something (and learn).

What Developers Get Wrong About Storing Sensor Data

Team Tiger Data — Thu, 19 Mar 2026 14:08:03 +0000

Sensor Data Looks Simple Until It Isn’t

Sensor data appears straightforward. It just has timestamps, numeric readings, and maybe a device identifier. Compared to transactional application data, sensor data feels uniform and predictable. Teams often assume they can store it using familiar relational database schemas and grow from there.

That assumption falls apart instantly when scale explodes. Devices multiply, sampling rates rise, and historical data accumulates indefinitely. Queries shift from single-row lookups to time windows and aggregations. Data arrives out of order. Storage costs grow exponentially. Systems designed around transactional assumptions crack in ways that are difficult to correct once data volume locks architecture in place.

The root problem is conceptual. Sensor data looks like rows but behaves like a time-ordered stream whose value declines with age. Engineers must design the database as a time-series log with decay from the outset, rather than adapting it from a transactional model later. The following sections show how relational database approaches are inadequate for handling sensor data, and what a more suitable architecture looks like.

Default Model: Treating Sensor Data Like Rows

Most database developers approach sensor data with a transactional mindset. They design normalized schemas, enforce relational integrity, and add indexes for point queries. They only work for mutable business entities such as users or orders.

Sensor data, however, is append-only. New measurements arrive continuously and are rarely updated. Sustained ingestion and time-range retrieval are dominant, not row mutation or lookup. When schemas assume row-oriented access, data ingestion becomes join-heavy, indexing costs grow with volume, and write throughput falls behind input data flow.

Treating sensor data as rows creates problems precisely where sensor systems spend most of their effort: writing and scanning time-ordered streams.

Where That Model Breaks

As the system grows, several problems appear simultaneously.

First , ingestion is continuous and bursty. Devices reconnect and flush buffers, producing spikes rather than steady flows. Row-oriented schemas struggle to absorb these bursts efficiently.

Second , growth compounds across multiple axes: more devices, higher sampling frequency, additional metrics, and longer retention. Storage volume grows quickly, turning early schema choices into long-term constraints because migrating historical time-series data is costly and risky.

Third , queries shift toward time windows. Monitoring, analytics, and diagnostics rely on ranges, aggregates, and rates over time rather than individual rows. Row-optimized indexing performs poorly for these scans.

Fourth , operational realities inevitably create problems. Timestamps arrive late or out of sequence. Data must be replayed or corrected. Systems designed for ordered inserts encounter fragmentation and duplication under these conditions.

Each constraint highlights the same reality. Sensor workloads are shaped by time and continuity, not by relational identity.

Key Insight: Sensor Data Is a Log With Decay

Sensor data has two defining properties.

It is a log: append-only, time-indexed, and rarely modified after arrival.
It decays: its value decreases as it ages, even as its volume accumulates.

Recent data require high-resolution monitoring and debugging. Older data supports trends and aggregates. Very old data is rarely queried except in a summarized form. Yet without lifecycle awareness, systems retain all data at equal resolution and cost.

Once teams understand that sensor data is a log with decay , the correct architecture becomes clear. Storage must optimize for append throughput and time-range access while permitting data to evolve in resolution and tier as it ages.

Time-Series Architecture

Time-series data that loses value over time requires the database architecture to have a few key properties.

Log-optimized ingestion

Writes must be sequential and batched, minimizing per-row overhead. Storage engines and schemas should favor append operations over update operations so ingestion scales with device fleets and burst conditions.

Time-partitioned organization

Data should be grouped primarily by time, corresponding its physical storage with dominant query patterns. Time partitioning keeps recent data localized and keeps historical segments compact and independent.

Lifecycle tiering

Because sensor data’s value declines with age, resolution, and storage cost should decline as well. High-resolution recent data is hot, and older data is compressed, downsampled, or moved to cheaper storage tiers while preserving analytical performance.

Role separation

Operational monitoring, historical analytics, and archival retention create different latency and throughput challenges. Separating these roles prevents continuous ingestion from degrading analytical performance and allows each layer to evolve independently.

These properties are not optimizations layered onto transactional storage. Instead, they are intentional design choices needed to handle the key aspects of time-series data: continuous append, time-range access, and aging value.

What This Enables for Developers

Architectures aligned with time-series data change how systems scale and operate.

Ingestion stays stable as fleets expand because write operations match append patterns rather than row mutation. Query cost stays predictable because time-range scans match with storage layout. Storage growth stays bounded relative to insight because data resolution declines with age. Operational corrections and replays become routine rather than disruptive because logs tolerate disorder.

Developers spend less effort compensating for schema problems and more effort deriving insight from data. Systems stay adaptable as deployments grow from prototypes to global fleets.

Why Time-Series Architecture Becomes Inevitable

Engineers only design transactional database models for mutable records whose value stays relatively stable over time. Sensor data is the opposite. It is filled with immutable events whose volume grows continuously while their value declines with age. As ingestion becomes constant, queries become time-range-driven, and history accumulates indefinitely, databases built on transactional assumptions develop write bottlenecks, inefficient scans, and rising storage costs.

Once teams understand that sensor data is just an append-only data stream with aging value, the architectural solution becomes clear. Systems must ingest sequentially, organize primarily by time, reduce resolution as data ages, and separate operational and historical workloads. These structures stem directly from how sensor data behaves, not a preference for any particular technology.

Treating sensor data as rows delays problems but does not fix them. As scale grows, transactional models diverge further from workload reality, while time-series architectures stay matched to it. Database design, therefore, can’t be retrofitted late without cost and disruption. It must start from the correct model: sensor data as a time-series log with decay.

Your Rails App Isn’t Slow—Your Database Is

Team Tiger Data — Tue, 06 May 2025 12:23:00 +0000

In case you missed the quiet launch of our timescaledb-ruby gem, we’re here to remind you that you can now connect PostgreSQL and Ruby when using TimescaleDB. 🎉 This integration delivers a deeply integrated experience that will feel natural to Ruby and Rails developers.

How to Scale Your Rails App Analytics with TimescaleDB

If you’ve worked with Rails for any length of time, you’ve probably hit the wall when dealing with time-series data. I know I did.

Your app starts off smooth—collecting metrics, logging events, tracking usage. But one day, your dashboards start lagging. Page load times creep past 10 seconds. Pagination stops helping. Background jobs queue up as yesterday’s data takes too long to process.

This isn’t a Rails problem. Or even a PostgreSQL problem. It’s a “using the wrong tool for the job” problem.

In this post, I’ll show you how we solve these challenges at Timescale—and how you can too. I’ll walk through the real implementation patterns we use in production Rails apps, using practical code examples instead of abstract concepts.

The Growing Time-Series Data Challenge

A few years ago, I was building analytics for a high-traffic Rails app. Despite adding indexes and optimizing queries, performance kept degrading as our data grew.

Like most apps, we started with simple timestamp columns and standard ActiveRecord queries:

class Event < ApplicationRecord
  scope :recent, -> { where('created_at > ?', 1.week.ago) }
  scope :by_day, -> { group("DATE_TRUNC('day', created_at)").count }
end

This works fine at first. But as your table grows to millions (or billions) of rows, things slow to a crawl:

5ms when you have 10K rows
2000ms when you have 10M rows Event.where(user_id: 123).by_day

And the problems compound when you need to:

Track high-volume events (like API calls or page views)
Keep historical data accessible for trends
Run complex aggregations across time
Maintain dashboard performance as data scales

Over the years, I tried all the usual tricks:

Additional indexes: Helped at first, then hurt insert performance
Manual partitioning: Fragile and hard to manage
Pre-aggregation jobs: Complex and often stale
Custom caching: Difficult to maintain, always a step behind

It felt like fighting my database instead of working with it.

Why PostgreSQL Falls Short for Time-Series

PostgreSQL is a fantastic general-purpose database. But time-series data introduces new demands that standard Postgres tables aren’t designed for. Let’s break that down:

Insertion pattern: Data constantly arrives in time order, but old data rarely changes
Query pattern: Most queries use time bounds (WHERE created_at BETWEEN x AND y)
Aggregation pattern: You’re grouping by time (hourly, daily, monthly)
Storage pattern: The dataset grows linearly—forever
Access pattern: Recent (hot) data is queried far more than older (cold) data

These characteristics expose several pain points:.

No built-in partitioning for time
Index bloat as tables grow
Inefficient time-based queries
Manual rollups and background jobs
Difficulty managing large historical datasets

And that’s exactly where TimescaleDB comes in.

TimescaleDB: PostgreSQL, But Built for Time-Series

TimescaleDB is a PostgreSQL extension built to handle time-series and real-time workloads—without giving up the safety and simplicity of Postgres.

Now with the timescaledb Ruby gem, it integrates cleanly into Rails. You don’t have to leave behind ActiveRecord, or rewrite your models, or learn a whole new stack.

Here’s what TimescaleDB brings to your Rails app:

Hypertables: Automatic time-based partitioning, transparent to your queries
Optimized time indexes: Stay fast even as your data grows
Built-in compression: Reduce storage by 90–95%
Continuous aggregates: Pre-computed rollups that stay fresh automatically

And most importantly? You keep your Rails patterns.

These work just like before:

Event.where(user_id: 123).where(created_at: 1.month.ago..Time.now)
Event.group_by_day(:created_at).count  # using the groupdate gem

Real Performance Gains Without Rewriting Everything

With Timescale, our analytics workflows went from laggy to fast—without adding new caching layers or complex ETL.

Across production workloads, teams have seen:

Sub-second queries on tens of millions of rows
95%+ compression on time-series datasets
Fewer background jobs, thanks to continuous aggregates
Simplified code—no more rollup scripts or cache warmers

It feels like your app leveled up, without any extra complexity.

Continuous Aggregates in One Line of Ruby

One of TimescaleDB’s most powerful features is continuous aggregates—think materialized views that update automatically in the background.
And with the timescaledb gem, defining them looks like this:

class Download < ApplicationRecord
  extend Timescaledb::ActsAsHypertable
  include Timescaledb::ContinuousAggregatesHelper

  acts_as_hypertable time_column: 'ts'

  scope :total_downloads, -> { select("count(*) as total") }
  scope :downloads_by_gem, -> { select("gem_name, count(*) as total").group(:gem_name) }

  continuous_aggregates(
    timeframes: [:minute, :hour, :day, :month],
    scopes: [:total_downloads, :downloads_by_gem]
  )
end

This single model creates a cascade of continuously updated rollups—from minute to month—all while sticking to the ActiveRecord patterns you know and love.

Why It Matters

If you're building a Rails app that tracks metrics, logs, events, or any kind of time-based data, TimescaleDB gives you a clear path to scale without duct tape and complexity.

Reduce load on your app servers—let the DB do the aggregating
Eliminate complex background jobs—less moving parts to break
Get predictable performance—even with billions of rows
Stick with Rails conventions—write less custom SQL
Continuous aggregates alone can replace dozens of lines of rollup - code and hours of maintenance work.

Try It Yourself

Rails developers deserve a time-series database that just works. TimescaleDB gives you the performance and scale your app needs without giving up the elegance of ActiveRecord.

If you’re curious, here’s how to get started:

Install TimescaleDB (it’s just a Postgres extension)
Add the timescaledb gem to your Gemfile
Identify models with time-based data
Start with hypertables, then add continuous aggregates as needed

You can self-host, or try Timescale Cloud for a fully managed option.

FAQ: TimescaleDB for Ruby on Rails Developers

Q: Do I need to change how I use ActiveRecord?

A: Nope! TimescaleDB works with your existing ActiveRecord models. Just add the timescaledb gem and use the acts_as_hypertable macro to enable time-series functionality.

Q: How is TimescaleDB different from just using PostgreSQL?

A: TimescaleDB is a PostgreSQL extension. It gives you automatic time-based partitioning (hypertables), faster time-based queries, built-in compression, and continuous aggregates—all while staying 100% SQL- and Rails-compatible.

Q: Can I keep using the gems I already use for date grouping, like groupdate?

A: Yes. TimescaleDB works seamlessly with gems like groupdate. You can continue using .group_by_day, .group_by_hour, etc., and get better performance under the hood.

Q: What kind of performance improvements can I expect?

A: Teams have seen sub-second query times on tens of millions of rows and 95%+ storage savings using TimescaleDB’s compression. The biggest wins are in read-heavy, time-bounded queries (e.g., user activity, logs, metrics).

Q: What’s the learning curve for continuous aggregates?

A: It’s minimal. The timescaledb gem lets you define continuous aggregates using a simple DSL that reuses your existing scopes. You don’t need to learn new SQL or create custom rollup jobs.

Q: Can I use this in production? Is it stable?

A: Yes. TimescaleDB powers production workloads at companies like NetApp, Linktree, and RubyGems.org. It’s backed by years of performance and reliability improvements.

Q: Do I need to self-host? Or is there a managed option?

A: Both! You can self-host TimescaleDB or use Timescale Cloud, a fully managed PostgreSQL service with built-in TimescaleDB, HA, backups, and usage-based pricing.

Q: Where can I learn more?

Ruby Quickstart in Timescale Docs
GitHub: timescaledb-ruby
Fully Managed Timescale Cloud (free for 30 days)
Install the open-source TimescaleDB extension

We Listened: Pgai Vectorizer Now Works With Any Postgres Database

Team Tiger Data — Mon, 05 May 2025 15:01:35 +0000

TL;DR:
We're excited to announce that pgai Vectorizer—the tool for robust embedding creation and management—is now available as a Python CLI and library, making it compatible with any Postgres database, whether it be self-hosted Postgres or cloud-hosted on Timescale Cloud, Amazon RDS for PostgreSQL, or Supabase.

This expansion comes directly from developer feedback requesting broader accessibility while maintaining the Postgres integration that makes pgai Vectorizer the ideal solution for production-grade embedding creation, management, and experimentation. To get started, head over to the pgai GitHub.

Why We Built Pgai Vectorizer for Postgres

When we first launched pgai Vectorizer, we aimed to simplify vector embedding management for developers building AI systems with Postgres. We heard the horror stories of developers struggling with complex ETL (extract-transform-load) pipelines, embedding synchronization issues, and the constant battle to keep embeddings up-to-date when source data changes. Teams were spending more time maintaining infrastructure than building useful AI features.

Many developers found themselves cobbling together custom solutions involving message queues, Lambda functions, and background workers just to handle the embedding creation workflow. Others faced the frustration of stale embeddings that no longer matched their updated content, leading to degraded search quality and hallucinations in their RAG applications.

Pgai Vectorizer solved these problems with a declarative approach that automated the entire embedding lifecycle with a single SQL command, similar to how you'd create an index in Postgres. The tool resonated with developers and quickly gained traction among AI builders. However, we soon started hearing a consistent piece of feedback that would shape our next steps.

The Change: Moving From Extension-Only to Python CLI and Library

After our initial launch, we received consistent feedback from developers who wanted to use pgai Vectorizer with their existing managed Postgres databases. While our extension-based approach worked great for self-hosted Postgres and Timescale Cloud, users on platforms like Amazon RDS for PostgreSQL, Supabase, and other managed database services couldn't use pgai Vectorizer unless their cloud provider chose to make it available.

Requests for pgai Vectorizer support on Supabase, Azure PostgreSQL, and Amazon RDS.

We knew we needed to make pgai Vectorizer more accessible without compromising its seamless Postgres integration. The solution? Repackaging our core functionality as a Python CLI (command-line interface) and library that can work with any Postgres database while maintaining the same robustness and "set it and forget it" simplicity.

This approach gives developers the best of both worlds: the powerful vectorization capabilities of pgai Vectorizer with the flexibility to use their existing database infrastructure, regardless of provider. The Python library handles the creation of database objects that house the pgai Vectorizer internals, and provides a SQL API that handles loading data, creating embeddings, and synchronizing changes, all while writing the results back to your Postgres database.

The library maintains all the core functionality that made pgai Vectorizer valuable:

Embedding creation and management: Automatically create and synchronize vector embeddings from Postgres data and S3 documents. Embeddings update automatically as data changes.
Production-ready out-of-the-box : Supports batch processing for efficient embedding generation, with built-in handling for model failures, rate limits, and latency spikes.
Experimentation and testing: Easily switch between embedding models, test different models, and compare performance without changing application code or manually reprocessing data.
Plays well with pgvector and pgvectorscale: Once your embeddings are created, use them to power vector and semantic search with pgvector and pgvectorscale. Embeddings are stored in the pgvector data format.

*What this means for existing users: *

Timescale Cloud customers: Existing vectorizers running on Timescale Cloud will continue to work as is, so no immediate action is necessary. We encourage you to use the new pgai Python library to create and manage new vectorizers. To do so, you have to upgrade to the latest version of both the pgai extension in Timescale Cloud and the pgai Python library. Upgrading the extension decouples the vectorizer-related database objects from the extension, therefore allowing them to be managed by the Python library. Pgai Vectorizer remains in Early Access on Timescale Cloud. See this guide for details and instructions on upgrading and migrating.
Self-hosted users: Existing self-hosting vectorizers will also continue to work as is, so no immediate action is required. If you already have the pgai extension installed, you’ll need to upgrade to version 0.10.1. Upgrading the extension decouples the vectorizer-related database objects from the extension, therefore allowing them to be created and managed by the Python library. See this guide for self-hosted upgrade and migration details and instructions.

What this means for new users: Whether you use pgai Vectorizer on Timescale Cloud or self-hosted, this change means a simplified installation process and more flexibility—you now have tighter integrations between pgai Vectorizer and your search and RAG backends in your AI applications. Self-hosted users no longer need to install the pgai extension to use pgai Vectorizer. Timescale Cloud customers will continue to get the pgai extension auto-installed for them. To try pgai Vectorizer for yourself, here’s how you can get started.

Pgai Vectorizer Works With Any Postgres Database

The new Python library implementation of pgai Vectorizer works with virtually any Postgres database, including:

Timescale Cloud
Self-hosted Postgres
Amazon RDS for PostgreSQL
Supabase
Google Cloud SQL for PostgreSQL
Azure Database for PostgreSQL
Neon PostgreSQL
Render PostgreSQL
DigitalOcean Managed Databases
Any other self-hosted or managed Postgres service running PostgreSQL 15 and later.

The new implementation addresses one of our most requested features from the community. Users were actively building AI applications with these managed services, but couldn't take advantage of pgai Vectorizer's powerful embedding management capabilities.

How to Use Pgai Vectorizer: A Quick Refresher

A standout feature of the new Python library is its enhanced support for document processing directly from cloud storage.

With the expanded Amazon S3 integration, you can now seamlessly load documents and generate embeddings based on file URLs stored in your Postgres table. Pgai Vectorizer automatically loads and parses each into an LLM-friendly format like Markdown, then generates the required chunks for embedding creation, all according to your specification.

For document vectorization, we've included support for parsing multiple formats, including PDF, DOCX, XLSX, HTML, images, and more using IBM Docling, which provides advanced document understanding capabilities. This makes it easy to build powerful document search and retrieval systems without leaving the Postgres ecosystem.

Getting started with the pgai Vectorizer Python library is straightforward. Install pgai on your database via:

pip install pgai
pgai install -d postgresql://postgres:postgres@localhost:5432/postgres

Afterward, your database is enhanced with pgai’s capabilities. Here's a simple example of how to create a vectorizer for processing text data from a database column named ‘text’:

SELECT ai.create_vectorizer(
    'wiki'::regclass,
    if_not_exists => true,
    loading => ai.loading_column(column_name=>'text'),
    embedding => ai.embedding_openai(model=>'text-embedding-ada-002', dimensions=>'1536'),
    destination => ai.destination_table(view_name=>'wiki_embedding')
)

For document processing, you can use this configuration, which shows a document metadata table in PostgreSQL with references to data in Amazon S3:

-- Document source table
CREATE TABLE document (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    uri TEXT NOT NULL,
    content_type TEXT,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    owner_id INTEGER,
    access_level TEXT,
    tags TEXT[]
);

-- Example with rich metadata
INSERT INTO document (title, uri, content_type, owner_id, access_level, tags) VALUES
    ('Product Manual', 's3://my-bucket/documents/product-manual.pdf', 'application/pdf', 12, 'internal', ARRAY['product', 'reference']),
    ('API Reference', 's3://my-bucket/documents/api-reference.md', 'text/markdown', 8, 'public', ARRAY['api', 'developer']);

SELECT ai.create_vectorizer(
    'document'::regclass,
    loading => ai.loading_uri(column_name => 'uri'),
    chunking => ai.chunking_recursive_character_text_splitter(
        chunk_size => 700,
        separators => array[E'\n## ', E'\n### ', E'\n#### ', E'\n- ', E'\n1. ', E'\n\n', E'\n', '.', '?', '!', ' ', '', '|']
    ),
    embedding => ai.embedding_openai('text-embedding-3-small', 768),
    destination => ai.destination_table('document_embeddings')
);

Run the worker via:

pgai vectorizer worker -d 
postgresql://postgres:postgres@localhost:5432/postgres

And watch the magic happen as pgai creates vector embeddings for your source data.

Get Started With Pgai Vectorizer Today

We're excited to see what you'll build with the new pgai Vectorizer, whether you're creating semantic search, RAG, or next-gen agentic applications.

Check out the GitHub repository to explore capabilities and getting started guides.

As you can tell by this post, we really value community feedback. If you encounter any issues or have suggestions for improvements, please open an issue on GitHub or join our community Discord. Your input will help shape the future development of pgai Vectorizer as we continue to enhance its capabilities.

PostgreSQL vs. Qdrant for Vector Search: 50M Embedding Benchmark

Team Tiger Data — Fri, 02 May 2025 14:35:37 +0000

Vector search is becoming a core workload for AI-driven applications. But do you really need to introduce a new system just to handle it?

We ran a performancebenchmark to find out: comparing PostgreSQL (using pgvector + pgvectorscale) with Qdrant on 50 million embeddings.

The results at 99% recall:

Sub-100ms query latencies
471 queries per second (QPS) on Postgres—11x higher throughput than Qdrant (41 QPS)

Head to the full write-up for a deep dive into our vector database comparison.

For vectors, Postgres is all you need.

At 99% recall, Postgres delivers sub-100ms query latencies and handles 11x more query throughput than Qdrant (471 QPS vs. Qdrant’s 41 QPS).

The results show that thanks to pgvectorscale, Postgres can keep up with specialized vector databases and deliver as good, if not better performance at scale. Learn more about why Postgres wins for AI and vector workloads.

Turning PostgreSQL Into a High-Performance Vector Search Engine

How? We built pgvectorscale to push Postgres to its limits for vector workloads—without compromising recall, latency, or cost-efficiency. It turns your favorite relational database into a high-performance vector search engine.

✅ No extra systems.
✅ No new query languages.
✅ Just Postgres.

We used RTABench to run a transparent, reproducible evaluation—designed for real-world, high-scale workloads.

Curious about the architecture behind it all?

👉 Read our whitepaper on building Timescale for real-time and AI workloads

It dives into how we engineered Timescale to handle time-series, vector, and relational data—all in one Postgres-native platform.

TL;DR: For many vector workloads, Postgres is all you need.

Have you used Postgres or Qdrant for vector search?
What’s your stack look like today—and where do you feel the friction?

👉 Postgres vs Qdrant: which side are you on? Comment down below!

Connecting S3 and Postgres: Automatic Synchronization Without ETL Pipelines

Team Tiger Data — Thu, 01 May 2025 12:32:36 +0000

Modern applications need data that's both accessible and fast. You have data in S3, but transforming it into usable insights requires complex ETL (extract-transform-load) pipelines. With our new livesync for S3 and pgai Vectorizer features, Timescale transforms how you interact with S3 data.

Two Powerful Postgres–S3 Integration Approaches

Our new features offer distinct approaches to working with S3 data:

Livesync for S3 brings your structured S3 data directly into Postgres tables, automatically synchronizing files as they change.
pgai Vectorizer leaves documents in S3 but generates searchable embeddings and metadata in Postgres, connecting unstructured content with structured data for RAG, search, and agentic applications.

Both eliminate complex ETL pipelines, letting you work with S3 data using familiar SQL.

Transform S3 to Analytics in Seconds: Automatic Data Synchronization With Livesync

S3 is where countless organizations store their data, but Timescale Cloud is where they unlock insights. Livesync for S3 bridges this gap, eliminating the traditional complexity of moving data between these systems.

The problem: Complex ETL pipelines for S3 data

Data management challenges create significant obstacles when bridging S3 storage and analytics environments. Organizations struggle with the manual effort required to transport data between S3 buckets and analytical databases, requiring custom integration code that demands ongoing maintenance. This challenge is compounded by the brittle and resource-intensive nature of maintaining ETL processes.

Many organizations find themselves caught in a constant battle to ensure data freshness, requiring vigilant monitoring systems to confirm that analytics platforms accurately reflect the most current information in S3 repositories. The culmination of these challenges frequently manifests as performance bottlenecks, where inefficient data transfer mechanisms cause critical delays in delivering up-to-date information to customer-facing applications, leading to poor user experiences and customers making decisions based on stale data.

The solution: Automatic data synchronization

Livesync for S3 bridges this gap, eliminating the traditional complexity of moving data between these systems. We've engineered livesync for S3 to bring stream-like behavior to object storage, effectively turning your S3 bucket into a continuous data feed.

Our solution delivers speed and simplicity:

Zero-ETL experience : Eliminate complex pipelines or custom integration code.
Real-time data pipeline : Turn your S3 bucket into a continuous data feed with automatic synchronization.
Familiar tools : Use S3 for storage and Timescale Cloud for analytics without compromise.
Minimal configuration : Connect to your S3 bucket, define mapping, and let livesync handle the rest.

How livesync works

Behind the scenes, we're doing the heavy lifting:

Schema mapping that infers your data from CSV or Parquet files to hypertables
Managing the initial data load
Maintaining continuous synchronization
Intelligent tracking of processed files to prevent duplicates or missed data

This enables teams across multiple industries to build robust pipelines. For organizations with production applications on Postgres looking to scale their real-time analytics, livesync for S3 has a sister solution—livesync for Postgres—which lets you keep your Postgres as-is while streaming data in real time to a Timescale Cloud instance optimized for analytical workloads.

The inner workings of livesync for S3

Secure cross-account authentication

Livesync employs a robust security model using AWS role assumption. Our service assumes a specific role in your AWS account with precisely the permissions needed to access your S3 data. To prevent confused deputy attacks, we implement the industry-standard External ID verification using your unique Project ID/Service ID combination.

Smart polling and file discovery

Behind the scenes, livesync intelligently scans your S3 bucket using optimized ListObjectsV2 calls. Starting with the prefix from your pattern (like "logs/" from "logs/**/*.csv"), it applies glob matching to find relevant files. The system tracks processed files in lexicographical order, ensuring no file is missed or duplicated.

To maintain performance, livesync for S3 manages an orderly queue limited to 100 files per connection. When files are plentiful, polling accelerates to every minute; when caught up, it follows your configured schedule. You can always trigger immediate processing with the "Pull now" button.

Optimized data processing pipeline

Livesync handles different file formats with specialized techniques:

CSV files are analyzed for compression (UTF-8, ZIP, GZIP), then processed using high-performance parallel ingestion.
Parquet files undergo efficient conversion before being streamed into TimescaleDB (which lives at the core of your Timescale Cloud service).

The entire pipeline includes intelligent error handling, which is clearly visible in the dashboard. After three consecutive failures, livesync automatically pauses to prevent resource waste, awaiting your review.

This architecture delivers the perfect balance of reliability, performance, and operational simplicity, bringing your S3 data into Timescale Cloud with minimal configuration and maximum confidence.

Build powerful ingest pipelines with minimal configuration:

IoT telemetry flows: Connect devices that log to S3 (like AWS IoT Core) directly to time-series analytics.
Streaming data persistence: Automatically process data from Kinesis, Kafka, or other streaming platforms that land files in S3 and transform into TimescaleDB hypertables for high-performance querying.
Crypto/financial data analytics: Sync trading data from S3 into TimescaleDB for real-time analytics on recent market movements and long-term historical analysis for backtesting and trend identification.

Currently supporting CSV and Parquet file formats, livesync delivers a frictionless way to unlock the value of your data stored in S3.

Simple setup, powerful results

Livesync for S3 continuously monitors your S3 bucket for incoming sensor data, automatically maps schemas, and syncs data into TimescaleDB hypertables in minutes. This enables operators to query millions of readings with millisecond latency, driving real-time dashboards that catch anomalies before equipment fails. Livesync for S3 ensures that syncing from S3 to hypertables remains smooth, dependable, and lightning-fast.

Setting up livesync for S3 is surprisingly straightforward:

Connect to your S3 bucket with your credentials.
Define how your objects map to TimescaleDB tables.
Let livesync for S3 handle the rest—monitoring and ingesting new data automatically.

Behind the scenes, we're doing the heavy lifting of schema mapping, managing the initial data load, and maintaining continuous synchronization. The system intelligently tracks what it's processed, so you never have duplicate data or missed files.

For example, in manufacturing environments where sensors continuously capture critical equipment data through AWS IoT Core and store it in S3, livesync ensures this data becomes immediately queryable in TimescaleDB. This enables operators to identify anomalies before equipment fails, turning static S3 storage into actionable intelligence.

Zero maintenance, maximum performance

Once configured, livesync for S3 delivers ease and performance:

Zero-maintenance operation once configured
Schema mapping that infers your data from CSV or Parquet files to hypertables
Automatic retry mechanisms for transient failures
Fine-grained control over which objects sync and when
Complete observability with detailed history of file imports and error messages (if any)

Simplify Document Embeddings With Pgai Vectorizer

Searching unstructured documents embeddings with pgvector

While livesync brings S3 data into Postgres, pgai Vectorizer takes a different approach for unstructured documents. It creates searchable vector embeddings in Postgres from documents stored in S3 while keeping the original files in place.

The problem: Complex pipelines for document search

AI applications using RAG (retrieval-augmented generation) can help businesses unlock insights from mountains of unstructured data. Today, that unstructured data’s natural home is Amazon S3. On the other hand, Postgres has become the default vector database for developers, thanks to extensions like pgvector and pgvectorscale. These extensions enable them to build intelligent applications with vector search capabilities without needing to use a separate database just for vectors.

We’ve previously written about how vector databases are the wrong abstraction because they divorce the source data from the vector embedding and lose the connection between unstructured data that's being embedded and the embeddings themselves. This problem is especially apparent for documents housed in object storage like Amazon S3.

Before pgai Vectorizer, developers typically needed to manage:

Complex ETL pipelines to chunk, format, and create embeddings from source data
Multiple systems: a vector database for embeddings, an application database for metadata, and possibly a separate lexical search index
Data synchronization services to maintain a single source of truth
Queuing systems for updates and synchronization
Monitoring tools to catch data drift and handle rate limits from embedding services
Alert systems for stale search results
Validation checks across all these systems

Processing documents in AI pipelines introduces several challenges, such as managing diverse file formats (PDFs, DOCX, XLSX, HTML, and more), handling complex metadata, keeping embeddings up to date with document changes, and ensuring efficient storage and retrieval.

The solution: Automatic document vectorization

To solve these challenges, Timescale has added support for document vectorization to pgai Vectorizer, giving developers an automated way to create embeddings from documents in Amazon S3 and keep those embeddings synchronized as the underlying data changes, eliminating the need for external ETL pipelines and queuing systems.

Pgai Vectorizer provides a streamlined approach where developers can reference documents in S3 (or local storage) via URLs stored in a database table. The vectorizer then handles the complete workflow—downloading documents, parsing them to extract content, chunking text appropriately, and generating embeddings for use in semantic search, RAG, or agentic applications.

This integration supports a wide variety of file formats, including:

Documents: PDF, DOCX, TXT, MD, AsciiDoc
Spreadsheets: CSV, XLSX
Presentations: PPTX
Images: PNG, JPG, TIFF, BMP
Web content: HTML
Books: MOBI, EPUB

For developers, pgai Vectorizer for document vectorization offers three key benefits:

Get started more easily → Automatic embedding creation with a simple SQL command manages the entire workflow from document reference to searchable embeddings.
Spend less time wrangling data infrastructure → Automatic updating and synchronization of embeddings means your vector search stays current with your S3 documents without manual intervention. It’s as simple as adding a new row or updating a “modified_at” column in the documents table, and pgai Vectorizer will take off any (re)processing.
Continuously improve your AI systems → Testing and experimentation with different embedding models or chunking strategies can be done with a single line of SQL, allowing you to optimize your application's performance.

By keeping your embeddings automatically synchronized to the source documents in S3, pgai Vectorizer ensures that your Postgres database remains the single source of truth for both your structured and vector data.

Under the hood: How pgai Vectorizer works with Amazon S3

Pgai Vectorizer simplifies the entire document processing pipeline through a streamlined architecture that connects your Amazon S3 documents with Postgres. Here's how it works:

Architecture overview

Architecture overview of pgai Vectorizer: The vectorizer system takes in source data from Postgres tables and S3 buckets, creates embeddings via worker processes running in AWS Lambda using user-specified parsing, chunking, and embedding configurations, and stores the final embeddings in Postgres tables using the pgvector data type.

The pgai Vectorizer architecture for document vectorization consists of several key components:

Data sources: Postgres and Amazon S3

Text and metadata residing in Postgres tables
Postgres tables containing URLs that reference documents in Amazon S3 (which serves as the data aggregation layer where your documents reside)

Vectorization configuration

Stored in Postgres, allowing you to manage everything through familiar SQL commands
Defines chunking strategies, embedding models, and processing parameters

Vectorizer worker (AWS Lambda)

A daemon process that handles the actual work of processing documents
Responsible for downloading, parsing, chunking, and embedding creation
Automatically manages synchronization between source documents and embeddings

Destination

All embeddings are stored in Postgres alongside metadata
Enables unified queries across both structured data and vector embeddings

Document processing pipeline

The document vectorization process follows these steps:

Documents are referenced via URLs stored in a database column.
The vectorizer downloads documents using these URLs.
Documents are parsed to extract text content in an embedding-friendly format.
The content is chunked using configurable chunking strategies.
Chunks are processed for embedding generation using your chosen embedding model.
Embeddings are stored in Postgres with references to the source documents.

_ Pgai Vectorizer document processing pipeline showing how files in Amazon S3 get parsed, chunked, formatted, and embedded in order to be used in vector search queries in a Postgres database._

Key components

Loader : Loads files from Amazon S3
Parser : Extracts content from retrieved files, handling different document formats
Chunking : Splits content into appropriate sizes for embedding models
Formatting : Organizes chunks with metadata from the source files
Embedding generator : Processes chunks into vector embeddings

Use cases for pgai Vectorizer document vectorization

Pgai Vectorizer's document vectorization capabilities enable several powerful use cases across industries by connecting S3-stored documents with Postgres vector search:

Financial analysis

Automatically vectorize financial documents from S3 without custom pipelines. Connect document insights with quantitative metrics for unified queries.

Legal document management

Maintain synchronized knowledge bases of legal documents with automatic embedding updates. Test different models for your specific domain.

Enhanced customer support

Make knowledge base content immediately searchable as it changes, connecting support documents with customer data.

Research systems

Build research AI with continuously updated paper collections, connecting published findings with experimental time-series data.

In each case, pgai Vectorizer eliminates infrastructure complexity while enabling continuous improvement through its "set it and forget it" synchronization and simple experimentation capabilities.

Try out the S3 features in Timescale Cloud Today

Livesync and pgai Vectorizer are just the first steps in our vision to unify Postgres and object storage into a single, powerful lakehouse-style architecture—built for real-time AI and analytics.

→ Try Livesync for S3.

→ Try pgai Vectorizer.

→ Sign up for Timescale Cloud and get started in seconds.

We can’t wait to see what you build.

Day 4 preview: Developer Tools That Speed Up Your Workflow: Introducing SQL Assistant, Recommendation Engine, and Insights

Tomorrow, we'll reveal how Timescale delivers high-speed performance without sacrificing simplicity through SQL assistant with agent mode , recommendation engine , and Insights. See how plain-language queries eliminate SQL wrangling, how automated tuning keeps databases optimized with a single click, and why developers finally get both the millisecond response times users demand and the operational simplicity teams need.

Postgres vs. Qdrant: Why Postgres Wins for AI and Vector Workloads

Team Tiger Data — Wed, 30 Apr 2025 16:01:47 +0000

It's Timescale Launch Week and we’re bringing benchmarks: Postgres vs. Qdrant on 50M Embeddings.

There’s a belief in the AI infrastructure world that you need to abandon general-purpose databases to get great performance on vector workloads. The logic goes: Postgres is great for transactions, but when you need high-performance vector search, it’s time to bring in a specialized vector database like Qdrant.

That logic doesn’t hold—just like it didn’t when we benchmarked pgvector vs. Pinecone.

Like everything in Launch Week, this is about speed without sacrifice. And in this case, Postgres delivers both.

We’re releasing a new benchmark that challenges the assumption that you can only scale with a specialized vector database. We compared Postgres (with pgvector and pgvectorscale) to Qdrant on a massive dataset of 50 million embeddings. The results show that Postgres not only holds its own but also delivers standout throughput and latency, even at production scale.

This post summarizes the key takeaways, but it’s just the beginning. Check out the full benchmark blog post on query performance, developer experience, and operational experience.

Let’s dig into what we found and what it means for teams building production AI applications.

The Benchmark: Postgres vs. Qdrant on 50M Embeddings

We tested Postgres and Qdrant on a level playing field:

50 million embeddings , each with 768 dimensions
ANN-benchmarks , the industry-standard benchmarking tool
Focused on approximate nearest neighbor (ANN) search, no filtering
All benchmarks run on identical AWS hardware

The takeaway? Postgres with pgvector and pgvectorscale showed significantly higher throughput while maintaining sub-100 ms latencies. Qdrant performed strongly on tail latencies and index build speed, but Postgres pulled ahead where it matters most for teams scaling to production workloads.

For the complete results, including detailed performance metrics, graphs, and testing configurations, read the full benchmark blog post.

Why It Matters: AI Performance Without the Rewrite

These results aren’t just a technical curiosity. They have real implications for how you architect your AI stack:

Production-grade latency: Postgres with pgvectorscale delivers sub-100 ms p99 latencies needed to power real-time or responsive AI applications.
Higher concurrency : Postgres delivered significantly higher throughput, meaning you can support more simultaneous users without scaling out as aggressively.
Lower complexity : You don't need to manage and integrate a separate, specialized vector database.
Operational familiarity : You leverage the reliability, tooling, and operational practices you already have with Postgres.
SQL-first development : You can filter, join, and integrate vector search naturally with relational data, without learning new APIs or query languages.

Postgres with pgvector and pgvectorscale gives you the performance of a specialized vector database without giving up the ecosystem, tooling, and developer experience that make Postgres the world’s most popular database.

You don’t need to split your stack to do vector search.

What Makes It Work: Pgvectorscale and StreamingDiskANN

How can Postgres compete with (and outperform) purpose-built vector databases?

The answer lies inpgvectorscale (part of thepgai family), which implements the StreamingDiskANN index (a disk-based ANN algorithm built for scale) for pgvector. Combined with Statistical Binary Quantization (SBQ), it balances memory usage and performance better than traditional in-memory HNSW (hierarchical navigable small world) implementations.

That means:

You can run large-scale vector search on standard cloud hardware.
You don’t need massive memory footprints or expensive GPU-accelerated nodes.
Performance holds steady even as your dataset grows to tens or hundreds of millions of vectors.

All while staying inside Postgres.

When to Choose Postgres, and When Not To

To be clear: Qdrant is a capable system. It has faster index builds and lower tail latencies. It’s a strong choice if you’re not already using Postgres, or for specific use cases that need native scale-out and purpose-built vector semantics.

However, for many teams—especially those already invested in Postgres— it makes no sense to introduce a new database just to support vector search.

If you want high recall, high throughput, and tight integration with your existing stack, Postgres is more than enough.

Want to Try It?

Pgvector and pgvectorscale are open source and available today:

pgvector GitHub
pgvectorscale GitHub
Or save time and access both by creating a free Timescale Cloud account

Vector search in Postgres isn’t a hack or a workaround. It’s fast, it scales, and it works. If you’re building AI applications in 2025, you don’t have to sacrifice your favorite database to move fast.

Up Next at Timescale Launch Week

Next up, we’re taking Postgres even further: Learn how to stream external S3 data into Postgres with livesync for S3 and work with S3 data in place using the pgai Vectorizer. Two powerful ways to seamlessly integrate external data from S3 directly into your Postgres workflows!

Connect Any Postgres to Real-Time Analytics

Team Tiger Data — Tue, 29 Apr 2025 18:14:58 +0000

TLDR: We built livesync for Postgres to solve the analytics-vs-stability dilemma. Stream data from any Postgres instance directly into Timescale Cloud with zero downtime and no application changes. It performs historical backfills at 150GB/hour while capturing live changes through CDC, automatically converting tables to hypertables. Your production database remains untouched while you gain columnar storage, compression, and time-partitioning capabilities that dramatically accelerate queries. No more complex ETL pipelines or risky migrations, just high-performance analytics without compromising system reliability. Read the full article.

Scaling real-time analytics on Postgres has always been a balancing act. Timescale was built to solve this problem, to make Postgres scalable, fast, and analytics-ready without sacrificing reliability.

But teams with production applications on vanilla Postgres (or locked into other database-as-a-service platforms) often find themselves stuck. They face an impossible choice: risk downtime to migrate, build brittle ETL (extract-transform-load) pipelines, or live with the slow drag of overloaded systems.

We built Timescale livesync for Postgres to break this cycle—and today, we’re excited to kick off a Timescale Launch Week by introducing it.

Livesync lets you stream data from any Postgres database, whether it's running on Amazon RDS for PostgreSQL, Amazon Aurora, Azure PostgreSQL, self-hosted, or elsewhere, into Timescale Cloud with zero downtime, no application rewrites, and no disruption to your existing systems.

You get the analytical speed of Timescale’s hypertables and hypercore, providing seamless time-based partitioning, automatic columnar storage, and unbeatable compression without sacrificing the production stability you’ve worked hard to build. If you’re after a refresher on what Timescale can do, then check out our whitepaper.

You get speed without sacrifice.

Why Use Livesync for Real-Time Analytics?

When your application needs to deliver real-time insights, your options have traditionally been limited:

Risky full migrations , often requiring downtime windows that never feel safe enough.
Heavy, custom-built ETL pipelines that introduce complexity, lag, and new points of failure.
Overloaded production systems , where even simple dashboards slow down, and you risk impacting your critical transactional workloads.

In an ideal world, every application that faces these problems would migrate directly to Timescale (we are just Postgres after all!), but we understand that’s not always realistic. For many teams, the stakes are too high to move fast and break things. Systems powering financial transactions, IoT networks, and SaaS platforms can’t tolerate disruption, yet they can't ignore the need for analytics.

Livesync offers a new path forward: keep your existing Postgres exactly as it is , but stream your data in real time to a dedicated Timescale Cloud instance optimized for analytical workloads.

No downtime. No risk. No performance hits to production. To get the most out of Timescale Cloud, we’d still advise that you start planning to fully migrate in the future, but for now you have blazing-fast real-time analytics.

How Livesync for Postgres Works

Livesync uses Postgres’ logical replication protocol to stream changes from your production database into Timescale Cloud. But rather than simply duplicating your data, livesync extends logical replication with high-throughput ingestion, automatic hypertable creation, and a cloud-native architecture that prepares your data for real-time analytics at scale.

Zero-downtime setup and continuous replication

When you first connect livesync for Postgres, it performs an initial historical backfill, copying your existing data into Timescale at speeds of up to 150 GB per hour.

At the same time, it begins capturing and streaming live changes through change data capture (CDC), recording every insert, update, and delete from your source Postgres database as they happen. You can choose exactly which tables to replicate, moving only the data you need for analytics, while the rest of your production system continues operating normally, with no maintenance windows, no locks, and no downtime risk.

Behind the scenes, livesync uses a microservice that connects to your source database as a logical replication subscriber, consuming changes directly from your Postgres publication.

Unlike pure logical replication, livesync for Postgres automatically prepares your data for high-performance analytics: It can create corresponding tables inside Timescale Cloud and configure them as hypertables, unlocking immediate benefits like native time-based partitioning and faster time-series queries.

Once your data is live in Timescale Cloud, you can optionally enable columnar storage and compression to further accelerate analytics and optimize storage, without modifying your ingestion or sync setup. This tight integration ensures that Livesync for Postgres doesn't just mirror your data—it sets it up to scale.

Non-Invasive, Read-Only Architecture for Real-Time Analytics

Livesync is designed to be as non-invasive as possible. It creates a lightweight publication and replication slot on your Postgres database but does not alter your existing schemas, application code, or database connections.

Your production environment remains fully operational at all times. Livesync simply observes and streams changes without interfering, making it especially well-suited for production systems with strict stability, compliance, or uptime requirements.

High-Performance, Scalable by Design

Livesync for Postgres is engineered for high throughput across both historical and live data.

During the initial backfill, livesync achieves historical copy speeds of up to 150 GB per hour. During live operations, it can sustain 30,000 to 40,000 DML operations per second.

Future improvements, including intra-table parallelization and adoption of logical replication protocol v2 (which allows streaming of in-flight transactions rather than send on commit semantics), are already on our roadmap to push these limits even further.

Seamless Integration with Existing Architectures

Livesync fits cleanly into your existing infrastructure, whether you're running RDS, Aurora, Azure Database for PostgreSQL, or a self-managed instance.

Your operational systems continue working exactly as they do today. Livesync simply adds a Timescale-powered analytics layer next to your source database, letting you redirect analytical queries to Timescale by switching to a new connection string when you're ready.

No replatforming, no rewrites, no downtime. Just faster SQL real-time analytics.

When to Choose Livesync

Livesync is the right choice when:

Zero downtime is non-negotiable , such as in financial systems, healthcare apps, production SaaS platforms.
You need real-time insights today , but can't risk application rewrites or complex migrations.
You want to incrementally adopt Timescale , starting small and expanding over time.
You’re evaluating Timescale performance before committing to full migration.

Whether you're monitoring 10,000 IoT sensors, analyzing 50 million transactions, or building dashboards for end users, livesync gets you there—without the "lift and pray" gamble of traditional migration projects.

(That said, if you're open to consolidating on a single database, check out our migration tooling which takes the risk out of full migrations to Timescale.)

Real-World Livesync Applications

Livesync is built for real-world systems where uptime, performance, and gradual adoption matter. Here’s how different verticals might use it to accelerate analytics without disruption:

Financial services : Offload heavy historical queries to Timescale without risking downtime or introducing disruptive schema changes. Keep OLTP workloads fast and stable while running complex analytics separately.
IoT environments : Send millions of time-series events daily directly into hypertables, enabling real-time rollups, faster trend analysis, and storage optimizations like compression—without custom ETL pipelines or manual partitioning.
AWS-native architectures : Layer Timescale Cloud analytics on top of existing RDS or Aurora deployments, delivering sub-second analytical performance without replatforming or disrupting operational systems.

In every case, livesync removes the traditional pain of "faster analytics" projects, delivering immediate real-world results without sacrificing production stability.

Setting Up Livesync

Getting started is simple!

Create a Timescale Cloud service.
Connect livesync to your existing Postgres—no code changes required (see the Actions tab when you manage your service).
Configure your source database , either by providing your host, port, user and database or a Postgres connection string.
Select your tables , configure hypertables, and hit start.
Watch your data flow into Timescale—with full visibility, full control, and no disruption.

Once live, you can redirect your analytical queries to Timescale’s battle-tested storage engine, unlocking sub-second dashboards and deep insights without impacting your production system.

And For Our Next Trick...

Connecting any Postgres to real-time analytics is just the start. This week is a Timescale Launch Week, a celebration of new features and new ways to move faster without compromise. Every day, we’re showing how you can deliver speed without sacrifice across your entire stack:

Day 1 : Connect Any Postgres to Real-Time Analytics — Start using livesync for Postgres and add real-time analytics to your existing stack.
Day 2 : Compare Pgvector and Qdrant — See a side-by-side breakdown of two popular open-source vector databases.
Day 3 : Connect S3 Data to Postgres — Use livesync for S3 and the pgai Vectorizer to work with external data directly inside Postgres.
Day 4 : Supercharge Your Developer Experience — Use our SQL Assistant’s new AI agent mode, get recommendations to tune your instance, and explore new query visibility tools with Timescale Insights.
Day 5 : Strengthen Security and Compliance — Maintain control while scaling your analytics performance.

Try Livesync Today

Start your free Timescale trial and set up livesync for Postgres today.

And stay tuned, in the coming weeks we'll dive deeper into how we built our livesync products.

Building IoT Pipelines for Faster Analytics With IoT Core

Team Tiger Data — Mon, 28 Apr 2025 14:58:20 +0000

What Is AWS IoT Core

AWS IoT Core is Amazon’s managed IoT service that lets you easily connect and manage Internet of Things devices. This can be done using the MQTT protocol, which was specifically designed for IoT use cases to be lightweight and fault-tolerant. If MQTT isn’t your cup of tea, you also have the option to use HTTP, although this is less common for reasons beyond this post.

While IoT Core can be used solely as a message broker, allowing IoT devices to send and receive messages by way of publishing and subscribing to MQTT topics, you can also use the message routing functionality to receive select messages using a SQL-like rule and forward them to other Amazon Web Services. These include Kinesis, SQS, and Kafka, but most importantly, AWS Lambda.

If you’ve followed the Do More With Timescale on AWS series, you’ve undoubtedly seen that with a simple AWS Lambda function, we can very easily insert any kind of time-series data into a Timescale database.

Today, we’ll go over how to set up a message routing rule and set up a Lambda function as an action so that for every MQTT message, a Lambda function gets triggered.

If you’d rather watch a video on how to achieve this, click on the video below!

Writing the Action Lambda Function

Before deploying any resources to AWS, let’s write our AWS Lambda function, which we will use as a trigger in AWS IoT Core to insert MQTT messages into Timescale.

For the sake of simplicity, I’ve written the Lambda function in Python and kept the code as simple as possible. A side effect of this is that the function doesn’t have adequate type-checking, error handling, or secret management. For that reason, it is not recommended to use this function in a production environment.

Initialization

Let’s start by writing the initialization portion of our function. This will be executed once, so long the function stays “hot.” By importing our libraries and creating a database connection in the initialization portion, we forgo having to create a new database connection for each Lambda execution. This will save valuable time (and consequently, a lot of money).

First, import the psycopg2 library, which we’ll use to connect to our Timescale database.

import psycopg2

Then we create a variable called connection_string which holds the connection string to our database. As mentioned previously, for the sake of simplicity, we are omitting any sensible secret management.

An easy way to do this would be to add your connection string as an environment variable to your Lambda function and use the os.getenv function to retrieve it or to use an alternative secret management solution like AWS Secrets Manager.

Since we will be using Lambdas, we strongly recommend adding a connection pooler to your service and using transaction mode. Add a connection pooler to your service by going to the Connection info panel of your service in the Timescale dashboard, clicking the Connection pooler tab, and then clicking Add a connection pooler. From there, choose “Transaction pool” in the Pool drop-down. The Service URL will be your connection string.

Do note that this connection string does not contain your service password, so you will need to manually add this as follows.

conn_str = "postgres://tsdbadmin:passwd@service.tsdb.cloud.timescale.com:5432/tsdb"

Afterward, we create a psycopg2 connection object from which we create a cursor that we will use to insert rows into our database.

conn = psycopg2.connect(conn_str)
cursor = conn.cursor()

Lambda handler

Then, we move on to the main handler of our Lambda function. This will be run exactly once for every execution.

We execute a SQL insert statement into the sensor hypertable. In this case, the event consists of a single float, which could be a temperature reading from an IoT sensor or the battery percentage of an electric car.

We also use the PostgreSQL NOW() function to indicate when this event happened. In a production environment, it might be advisable to add a timestamp on the IoT sensor itself, as MQTT messages routed through AWS IoT Core can incur a small time delay.

After our execution, we commit the transaction and return the function. As mentioned at the beginning of this blog post, this simple function can benefit hugely from logging and more graceful error handling.

def handler(event, context):
    # try to insert the value into the sensor table
    cursor.execute("INSERT INTO sensor (time, value) VALUES (NOW(), %s);", [event["value"]])
    conn.commit()
    return

You can find the full Lambda function code in this GitHub repository.

Creating the Sensor Hypertable

Before we continue, it’s important to create the sensor hypertable in our Timescale instance by executing the following SQL queries on our database.

CREATE TABLE sensor (time TIMESTAMPTZ, value DOUBLE PRECISION);
SELECT create_hypertable('sensor', 'time');

Creating the Lambda Function in AWS

Publish the function

Once we’ve successfully written the code, we can package our function along with the psycopg2 library in a Docker container and publish it to AWS ECR. You can find the full code, Dockerfile, and build script in this GitHub repository.

Create the function

Once the Docker container has finished publishing to ECR, we can create a new Lambda function using its container image URI. We will name our function timescale-insert. Make sure to pay attention to what architecture you used to build the lambda function. If you are using an M1 (or higher) Mac, this will be arm64. In most other cases, you can use x86_64.

Due to the nature of containers, we don’t need to change any settings once the Lambda function has been created!

Create the IoT Core Message Routing Rule

IoT Core has a feature called ‘message routing,’ which allows you to (as the name suggests) route messages to different services. Throughout this section, you will learn more about how rules work.

Create a new rule by pressing the orange 'Create rule' button.

Give the rule an appropriate name. Since IoT Core rule names cannot include dashes, we will name it: timescale_insert.

Next up, we need to create a SQL statement that will be used to ‘query’ all incoming MQTT messages in IoT Core. This SQL statement consists of three parts:

A SELECT clause that selects specific fields of the MQTT message payload.
A FROM clause that is used to specify the MQTT topic we want to query on
A WHERE clause that is used to exclude certain MQTT messages where a condition is not met.

In our query, we will be selecting every field (*) of messages on the my-topic/thing topic. We won’t be using a where clause because we want to insert every MQTT message on the topic in our Timescale database. Then, we click ‘Next.’

After configuring our SQL statement, we need to add an action. Select the Lambda action type, then find and select the timescale-insert function we created earlier! As you can see, there is an option to add multiple rule actions to a single IoT Core rule. This would allow you to stream your data to multiple destinations, for example, two (or more) Timescale databases or hypertables. You name it; it can be done!

Then lastly, click on the orange ‘Create rule’ button.

If all goes well, your newly created rule should be active!

Testing Our Message Routing Rule

Now that we’re done writing our Lambda function, let’s set up our Timescale hypertable and IoT Core message rule. It’s time to put it to the test!

To do this, I’ve repurposed a Python script written by AWS. You can find the original AWS tutorial or clone the modified Python script here. Do note that you will have to add your own certificates and ‘things’ to AWS IoT Core for this to work (which could be a blog post on its own).

In case you don’t feel like reading the Python code, these are the sequential steps taken by the script:

Create an MQTT connection to the AWS IoT Core endpoint using the appropriate device certificates.
Generate an array of floats.
Iterate over the array: a) Synthesize JSON message with a float from the array created in step 2. b) Publish the JSON message to the my-topic/thing MQTT topic. c) Sleep for one second.
Gracefully disconnect.

We run this script for a handful of seconds, connect to our Timescale database using psql and execute a query to retrieve all the rows in the sensor table.

And as you can see, we’ve achieved our goal of piping MQTT data from AWS IoT Core into a Timescale database with a single AWS Lambda function!

AWS IoT Core: The End

Congratulations! You have successfully set up an AWS IoT Core message rule that streams MQTT messages originating from a simple (albeit fake) sensor into a Timescale database.

You are now primed and ready to build an endless stream of performant IoT pipelines that can accelerate your real-time IoT dashboards and analytics!

If you want to learn more about how Timescale can improve your workloads on AWS, check out this YouTube playlist filled with other AWS and Timescale-related tutorials!

More resources

Text-to-SQL: A Developer’s Zero-to-Hero Guide

Team Tiger Data — Fri, 25 Apr 2025 15:09:52 +0000

TL;DR
Build your own text-to-SQL system that translates natural language into database queries. This guide covers implementation approaches from rule-based to ML models, practical code examples, and production-ready best practices for security and performance.

What You'll Learn

How to translate natural language queries into SQL with NLP
Building both rule-based and ML-based text-to-SQL systems
Implementing error handling, security, and performance optimizations
Advanced features like multi-turn conversations and visualization
Troubleshooting common challenges in real-world deployments

The Developer's Text-to-SQL-Challenge

As developers, we've all been there:

PM: "Can you pull last quarter's revenue by product category?"
You: "Give me an hour to write the SQL..."

What if anyone in your organization could get answers directly from your database without knowing SQL? That's the promise of text-to-SQL systems.

This guide will show you how to build a production-ready text-to-SQL pipeline that empowers non-technical users while maintaining security and performance. We'll focus on practical implementation rather than theory.

The Building Blocks of Text-to-SQL System: SQL and NLP

Before going into the details of building a text-to-SQL system, let's understand the two core pillars that enable the translation of human-readable questions into database queries:

SQL (Structured Query Language)
Natural Language Processing (NLP)

These technologies work together to translate human-readable questions into database queries. Let’s break them down.

Understanding SQL

SQL is the language of relational databases. It helps us to interact with structured data, retrieve information, and perform complex operations like filtering, sorting, and aggregating. Here’s a quick look at the basics:

SELECT: specifies the columns you want to retrieve
FROM: specifies the table containing the data
WHERE: filters rows based on conditions
GROUP BY: aggregates data based on one or more columns
ORDER BY: sorts result in ascending or descending order
JOIN: combines data from multiple tables based on related columns

For instance, we can create a query that calculates the total revenue by city for 2024, sorted in descending order.

SELECT city, SUM(revenue)
FROM sales
WHERE year = 2024
GROUP BY city
ORDER BY SUM(revenue) DESC;

Schema design

A database schema defines the structure of your data, including tables, columns, and relationships. For example, a sales table might have columns like invoice_id, date, product, and revenue. A well-designed schema allows text-to-SQL systems to generate accurate queries.

Natural language processing (NLP)

NLP enables machines to understand and process human language. In the text-to-SQL context, NLP helps interpret natural language questions and map them to database structures. Here’s how it works:

Tokenization: It’s about breaking down a sentence into individual words or tokens. For example:
Input: "Show me sales in New York."
Tokens: ["Show", "me", "sales", "in", "New", "York"]
Intent recognition: Identifying the user’s goal. For instance, the question "What’s the total revenue?" intends to perform an aggregation (SUM).
Entity extraction: Detecting key pieces of information, such as:
Dates: "last quarter" → WHERE date BETWEEN '2023-07-01' AND '2023-09-30'.
Locations:"New York" → WHERE city = 'New York'.
Schema linking: Mapping natural language terms to database schema elements. For example:
"sales" → sales table.
"revenue" → revenue column.

For instance, if a user asks, “What are the top five products by sales in Q1 2023?”, an NLP model would:

Identify key entities like “products,” “sales,” and “Q1 2023.”
Map these to corresponding database tables and columns.
Generate an SQL query.

SELECT product_name, SUM(sales_amount) AS total_sales
FROM sales
WHERE quarter = 'Q1' AND year = 2023
GROUP BY product_name
ORDER BY total_sales DESC
LIMIT 5;

Text-to-SQL Implementation Approaches

Different implementation approaches can be employed for building a text-to-SQL pipeline, depending on the queries' complexity, the database's size, and the level of accuracy required. Below, we’ll discuss two primary approaches, including:

Rule-based systems
Machine learning-based systems

Rule-based systems

Rule-based systems depend on manually crafted rules and heuristics to convert natural language queries into SQL commands. These systems are deterministic, which means they adhere to a fixed set of instructions to generate queries.

Rule-based systems work by parsing natural language inputs into structured representations and then applying a set of predefined templates or grammatical rules to generate SQL queries. For example, the rule for the query, “Show me sales in New York last quarter," can look like this:

IF "sales" AND "in [location]" AND "last quarter"  
THEN:  
  SELECT * FROM sales  
  WHERE city = [location]  
  AND date BETWEEN [start_of_quarter] AND [end_of_quarter];

And the generated SQL query will look like this:

SELECT * FROM sales  
WHERE city = 'New York'  
AND date BETWEEN '2023-07-01' AND '2023-09-30';

But, as databases grew in size and complexity, rule-based systems became impractical, paving the way for machine learning-based approaches.

Machine learning-based systems

Machine learning (ML) approaches to text-to-SQL use algorithms to learn how to map between natural language inputs and SQL queries. These systems can handle more complex and varied queries compared to rule-based methods.

Machine learning models depend on feature engineering to extract relevant input text and database schema information. Features such as part-of-speech tags, named entities, and schema metadata (e.g., table names and column types) are extracted from the input. A classifier or regression model then predicts the corresponding SQL query based on these features.

LSTM-based models

Long short-term memory (LSTM) networks were among the first deep-learning approaches applied to text-to-SQL tasks. They can effectively model the sequential nature of natural language and SQL queries.

For instance, Sequence-to-Sequence (Seq2Seq) architectures commonly used with LSTMs treat the problem as a translation task, converting natural language sequences into SQL sequences. They consist of two elements:

An encoder processes the input natural language query and generates a context vector that understands the query's meaning.
A decoder uses the context vector to generate the SQL query step-by-step.

Transformer-based models

Transformer-based models, like BERT, GPT, and Llama, have become the dominant approach in text-to-SQL. These models use a self-attention mechanism, allowing them to understand contextual relationships in the input text and the database schema much more effectively. Self-attention enables the model to understand, for example, that "top five products" implies sorting and limiting results.

Moreover, transformers can better handle schema information by incorporating it into the model's input or using specialized schema encoding techniques.

Best Text-to-SQL Practices and Considerations

Building a text-to-SQL system is more than just wiring together NLP models and databases. You need to adopt industry-tested practices and anticipate common pitfalls to ensure reliability, scalability, and security. There are actionable strategies to optimize your system—which we’ll discuss next—including schema design, error handling, and navigating real-world challenges.

Data preparation and schema design

The quality of your database schema directly impacts the performance and accuracy of your text-to-SQL system. Ensure that your database is well-structured, with normalized tables to minimize redundancy. Use intuitive and descriptive column names that align with natural language terms. Provide metadata about tables, columns, and relationships (e.g., unit_price → "USD, before tax") to help the system map natural language inputs to the correct schema elements.

-- Good Schema  
CREATE TABLE sales (  
    order_id INT PRIMARY KEY,  
    order_date DATE,  
    customer_id INT,  
    total DECIMAL(10,2)  -- Total amount in USD  
);

-- Poor Schema  
CREATE TABLE tbl1 (  
    col1 INT,  
    col2 DATE,  
    col3 INT,  
    col4 DECIMAL(10,2)  
);

Handling ambiguity and user intent

Natural language is inherently ambiguous, and users may phrase queries in unexpected ways. Addressing their ambiguity is crucial for generating accurate SQL queries. One study found that nearly 20 % of the user questions are problematic, including 55 % ambiguous and 45 % unanswerable.

There are multiple ways to handle the ambiguities, including:

Clarification prompts: If the input is unclear, prompt the user for clarification. This approach improves user experience and reduces errors.
Synonym mapping: Map synonyms and variations to standardized terms in the database schema. For example, recognize “earnings,” “revenue,” and “income” as referring to the sales_amount column.
Context awareness: Maintain context across multi-turn conversations to handle follow-up questions effectively.

Error handling

Plan for failures to maintain user trust because even the most advanced systems will occasionally generate incorrect queries. Implementing an error-handling strategy ensures a smooth user experience. Error handling strategies can include:

Graceful error messages: These provide clear and actionable feedback when a query fails or produces no results.
Fallback strategies: If the primary model fails, refer to simpler methods (e.g., rule-based templates) or ask the user to rephrase their query.
Logging and monitoring: Log failed queries and analyze them to identify patterns or recurring issues. Use this data to improve the system iteratively.

Example:

try:  
    sql = generate_sql(query)  
except AmbiguityError as e:  
    return {"error": "Please clarify your question.", "options": e.options}  
except UnsafeQueryError:  
    return {"error": "This query is not permitted."}

Security and privacy concerns

Text-to-SQL systems interact directly with databases, prioritizing security to protect your database from malicious or accidental harm.

Access control: Restrict access to sensitive tables or columns based on user roles.
Input validation: Sanitize user inputs to prevent SQL injection attacks.
Data masking: Mask sensitive information in query results (e.g., partial credit card numbers or anonymized customer IDs).
Audit trails: Maintain logs of all queries executed through the system to track usage and detect unauthorized activity.

Performance optimization

Efficient query generation and execution are essential for delivering timely results, especially for large-scale databases.

Indexing: Ensure that frequently queried columns are indexed to speed up search operations.
Caching: Cache frequently requested queries and their results to reduce database load.
Query simplification: Optimize generated SQL queries by removing unnecessary joins or filters.
Parallel processing: Leverage parallelism for complex queries involving multiple tables or aggregations.

Advanced Features in Text-to-SQL Systems

Enhancing a text-to-SQL system with advanced capabilities, including features that boost usability, scalability, and user satisfaction, is essential. Below are key advanced features of the system.

Contextual understanding and multi-turn conversations

One significant improvement in modern text-to-SQL systems is their ability to maintain context across multiple interactions, enabling multi-turn conversations. This feature is handy when users refine their queries based on previous results or ask follow-up questions.

For instance, if a user asks about sales from the last quarter and then follows up with a request to break it down by product line, the system understands that the second query refers to the same time period. The system reduces repetition and frustration by maintaining session-based memory and tracking entities like dates or regions mentioned earlier, enabling users to build on previous queries without starting over.

Integration with other systems and platforms

Text-to-SQL systems can be extended beyond standalone applications by integrating with other tools and platforms, creating end-to-end analytics workflows. Real-world use cases often require combining data from multiple sources or pushing results to external systems for further analysis or visualization.

For example, connecting the system to business intelligence (BI) tools like Tableau or Power BI allows users to generate interactive dashboards and reports directly from their natural language queries. Similarly, integrating with CRM (customer relationship management) or ERP (enterprise resource planning) systems enables users to query operational data seamlessly, such as asking how many deals were closed last month. The system can also pull data from external APIs or cloud storage services, combining internal datasets with external market trends to provide a unified view of information.

Generating visualizations from SQL output

Transforming raw query results into visual formats is another powerful feature that enhances usability and makes data more accessible to non-technical users. Visualizations help users quickly identify trends, patterns, and outliers in the data, reducing the cognitive load associated with interpreting raw tables.

Additionally, providing options to export visualizations as PDFs, PNGs, or interactive HTML files makes it easier for users to share insights with stakeholders. By presenting data in a digestible format, the system ensures that insights are not only actionable but also easily shareable.

Common Challenges in Text-to-SQL Systems

While text-to-SQL systems offer immense benefits for democratizing data access, they are not without their challenges. Here are common challenges developers and users face with these systems:

Ambiguity in natural language queries: Natural language inputs can be vague or open to multiple interpretations, leading to incorrect SQL queries.
Handling complex queries: Text-to-SQL systems may fail to generate correct SQL for complex queries that involve joins, subqueries, or nested logic.
Poor schema: Poor schemas in text-to-SQL systems can lead to incorrect column or table mappings, resulting in irrelevant query results.
Performance and scalability: Text-to-SQL systems that query large datasets or generate complex SQL can strain computational resources and slow performance.
Error recovery: Even the most advanced systems occasionally generate incorrect queries. Implementing robust error recovery strategies is essential to maintaining user trust and improving the system iteratively.

Conclusion

Text-to-SQL connects human language with database queries, enabling users to effortlessly access and analyze data without the need to write code. It uses NLP to understand user intent by translating natural language questions into SQL and mapping it to the database schema.

The main advantages of using text-to-SQL include enhanced data accessibility for non-technical users and quicker data analysis. For time-series data, leveraging a powerful time-series database like Timescale Cloud can greatly improve the performance and scalability of your text-to-SQL system.

To experience the power of time-series data with text-to-SQL, try Timescale today.