Relational or NoSQL? Choosing a Database From Your System’s Requirements.

A relational database is usually the better choice when relationships, multi-record transactions, constraints, and flexible queries matter. NoSQL is appropriate when the workload naturally fits a specialized model such as key-value lookup, self-contained documents, graph traversal, or large distributed writes. Choose from access patterns and failure tolerance—not data shape alone.

In this article you will learn:

• oHow to evaluate a workload before selecting a database
• How relational, document, key-value, wide-column, and graph databases differ
• Why NoSQL does not mean schema-free
• When polyglot persistence is justified
• Why PostgreSQL fits an early-stage B2B invoicing platform
• Which metrics should trigger future architectural changes

A team designing a new application will eventually face a familiar question:

Should we use a relational database or NoSQL?

The debate is often reduced to a few convenient rules:

• Use SQL when the data is structured.
• Use NoSQL when the schema might change.
• Use PostgreSQL for consistency.
• Use MongoDB when you need flexibility.
• Use NoSQL when the application needs to scale.

Each statement contains a fragment of truth. None is sufficient for making a production database decision.

A relational database can store flexible JSON documents. A document database still has a schema, even when the database does not enforce it. PostgreSQL can support substantial workloads, while a badly designed NoSQL system can develop serious performance and consistency problems.

The useful question is not:

Which database is better?

It is:

Which database model provides the guarantees and access patterns this system requires, at an operational cost the team can sustain?

To answer that, we must first understand what the application is asking its storage layer to protect.

A database choice is a correctness decision

Suppose we are building a multi-tenant B2B invoicing, payments, and dispute-resolution platform.

Businesses use the system to:

• Register clients
• Create and issue invoices
• Add invoice line items
• Record partial and complete payments
• Process refunds
• Upload withholding-tax certificates
• Receive payment-provider webhooks
• Generate financial reports
• Maintain user sessions
• Send receipts and notifications
• Preserve an audit history

This looks like one application, but it contains several distinct data workloads.

The mistake would be choosing a database before examining these differences.

Start with requirements, not database names

Before comparing PostgreSQL, MongoDB, Redis, Cassandra, DynamoDB, or any other product, describe the workload through five questions.

1. What relationships exist?

Consider an invoice:

• It belongs to one business.
• It is issued to one client.
• It contains one or more line items.
• It may receive several payments.
• A payment may produce one or more refunds.
• It may have a withholding-tax certificate.

These relationships are not incidental. They help determine whether the financial record is valid.

A session has a different shape:

1session_id -> user_id, business_id, expiry_time, authorization_version

Its access model is closer to a direct key lookup than a financial report.

2. What must always remain correct?

The invoicing platform contains rules that must remain true regardless of which form, API route, worker, or external integration modifies the data.

For example:

• An invoice cannot reference a nonexistent client.
• An invoice number cannot be repeated within the same business.
• A payment-provider retry cannot create the same payment twice.
• Completed refunds cannot exceed the refundable payment balance.
• Data belonging to one business cannot be linked to another business.
• An issued invoice must preserve the values that were presented to the client.

These rules are invariants. They are more useful for database selection than labels such as “structured” or “flexible.”

3. How will the data be accessed?

A database must support the questions the application needs to ask:

• List overdue invoices for one business.
• Find an invoice by its business-facing invoice number.
• Load an invoice with its client and line items.
• Calculate a client’s outstanding balance.
• Reconcile a payment using its provider reference.
• Retrieve unprocessed webhook receipts.
• Find a session using an opaque token.

The last two queries are substantially different from cross-table financial reporting.

4. How will the data change?

Different records have different write patterns.

Frequently changing data might include:

• Draft invoices
• Session expiry information
• Notification delivery status

Append-heavy data might include:

• Payment events
• Webhook receipts
• Audit events

Other records should become effectively immutable after a business event:

• Issued invoice values
• Completed payments
• Finalized refunds

The write pattern affects transaction design, indexes, retention, partitioning, and storage cost.

5. What failure can the system tolerate?

A dashboard total that is several seconds behind may be acceptable. A duplicate refund is not.

Different workloads can tolerate different combinations of:

• Stale reads
• Delayed processing
• Temporary unavailability
• Duplicate delivery
• Regenerable derived data
• Manual reconciliation

The database decision should reflect the cost of being wrong, not just the expected amount of traffic.

Relational and NoSQL are families of capabilities

The usual comparison is overly broad:

Modern systems do not fit neatly into this table. Relational databases can store JSON, partition large tables, use replicas, and participate in distributed architectures. Some NoSQL databases provide transactions, secondary indexes, schema validation, and strong consistency options.

More importantly, NoSQL is not one model. It includes document, key-value, wide-column, and graph databases, each designed for different access patterns.

Relational databases

Relational databases organize data into tables connected by declared relationships.

An invoicing schema might contain:

1businesses clients invoices invoice_line_items payments refunds withholding_certificates

Primary keys, foreign keys, unique constraints, and check constraints allow the database to reject some invalid states directly.

1CREATE TABLE invoice_line_items ( id UUID PRIMARY KEY, invoice_id UUID NOT NULL REFERENCES invoices(id), description TEXT NOT NULL, quantity NUMERIC(12, 4) NOT NULL CHECK (quantity > 0), unit_price NUMERIC(19, 4) NOT NULL CHECK (unit_price >= 0) );

The value is not simply that the data is stored in rows. The database knows that a line item cannot reference an invoice that does not exist.

Relational databases are particularly effective when:

• The data contains important relationships.
• Several records must change atomically.
• Flexible querying and reporting matter.
• Referential integrity must be enforced.
• Business rules can be represented as constraints.
• Access patterns may evolve over time.

These properties describe most authoritative financial records in the invoicing platform.

Document databases

A document database stores related information in self-contained documents, commonly represented as JSON-like structures.

An invoice might be stored as:

1{ "invoiceId": "inv_1024", "businessId": "biz_18", "client": { "id": "client_42", "name": "Acme Supplies" }, "lineItems": [ { "description": "System implementation", "quantity": 1, "unitPriceMinor": 150000 } ], "currency": "KES", "status": "ISSUED" }

This is attractive because the complete invoice can be retrieved without joining several tables. It may work well when the document is the natural transaction and retrieval boundary.

It also creates questions:

• What happens when the client changes its legal name?
• Which client fields are current values and which are historical snapshots?
• How are concurrent payment and refund operations coordinated?
• How are cross-document relationships protected?
• How does the system aggregate millions of embedded line items?

Document databases work especially well when records are naturally self-contained, nested data is usually retrieved together, and cross-document constraints are limited.

They are not “schema-free.” The schema has moved into application validation, serialization code, indexes, migration jobs, and support for historical document versions.

Key-value stores

A key-value store retrieves a value using a unique key:

1session:7f31ca -> session data

It is optimized for predictable operations such as:

• Get by key
• Set by key
• Delete by key
• Expire after a duration
• Atomically increment a counter

This model is well suited to sessions, rate limits, short-lived tokens, caches, and some coordination primitives.

It is less suitable for a question such as:

Find all overdue invoices for clients who made partial payments during the current quarter.

The difficulty is not necessarily the amount of data. The query does not match the key-value access model.

Wide-column databases

Wide-column databases are often designed around partition keys and predetermined access patterns. They can support large distributed, write-heavy workloads where denormalization is acceptable.

An event workload might be organized as:

1partition key: business_id + event_month clustering key: occurred_at

This makes it efficient to retrieve one business’s events for a particular month. A query that uses a different dimension may require another table, index, or precomputed projection.

Wide-column systems are a strong fit when:

• Write volume is extremely high.
• Data is distributed across many nodes.
• Access patterns are predictable.
• Cross-record transactions are limited.
• Denormalized projections are acceptable.

Graph databases

Graph databases represent entities as nodes and relationships as edges. They are useful when traversal through relationships is the main query:

• Fraud networks
• Social connections
• Recommendation paths
• Organizational relationships
• Dependency graphs

A graph database could help investigate connections between suspicious businesses, payment accounts, users, and devices. That does not make it the natural primary ledger for invoice and payment processing.

Object storage is a different category

Invoice PDFs, tax certificates, and attachments are large binary artifacts. Storing them directly inside the primary relational database would enlarge backups, consume database storage, and reduce the proportion of useful transactional data held in memory.

Object storage is a better fit for the bytes themselves. PostgreSQL can retain:

• The object key
• Business ownership
• Content type
• File size
• Integrity checksum
• Generation status
• Created and retention timestamps

Object-store writes cannot share a local transaction with PostgreSQL. Upload state must therefore be tracked explicitly, with retry and cleanup behavior for incomplete operations. Immutability, retention, and object-lock behavior must also be configured rather than assumed.

System of record versus supporting store

The platform does not need one storage model for every concern.

This is a deliberately small architecture. It uses specialized storage where the workload is genuinely different, while avoiding additional stateful services before they are needed.

Separate durable receipt from eventual processing

Payment webhooks illustrate why one workflow may contain more than one consistency requirement.

The initial provider event should be authenticated, durably recorded, and deduplicated before the system acknowledges it. The resulting payment processing, PDF generation, receipt delivery, dashboard refresh, and search indexing may happen asynchronously.

1flowchart TD A["Payment-provider webhook"] --> B["Verify authenticity"] B --> C["Insert unique receipt"] C --> D["Acknowledge provider"] C --> E["Process asynchronously"] E --> F["Update financial state"]

The boundaries are:

• Webhook ingestion: durable and idempotent
• Webhook processing: asynchronous and retryable
• Financial state change: transactional and strongly protected
• Dashboard projection: allowed to become consistent later

A raw webhook payload may fit naturally in a PostgreSQL JSONB column, while a unique provider-event identifier prevents duplicate receipt records.

1INSERT INTO webhook_receipts ( provider, provider_event_id, payload, received_at ) VALUES ($1, $2, $3::jsonb, NOW()) ON CONFLICT (provider, provider_event_id) DO NOTHING;

Idempotency does not come from hashing a value or checking for it in application code. It comes from defining a stable operation identity and enforcing its uniqueness at the authoritative write boundary.

Financial correctness does not mean serializing every transaction

Payments and refunds need strong transactional protection. That does not mean every operation must automatically use the strictest available isolation level.

The important invariant is:

1Completed refund total <= captured payment amount

Depending on contention and the database design, the system might protect it using:

• A unique constraint
• A row-level lock
• An atomic conditional update
• A serialized aggregate
• A serializable transaction with retry handling

The isolation mechanism is an implementation decision. The invariant is the requirement.

PostgreSQL’s synchronous durability also introduces commit latency compared with an in-memory system. That latency pays for stronger recovery behavior, but actual capacity must be measured against the deployed hardware, index design, transaction size, and contention pattern. Claims such as “this database handles thousands of writes per second” are not architecture; they are hypotheses to benchmark.

Choose the smallest architecture that meets the requirements

Polyglot persistence means using different storage technologies according to workload. It can be valuable, but every additional stateful system requires:

• Deployment and upgrades
• Authentication and network security
• Backups and recovery testing
• Monitoring and capacity planning
• New incident and failure procedures
• Additional developer knowledge
• Data synchronization and replay mechanisms

The question is not:

Could Redis make session lookup faster?

It almost certainly could.

The useful question is:

Are session lookups slow or expensive enough to justify operating Redis and introducing another failure boundary?

The same test applies to brokers, search engines, replicas, analytics databases, and globally distributed databases.

The day-one B2B invoicing architecture

The platform’s initial architecture can remain compact:

1flowchart TD A["Application"] --> B["Managed PostgreSQL"] A --> C["Object storage"] B --> D["Background worker"] D --> A B --> E["Backup and audit archive"]

PostgreSQL holds:

• Businesses, users, and client profiles
• Invoices and line items
• Payments and refunds
• Webhook receipts
• Transactional jobs or outbox records
• Authoritative audit events
• Server-side sessions, if the product requires them

Object storage holds:

• Generated invoice PDFs
• Withholding-tax certificates
• Receipts and supporting attachments

A background worker handles:

• Webhook processing
• PDF generation
• Receipt delivery
• Notifications
• Audit export

When a business operation must produce asynchronous work, the financial mutation and job or outbox record are committed in the same PostgreSQL transaction.

1flowchart TD A["Business transaction"] --> B["Write state and outbox row"] B --> C["Commit"] C --> D["Relay or worker claims job"] D --> E["Perform side effect"]

If the platform later introduces a dedicated broker, the outbox remains useful. It closes the gap between committing authoritative state and publishing a message to a different system.

Sessions require a security decision, not just a latency decision

An early-stage application has at least three reasonable session approaches:

Middleware can perform coarse authentication checks, but sensitive authorization should still be enforced at the trusted server and database boundary. Cached role claims can become stale after a user is deactivated or loses a permission.

Common database-selection myths

“NoSQL databases do not have schemas”

Every persistent model has a schema.

If one invoice stores invoiceId and an integer total while another stores invoice_id and a formatted string, the application must understand both representations. The schema is now enforced through validation, serialization, index definitions, migration jobs, and developer conventions.

Schema flexibility changes where and when validation happens. It does not remove the schema.

“Relational databases cannot scale”

Relational systems can grow through better indexes, connection pooling, larger instances, replicas, partitioning, caching, workload separation, and—when necessary—sharding or distributed relational designs.

The meaningful question is not whether PostgreSQL scales forever. It is whether the expected workload will exceed its practical capacity before the product has evidence and resources to adopt a more complex architecture.

For many B2B systems, poor queries and premature infrastructure create problems before the relational model does.

“NoSQL is always faster”

Performance depends on the operation.

A key-value store is an excellent fit for a direct lookup. A denormalized store answering a new relational question may need several requests, application-side joins, duplicated fields, or precomputed projections.

A database is fast when its model, partitioning, and indexes match the access pattern.

“Joins are inherently slow”

A join is not automatically expensive. Its performance depends on indexes, table sizes, filter selectivity, row counts, memory, and the chosen query plan.

Denormalization can improve a specific read, but it transfers complexity into writes and synchronization. Copying a client’s current address into thousands of editable draft invoices removes one join while creating a mass-update problem.

Historical invoice snapshots are different: deliberate duplication preserves the facts as they existed when the invoice was issued.

“Flexible schemas eliminate migrations”

Old and new document shapes can coexist, but the application must then read several versions, backfill missing fields, rebuild indexes, validate new writes, and eventually retire legacy formats.

Flexible schemas change the migration strategy. They do not eliminate migration work.

“A more advanced system uses more databases”

A system with PostgreSQL, Redis, RabbitMQ, OpenSearch, and ClickHouse is not automatically better engineered.

Each component should solve a specific observed problem. Maturity is demonstrated by justified boundaries, not by the number of technologies deployed.

Define promotion triggers before adding infrastructure

The initial architecture should be allowed to evolve. The team should define what evidence would justify each addition.

The figures below are investigation thresholds, not universal limits. Crossing one should trigger profiling, workload attribution, and testing before an architectural change.

For example, high PostgreSQL CPU alone does not justify Redis. The team must first show that session or rate-limit queries are responsible and that indexes, pooling, query changes, or stateless tokens cannot meet the requirement more simply.

Similarly, introducing a broker does not eliminate the database-to-broker consistency boundary. A transactional outbox or change-data-capture mechanism is still needed so that committed events can be published and replayed reliably.

The architecture decision

For the B2B invoicing platform, the resulting decision is:

Use managed PostgreSQL as the authoritative store for structured operational and financial data. Use object storage for binary artifacts. Begin with a PostgreSQL-backed transactional job or outbox mechanism, and add specialized stateful systems only when measured workload or contractual requirements justify them.

The main decision drivers, in order, are:

1. Transactional correctness
2. Referential integrity
3. Operational simplicity
4. Tenant isolation
5. Auditability
6. Team familiarity
7. Cost
8. Query flexibility
9. Read latency
10. Write throughput
11. Horizontal distribution

PostgreSQL was selected because the financial domain contains strong relationships, multi-record invariants, flexible reporting requirements, and a need for durable transactions. JSONB can accommodate limited variable metadata without moving authoritative records into a separate document database.

Tenant-scoped foreign keys and queries remain mandatory. PostgreSQL Row-Level Security may provide defense in depth when it is configured and tested carefully, but it does not replace application authorization, safe connection context management, or tenant-aware constraints.

The choice has consequences:

• Background work and audit events initially share primary-database resources.
• Large append-heavy tables may later need retention automation or partitioning.
• Stateless sessions trade operational simplicity for less immediate revocation.
• Object-store operations require explicit upload-state recovery.
• A single primary region cannot satisfy every possible global availability target.

These are accepted limitations, not hidden assumptions.

A practical database-selection checklist

Before choosing a database, answer these questions.

Data model

• Which entities exist?
• Which relationships must be enforced?
• Is the natural transaction boundary one record, one aggregate, or several tables?
• Which values are current profiles and which are historical snapshots?

Correctness

• Which invariants must never be violated?
• Which writes must commit atomically?
• Which operations require idempotency?
• What is the consequence of stale or duplicated data?

Access patterns

• Which lookups dominate normal traffic?
• Which joins and aggregations are required?
• Are queries fixed in advance or likely to evolve?
• Which data must support full-text, graph, or analytical queries?

Scale and distribution

• What are the measured or defensible traffic estimates?
• Is the workload read-heavy, write-heavy, or append-heavy?
• Does the system need more than one region?
• What recovery point and recovery time objectives are contractual?

Operations

• Can the team monitor, back up, restore, secure, and upgrade the system?
• How will data be synchronized between stores?
• Can a derived store be rebuilt from the source of truth?
• What new failure boundary does each component introduce?

Evolution

• What metric would demonstrate that the current design is insufficient?
• Can the problem be resolved through query, schema, or index improvements first?
• Which responsibility would move to a new component?
• How would that component fail, recover, and reconcile?

Conclusion

Relational versus NoSQL is not a contest with one universal winner.

A relational database is often the right system of record when relationships, multi-record transactions, constraints, and evolving queries dominate. A document, key-value, wide-column, graph, analytical, or search system becomes valuable when its access model solves a specific workload better enough to justify another operational boundary.

For the B2B invoicing platform, the decision is not “PostgreSQL forever.” It is “PostgreSQL for the authoritative financial core until evidence requires a specialized addition.”

That distinction is what turns a technology preference into an engineering decision.

The next article in this series will examine the guarantee at the center of that decision: what ACID transactions protect, what BASE systems trade differently, and what “consistency” actually means in a production workflow.