Custom Software Development with Laravel and PostgreSQL

Laravel runs on either, so choosing PostgreSQL is a decision rather than a default - and it is worth being specific about what it buys, because most of the comparisons available are about benchmarks and almost none are about what actually happens on a Thursday afternoon during a deployment.

A failed Laravel migration rolls back

This is the difference that changes how a team behaves, and it is rarely the one anybody mentions.

PostgreSQL applies schema changes transactionally. A migration with six statements that fails on the fourth leaves the database exactly as it was. Nothing partial, nothing to unpick, and the migration can be corrected and run again.

MySQL does not. The first three statements are applied, the migration is recorded as not having run, and somebody now has to establish by hand which half of it exists. In production, under time pressure, with the deployment half-finished.

The consequence is cultural rather than technical. On a system where a failed migration is recoverable, migrations get written as they should be - small, specific, reviewed. On a system where a failed migration is an incident, they get bundled up, deferred, and eventually applied by hand. That is how schemas drift out of version control, and it is the condition we most often find on inherited projects. The related reading is the migration that took the site down.

Constraints Eloquent cannot route around

A rule enforced in a model holds for code that goes through the model.

It does not hold for a mass update. It does not hold for a query builder call that writes to the table directly, which every codebase has somewhere. It does not hold for the console command somebody wrote to fix a data problem at nine in the evening, or for the import that runs once a quarter and was never reviewed.

Enforced below the application, it holds for every one of them. A unique index settles two simultaneous requests for the same record, which checking first never can - the check and the insert are separate statements and the gap between them is where the duplicate appears. A foreign key makes an orphaned row impossible rather than improbable. A check constraint keeps a quantity positive in the row, not merely in whatever form last validated it.

PostgreSQL adds two that matter in practice. An exclusion constraint refuses a booking whose date range overlaps an existing one for the same resource, which closes a race condition that is genuinely hard to close in PHP. And a partial index covers only the rows worth covering, so a query across open orders is not paying the cost of five years of closed ones.

JSONB, and where the column should start

Some data has no schema to design. A payload from a provider who alters it without notice. Per-tenant settings. A form the customer builds themselves.

PostgreSQL handles that properly rather than grudgingly - JSONB is stored compactly, queried with real operators, indexed with GIN, and casts cleanly onto an Eloquent attribute. It is a reasonable design choice, not a retreat.

The decline is gradual and it is always the same story. Adding a key to JSON needs no migration and attracts no review, so that is where fields start going. By the second year nobody can answer a question about the data without reading PHP, and pulling a field back out into a column means writing a migration that parses three years of inconsistently shaped rows.

The test that prevents it: if you will ever filter by it, sort by it, join on it or report on it, it is a column. Everything else can live in JSONB, and should.

Reporting without a second database

A great many Laravel applications pull thousands of rows into PHP and loop over them to produce a figure, because the query to do it in SQL looked unfamiliar.

Window functions make running totals, rankings and period-over-period comparisons a single query. Common table expressions make a multi-step calculation readable rather than nested. Lateral joins handle the top-three-per-customer shape that otherwise becomes an N+1 loop. Each of these replaces a page of application code with a query the database executes in one pass.

Pointed at a read replica, that reporting stays off the transactional workload entirely. A separate warehouse is a real answer when reporting genuinely competes with production or spans several systems - but that should follow a measurement rather than precede one, and most applications reach it later than they expect. Database and Eloquent engineering is where this work sits.

What actually changes in Eloquent

Honest about the friction, because there is some.

String comparison is case-sensitive, so a where on an email or a reference that worked on MySQL may stop matching. LIKE is case-sensitive and ILIKE is the case-insensitive form, which is not portable - so a query written for one database needs attention on the other.

Comparisons are strictly typed. Passing a string where the column is an integer raises an error instead of silently coercing, which is better behaviour and will surface in code that relied on the coercion.

Auto-incrementing keys use sequences, which matters when seeding data with explicit identifiers or copying rows between environments. And anything written as raw SQL is now database-specific, which is an argument for keeping raw SQL in few, named places rather than scattered through models.

None of these are obstacles. All of them are cheaper to know about in week one than to discover in a test suite that passes locally and fails on staging.

The shape of the work on Laravel and PostgreSQL, and where the schema fits in it

In the first phase, deliberately, alongside the workflow it is built around - not in a design document that precedes the code and not in a review that follows it.

Four of them are worth naming because they are the ones that become migrations across every related table if they are deferred. The set of constraints, and what each is there to make impossible. The boundary between a column and JSONB. How a schema change will behave against a table that eventually holds forty million rows, which decides whether an index is created concurrently or with a lock nobody budgeted for. And whether reporting reads from a replica, because retrofitting that means auditing every query for which connection it used.

The rest of the engagement is structured as on the custom software page - phases, each agreed in writing, each finishing with something deployed.

What you get with a Laravel and PostgreSQL build

Beyond the application itself and its repository, the things specific to this choice.

The migrations, which are the database's history and the reason a schema change can be reviewed like any other code. A note on the data model that explains the shape rather than restating it. An index inventory saying which query each index exists for, so that a future developer removing one knows what will slow down. The constraints annotated with what they prevent. And the reporting queries written down and explained - a window function nobody understands is one that gets replaced by a loop in PHP the first time it needs changing.

When MySQL is the better answer

If your hosting, your operations team or your client's policy is built around MySQL, that is a real constraint and not a technical deficiency. A team that knows how to restore, monitor and tune one database is worth more than a marginally better feature set in one nobody has operated.

If an existing application is already on MySQL and healthy, migrating it for the features above is rarely worth the disruption on its own. Do it when there is another reason to touch the data layer.

And if the application is small, read-mostly, and the schema is settled, the difference between the two will not be the thing that decides whether the project succeeds.