Off-SingleStore Database Platform Research

Research only, no decision yet

This document captures exploration into alternatives to SingleStore Helios for our backing database. It is not a committed migration plan. Treat it as a workload analysis, a survey of candidate platforms, and a list of open questions to inform a future decision.

Motivation for the Research

We are evaluating whether SingleStore Helios remains the best fit for the platform's current and projected workloads. The drivers prompting this evaluation:

• Cost. Helios licensing is the largest single line item in our database bill.
• Product velocity. SingleStore's roadmap has not moved on the features we increasingly want (continuous aggregates, ergonomic time-series, Postgres-grade extensions, broader ecosystem of managed offerings).
• Storefront OLTP performance. Catalog browsing on storefronts performs poorly on SingleStore's columnstore default; rowstore at scale is expensive. Four farfalla tables (sign_up_intents, sales_settings, orders_gateways, pla_tenant_features) are already explicitly converted to ROWSTORE, suggesting the columnstore default is not serving the OLTP workload.

None of the above is by itself decisive. The point of the research is to size the alternatives against our actual workloads and surface the tradeoffs.

Scope of the Research

Services using SingleStore today: farfalla, medusa, coniglio.

Services unaffected (no SingleStore dependency): zoo, vito, fenice, volpe, castoro, micelio, publicalanewwebsite.

Out of scope for this document: search/discovery (Meilisearch, Typesense), object storage, queues. The focus is the relational + time-series store.

Workload Findings

The shape of the workload across the three services drives any platform choice. Findings below are descriptive; they are not prescriptions.

Farfalla

Hybrid OLTP and roughly 30% per-tenant OLAP. SingleStore-specific surfaces in use:

• 5 Projections: coupons_non_gift_by_tenant_created, taxables_by_issue_id, and others used as covering pre-aggregations for hot dashboard queries.
• PERSISTED generated columns with non-trivial expressions (marketplace_enabled chains JSON extraction with regex; normalized_value does LOWER(REGEXP_REPLACE(...))).
• FULLTEXT VERSION 2 indices on issues.name and terms.name, queried via MATCH ... AGAINST in app/Domains/Storefront/Services/InventoryService.php with custom weighted scoring across issues, contributors, and terms.
• JSON path extraction in migrations (JSON_EXTRACT_DOUBLE, JSON_EXTRACT_STRING).
• No stored procedures, pipelines, vector columns, or BSON.

The "OLAP" portion is per-tenant reporting, not analytics-at-scale. Cardinality is bounded by a single tenant's data.

Medusa

Pure OLTP. Approximately 97% standard SQL. Only SingleStore-specific surfaces are shardKey() and sortKey() calls in 2026 migrations (imports, import_records, import_files, content_intake_team). JSON columns use plain Eloquent casting. No FULLTEXT, no procedures, no projections, no vectors.

This service is portable to almost any standard relational target with minimal code change.

Coniglio

Time-series analytics with HTTP-driven ingest and a 20-endpoint analytics API in app/Http/Controllers/General*Handler.php. All analytics endpoints query the rolled-up sessions table via DB::table('sessions')->selectRaw(... GROUP BY ...). The rollup itself is maintained by app/Jobs/TrackEventsAggregator.php on 1-hour windows per tenant.

Live size on prod (queried 2026-04-26 against singlestore_production_readonly):

Table	Rows	Disk (logical)
track_events	932 M	~99 GB
sessions	40 M	~6.7 GB
track_events_aggregations	1.4 M	~0.5 GB
Live total	~973 M	~106 GB

Plus ~6 GB of cleanup-candidate tables (sessions_old_20241226, track_events_backup_20250728, sessions_backup_20250728).

Last 12 full months of ingest (2025-04 to 2026-03):

Metric	Value
Total events	~234 M
Avg per month	~19.5 M
Avg per day	~641 K
Trend	Flat-ish

10x growth forecast at today's bytes-per-row, with indefinite retention:

Horizon	Rows	Logical size
1 year	2.34 B	~250 GB
5 years	11.7 B	~1.25 TB
10 years	23.4 B	~2.5 TB

SingleStore-specific surfaces in coniglio: none beyond columnstore defaults and shard/sort keys. JSON event_properties is opaque to SQL today (read at the application layer in SessionProjector).

Candidate Platforms Considered

Candidate	Best at	Constraints / risks for our shape
PlanetScale Postgres	Unified Postgres for OLTP + Timescale	GA Sept 2025, young; sharding (Neki) not GA
Tigerdata Cloud (managed Timescale)	Most mature TimescaleDB experience	Adds a vendor; OLTP services either move there too or split
AWS Aurora Postgres + ClickHouse	Battle-tested OLTP + best OLAP ceiling	Two engines; TimescaleDB unsupported on Aurora/RDS proper
Self-hosted Postgres + Timescale	Lowest unit cost; full control	Operational burden falls on us
Stay on SingleStore	Status quo; one engine	Cost trajectory; OLTP catalog reads remain unsolved

PlanetScale Postgres in More Detail

The leading candidate explored so far. Standard upstream PostgreSQL on PlanetScale's own Kubernetes operator. Not a fork, not built on Neon. GA September 2025; Postgres 18 supported.

Aspect	Detail
Topology	Primary + 2 replicas across availability zones, streaming replication
Storage	PlanetScale Metal (direct-attached NVMe) or EBS up to 64 TiB
Connection pooling	PgBouncer via PlanetScale's proxy layer
Sharding roadmap	Neki (PlanetScale's own project; not GA at time of writing)

PlanetScale's supported community extensions cover what our workload would need:

Extension	Relevance
timescaledb	Coniglio: hypertables, continuous aggregates, compression, retention
pg_trgm	Farfalla: fuzzy text matching (alternative to SingleStore FULLTEXT v2)
pg_partman	Optional native partitioning where Timescale isn't appropriate
pg_cron	Scheduled jobs (failover behavior to verify before relying on it)
pg_duckdb	Available as an in-process columnar OLAP option

Full list at planetscale.com/docs/postgres/extensions.

Migration Impact (Hypothetical, if Pursued)

Estimates of effort if a migration to PlanetScale Postgres were chosen. Not committed scope.

Farfalla

• Projections to standard materialized views or partial indexes per access pattern.
• PERSISTED columns to GENERATED ... STORED; index creation must be re-verified per column, especially for marketplace_enabled.
• FULLTEXT v2 to tsvector + pg_trgm (loses custom weighted ranking; would need to confirm acceptable).
• 4 explicit ROWSTORE tables become regular Postgres tables with no special handling.
• Multi-tenancy pattern (tenant_id everywhere) ports unchanged.

Medusa

• Strip shardKey() and sortKey() calls from migrations.
• Effectively no other code change.

Coniglio

• Convert track_events and sessions to TimescaleDB hypertables, partitioned on created_at and started_at respectively.
• Replace TrackEventsAggregator with TimescaleDB continuous aggregates feeding the existing 20 dashboard endpoints. Refresh policy up to 5 minutes is acceptable per current product expectations.
• Compression policy on raw track_events after 30 days.
• Retention is "indefinite" per current requirement; revisit once continuous aggregates are in place. ClickHouse becomes the escalation path if raw track_events outgrows a single Postgres node before Neki sharding ships.

Sketch of a Possible Order (Not Approved)

If the platform decision lands on PlanetScale Postgres, a per-service big-bang cutover with this order would minimize risk:

Order	Service	Rationale
1	medusa	Lowest risk; validates the Postgres ops story (backups, monitoring, deploy hooks).
2	coniglio	Single-purpose, isolated. Exercises TimescaleDB before farfalla bets on it.
3	farfalla	Largest scope. Biggest payoff (kills FULLTEXT v2 lock-in; row-oriented catalog reads).

Open Questions

• Confirm PlanetScale's shipped TimescaleDB version and the supported continuous-aggregate feature set (real-time aggregates, hierarchical aggregates, compression on the aggregate itself).
• Verify pg_cron failover behavior on a managed primary (cron jobs are per-node and have known footguns on managed Postgres).
• Validate that marketplace_enabled (JSON + regex) translates to a stable, indexed GENERATED ... STORED column on Postgres.
• Decide whether InventoryService weighted full-text scoring is acceptable as tsvector + pg_trgm, or whether to introduce Meilisearch / Typesense for that one path.
• Sanity-check public references for multi-TB single-cluster Postgres on PlanetScale before betting on it for the 10-year coniglio horizon.
• Quantify the cost delta between Helios and the leading PlanetScale Postgres configuration before any committee or budget conversation.
• Decide whether "indefinite" retention on raw track_events is a hard requirement or a "until rollups are queryable" requirement; that single answer changes the size of the long-horizon problem.

Resources

• PlanetScale Postgres GA announcement
• PlanetScale Postgres architecture
• Supported PostgreSQL extensions
• Postgres 18 on PlanetScale
• TimescaleDB continuous aggregates
• Original Migration to SingleStore docs