PostgreSQL in Laravel: A Comprehensive Guide for Performance, Features, and Configuration

Introduction

PostgreSQL is a powerful open-source relational database that is often overshadowed by MySQL in Laravel applications. However, after Laravel Cloud introduced PostgreSQL support earlier than MySQL, it quickly gained attention for its robust features and performance advantages.

In this blog, we will explore:

• Why PostgreSQL might be the better choice over MySQL for your Laravel projects.
• How to configure PostgreSQL in Laravel.
• How to migrate from MySQL to PostgreSQL smoothly.
• Performance comparisons between PostgreSQL and MySQL with real-world scenarios, specifically focusing on complex queries and large datasets.

Why Use PostgreSQL over MySQL in Laravel Projects?

PostgreSQL offers several compelling advantages over MySQL, especially for applications that require advanced features or performance optimization.

1. Performance Optimization

• Faster with Complex Queries: PostgreSQL’s query planner allows it to optimize complex queries, especially when dealing with large datasets or joins.
• RANDOM() vs. RAND(): PostgreSQL’s RANDOM() function performs better than MySQL’s RAND() for queries that require fetching random records. Example: PostgreSQL’s RANDOM() is 2-3x faster than MySQL’s RAND() when fetching random records from large datasets.

2. Advanced Features

• JSONB Support & Full-Text Search: PostgreSQL excels in handling JSON data and performing full-text searches on large datasets, making it ideal for modern applications that need to store unstructured data or perform search-based operations.
• UUID Support: PostgreSQL supports UUIDs as primary keys, which is useful for applications that require globally unique identifiers (e.g., distributed systems or microservices).

3. Scalability

• Efficient Handling of Large Datasets: PostgreSQL is designed for high-performance data retrieval and can handle large, complex queries better than MySQL, especially when dealing with advanced data types like arrays, ranges, or enums.
• MVCC (Multi-Version Concurrency Control): PostgreSQL’s concurrency model allows it to perform well in highly concurrent environments, reducing locking issues and ensuring smoother performance.

4. Extensions & Customization

• PostgreSQL Extensions: PostgreSQL supports an extension system, such as PostGIS for geospatial data, that provides more flexibility compared to MySQL’s plugin system. This allows you to extend the database capabilities as needed.

Real-World Scenario: Why PostgreSQL for Laravel Projects?

Problem:

Many Laravel developers default to using MySQL because it’s widely supported and has been the go-to choice for many projects. However, when projects grow or require advanced features (e.g., JSONB or complex queries), performance bottlenecks may occur in MySQL. PostgreSQL could solve these issues, but it requires proper integration and configuration in Laravel.

Solution:

In this guide, we’ll go step-by-step through the process of configuring PostgreSQL in Laravel, comparing it to MySQL, and exploring performance benchmarks to demonstrate when PostgreSQL outperforms MySQL in large-scale applications.

Setting Up PostgreSQL in Laravel

Step 1: Install PostgreSQL

For Local Development:

• Ubuntu (Linux):

sudo apt update
sudo apt install postgresql postgresql-contrib

sudo apt update
sudo apt install postgresql postgresql-contrib

• macOS (Homebrew):

brew install postgresql
brew services start postgresql

brew install postgresql
brew services start postgresql

• Windows: Download PostgreSQL from the official website and follow the installation instructions.

Step 2: Configure Laravel for PostgreSQL

Update .env File

Once PostgreSQL is installed, update your .env file to configure the database connection:

DB_CONNECTION=pgsql
DB_HOST=127.0.0.1
DB_PORT=5432
DB_DATABASE=your_database
DB_USERNAME=postgres
DB_PASSWORD=your_password

DB_CONNECTION=pgsql
DB_HOST=127.0.0.1
DB_PORT=5432
DB_DATABASE=your_database
DB_USERNAME=postgres
DB_PASSWORD=your_password

Update config/database.php

Make sure the pgsql configuration in config/database.php is set correctly:

'pgsql' => [
    'driver' => 'pgsql',
    'host' => env('DB_HOST', '127.0.0.1'),
    'port' => env('DB_PORT', '5432'),
    'database' => env('DB_DATABASE', 'forge'),
    'username' => env('DB_USERNAME', 'forge'),
    'password' => env('DB_PASSWORD', ''),
    'charset' => 'utf8',
    'prefix' => '',
    'schema' => 'public',
    'sslmode' => 'prefer',
],

'pgsql' => [
    'driver' => 'pgsql',
    'host' => env('DB_HOST', '127.0.0.1'),
    'port' => env('DB_PORT', '5432'),
    'database' => env('DB_DATABASE', 'forge'),
    'username' => env('DB_USERNAME', 'forge'),
    'password' => env('DB_PASSWORD', ''),
    'charset' => 'utf8',
    'prefix' => '',
    'schema' => 'public',
    'sslmode' => 'prefer',
],

Step 3: Create Database & User in PostgreSQL

Log in to PostgreSQL and create your database and user:

sudo -u postgres psql

Then run the following SQL commands:

CREATE DATABASE your_database;
CREATE USER your_user WITH PASSWORD 'your_password';
ALTER ROLE your_user SET client_encoding TO 'utf8';
ALTER ROLE your_user SET default_transaction_isolation TO 'read committed';
ALTER ROLE your_user SET timezone TO 'UTC';
GRANT ALL PRIVILEGES ON DATABASE your_database TO your_user;

CREATE DATABASE your_database;
CREATE USER your_user WITH PASSWORD 'your_password';
ALTER ROLE your_user SET client_encoding TO 'utf8';
ALTER ROLE your_user SET default_transaction_isolation TO 'read committed';
ALTER ROLE your_user SET timezone TO 'UTC';
GRANT ALL PRIVILEGES ON DATABASE your_database TO your_user;

Step 4: Run Laravel Migrations with PostgreSQL

After configuring PostgreSQL in .env and creating the database and user, run Laravel migrations:

php artisan migrate

Your PostgreSQL database should now be fully integrated into your Laravel project, and you can begin using PostgreSQL-specific features like UUID and JSONB for better performance.

PostgreSQL vs MySQL: Performance Benchmarks

Let’s benchmark PostgreSQL and MySQL to determine if PostgreSQL really performs better.

Test Setup

We created identical databases in MySQL and PostgreSQL:

• Table: bookings → 400K records
• Related Table: apartments → 200K records

All queries were executed using Laravel’s Eloquent ORM with the Laravel Benchmark class, running 10 iterations per test.

Query 1: Get the Latest Bookings

A simple query with JOIN, WHERE, and ORDER BY:

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);

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);

Results:

💡 Observation:

Both databases perform exceptionally well for this type of query. The difference is negligible for most practical purposes.

Query 2: Get Random Bookings

A more complex query using inRandomOrder():

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

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

Results:

💡 Observation:

PostgreSQL is ~2.6x faster for random queries.

Under the Hood

Here’s how the SQL queries are generated for each database:

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

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

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

Direct SQL Results:

💡 Conclusion: PostgreSQL’s RANDOM() function is significantly faster than MySQL’s RAND().

Migrating from MySQL to PostgreSQL

Migrating a Laravel project from MySQL to PostgreSQL can be challenging, but it’s doable with tools like pgloader.

Use pgloader:

Install pgloader and run the migration command:

pgloader mysql://user:password@localhost/mydatabase postgresql://user:password@localhost/mydatabase

However, be aware of potential issues related to data types and schema differences.

Tip: It’s highly recommended to test the migration on a staging server before moving to production.

PostgreSQL-Specific Features in Laravel

UUIDs as Primary Keys

PostgreSQL makes it easy to work with UUIDs as primary keys, offering globally unique identifiers:

$table->uuid('id')->primary();

Full-Text Search

PostgreSQL’s full-text search can help you search large datasets efficiently. Here’s an example:

SELECT * FROM articles WHERE to_tsvector('english', content) @@ to_tsquery('laravel');

Conclusion & CTA

PostgreSQL provides advanced features, better performance, and scalability compared to MySQL in certain use cases. Whether you’re working with JSONB, UUIDs, or complex queries, PostgreSQL could be the better choice for your Laravel applications.

Need help migrating to or integrating PostgreSQL in your Laravel app?