SQL or NoSQL is the wrong first question

1. Describe the work before naming a product

SQL describes a query language; NoSQL groups several different database designs. Neither label tells you whether an order can oversell stock, how a promotion burst behaves, or who restores the service after a mistake. Start with operations and consequences.

Consider a hypothetical SaaS orders service for independent retailers. Its planning assumptions are 20,000 orders per day, promotion peaks of 40 new orders and 300 order lookups per second, and 50 GB growing to 150 GB within a year. The target is a 95th-percentile database time below 200 milliseconds for creating an order, excluding payment-provider time. These are illustrative requirements, not measured results or database capacity claims.

The buyer must immediately see a successful order. Reports may lag by five minutes. Stock must never fall below zero. Those requirements are more useful than a projected user count because they describe actual database work.

2. Find the relationships and update boundaries

List the frequent reads: fetch one order, list a customer's recent orders, locate unfulfilled orders for a retailer, and summarize sales by product. Record filter fields, sorting, result sizes, and which requests need pagination. A plausible schema and matching indexes belong in the evaluation.

In this example, an order, its line items, and a stock reservation must succeed or fail together. Customers and products have identities reused across many orders. That makes related tables a practical starting point. Flexible product attributes can live alongside them: PostgreSQL supports JSON and indexable JSONB, so occasional shape variation does not by itself require a document database.

Payment confirmation is an external operation. A database transaction cannot automatically roll back a payment provider. Use an explicit order state, idempotent payment requests, and reconciliation for uncertain outcomes.

3. Give each candidate a specific job

Managed PostgreSQL is the first candidate for this service because shared entities, relational queries, and changes spanning several records are central. That is a workload judgment to test, not a universal ranking.

• SQLite deserves consideration for an embedded tool or a service with local storage and short, queueable writes. It allows one writer at a time per database file. Test contention; do not assume that either a website or a small team rules it out.
• A document database such as MongoDB becomes more attractive when most reads and updates address one naturally bounded document. MongoDB has atomic single-document writes and also supports multi-document transactions. Repeated cross-document coordination still deserves deliberate modeling.
• An analytical engine such as ClickHouse is a candidate for large scans and grouped reporting. It uses SQL too. Add it when measured reporting needs justify a separate copy, synchronization, and another service to operate; ordinary reports may fit the primary database initially.

4. Specify what must remain true under concurrency

A transaction groups changes, but the word alone does not settle concurrent behavior. Imagine two checkouts competing for the last item. Both can read a quantity of one before either writes. The application needs a conditional stock update, appropriate locking, or an isolation strategy that detects the conflict, with the order and reservation in the same transaction.

PostgreSQL defaults to Read Committed isolation; separate statements can observe different committed snapshots. Serializable isolation provides stronger guarantees but applications must handle serialization failures by retrying the transaction. Constraints and idempotency keys also address different problems: preserve valid data and recognize repeated requests.

Test two simultaneous purchases, duplicate submissions, a timeout after commit, and a process failure mid-checkout. Verify the resulting records as well as the response time. An attractive average latency cannot compensate for accepting the same order twice.

5. Separate commit success from replica freshness

A successful transaction does not mean every replica can immediately return its changes. PostgreSQL streaming replication is asynchronous by default. A confirmation page routed to a lagging replica can therefore appear to lose the order temporarily. Read the confirmation from the writer, or implement a verified mechanism that waits for the required replica state.

Synchronous replication settings have distinct guarantees. In PostgreSQL, remote_apply waits for the selected synchronous standby or standbys to replay the transaction, making it visible there; other acknowledgment modes do not imply that same visibility. This can increase commit waiting and affect availability.

For MongoDB, inspect read preference, read concern, write concern, and session behavior together. Multi-document transaction support does not make every secondary read current. Let the five-minute reporting allowance guide reporting reads without weakening checkout requirements.

6. Decide where writes originate and conflicts meet

Assume the example starts with one writing region and customers in two continents. Measure the whole request across that distance. Placing a read replica closer to customers does not create an independent local writer: PostgreSQL's primary/standby arrangement and an individual MongoDB replica set route writes through a primary.

If both regions must accept purchases during a network partition, revisit the design. Can each retailer belong to one writing region? Can inventory be allocated regionally? Or must every purchase coordinate over shared stock? Document the accepted conflict and outage behavior before comparing distributed products. A regional storefront requirement and a requirement for concurrent regional writes are different constraints.

7. Price the service and the work around it

Suppose the hypothetical team has two application engineers, no dedicated database operator, and a $600 monthly infrastructure ceiling. Managed hosting is worth evaluating because it transfers some maintenance responsibilities. It does not remove schema design, query tuning, access configuration, incident decisions, or restore validation. Amazon RDS explicitly leaves query tuning with the customer.

Compare a twelve-month estimate covering compute, storage growth, backups, standby capacity, network transfer, monitoring, support, and engineering hours. Treat introductory credits separately. A self-managed option may be appropriate when the team can perform upgrades, monitor replication, restore backups, and cover incidents; its smaller hosting invoice is only one input.

Set recovery targets explicitly. An illustrative requirement might allow five minutes of data loss and a one-hour recovery after a regional disaster. Validate that target with a restore exercise; replicas and provider availability promises are not substitutes for recoverable backups.

8. Make a testable decision with an exit condition

Fill in this brief, then prototype the order transaction and three busiest reads with representative data. Include one high-traffic retailer so averages do not hide contention. Record latency percentiles, errors, retry rates, resource usage, and recovery results against the brief. No performance measurements are claimed in this guide.

Reconsider PostgreSQL if the workload becomes mainly isolated documents, deployment must be embedded, analytics dominates, regional writing requirements change, or realistic tests miss the budget and service targets after reasonable tuning. Keep the decision and its revision triggers together. Official source documentation was reviewed on 2026-09-07.

WORKLOAD BRIEF
Core operations and example queries: [fill in]
Records that change together: [fill in]
Rules that must never break: [fill in]
Peak reads/writes; burst duration: [fill in]
Largest tenant; present/year-one data size: [fill in]
Latency percentile and measurement boundary: [fill in]
Read-after-write needs; allowed reporting lag: [fill in]
Writing regions; partition behavior: [fill in]
Acceptable data loss and recovery time: [fill in]
Monthly infrastructure and staff-time budget: [fill in]
Operations owner; restore-test date: [fill in]
Candidate; pass criteria; revision triggers: [fill in]