PostgreSQL in Laravel: What You Need to Know

PostgreSQL is not the most popular DB for Laravel, but it got more attention after they released Laravel Cloud early access with PostgreSQL before MySQL.

They released MySQL in Laravel Cloud very soon after this tweet, but still, PostgreSQL was a "weird" first choice, although State of Laravel 2024 shows it's used only by 30% of Laravel devs:

Also, on the stage of Laracon EU, Jess Archer mentioned they used PostgreSQL for Nightwatch.

So, is PostgreSQL a better choice?

In this long tutorial, I'll try to summarize the cases WHEN you may want to use Postgres. We will dive into the differences in features, performance, and configuration.

We'll look at PostgreSQL from the perspective of a Laravel+MySQL developer, trying to answer these questions:

Why PostgreSQL instead of MySQL
Configuring PostgreSQL for Laravel
Switching existing MySQL DB to PostgreSQL
Is PostgreSQL faster than MySQL? (we'll benchmark)
Raw Query syntax differences
Extra PostgreSQL functions that MySQL doesn't have

WHY You Would Want to Use PostgreSQL?

Let's start with a short TL;DR version. Here are a few potential reasons to start using Postgres over MySQL.

Performance: For some cases, native PostgreSQL functions are faster than in MySQL.
Extra features: better handling of JSON(B) columns, UUIDs, full-text search, and a few more cases.
Specific data types: if you want to use something like arrays, ranges or other PostgreSQL-specific types.
Extensions: PostgreSQL's extension system (like PostGIS for geospatial data) offers more flexibility than MySQL's plugin system.

We will talk about all of them in-depth later in this article, but for now, the general advice:

For the MAJORITY of simple projects, you would NOT feel the difference between MySQL and PostgreSQL, so there is not much benefit to switching.

However, if some of the points above interest you, let's dive deeper into the Postgres world.

NEW Laravel Project: How to Configure PostgreSQL?

Laravel officially supports five database engines:

MariaDB
MySQL
PostgreSQL
SQLite
SQL Server

So there's no need to install extra drivers or packages for PostgreSQL in Laravel.

All you need to do is install PostgreSQL itself and change a few things in the Laravel .env file.

To install PostgreSQL locally, I used DBNgin, which was a few-clicks installation.

If you want to install PostgreSQL on a live server or on a remote VPS, it also shouldn't be a problem: many providers support it alongside MySQL. Here's the screenshot of creating a Digital Ocean droplet via Laravel Forge:

Now, configuration. Here are the typical default values for MySQL, at least for me on Laravel Herd + DBNgin:

.env:

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=project
DB_USERNAME=root
DB_PASSWORD=

Here's my local version of PostgreSQL:

.env

DB_CONNECTION=pgsql
DB_HOST=127.0.0.1
DB_PORT=5432
DB_DATABASE=project
DB_USERNAME=postgres
DB_PASSWORD=

The only real difference is the driver: DB_CONNECTION=mysql vs. DB_CONNECTION=pgsql.

Depending on your connection setup, the other differences in the default port, database name, and username/password may differ.

And then, yeah, just continue creating a PostgreSQL database and working with it in a typical way: Laravel migrations, seeders, Eloquent operations, etc.

Existing Laravel Project: Migrate to PostgreSQL?

Starting with PostgreSQL on a new Laravel project is easy. But migrating the existing MySQL DB to Postgres can be very tricky.

I will be honest: I've never performed such migration myself, but online sources suggest a tool like pgloader for this.

They also emphasize fundamental differences in internal structure, so your exported MySQL DB may NOT import correctly into Postgres.

This is one of the best articles I've found, from 2024: Migrating from MySQL to PostgreSQL

All in all, I would NOT recommend moving from MySQL to PostgreSQL on a live project, especially if the database is big or complex enough—unless, of course, you have good reasons for doing so.

PostgreSQL vs MySQL: Performance Benchmarks

Now, let's try to answer the question, "Is it faster? " Let's start by measuring the speed of SELECT queries on a few big DB tables.

Our database setup is this:

Table bookings with 400K records
Related table apartments with 200K records

I've created identical databases in MySQL and PostgreSQL. Let's run a few Eloquent queries.

Query 1. Get the Latest Bookings.

Let's start with a simple query that contains join, where, and orderBy.

Eloquent Query:

Benchmark::dd(function () {
  Booking::join("apartments", "bookings.apartment_id", "=", "apartments.id")
    ->select("bookings.*", "apartments.name")
    ->where("apartments.capacity_adults", ">", 2)
    ->orderBy("bookings.id", "desc")
    ->limit(10)
    ->get();
}, 10);

We're using Benchmark Laravel class to run 10 iterations, and here's the result:

MySQL: 1.507ms
PostgreSQL: 2.571ms

You could say MySQL is much faster here, but the query time is so small, at 1-2ms, that it's not even a valid comparison case. They're both fast. Let's try something more complex.

Query 2. Get RANDOM Bookings.

Here's the query for our second experiment:

Benchmark::dd(function () {
  Booking::join("apartments", "bookings.apartment_id", "=", "apartments.id")
    ->select("bookings.*", "apartments.name")
    ->inRandomOrder()
    ->take(5)
    ->get();
}, 10);

See that inRandomOrder()? It will change a lot.

MySQL: 504.661ms
PostgreSQL: 189.351ms

See? For this query, PostgreSQL is 2.6x times faster!

Interestingly, the SQL query is a bit different under the hood, as MySQL has a RAND() function, and PostgreSQL has a RANDOM() function.

MySQL:

SELECT `bookings`.*, `apartments`.`name`
FROM `bookings`
INNER JOIN `apartments`
  ON `bookings`.`apartment_id` = `apartments`.`id`
WHERE `bookings`.`deleted_at` IS NULL
ORDER BY RAND()
LIMIT 5

PostgreSQL:

SELECT "bookings".*, "apartments"."name"
FROM "bookings"
INNER JOIN "apartments"
  ON "bookings"."apartment_id" = "apartments"."id"
WHERE "bookings"."deleted_at" IS NULL
ORDER BY RANDOM()
LIMIT 5

I tried to run those SQL queries directly with my TablePlus client outside of Laravel to double-check. And yup, confirmed: