Relational vs. NoSQL Databases: Why PostgreSQL Is My Default
Choosing a data model by looking at the pain it creates
My default is PostgreSQL, not because relational databases fit every problem, but because the cost of a well-defined model is usually lower than the cost of discovering that the data has no reliable shape.
I have spent most of my career working with relational databases, mainly MySQL and PostgreSQL. More recently, I have used MongoDB more often. That experience has made me more comfortable with document databases, but it has not changed my default: when I start a project without a strong reason to choose otherwise, I reach for PostgreSQL.
This is not a relational-versus-NoSQL benchmark. The more useful question is what kind of pain each model moves into your application, your migrations, and your production operations. Both can work well. Both can become awkward when the shape of the data and the way the product uses it stop matching.
A defined schema is a useful constraint
Relational modeling asks you to describe entities, relationships, constraints, and the shape of important values. That takes thought up front. When a requirement changes, the change may involve a migration, a backfill, and a deployment plan. For a busy table, even a simple schema change deserves care: locks, table size, compatibility between application versions, and rollback strategy all matter.
That friction is real. It is also useful feedback. A schema makes assumptions visible and gives the database a chance to enforce some of them. A foreign key can prevent a reference to a row that does not exist. A NOT NULL constraint can stop incomplete records from entering the system. A unique constraint can protect a business rule even when two application requests race. PostgreSQL's constraint documentation is a good reminder that integrity is not only an application concern.
This is part of why I like relational databases for the same reason I tend to prefer statically typed languages over dynamically typed ones. The analogy is not exact, and Python and JavaScript are not untyped languages. Still, in both cases, making structure explicit earlier can help a team catch invalid assumptions closer to where they are introduced. It does not remove bugs, but it can make some classes of change easier to reason about.
Flexibility does not remove schema decisions
MongoDB gives a document a natural home when the data is usually read and written together. An order with a bounded list of line items, for example, can be represented as one document. This can make common access patterns straightforward and keep related data close together.
The trade-off is that embedding is a decision about ownership and change. If an embedded object is shared by many documents, updating it can mean coordinating multiple copies. If a document grows without a useful bound, it may become hard to update or inefficient to retrieve. If a relationship is referenced instead, the application may need multiple queries or an aggregation pipeline to reconstruct what one SQL join would express directly.
MongoDB is often called schemaless, but the data still has a shape. If the database does not enforce that shape, the application and the team must. Different code paths can write subtly different versions of a document. A field can be absent, null, or present with a different type. An old document may still be in production after the code has moved on. Without validation and a migration policy, flexibility can turn into schema drift.
That flexibility is valuable when records genuinely vary, when a bounded aggregate is the unit of work, or when the access patterns are well understood. It is less valuable when the product is still changing and nobody can confidently say which fields will be needed together six months from now. In that situation, avoiding a migration today may only defer the modeling decision until it is more expensive to make.
The difficult part is often the next query
The first version of a data model tends to reflect the first screens or endpoints. The pain appears when new questions arrive: show all invoices for a customer across several years; find every order containing a product; compare activity across tenants; introduce a report that groups records by a field the original access pattern did not need.
With a relational model, joins and ad hoc queries are often a strength. The schema can evolve to support new relationships, and the database can combine data without the application loading every record and stitching it together. But that convenience does not make every query cheap. Indexes, query plans, data volume, and write contention still need attention.
With a document model, a carefully chosen aggregate can make the common path simple. The risk is coupling the storage shape too tightly to today's reads. A new access pattern can require duplicating data, changing the document boundary, adding an index with a real write and storage cost, or maintaining a separate read model. Denormalization can be the right choice, but it makes update ownership and consistency part of the design rather than something to postpone.
When I evaluate a model, I try to list more than the first few queries. I ask which relationships must remain valid, which values change together, what is likely to be reported on later, and which copies of a fact need to be updated. Those questions reveal the likely maintenance cost better than a generic claim about one database being faster.
PostgreSQL can cover more than relational rows
Choosing PostgreSQL does not mean every value must be split into normalized columns and tables. PostgreSQL's jsonb support can be useful for attributes that are genuinely variable while keeping core entities, relationships, and invariants in a relational model. The JSON types documentation covers indexing and operators as well as storage.
I treat this as a pragmatic boundary, not a way to avoid modeling altogether. If a JSON field becomes central to filtering, reporting, authorization, or relationships, it may deserve a first-class column or table. Keeping the stable core explicit and the variable edge flexible can be a good compromise, provided the team knows which fields are allowed to drift and which are part of the contract.
Be careful where change logic lives
I am cautious about using database-side mechanisms to implement application workflows. In MongoDB, change streams let an application subscribe to persisted changes. In relational databases, triggers can execute database functions when rows are changed. They are different mechanisms, but either can become a hidden path for behavior if used to encode business decisions that are otherwise owned by the application.
The pain is not simply that the code lives in an unusual language. It is that behavior becomes split across layers. A developer following an API request may see an application write and miss the additional effect triggered by a change stream consumer or a database trigger. Tests, deployment order, retries, permissions, and production debugging now need to account for logic in more than one place. A trigger can also run inside the transaction that modified the row, so its failure and latency affect that write; PostgreSQL documents this execution model in its trigger documentation.
There are legitimate database-local uses. Constraints should remain in the database. A trigger may be appropriate for a narrow audit requirement or an invariant that must apply regardless of which client writes the table. The warning is about hiding application workflows there: for example, sending notifications, making decisions about a business process, or silently coordinating several systems. If we choose such a mechanism, it should be deliberate, visible, documented, and tested as part of the system.
For propagating database changes into an event architecture, Kafka Connect and connectors such as Debezium have been more useful in my experience. They can capture row-level changes and publish them to Kafka without putting domain policy into the database. The Debezium PostgreSQL connector, for example, reads changes through PostgreSQL's logical decoding and streams records to Kafka topics.
That stream is change data capture, not automatically a business event. A row update does not necessarily mean OrderApproved or CustomerNotified; consumers still need a clear contract and domain interpretation. I prefer keeping that meaning in application-owned code, while using connectors to move data reliably. CDC also adds operational responsibilities, including replication slots, WAL retention, monitoring, and recovery, so it is not free plumbing.
My default and the exceptions
I would start with PostgreSQL when the domain has meaningful relationships, data integrity matters across those relationships, requirements are still likely to change, or the product will need exploratory queries and reporting. Its explicit model gives me a dependable place to put important invariants, and jsonb leaves room for data that is naturally less structured.
I would consider MongoDB when the domain naturally consists of bounded documents that are read and updated together, variation within those documents is a real property of the data, and the access patterns are understood well enough to choose embedding and references intentionally. I would also make document validation and evolution part of the design from the beginning.
Neither choice eliminates modeling. Relational databases make more of the model explicit in tables, constraints, and migrations. Document databases can make some shapes easier to represent, while asking the application to take more responsibility for relationships and consistency. The right choice is the one whose likely changes and failure modes your team can explain and operate.
Conclusion
My preference for PostgreSQL comes from the kinds of problems I would rather handle deliberately: defining relationships, evolving a schema with migrations, and letting the database enforce invariants. MongoDB has been useful when a document matches the work the application actually performs, but its flexibility does not make modeling or evolution disappear.
The database decision is not a referendum on SQL or NoSQL. It is a decision about where you want the complexity to live. Start with the shape and lifecycle of the data, make the expected pain visible, and choose the model your team can keep coherent as the product grows.
