Select PostgreSQL 17 vs MySQL 8 for Laravel 13 on Ubuntu 24.04 PostgreSQL 17 vs MySQL 8 for Laravel 13 on Ubuntu 24.04
Select PostgreSQL 17 vs MySQL 8 for Laravel 13 on Ubuntu 24.04 PostgreSQL 17 vs MySQL 8 for Laravel 13 on Ubuntu 24.04

PostgreSQL 17 vs MySQL 8 for Laravel 13 on Ubuntu 24.04

Choosing between PostgreSQL 17 and MySQL 8 for Laravel 13? This guide compares both engines and covers installation, hardening, Eloquent config, tuning, and backups on Ubuntu 24.04.

You’re spinning up a new Laravel 13 app on Ubuntu 24.04, and the first architectural decision blocks everything else: PostgreSQL 17 or MySQL 8? Pick wrong and you’ll fight JSON queries, replication headaches, or migration failures for months. This guide compares both engines for real Laravel workloads, then walks through installation, hardening, Eloquent configuration, tuning, and backups — with the exact commands and config that survived production.

PostgreSQL 17 vs MySQL 8 for Laravel workloads

The Laravel PostgreSQL vs MySQL debate rarely has a universal winner. It depends on your query patterns. Here’s how the two engines behave under the load Laravel apps actually generate.

Where PostgreSQL 17 pulls ahead

  • Advanced data types: Native JSONB, arrays, ranges, and full-text search with GIN indexes. If your app stores flexible metadata or does complex filtering on JSON columns, PostgreSQL is faster and more expressive.
  • Concurrency: MVCC without read locks means heavy write-and-read mixed workloads scale cleanly. Long analytical queries don’t block writers.
  • Correctness: Strict type checking, real CHECK constraints, and transactional DDL — you can wrap migrations in a transaction and roll back cleanly if one statement fails.
  • PostgreSQL 17 improvements: Better vacuum performance, faster COPY bulk loads, and improved query planning for correlated subqueries — common in Eloquent’s whereHas generated SQL.

Where MySQL 8 wins

  • Read-heavy simplicity: For CRUD-dominant apps with straightforward indexed lookups, MySQL 8 with InnoDB is extremely fast and predictable.
  • Ecosystem familiarity: Most shared hosts, managed services, and Laravel tutorials assume MySQL. Debugging is easier when every Stack Overflow answer matches your stack.
  • Replication maturity: Native async and semi-sync replication is battle-tested and simple to configure.
  • Tooling: MySQL 8 added window functions, CTEs, and a real JSON type, closing much of the historical gap with PostgreSQL.

Practical recommendation: Choose PostgreSQL 17 for Laravel when you rely on JSONB, need transactional migrations, or expect complex reporting. Choose MySQL 8 on Ubuntu for Laravel when your team knows it, your hosting mandates it, or your workload is simple high-volume CRUD.

Installing and securing on Ubuntu 24.04 LTS

Ubuntu 24.04 database setup differs slightly between the two. Both start with an updated system:

sudo apt update && sudo apt upgrade -y

PostgreSQL 17 on Ubuntu 24.04

The default Ubuntu repos ship an older PostgreSQL. Add the official PGDG repository to get 17:

sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
sudo apt update
sudo apt install -y postgresql-17
sudo systemctl enable --now postgresql

Create a database and role for Laravel:

sudo -u postgres psql
CREATE DATABASE laravel_app;
CREATE USER laravel_user WITH ENCRYPTED PASSWORD 'strong_password_here';
GRANT ALL PRIVILEGES ON DATABASE laravel_app TO laravel_user;
\c laravel_app
GRANT ALL ON SCHEMA public TO laravel_user;
\q

Gotcha: On PostgreSQL 15+, the public schema no longer grants create rights to all users by default. Skip that last GRANT ON SCHEMA and your migrations fail with permission denied for schema public. This trips up nearly everyone upgrading from older versions.

Harden authentication by editing /etc/postgresql/17/main/pg_hba.conf. Use scram-sha-256 instead of trust for local connections:

local   all   laravel_user   scram-sha-256
host    all   laravel_user   127.0.0.1/32   scram-sha-256

Reload after changes: sudo systemctl reload postgresql.

MySQL 8 on Ubuntu 24.04

sudo apt install -y mysql-server
sudo systemctl enable --now mysql
sudo mysql_secure_installation

The mysql_secure_installation script removes anonymous users, disables remote root login, and drops the test database — run it every time. Then create the app database:

sudo mysql
CREATE DATABASE laravel_app CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'laravel_user'@'127.0.0.1' IDENTIFIED BY 'strong_password_here';
GRANT ALL PRIVILEGES ON laravel_app.* TO 'laravel_user'@'127.0.0.1';
FLUSH PRIVILEGES;
EXIT;

Gotcha: Always specify utf8mb4, not utf8. The legacy utf8 alias is 3-byte and silently corrupts emojis and some CJK characters. Laravel’s default utf8mb4 assumes this; a mismatched database charset causes Incorrect string value errors on insert.

Configuring Laravel .env and config/database.php

Laravel database configuration lives in .env for credentials and config/database.php for engine-specific options.

PostgreSQL .env

DB_CONNECTION=pgsql
DB_HOST=127.0.0.1
DB_PORT=5432
DB_DATABASE=laravel_app
DB_USERNAME=laravel_user
DB_PASSWORD=strong_password_here

Install the PHP PostgreSQL driver or connections silently fail:

sudo apt install -y php8.3-pgsql
sudo systemctl restart php8.3-fpm

MySQL .env

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=laravel_app
DB_USERNAME=laravel_user
DB_PASSWORD=strong_password_here

In config/database.php, the MySQL connection benefits from strict mode and a sane charset:

'mysql' => [
    'driver' => 'mysql',
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'strict' => true,
    'engine' => 'InnoDB',
    // ...
],

Tip: Keep strict => true. It surfaces bad data (zero dates, division by zero) as errors instead of silently coercing values — the same behavior PostgreSQL enforces by default. Turning it off to “fix” errors just hides bugs.

Eloquent quirks and migration differences

Most Eloquent code is portable, but a few differences bite when you switch engines or write raw SQL.

Case sensitivity

PostgreSQL is case-sensitive for string comparisons by default; MySQL’s default collation is case-insensitive. A where('email', $value) that works on MySQL may miss rows on PostgreSQL. Use whereRaw('LOWER(email) = ?', [strtolower($value)]) or the citext extension for case-insensitive columns.

JSON columns

Both support $table->json('meta'), but PostgreSQL maps it to jsonb and lets you index it. Eloquent’s arrow syntax works on both:

Model::where('meta->status', 'active')->get();

On PostgreSQL, add a GIN index for these lookups to matter at scale — more below.

Auto-increment vs sequences

PostgreSQL uses sequences under the hood. If you bulk-insert rows with explicit IDs (during a data import, for example), the sequence isn’t advanced and the next insert collides with a duplicate key error. Reset it:

SELECT setval('users_id_seq', (SELECT MAX(id) FROM users));

Boolean handling

MySQL stores booleans as TINYINT(1) returning 0/1; PostgreSQL returns true native booleans. Cast in your model with protected $casts = ['is_active' => 'boolean'] so both engines behave identically in PHP.

Transactional migrations

PostgreSQL wraps each migration in a transaction, so a mid-migration failure rolls back cleanly. MySQL’s DDL is not transactional — a failed migration can leave half-created tables. On MySQL, keep migrations small and idempotent, and test rollbacks before deploying.

Indexing, connection pooling, and query optimization

Good Laravel Eloquent database performance is mostly about indexes and connection reuse.

Index the columns you actually filter on

Schema::table('orders', function (Blueprint $table) {
    $table->index('user_id');
    $table->index(['status', 'created_at']);
});

Composite indexes must match query order. An index on (status, created_at) helps where status ... order by created_at but not a query filtering only on created_at.

For PostgreSQL JSONB filtering, add a GIN index:

CREATE INDEX idx_orders_meta ON orders USING GIN (meta);

Diagnose slow queries with EXPLAIN

Both engines expose the planner. Enable Laravel query logging in development, grab the SQL, and run:

-- PostgreSQL
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'pending';
-- MySQL
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'pending';

Look for sequential/full table scans on large tables — that’s your missing index. This is the single highest-impact Laravel database tuning step.

Connection pooling

PHP-FPM opens a fresh database connection per request, which hurts under high concurrency. For PostgreSQL, put PgBouncer in front in transaction pooling mode:

sudo apt install -y pgbouncer

Point Laravel’s DB_PORT at PgBouncer (default 6432) instead of PostgreSQL directly. Critical gotcha: in transaction pooling mode you must disable prepared statement caching, or you’ll hit prepared statement "..." already exists. Set 'options' => [PDO::ATTR_EMULATE_PREPARES => true] in the connection config, or use session pooling.

MySQL doesn’t need an external pooler for most Laravel apps; tune max_connections and use persistent connections cautiously. Raising innodb_buffer_pool_size to roughly 60–70% of available RAM on a dedicated server gives the biggest performance jump.

Backup strategies and automated dump scripts

Backups are non-negotiable for production Laravel apps. Both engines ship logical dump tools.

PostgreSQL automated dump

#!/bin/bash
set -euo pipefail
STAMP=$(date +%F_%H%M)
DEST=/var/backups/pgsql
mkdir -p "$DEST"
PGPASSWORD='strong_password_here' pg_dump -U laravel_user -h 127.0.0.1 \
  -Fc laravel_app > "$DEST/laravel_app_$STAMP.dump"
find "$DEST" -name '*.dump' -mtime +14 -delete

The custom format (-Fc) compresses and allows selective restore with pg_restore.

MySQL automated dump

#!/bin/bash
set -euo pipefail
STAMP=$(date +%F_%H%M)
DEST=/var/backups/mysql
mkdir -p "$DEST"
mysqldump --single-transaction --quick --user=laravel_user \
  --password='strong_password_here' laravel_app | gzip > "$DEST/laravel_app_$STAMP.sql.gz"
find "$DEST" -name '*.sql.gz' -mtime +14 -delete

Use --single-transaction so InnoDB tables are dumped consistently without locking writes. Without it, a backup during traffic can capture a torn state.

Schedule either script with cron:

0 3 * * * /usr/local/bin/db_backup.sh >> /var/log/db_backup.log 2>&1

Best practice: A backup you’ve never restored isn’t a backup. Test recovery on a staging server monthly, and copy dumps off-host to object storage. Store credentials in a root-only ~/.pgpass or MySQL option file rather than inline in scripts.

FAQ

Is PostgreSQL or MySQL better for Laravel?

Both are fully supported. Choose PostgreSQL 17 for Laravel when you need JSONB indexing, transactional migrations, or complex analytical queries. Choose MySQL 8 for simpler CRUD-heavy apps, wider hosting support, and team familiarity.

Does Laravel 13 support PostgreSQL 17?

Yes. Laravel 13 works with PostgreSQL 17 through the pgsql driver. Install php8.3-pgsql, set DB_CONNECTION=pgsql, and grant your user rights on the public schema to avoid migration permission errors.

How do I fix “permission denied for schema public” in PostgreSQL 17?

Connect to the target database and run GRANT ALL ON SCHEMA public TO laravel_user;. PostgreSQL 15+ removed the default create privilege on the public schema, which breaks Laravel migrations until you grant it explicitly.

What causes “Incorrect string value” errors in MySQL with Laravel?

A charset mismatch. Create your database with utf8mb4 (not the legacy 3-byte utf8) so it can store emojis and multibyte characters, matching Laravel’s default utf8mb4 connection charset.

Do I need PgBouncer for a Laravel PostgreSQL app?

Only under high concurrency. PHP-FPM opens a connection per request, so PgBouncer in transaction pooling mode reduces overhead. If you use it, enable emulated prepares to avoid duplicate prepared statement errors.

Tags