Database Selection and Trade-offs Questions

Direct answer

For a regulated financial client, recommend an open-source database engine paired with a paid commercial support contract, rather than either a purely community-supported open-source deployment or a fully proprietary enterprise engine. A regulated environment needs a contractually-backed support SLA and a documented patch-liability chain, and that is purchasable for a mature open-source engine without taking on a proprietary engine's licensing cost and deeper lock-in.

Structured elaboration

Worked example

A hypothetical regulated client running a core-banking-adjacent reporting system: recommend PostgreSQL, encrypted at rest and in transit, with a commercial support subscription whose SLA terms (severity-1 response time, patch delivery commitment) are documented as part of the audit evidence package. Principal risks and mitigations: (1) no single vendor to hold accountable in a fully self-integrated open-source stack, mitigated by the support contract plus a documented internal runbook and a named on-call owner; (2) license drift as the ecosystem's tooling changes hands or terms change, mitigated by tracking the specific license of every component, the core engine, extensions, and backup tooling, in an inventory reviewed at least annually; (3) skills concentration in one or two engineers, mitigated by funding certification and training for a second and third engineer before the first becomes a single point of failure.

Trade-offs & pitfalls

Don't treat open source as automatically cheaper for a regulated client: the audit and compliance overhead of proving controls on a self-assembled stack can exceed a proprietary vendor's bundled compliance tooling, so an honest TCO comparison has to include the cost of building the evidence a regulator will ask for, not just license fees against support fees.

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

Use a hybrid fanout: fanout-on-write (push a new post into every follower's precomputed feed at write time) for the vast majority of accounts, and fanout-on-read (pull the author's recent posts at read time and merge them into the feed) for high-follower accounts. Pure fanout-on-write collapses under the write amplification (one write turning into many downstream writes, here one post turning into one inbox write per follower) of a handful of extremely popular accounts, and pure fanout-on-read makes every ordinary read expensive by fanning out a query per followed account at read time.

Structured elaboration

Query pattern to storage mapping.

• Post storage (durable, high write volume, simple key lookup by post id): a wide-column store (Cassandra/Scylla) with replication factor (RF) of 3, a typical durability/availability choice giving each row three replicas so the loss of one node does not lose data, because posts are write-once, read-many and don't need relational joins.
• Precomputed per-user feed ("inbox"): a key-value cache (Redis, or a wide-column "feed" table keyed by user id holding a sorted list of post ids), because a feed read needs to be a single fast lookup by user id, not a fan-out query computed at read time.
• Full-text search over posts: a dedicated search engine (Elasticsearch/OpenSearch), a genuinely different query shape ("rank posts matching these words") that belongs in neither of the above.

Fanout math (worked). 50,000 posts/min is about 833 posts/sec:

posts_per_min = 50_000
posts_per_sec = posts_per_min / 60
avg_followers = 200  # illustrative average, for order-of-magnitude reasoning
naive_fanout_writes_per_sec = posts_per_sec * avg_followers
celeb_followers = 5_000_000
celeb_single_post_fanout = celeb_followers  # one post, fanned out synchronously
print(f"posts/sec: {posts_per_sec:.1f}")
print(f"naive fanout-on-write at {avg_followers} avg followers: {naive_fanout_writes_per_sec:,.0f} inbox writes/sec")
print(f"a single post from a {celeb_followers:,}-follower account: {celeb_single_post_fanout:,} inbox writes for ONE post")

posts/sec: 833.3
naive fanout-on-write at 200 avg followers: 166,667 inbox writes/sec
a single post from a 5,000,000-follower account: 5,000,000 inbox writes for ONE post

The steady-state average (about 167,000 inbox writes/sec at this illustrative follower count) is a large but achievable rate for a wide-column store or Redis cluster built for high write throughput. The problem is the TAIL: one post from a 5-million-follower account would generate 5 million inbox writes in one event, dwarfing the steady-state load. For accounts past a monitored follower-count threshold, don't fan out on write; merge their few recent posts into a requesting user's feed at READ time instead, cheap because there are few of them and they are easy to cache. This hybrid is the standard, well-documented pattern large-scale feed systems use for exactly this problem.

Consistency and caching. Eventual consistency is explicitly acceptable per the requirements, so post-create can return immediately while fanout happens asynchronously in a background worker, and Redis in front of the wide-column feed store absorbs the read-hot top-of-feed page (well inside the p95 under 100ms budget; a cache miss falling through to the wide-column store is still fast, single-digit to tens of milliseconds).

flowchart TD
    W[New post] --> C[(Cassandra: posts, RF=3)]
    W --> F{Author follower count}
    F -- normal account --> FW[Async fanout worker]
    FW --> Redis[(Redis / wide-column feed store: per-user inbox)]
    F -- celebrity account --> Skip[Skip fanout-on-write]
    W --> Search[(OpenSearch: async index)]
    Reader[Feed read] --> Redis
    Reader --> Merge[Merge in celebrity posts at read time]

Trade-offs & pitfalls

The celebrity threshold needs an explicit, monitored cutover, a follower-count boundary that routes an account from fanout-on-write to fanout-on-read, or the hot-write problem reappears in production the first time an ordinary user suddenly goes viral. Full-text search staleness (posts indexed asynchronously) means a just-posted message might not be searchable for a few seconds, acceptable under the stated eventual-consistency tolerance, but worth stating explicitly rather than assuming.

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

For scenario A, ad hoc analytics and complex queries, choose PostgreSQL: its SQL vocabulary for joins, aggregates, and window functions, plus general-purpose secondary indexing, directly serves "ask a new question of the data without redesigning the schema." For scenario B, extremely high write throughput with simple, known access patterns, choose DynamoDB: its partition-based architecture scales write throughput close to linearly by spreading load across partitions, without the single-primary ceiling a relational database eventually hits, and "simple access patterns" is precisely the assumption DynamoDB's key design is built around.

Structured elaboration

Recommendation with rationale. Scenario A needs query flexibility more than raw write scale. Analysts asking new questions cannot design a key-value schema for a question that does not exist yet, so PostgreSQL, ideally with a dedicated read replica so ad hoc queries do not compete with production writes, fits directly; if analytics volume outgrows what that replica comfortably serves, the next step is exporting into a columnar warehouse, not replacing the log store itself.

Scenario B needs write scaling more than query flexibility. A well-chosen partition key, for example the identifier the events are naturally grouped by, gives near-linear write scaling without a team manually sharding a relational primary, and "simple access patterns" is the exact condition DynamoDB's design assumes rather than fights.

Worked example

Suppose scenario B's target is 50,000 writes per second spread across 100,000 distinct users, a reasonable partition key candidate. That averages 50,000 / 100,000 = 0.5 writes per second per user, far below a single partition's verified ceiling of roughly 1,000 write operations per second for a small item, so a naive per-user partition key absorbs this comfortably. The same 50,000 writes per second against a single relational primary would need careful connection pooling, batched commits, and eventually table partitioning, none of which DynamoDB requires the team to build by hand.

Trade-offs and pitfalls

A pitfall is choosing DynamoDB for scenario A because "it scales." Write scale was never the bottleneck in an ad hoc analytics scenario; query expressiveness was, and a partition-and-key-value model does not fix that.

A pitfall for scenario B is choosing PostgreSQL and assuming sharding can be bolted on later without a real migration project. Relational horizontal write scaling is genuine engineering work, not a configuration flag.

What would flip either recommendation: if scenario A's "ad hoc" queries stabilize into a small, known set, for example always "events for this user in this date range," DynamoDB with a well-chosen sort key handles that fine and more cheaply at scale than PostgreSQL. If scenario B's throughput target turns out to be moderate, a few thousand writes per second, a well-tuned PostgreSQL primary handles it without adopting DynamoDB's operational model at all.