Custom Software Development with Next.js and PostgreSQL

Choosing PostgreSQL for a custom build is a decision with specific consequences, and they are worth stating rather than assumed. It is the default on these projects, not a rule, and the reason is that it moves work out of the application and into a place where it cannot be bypassed.

Constraints the application cannot forget

The strongest argument for a relational database in custom software has nothing to do with performance.

An application is written by several people over several years, and not all of them will know the rules. A guarantee enforced in application code holds for the paths somebody remembered - and not for the import script, the console command, the scheduled job, or the admin screen added next spring.

A guarantee enforced in the database holds for all of them. A foreign key means an order cannot reference a customer that does not exist. A unique constraint means two concurrent requests cannot both create the same record, which no amount of checking-then-inserting in application code achieves. A check constraint means a quantity cannot be negative in the row, not merely in the form.

PostgreSQL goes further than most in a way that matters for real applications. An exclusion constraint can refuse a booking whose date range overlaps an existing one for the same resource - a race condition that is genuinely hard to close in application code and trivial to close here. Partial indexes let you index only the rows that matter, so a query over active records does not pay for the archive.

What that removes from the codebase

Each of those constraints is validation code you do not write, do not test and do not have to remember when adding a seventh way to create a record.

It is also the difference between a bug that is impossible and a bug that is unlikely. In a system that handles money, stock or appointments, the distinction is the entire point.

JSONB, and where the line is

Some data genuinely resists a schema. A webhook payload from a third party. A form whose fields the customer configures. Per-tenant settings that differ by tenant.

PostgreSQL handles that honestly: JSONB is stored efficiently, queried with real operators and indexed with GIN, so unstructured data is a first-class choice rather than a workaround.

The line worth holding is that anything you filter, sort, report on, join by or constrain gets a column. The failure pattern is familiar and slow - a JSON column becomes the place new fields go because adding a migration felt heavy, and two years later nobody can answer a question about the data without reading application code.

Search, without a second system

A large share of applications add a search service, a sync pipeline and an operational dependency to do something the database can already do.

Full-text search in PostgreSQL has stemming, ranking and multi-language configurations, and it indexes well. For a few hundred thousand records it is fast, and it has the significant advantage that search results cannot be stale, because there is nothing to synchronise.

Where it runs out is real and worth naming - typo tolerance, large-scale relevance tuning, faceted search over millions of documents. Reaching that point is a measurement, not a prediction, and arriving there later costs far less than operating a search cluster from the first week for a dataset that never needed one.

The one trap, and how it is handled

Every team meets this once. A Next.js application on a serverless runtime opens a database connection per instance, and PostgreSQL will accept a fixed number. Under traffic the application does not get slower - it starts refusing connections, which reads like a database failure and is a configuration mistake.

The answer is a connection pooler between the application and the database, and the part that catches people is that transaction-mode pooling is incompatible with prepared statements unless the client is configured for it. Connection pooling in a serverless runtime is the full explanation.

It is decided in week one on these builds, which is the difference between a known constraint and an incident.

How an engagement runs

In phases, each fixed in writing before it starts and each ending with something deployed that you can use. The first phase takes one workflow end to end rather than the outline of several, because that is what settles the schema against real data.

The database decisions come in that first phase rather than after it. Which constraints are enforced where, what is a column and what is JSONB, how migrations run against a table that will later be large, and what the pooling arrangement is. None of those are expensive to decide early and all of them are expensive to change once there is production data behind them.

What you receive

A deployed application at the end of each phase, the repository with its history intact, and the schema as migrations rather than as a current state - so the database has a history that can be replayed and reviewed.

With it: a written note on the data model and why it is shaped that way, the indexes and what each one is for, the pooling configuration, and the constraints with a line each explaining what they prevent. That last one is the document people actually come back to, because a constraint nobody understands is a constraint somebody eventually removes.

When PostgreSQL is not the answer

If the application is a thin layer over somebody else's API and holds almost no state of its own, a database is a component you are operating for no reason - and a simpler store is the better answer.

If the workload is genuinely a cache or a queue, it should be a cache or a queue. PostgreSQL can do both adequately and neither is what it is for.

And if the data is high-volume append-only measurement - sensor readings, telemetry at scale - a relational table will take a surprising amount of it, but there is a point beyond which a purpose-built store wins. That point should be established by measuring rather than by assuming it has already been reached.