Database Selection and Trade-offs Questions

Direct answer

For a large e-commerce business, the default recommendation is a managed Elasticsearch or OpenSearch deployment, not a from-scratch custom search engine and, in most cases, not a pure software-as-a-service (SaaS) product like Algolia. E-commerce search at real scale needs enough relevance customization (custom ranking signals, merchandising rules, complex faceting) that a SaaS product's more bounded configuration surface becomes limiting, while building a search engine from scratch means re-solving a genuinely hard distributed-systems problem the industry already solved well, for no real competitive advantage. Algolia stays the right call specifically when time-to-market and near-zero search-infrastructure operations matter more than deep relevance control, most often for a smaller catalog or a team with no search specialization at all.

Structured elaboration

Worked example

Consider a catalog on the order of a few million stock keeping units (SKUs) that needs custom ranking (boosting by margin, inventory availability, and personalization) and complex multi-facet filtering. Per Algolia's current published pricing, its free tier (Algolia has since retired the earlier "Build" tier name) caps out at 10,000 search requests per month and 50,000 records, useful only for a prototype; a catalog of a few million SKUs blows past that record allotment by a factor of 60 or more. Real e-commerce traffic at this catalog size lands on Algolia's pay-as-you-go Grow tier ($0.50 per additional 1,000 search requests beyond the 10,000 included, $0.40 per additional 1,000 records beyond the 100,000 included) or, past a scale Algolia no longer publishes a fixed price for, its custom-quoted Elevate tier. Using Algolia's own current per-unit Grow-tier rates as a back-of-envelope estimate I computed myself, not a vendor-published total: a 3,000,000-record catalog serving 5,000,000 search requests a month works out to roughly $1,160/month in record charges plus roughly $2,495/month in request charges, about $3,655/month combined, before any Elevate-tier volume discount or custom quote even enters the picture. At that spend level, a managed Elasticsearch or OpenSearch cluster sized for the same catalog and traffic typically gives deeper ranking control for comparable or lower ongoing cost, which is what tips the recommendation toward managed Elasticsearch or OpenSearch rather than SaaS for a business at this scale, with "build from scratch" staying off the table entirely unless search relevance is literally the company's core product rather than a supporting feature of it.

Trade-offs & pitfalls

The most common wrong turn is picking a SaaS product early for speed, succeeding, and only later discovering its relevance ceiling once the business has genuinely complex merchandising rules; migrating off a SaaS index to self-run search then means rebuilding ranking logic from a different mental model, not just moving data across. The opposite failure mode is also common: teams that build custom search "because ours is special" almost always underestimate the ongoing cost of keeping tokenization, ranking, and relevance competitive with what an off-the-shelf engine already solved years ago.

Direct answer

An Architecture Decision Record (ADR: a short, versioned document that captures one architectural decision, the reasoning behind it, and what it costs) for this choice should recommend a managed database-as-a-service (DBaaS: a cloud provider that operates the engine, patching, backups, and failover (automatically switching to a standby system if the primary one fails) for you) as the default for a small or midsize business (SMB), because the deciding factor for most SMB clients isn't the database technology, it's whether anyone on staff can be the on-call database administrator, and for a small team the honest answer is usually no.

Structured elaboration

An ADR earns its usefulness from its structure, not its length:

• Context: the situation forcing a decision now (a new service being stood up, a growth trigger, a compliance requirement), described in plain terms a non-technical stakeholder could follow.
• Decision drivers: the small list of things that actually determine the answer (team size and skills, budget, expected growth, uptime requirements), ranked, not just listed.
• Options considered: every option that was genuinely evaluated, including the ones not chosen. An ADR missing rejected options gives a future reviewer nothing to push back on when the decision needs revisiting.
• Trade-offs: what each option costs and gives up, ideally as a short table, so the comparison survives being read without the surrounding prose.
• Decision: the choice, stated as one sentence, with no hedging.
• Consequences: what becomes easier and what becomes harder as a direct result, including things the team is explicitly accepting as a cost.
• Migration notes: what would have to happen to change this decision later, and how expensive that would be, so a future team isn't rediscovering the exit cost from scratch.

Worked example

ADR-014: Managed PostgreSQL vs Self-Hosted PostgreSQL for the Order Management Service

Status: Accepted

Context: We are standing up a new order management service for a 12-person e-commerce SMB client with no dedicated infrastructure or database operations staff. The service needs a relational database for orders, inventory, and payments records.

Decision drivers: (1) no in-house database operations capacity, (2) predictable monthly cost more valuable than the lowest possible infrastructure price, (3) the client's engineers are comfortable with SQL but have never run production failover or point-in-time recovery.

Options considered: (A) a managed PostgreSQL service (automated backups, patching, and failover); (B) self-hosted PostgreSQL on virtual machines, operated by the client's own engineers.

Trade-offs:

Decision: Adopt Option A, a managed PostgreSQL service.

Consequences: The client gives up some configuration flexibility and pays a service premium, in exchange for automated backups and failover the client's own team is not currently equipped to build or operate. On-call burden for database availability moves to the vendor.

Migration notes: If the client later hires dedicated operations staff or hits a feature the managed tier doesn't support, the standard PostgreSQL wire protocol and dump/restore tooling keep a future move to self-hosted realistic; budget for a planned migration project rather than an emergency one if that day comes.

Trade-offs & pitfalls

Self-hosted is still the right call when a specific, named requirement rules out every managed option, for example a compliance mandate requiring physical control of the hardware that no vendor will contractually satisfy, or a need for a database extension or version no managed tier offers. The most common mistake a junior architect makes here is writing an ADR that states only the chosen option and skips "options considered" entirely, which reads as a justification written after the fact rather than a decision made with the alternatives genuinely on the table.

Direct answer

Recommend decoupling ingestion from storage with a message queue or stream (for example Kafka) plus batched writes, before reaching for either a NoSQL migration or a relational resharding project. A queue absorbs the SPIKE cheaply, since queues are built to buffer a bursty producer against a slower or rate-limited consumer, letting the relational database keep receiving writes at a smoothed, steady rate it can already handle. This fixes the ingestion-spike symptom directly, without the larger, harder-to-reverse costs of rewriting the data layer or resharding a live relational database.

Structured elaboration

Why this problem statement points at ingestion PATTERN, not raw capacity. "Struggles with ingestion spikes and storage cost" describes a system whose STEADY-STATE write rate the relational database already handles fine, but whose BURSTS exceed what a fixed-capacity instance can absorb in the moment, plus a separate storage-cost problem from data volume. That is a different diagnosis than a genuinely sustained high-throughput write rate that needs a fundamentally different storage engine, and it changes which fix is proportionate.

Option 1: write-optimized NoSQL (Cassandra/DynamoDB). Fixes burst absorption directly, since a log-structured merge-tree or auto-partitioned write path is built for exactly this. It is also the highest-cost, highest-risk option here: a full data-layer migration, an application rewrite to a different query model (likely losing ad-hoc SQL analytics), and a genuinely new operational skill set, justified only if the STEADY-STATE, not just peak, write rate has also outgrown what relational can sustainably handle, which this problem statement does not establish.

Option 2: sharding the relational database. Fixes a raw throughput ceiling by spreading writes across multiple instances, but does not fix the SPIKE problem on its own, since a single shard can still be hit by a spike disproportionate to its slice of traffic, the same hot-partition failure mode seen throughout this domain. It also adds real engineering cost: choosing a shard key, handling cross-shard queries, and bearing real rebalancing risk if that key choice turns out wrong later; of the three options, it is the hardest to reverse once live traffic depends on the sharding scheme.

Option 3: batching and streaming with message queues (the recommended default). A queue sits between producers and the relational database; producers publish at whatever bursty rate they generate data, and a consumer drains the queue and writes to the database in BATCHES at a smoothed, steady rate the instance was already provisioned for. This targets "struggles with ingestion spikes" directly, the spike now hits the queue, built to buffer, not the database, without touching the database's schema, query model, or ad-hoc analytics capability at all.

Data modeling, consistency, and query patterns. Data modeling stays largely unchanged from the current relational schema; the only new concept is the message schema published to the queue, which should be versioned independently of the database schema so producers and the batch consumer can evolve without a synchronized deploy. Consistency changes in one real, sometimes-overlooked way: writes become asynchronous relative to the producer, a publish is no longer immediately visible as a committed database row, so anything that previously relied on "write, then immediately read your own write" needs to either read from the queue or a cache instead, or accept a short visibility delay, typically the batch interval, measured in seconds. Query patterns are entirely unchanged, since the relational schema and indexes don't need to change; ad-hoc analytics keeps working exactly as before.

Storage cost. Batching also directly addresses the storage-cost half of the problem: writing in batches allows compression or deduplication before insert, and often lets cold or rarely queried data be routed to cheaper storage, for example partitioning older data into a lake tier, as part of the same pipeline rather than as a separate project.

flowchart LR
    P[Bursty producers] --> Q[[Kafka / message queue]]
    Q --> C[Batch consumer: smoothed write rate]
    C --> D[(Relational DB: unchanged schema)]
    D --> L[(Cold-data lake tier: older partitions)]

Worked example

Framed generically, since no concrete rate is given in the question: if baseline steady-state ingestion is some rate X events/sec and spikes to 10X for short windows, a queue with a consumer draining at a rate close to X turns the 10X spike into a temporary increase in QUEUE DEPTH, messages waiting briefly, none dropped or rejected, instead of a database overload. The queue's retention window only needs to be long enough to hold the spike's excess volume (spike duration multiplied by the gap between spike rate and drain rate) without the consumer permanently falling behind, a capacity-planning calculation done against the queue, not the database.

Trade-offs & pitfalls

Batching introduces a genuine consistency and freshness trade-off, writes are no longer instantly visible, that needs to be surfaced to whoever depends on "real-time" reads, not discovered later. Jumping straight to a NoSQL migration or a resharding project to solve what turns out to be a BURSTINESS problem, not a steady-state throughput problem, is the expensive overcorrection this answer is built to head off; reach for those only once queue-based buffering is confirmed insufficient, meaning the steady-state rate itself has grown past what relational can sustain, or ad-hoc SQL analytics is genuinely no longer needed.

Direct answer

Default to a managed database service (for example Amazon RDS or Aurora, DynamoDB, or MongoDB Atlas) unless there is a specific, named reason to self-host, because a managed offering removes an entire category of operational risk (patching, failover (automatically promoting a healthy replica to take over when the primary database fails), backup verification, version upgrades) that otherwise falls on the team's on-call rotation. The cases for self-managing are real but narrow, and should be named explicitly rather than assumed.

Structured elaboration

One example where self-managed is preferable: a regulated environment with a specific data-residency or network-isolation requirement (for example an air-gapped network) that no managed provider's available regions or deployment topology satisfies, or a team that depends on a specific database extension, fork, or build flag that isn't on the managed service's supported extension list. In both cases, the constraint isn't "we could save money," it's "the managed product literally cannot do the thing we need," which is a fundamentally different, much stronger justification than a cost argument.

Worked example

Rather than inventing a specific dollar figure I can't verify for either path, the honest way to reason about the cost axis is this: the fully-loaded cost of one senior engineer's on-call time is typically a substantial multiple of the price premium a managed service charges over raw compute, for a small-to-mid-size production database. That's why "we'll save money by self-hosting" deserves real skepticism as a stated justification unless the team can name concrete, recurring engineering hours it expects to save, not just point at the sticker-price difference between the managed tier and raw infrastructure cost.

Trade-offs & pitfalls

The biggest pitfall is treating "managed versus self-managed" as a one-time, permanent decision. Teams that grow real database specialization sometimes migrate FROM managed TO self-managed later, specifically to unlock a feature or a cost structure the managed tier doesn't offer at their scale, and that migration itself carries genuine risk (cutover planning, data-migration validation) that needs to be weighed honestly against simply staying managed and working around the limitation.

Direct answer

For a payments system, split the workload across two purpose-built platforms and connect them with a change stream, rather than forcing one database to be good at everything. Use a strongly consistent relational database (or a distributed SQL system like Google Cloud Spanner) as the single system of record for the money-moving path, stream every committed change out via change data capture (CDC, a mechanism that reads a database's internal transaction log and turns each row change into an event), and land that stream in a columnar analytics warehouse for fraud scoring and monthly reconciliation. The OLTP database (online transaction processing: a system optimized for many small, concurrent reads/writes with strict correctness) never talks directly to the analytics side in the request path, so a slow fraud query or a big reconciliation scan can never add latency to a customer's payment.

Architecture and data flow

Component 1: system of record (OLTP). A relational database (PostgreSQL/MySQL-compatible, or a managed option like Amazon Aurora or Cloud Spanner) holds accounts, ledger entries, and transaction state. Every payment is a single ACID transaction (atomicity, consistency, isolation, durability: the guarantee that a transaction either fully applies or not at all, leaves data valid, is isolated from concurrent transactions, and survives a crash once committed), run at SERIALIZABLE or the engine's strictest practical isolation level, so two concurrent debits against the same balance can never both succeed and double-spend the account. This is the only place writes to money happen.

Component 2: change stream. A CDC connector (Debezium is the common open-source choice) tails the OLTP database's write-ahead log and publishes one event per committed row change onto a durable log such as Kafka. This decouples "the transaction committed" from "everyone who needs to know about it finds out," so downstream consumers can be slow, restarted, or temporarily down without blocking payments.

Component 3: real-time fraud scoring. A stream processor (Kafka Streams or Flink) consumes the change stream, joins it against recent history and device/session signals, and scores each transaction. High-risk transactions write a hold flag back to the OLTP system through the normal transactional API, idempotently keyed by transaction ID so a retried or duplicated event cannot apply the same hold twice.

Component 4: analytics and reconciliation. The same change stream is sinked into a columnar warehouse (Snowflake, BigQuery, or Redshift). Monthly reconciliation jobs run here: compare internal ledger totals against external bank/processor settlement files, using checksums and row counts per batch to prove nothing was lost or double-counted in transit. This workload can run full-table scans and heavy joins without any risk to the OLTP system, because it reads from a copy, not the source.

flowchart LR
    Client[Payment client] --> API[Payment API service]
    API -->|write ledger row, serializable txn| OLTP[(Relational OLTP\nsystem of record)]
    OLTP -->|CDC stream| Bus[[Kafka change stream]]
    Bus --> Fraud[Stream fraud scorer]
    Fraud -->|hold flag, idempotent write-back| OLTP
    Bus --> WH[(Columnar warehouse\nreconciliation and analytics)]
    Bus --> Audit[(Append-only audit log)]

Worked example: why single-platform doesn't fit

Say the service processes 500 transactions per second (TPS) at peak, and a monthly reconciliation job needs to scan 90 days of transaction history (roughly 500 * 86,400 * 90 ≈ 3.9 billion rows) and join it against a settlement file. Running that scan directly against the OLTP primary would compete for the same buffer pool (the in-memory area where the database engine caches recently used data pages so it doesn't have to re-read them from disk) and lock manager that the live payment path depends on; even a well-indexed OLAP-style (online analytical processing: the counterpart to OLTP, large scans and aggregations for reporting rather than many small transactional writes) query with a large sort or hash join can hold shared resources long enough to add tail latency to concurrent writes. Separating the two means the OLTP database is sized and tuned purely for point lookups and small transactional writes (small working set, aggressive caching, low lock contention), while the warehouse is sized purely for sequential scans and columnar compression over the same 3.9 billion rows, with no shared contention between them.

Trade-offs and pitfalls

• Consistency boundary matters. The fraud-hold write-back has to be idempotent and the CDC pipeline has to guarantee at-least-once delivery with a de-duplication key downstream, or a redelivered event could double-apply a hold or a reversal. "Exactly-once" in practice means at-least-once delivery plus an idempotent consumer, not a magic delivery guarantee.
• Operational cost. This design adds CDC, a message bus, a stream processor, and a warehouse: four extra systems to monitor, upgrade, and page on. That overhead is justified here because payments cannot tolerate the alternative (a single database straining to be both a fast ledger and a fraud/analytics engine), but it would be over-engineering for a system without the same consistency and scale pressure.
• Reconciliation needs a proof, not just a copy. A common mistake is treating the warehouse copy as automatically correct. Reconciliation should independently verify completeness (row counts, checksums, watermarking on the CDC stream) rather than assuming replication never drops or duplicates an event.
• **A single globally-consistent NewSQL system (NewSQL: a class of databases that aim to keep relational ACID guarantees while scaling out like a distributed system, e.g. Spanner or CockroachDB) can replace the OLTP component if the team wants one fewer moving part for the transactional side, but it does not remove the need for a separate analytics store: OLAP-shaped queries still shouldn't run against the transactional cluster.