PostgreSQL for PHP developers — practical tips

Why PostgreSQL?

PostgreSQL is not just a "free MySQL". It is an advanced database system with features MySQL doesn't have:

• full support for JSON/JSONB with GIN indexes
• arrays as a column type
• Full-text search without separate tables
• CTEs (WITH queries) for complex queries
• extensions: pgcrypto, uuid-ossp, pgvector

Configuration in Symfony

DATABASE_URL="postgresql://user:secret@localhost:5432/mydb?serverVersion=16&charset=utf8"

Doctrine automatically picks the right driver and SQL dialect.

Migrations

# generates a migration file based on the diff between entities and the database
php bin/console doctrine:migrations:diff

# runs pending migrations
php bin/console doctrine:migrations:migrate

# migration status
php bin/console doctrine:migrations:status

Rule: never edit tables manually in production. Always via migrations.

Indexes — the key to performance

In Doctrine you add indexes via attributes on the entity:

#[ORM\Entity]
#[ORM\Table(name: 'blog_articles')]
#[ORM\Index(fields: ['status'], name: 'idx_status')]
#[ORM\Index(fields: ['publishedAt'], name: 'idx_published_at')]
class BlogArticle
{
    // ...
}

Directly in SQL:

-- index on the status column
CREATE INDEX idx_articles_status ON blog_articles(status);

-- composite index (status + date, descending)
CREATE INDEX idx_articles_feed ON blog_articles(status, published_at DESC)
WHERE status = 'published';

EXPLAIN ANALYZE

The most important tool for diagnosing slow queries:

EXPLAIN ANALYZE
SELECT a.*, u.name
FROM blog_articles a
JOIN users u ON u.id = a.author_id
WHERE a.status = 'published'
ORDER BY a.published_at DESC
LIMIT 10;

Look for in the result:

• Seq Scan — full table scan (a bad sign on large tables)
• Index Scan — uses an index (good)
• cost= — estimated cost
• actual time= — real time in ms

JSONB — flexible data

When you don't know the record's fields up front:

ALTER TABLE blog_articles ADD COLUMN meta JSONB DEFAULT '{}';

-- store arbitrary data
UPDATE blog_articles
SET meta = '{"reading_time": 5, "featured": true, "series": "symfony-basics"}'
WHERE id = 1;

-- query by a JSON value
SELECT title FROM blog_articles
WHERE meta->>'featured' = 'true';

-- GIN index on the entire JSON
CREATE INDEX idx_articles_meta ON blog_articles USING GIN(meta);

PostgreSQL vs MySQL comparison