MySQL vs PostgreSQL — key differences a PHP dev must know

Concept

MySQL and PostgreSQL are the two most common databases in the PHP ecosystem. They're both excellent, but have meaningful differences a PHP developer must understand.

MySQL (MariaDB):

• Default in shared hosting, Laravel Homestead, LAMP stack.
• STRICT_TRANS_TABLES mode (MySQL 5.7+): validates data properly. Off by default in older versions — beware.
• Default storage engine: InnoDB (ACID, row-level locking, foreign keys). MyISAM is legacy — avoid.
• AUTO_INCREMENT for primary keys.
• JSON column type supported (MySQL 5.7+) with -> / ->> operators.
• Full-text search with MATCH ... AGAINST.
• GROUP BY historically more permissive (allowed selecting non-aggregated columns — fixed in strict mode).
• LIMIT n for top-N queries.
• No native UUID type — use CHAR(36) or BINARY(16).
• Window functions available from MySQL 8.0.

PostgreSQL:

• More SQL-standard compliant.
• SERIAL / BIGSERIAL or GENERATED ALWAYS AS IDENTITY for auto-increment.
• Native UUID type. Native JSONB (binary JSON, indexed, faster than JSON).
• ARRAY types, HSTORE, geometric types, range types.
• Full-text search built-in with tsvector and tsquery — more powerful than MySQL's.
• Window functions available since version 8.4.
• RETURNING clause: INSERT INTO ... RETURNING id — get the inserted row without a separate SELECT.
• ON CONFLICT DO NOTHING / DO UPDATE (upsert) — cleaner than MySQL's INSERT ... ON DUPLICATE KEY UPDATE.
• Better support for RECURSIVE CTEs, complex queries.
• Row-level security (RLS) built-in.
• No auto-commit issue — BEGIN/COMMIT are explicit.

Which to choose: MySQL for simpler apps, shared hosting, existing MySQL stack. PostgreSQL for complex queries, JSON/JSONB, better standards compliance, advanced features. Both work perfectly with Laravel.

Code Example

-- AUTO INCREMENT
-- MySQL:
CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100));
-- PostgreSQL:
CREATE TABLE users (id SERIAL PRIMARY KEY, name VARCHAR(100));
-- Modern PostgreSQL:
CREATE TABLE users (id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name VARCHAR(100));

-- UPSERT (insert or update on conflict)
-- MySQL:
INSERT INTO page_views (page, count) VALUES ('home', 1)
ON DUPLICATE KEY UPDATE count = count + 1;
-- PostgreSQL (cleaner):
INSERT INTO page_views (page, count) VALUES ('home', 1)
ON CONFLICT (page) DO UPDATE SET count = page_views.count + 1;

-- RETURNING (PostgreSQL only) — get inserted row without extra SELECT
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com')
RETURNING id, created_at;

-- JSON
-- MySQL:
SELECT data->'$.email' FROM users;         -- extracts JSON value
SELECT data->>'$.email' FROM users;        -- extracts as text (unquoted)
-- PostgreSQL (JSONB):
SELECT data->>'email' FROM users;          -- text extraction
SELECT data->'address'->>'city' FROM users; -- nested
CREATE INDEX idx_users_email ON users ((data->>'email')); -- index on JSONB field

-- Full-text search
-- MySQL:
SELECT * FROM posts WHERE MATCH(title, body) AGAINST ('laravel eloquent' IN BOOLEAN MODE);
-- PostgreSQL:
SELECT * FROM posts WHERE to_tsvector('english', title || ' ' || body) @@ to_tsquery('laravel & eloquent');
CREATE INDEX idx_posts_fts ON posts USING GIN (to_tsvector('english', title || ' ' || body));

// Laravel config/database.php — specify the driver
'mysql' => [
    'driver' => 'mysql',
    'strict'  => true,   // enforce STRICT_TRANS_TABLES
    ...
],
'pgsql' => [
    'driver' => 'pgsql',
    ...
],
// Switch via DB_CONNECTION in .env