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

Notice: the full tutorial is for Premium members, but I've made the free version long enough to explain the main things about PostgreSQL.

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:

  • MySQL: 491ms
  • PostgreSQL: 186ms

Wow. Impressive. So, PostgreSQL's RANDOM() is 2-3x faster than MySQL's RAND().

Let's try one more query using specific DB functions.

Query 3. Group by Month

Let's calculate revenue grouped by month. It includes raw queries with different syntaxes:

  • MySQL has DB::raw("MONTH(created_at)")
  • PostgreSQL has DB::raw("EXTRACT (MONTH FROM created_at)"

MySQL

Benchmark::dd(function () {
  \App\Models\Booking::selectRaw(
    "COUNT(*) as count, SUM(total_price) as revenue"
  )
  ->whereYear("created_at", "2024")
  ->groupBy(DB::raw("MONTH(created_at)"))
  ->get();
}, 10);
Benchmark::dd(function () {
  \App\Models\Booking::selectRaw(
    "COUNT(*) as count, SUM(total_price) as revenue"
  )
  ->whereYear("created_at", "2024")
  ->groupBy(DB::raw("EXTRACT (MONTH FROM created_at)"))
  ->get();
}, 10);

Here's the result:

  • MySQL: 59.154ms
  • PostgreSQL: 28.256ms

So, PostgreSQL month extraction from date is 2x faster!

Query 4. Insert Performance

Let's insert a lot of data and see how both databases perform.

In the project above, I had a task to seed 400K bookings. Here's the code of the seeder:

database/seeders/BookingSeeder.php:

public function run(): void
{
    $users = User::where('role_id', Role::ROLE_USER)->pluck('id');
    $apartmentMin = Apartment::min('id');
    $apartmentMax = Apartment::max('id');
 
    $bookings = [];
    for ($i = 1; $i <= 400_000; $i++) {
        $startDate = now()->addDays(rand(1, 200));
        $bookings[] = [
            'apartment_id' => rand($apartmentMin, $apartmentMax),
            'start_date' => $startDate->toDateTimeString(),
            'end_date' => $startDate->addDays(rand(2,7))->toDateTimeString(),
            'guests_adults' => rand(1, 5),
            'guests_children' => rand(1, 5),
            'total_price' => rand(100, 2000),
            'user_id' => $users->random(),
            'rating' => null,
        ];
 
        if ($i % 500 == 0 || $i == $withoutRatings) {
            Booking::insert($bookings);
            $bookings = [];
        }
    }
}

The most important line is `Booking::insert($bookings) ', which slices the entire list of 400K records and inserts 500 rows into the database simultaneously.

So, I've run php artisan migrate:fresh --seed three times on both MySQL and PostgreSQL.

Three attempts on MySQL:

  • 10,158.65 ms
  • 10,875.25 ms
  • 9,783.95 ms

Three attempts on PostgreSQL:

  • 10,613.81 ms
  • 11,189.84 ms
  • 10,894.92 ms

So, for this particular experiment, bulk INSERT in MySQL seems a little bit faster by 5% or so. But I wouldn't call it a significant difference that you would feel in your projects. I would call it a tie here.

Benchmark Conclusion

All in all, the "regular" Eloquent queries should perform similarly between MySQL and PostgreSQL, but some specific database functions run faster in PostgreSQL if you use raw queries.

Speaking of raw queries...

Raw SQL Queries: Syntax Differences

You won't feel the differences between MySQL and PostgreSQL if you use only Eloquent queries. But if you start using raw queries, there are quite a few differences in syntax.

I will show you probably the most widely used ones:

1. YEAR() / MONTH() / DAY() Functions

MySQL:

SELECT
    YEAR(birth_date) AS birth_year,
    MONTH(birth_date) AS birth_month,
    DAY(birth_date) AS birth_day
FROM
    employees;

PostgreSQL needs extract:

SELECT
    EXTRACT (YEAR FROM birth_date) AS birth_year,
    EXTRACT (MONTH FROM birth_date) AS birth_month,
    EXTRACT (DAY FROM birth_date) AS birth_day
FROM
    employees;

Note that if you use this in the Eloquent method whereYear(), like User::whereYear('created_at', 2025)->get(), it will translate the query to both databases under the hood.

For that, Laravel framework has different "grammar" files for each DB engine. Compare these implementations of the same function dateBasedWhere() in "general" grammar file, and then overwritten specifically for Postgres.

src/Illuminate/Database/Query/Grammars/Grammar.php:

protected function dateBasedWhere($type, Builder $query, $where)
{
    $value = $this->parameter($where['value']);
 
    return $type.'('.$this->wrap($where['column']).') '.$where['operator'].' '.$value;
}

src/Illuminate/Database/Query/Grammars/PostgresGrammar.php:

protected function dateBasedWhere($type, Builder $query, $where)
{
    $value = $this->parameter($where['value']);
 
    return 'extract('.$type.' from '.$this->wrap($where['column']).') '.$where['operator'].' '.$value;
}

2. CONCAT()

MySQL:

SELECT CONCAT(first_name, ' ', last_name);

PostgreSQL:

SELECT first_name || ' ' || last_name;

3. LIMIT / OFFSET

MySQL:

SELECT * FROM employees LIMIT 5, 10; -- Skip 5, take 10

PostgreSQL:

SELECT * FROM employees LIMIT 10 OFFSET 5; -- Skip 5, take 10

4. CASE-SENSITIVITY

MySQL: Case-insensitive by default

SELECT * FROM employees WHERE name LIKE 'john%';

PostgreSQL: Case-insensitive with ILIKE

SELECT * FROM employees WHERE name ILIKE 'john%';

Extra PostgreSQL Features

Now, let's explore more PostgreSQL advantages: extra features that are missing in MySQL.

Feature 1. JSON(B) Support

PostgreSQL offers better JSON handling with JSONB type, allowing indexing and complex queries. MySQL's JSON support is more basic.

// PostgreSQL Migration
Schema::create('products', function (Blueprint $table) {
    $table->id();
    $table->jsonb('metadata');  // PostgreSQL JSONB type
    $table->timestamps();
});
 
// MySQL Migration
Schema::create('products', function (Blueprint $table) {
    $table->id();
    $table->json('metadata');   // MySQL JSON type
    $table->timestamps();
});

Laravel Eloquent works well with this feature.

// Model for both databases
class Product extends Model
{
    protected $casts = [
        'metadata' => 'array'
    ];
}

But now you can add raw queries using specific PostgreSQL syntax:

// PostgreSQL - Using containment operator @>
// Find products where metadata contains specific key-value
Product::whereRaw("metadata @> ?", ['{"color": "red"}'])->get();
 
// PostgreSQL - Using path operators ->
Product::whereRaw("metadata->>'color' = ?", ['red'])->get();
 
// PostgreSQL - Using jsonb_array_elements to query array elements
Product::whereRaw("jsonb_array_elements(metadata->'tags') @> ?::jsonb", ['{"name": "premium"}'])->get();

Compare to MySQL syntax:

// MySQL - Using JSON_CONTAINS
Product::whereRaw("JSON_CONTAINS(metadata, ?, '$.color')", ['"red"'])->get();
 
// MySQL - Using JSON_EXTRACT
Product::whereRaw("JSON_EXTRACT(metadata, '$.color') = ?", ['"red"'])->get();
 
// MySQL - Querying array elements (more verbose)
Product::whereRaw("JSON_CONTAINS(JSON_EXTRACT(metadata, '$.tags'), JSON_OBJECT('name', 'premium'))")->get();

The main points about JSONB's advantages:

  • Binary storage format (the 'B' in JSONB) eliminates the need to reparse JSON on each query
  • Native containment operators (@>) for faster searches
  • Better index support through GIN (Generalized Inverted Index)
  • More efficient storage by eliminating duplicate keys and maintaining a binary format

So, it's better to use PostgreSQL with the JSONB type if you need to frequently query JSON data, if you're dealing with complex JSON structures, and if performance is critical.

Feature 2. JSON "Cousin": Array Data Type

PostgreSQL supports native array types, while MySQL doesn't have direct array support.

For example, you want to save tags in one column without a separate table. Here's what migration would look like:

Schema::create('products', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->text('tags')->nullable();
    $table->timestamps();
});
 
// PostgreSQL-specific modification
DB::statement('ALTER TABLE products ALTER COLUMN tags TYPE text[] USING array[tags]');

Eloquent Model:

class Product extends Model
{
    protected $casts = [
        'tags' => 'array',
    ];
}

Usage Example:

// Creating with array
$product = Product::create([
    'name' => 'Smartphone',
    'tags' => ['electronics', 'mobile', 'gadget']
]);
 
// Querying array elements
$electronicsProducts = Product::whereRaw('? = ANY(tags)', ['electronics'])->get();
 
// Adding to the array
Product::find(1)->update([
    'tags' => array_merge($product->tags, ['sale', 'new'])
]);

In case of MySQL, you typically use JSON columns for this case. But PostgreSQL array operations generally are faster and more efficient

Query differences:

  • PostgreSQL: Can use ANY, @>, && array operators
  • MySQL: Uses JSON path expressions like JSON_CONTAINS

Feature 3. UUIDs as Native Type

PostgreSQL has a native uuid type, while MySQL typically stores UUIDs as CHAR(36) strings.

If we compare the code on the Laravel level, migration and Model files are identical.

Migration:

Schema::create('users', function (Blueprint $table) {
    $table->uuid('id')->primary();
    $table->string('name');
    $table->string('email')->unique();
    $table->timestamps();
});

app/Models/User.php:

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Str;
 
class User extends Model
{
    protected $keyType = 'string';
    public $incrementing = false;
 
    protected static function booted(): void
    {
        static::creating(function ($model) {
            if (!$model->id) {
                $model->id = Str::uuid()->toString();
            }
        });
    }
}

The difference is in the performance under the hood: PostgreSQL's native UUID type is more optimized for indexing and querying.

Additionally, you may use native PostgreSQL function to generate UUIDs with uuid-ossp extension, instead of doing that from Laravel/PHP level.

// Migration for PostgreSQL with native UUID generation
Schema::create('users', function (Blueprint $table) {
    // Enable extension if needed
    DB::statement('CREATE EXTENSION IF NOT EXISTS "uuid-ossp";');
 
    $table->uuid('id')->primary();
    // Use PostgreSQL's UUID generation function as default
    DB::statement('ALTER TABLE users ALTER COLUMN id SET DEFAULT uuid_generate_v4();');
 
    $table->string('name');
    $table->timestamps();
});

More Rarely Used Features

  • More powerful full-text search
  • Robust timezone support with timestamptz type
  • Ranges data type instead of separate date_from and date_to columns

Important Notice: If you use those PostgreSQL-specific features and raw query syntax, make sure you use PostgreSQL on all environments: the local server, when running automated tests, staging, and production. Don't use MySQL/SQLite on any environment to avoid inconsistency and potential bugs.

Wait, So Why Did They Choose Postgres for Cloud/Nightwatch?

For Laravel Cloud, it's not just PostgreSQL. It's SERVERLESS Postgres. They worked hard to auto-scale and auto-hibernate your database if it doesn't get queries.

You can listen to a podcast between Matt Stauffer and Taylor Otwell talking about it.

I asked the Laravel team at Laracon EU, and apparently, this automation was easier to achieve with PostgreSQL first; it was more tricky with MySQL.

For Nightwatch, let me show you the tweet chain, where Taylor and Jess gave the reasons.

So, honestly... there's no big reason? Laravel Cloud started with PostgreSQL because it was easier to implement the hibernating function, and then Nightwatch followed the same infrastructure.

As Jess mentioned, there's no "strong preference" between MySQL and PostgreSQL.

You can also read more about analytical queries with ClickHouse they mentioned. That tool is not specific to either DB; their docs mention integrations for both PostgreSQL and MySQL.

More Resources

Finally, if you want to use PostgreSQL in Laravel on a serious level, checkout a great package by Tobias Petry called Laravel PostgreSQL Enhanced.

The readme of the package is HUGE, listing all the features; I will mention just a few of them:

  • IDE Autocomplete
  • PHPStan
  • Migrations with Zero Downtime, Postgres Extensions, Functions, Table Options, Column Types like arrays/ranges, etc
  • Query Explain, Fulltext Search, Where Clauses, Order By, etc
  • Eloquent Casts and Date Formats
  • ... and much more

Also, I want to mention a course Mastering Postgres by one of the great educators I know, Aaron Francis. Not a cheap one, but I've seen many good reviews on Twitter.

No comments yet…

avatar
You can use Markdown

Recent New Courses