PostgreSQL for PHP Developers: Getting Started Guide

PostgreSQL has earned a reputation as the most advanced open-source relational database. Its feature set rivals commercial databases, with support for JSON, full-text search, custom data types, and advanced indexing. For PHP developers, PostgreSQL offers a robust alternative to MySQL with stronger SQL standards compliance and better support for complex queries.

This guide covers everything you need to start using PostgreSQL with PHP, from installation to advanced query patterns.

What You’ll Learn

• Installing and configuring PostgreSQL
• Creating databases, roles, and permissions
• Connecting from PHP with PDO
• CRUD operations with prepared statements
• Using PostgreSQL-specific features with PHP
• Security best practices for database access
• Performance optimization techniques

Installing PostgreSQL

macOS (Homebrew)

brew install postgresql@15
brew services start postgresql@15

Ubuntu/Debian

sudo apt update
sudo apt install postgresql postgresql-contrib
sudo systemctl start postgresql

Windows

Download the installer from the official PostgreSQL website and run the setup wizard. The installer includes pgAdmin for visual database management.

Connecting to PostgreSQL

Command Line

psql postgres

This connects to the default postgres database as your system user. Once connected, you can create roles and databases.

Creating a Role

CREATE ROLE my_app SUPERUSER LOGIN PASSWORD 'secret';

-- List all roles
\du

Output:

                          List of roles
 Role name |               Attributes               | Member of
-----------+----------------------------------------+-----------
 my_app    | Superuser, Create role, Create DB, ... | {}
 postgres  | Superuser, Create role, Create DB, ... | {}

Creating a Database

CREATE DATABASE my_app_db OWNER my_app;

Connecting from PHP

Use PDO (PHP Data Objects) for a consistent API across database systems.

<?php
 
declare(strict_types=1);
 
$dsn = sprintf(
    'pgsql:host=%s;port=%d;dbname=%s',
    'localhost',
    5432,
    'my_app_db',
);
 
$username = 'my_app';
$password = 'secret';
 
try {
    $pdo = new PDO($dsn, $username, $password, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false,
    ]);
} catch (PDOException $e) {
    die('Connection failed: ' . $e->getMessage());
}

The ATTR_EMULATE_PREPARES => false setting is important for PostgreSQL. It enables real prepared statements, which PostgreSQL handles natively.

CRUD Operations

Create Table

<?php
 
$pdo->exec("
    CREATE TABLE IF NOT EXISTS users (
        id SERIAL PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        email VARCHAR(255) UNIQUE NOT NULL,
        department VARCHAR(100),
        created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
    )
");

SERIAL creates an auto-incrementing integer column. TIMESTAMP WITH TIME ZONE stores timezone-aware timestamps.

Insert

<?php
 
$stmt = $pdo->prepare("
    INSERT INTO users (name, email, department)
    VALUES (:name, :email, :department)
    RETURNING id
");
 
$stmt->execute([
    'name' => 'Alice Johnson',
    'email' => '[email protected]',
    'department' => 'Engineering',
]);
 
$newId = $stmt->fetchColumn();
echo "Created user with ID: {$newId}" . PHP_EOL;

RETURNING id is a PostgreSQL feature that returns the generated ID in a single round trip.

Select

<?php
 
$stmt = $pdo->prepare("
    SELECT id, name, email, department, created_at
    FROM users
    WHERE department = :dept
    ORDER BY created_at DESC
");
 
$stmt->execute(['dept' => 'Engineering']);
$users = $stmt->fetchAll();
 
foreach ($users as $user) {
    echo sprintf(
        '%d: %s (%s) - %s',
        $user['id'],
        $user['name'],
        $user['email'],
        $user['created_at'],
    ) . PHP_EOL;
}

Update

<?php
 
$stmt = $pdo->prepare("
    UPDATE users
    SET department = :department
    WHERE id = :id
");
 
$stmt->execute([
    'id' => 1,
    'department' => 'Product',
]);
 
echo "Updated {$stmt->rowCount()} row(s)" . PHP_EOL;

Delete

<?php
 
$stmt = $pdo->prepare("DELETE FROM users WHERE id = :id");
$stmt->execute(['id' => 1]);

PostgreSQL-Specific Features

JSON Data

PostgreSQL has first-class JSON support. Store and query JSON directly.

<?php
 
$pdo->exec("
    CREATE TABLE IF NOT EXISTS products (
        id SERIAL PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        attributes JSONB
    )
");
 
// Insert JSON
$stmt = $pdo->prepare("
    INSERT INTO products (name, attributes)
    VALUES (:name, :attributes::jsonb)
");
 
$stmt->execute([
    'name' => 'Widget',
    'attributes' => json_encode([
        'color' => 'red',
        'weight' => 1.5,
        'dimensions' => ['width' => 10, 'height' => 5],
    ]),
]);
 
// Query JSON
$stmt = $pdo->query("
    SELECT name, attributes->>'color' AS color
    FROM products
    WHERE attributes @> '{\"color\": \"red\"}'::jsonb
");
 
$products = $stmt->fetchAll();

JSONB is the binary format of JSON. It supports indexing and advanced query operators like @> (contains).

Common Table Expressions (CTEs)

CTEs simplify complex queries by breaking them into named subqueries. They are especially useful for hierarchical data and multi-step aggregations.

<?php
 
$stmt = $pdo->query("
    WITH department_stats AS (
        SELECT
            department,
            COUNT(*) AS employee_count,
            AVG(salary) AS avg_salary
        FROM employees
        GROUP BY department
    )
    SELECT * FROM department_stats
    WHERE employee_count > 10
    ORDER BY avg_salary DESC
");
 
$departments = $stmt->fetchAll();

CTEs make queries more readable and can be referenced multiple times within the same statement, avoiding subquery duplication.

Full-Text Search

<?php
 
$pdo->exec("
    CREATE TABLE IF NOT EXISTS articles (
        id SERIAL PRIMARY KEY,
        title VARCHAR(255) NOT NULL,
        body TEXT NOT NULL,
        search_vector TSVECTOR
    )
");
 
// Create a GIN index for full-text search
$pdo->exec("
    CREATE INDEX articles_search_idx
    ON articles
    USING GIN(search_vector)
");
 
// Populate search vector
$stmt = $pdo->prepare("
    UPDATE articles
    SET search_vector = to_tsvector('english', title || ' ' || body)
    WHERE id = :id
");
 
// Search
$stmt = $pdo->prepare("
    SELECT title, ts_headline(body, plainto_tsquery('english', :query),
        'StartSel=<mark>, StopSel=</mark>') AS highlighted
    FROM articles
    WHERE search_vector @@ plainto_tsquery('english', :query)
");
 
$stmt->execute(['query' => 'database performance']);
$results = $stmt->fetchAll();

Transactions

PostgreSQL has robust transaction support with full ACID compliance.

<?php
 
try {
    $pdo->beginTransaction();
 
    // Deduct from account A
    $stmt = $pdo->prepare("
        UPDATE accounts SET balance = balance - :amount
        WHERE id = :account_id
    ");
    $stmt->execute(['amount' => 100.00, 'account_id' => 123]);
 
    // Add to account B
    $stmt = $pdo->prepare("
        UPDATE accounts SET balance = balance + :amount
        WHERE id = :account_id
    ");
    $stmt->execute(['amount' => 100.00, 'account_id' => 456]);
 
    $pdo->commit();
    echo "Transaction completed successfully" . PHP_EOL;
} catch (Exception $e) {
    $pdo->rollBack();
    echo "Transaction failed: " . $e->getMessage() . PHP_EOL;
}

Understanding PostgreSQL Roles and Permissions

PostgreSQL manages access through roles. A role can be a user, a group, or both. This flexibility lets you model complex permission hierarchies.

-- Create groups (roles without LOGIN)
CREATE ROLE read_access;
CREATE ROLE write_access;
CREATE ROLE admin_access;
 
-- Grant permissions to groups
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_access;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO write_access;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO admin_access;
 
-- Create users and assign to groups
CREATE ROLE alice LOGIN PASSWORD 'secure_pass1' IN ROLE read_access;
CREATE ROLE bob LOGIN PASSWORD 'secure_pass2' IN ROLE read_access, write_access;
CREATE ROLE charlie LOGIN PASSWORD 'secure_pass3' IN ROLE admin_access;

This approach simplifies permission management. Grant or revoke permissions at the group level, and all group members inherit the change.

User and Permission Management

-- Create a read-only role
CREATE ROLE readonly_user LOGIN PASSWORD 'readonly_pass';
GRANT CONNECT ON DATABASE my_app_db TO readonly_user;
GRANT USAGE ON SCHEMA public TO readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;
 
-- Create a role for application usage
CREATE ROLE app_user LOGIN PASSWORD 'app_pass';
GRANT CONNECT ON DATABASE my_app_db TO app_user;
GRANT USAGE, CREATE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;

Real-World Use Cases

E-Commerce Platforms

PostgreSQL’s JSONB stores flexible product attributes. Full-text search powers product search. Window functions handle order analytics.

Analytics Dashboards

PostgreSQL’s aggregate functions, window functions, and CTEs (Common Table Expressions) make complex reporting queries straightforward.

Geolocation Services

The PostGIS extension adds spatial data types and geospatial queries. Find nearby locations, calculate distances, and manage geographic polygons.

Content Management Systems

Full-text search, JSONB for flexible content fields, and array columns for tags make PostgreSQL ideal for CMS platforms.

Financial Systems

ACID compliance, serializable isolation levels, and careful transaction management ensure data integrity for financial applications.

Best Practices

• Always use prepared statements - Never concatenate user input into SQL. Use PDO prepared statements with named parameters.
• Set appropriate permissions - Create separate roles for read-only, read-write, and administrative access.
• Use connection pooling - PDO connections are expensive to create. Use PgBouncer or PDO::ATTR_PERSISTENT for high-traffic applications.
• Index strategically - Use EXPLAIN ANALYZE to understand query plans. Add indexes for WHERE, JOIN, and ORDER BY columns.
• Enable SSL connections - Configure PostgreSQL to require SSL for remote connections. Use sslmode=require in the DSN.
• Backup regularly - Use pg_dump for logical backups and WAL archiving for point-in-time recovery.

Common Mistakes to Avoid

• Using default credentials - PostgreSQL’s default postgres user has no password. Set strong passwords immediately.
• Forgetting to tune configuration - PostgreSQL defaults are conservative. Adjust shared_buffers, work_mem, and max_connections for your workload.
• Ignoring connection limits - Each PHP process opens a connection. In high-concurrency scenarios, you can exhaust max_connections.
• Using stored procedures unnecessarily - Keep business logic in PHP. Use database functions only for set-based operations.
• Over-indexing - Every index adds write overhead. Index only columns that appear in WHERE, JOIN, or ORDER BY clauses.

Frequently Asked Questions

How does PostgreSQL compare to MySQL for PHP applications?

PostgreSQL offers stronger SQL compliance, better JSON support, and more advanced indexing. MySQL has broader hosting support and simpler replication setups. Both are excellent choices.

What PHP extension do I need for PostgreSQL?

Use ext-pdo with ext-pgsql. Most PHP installations include these. Verify with php -m | grep pdo.

Does Laravel support PostgreSQL?

Yes. Laravel has first-class PostgreSQL support including migrations, schema building, and query builder features.

How do I handle PostgreSQL sequences with PDO?

Use RETURNING id in INSERT statements. If that is not available, call lastInsertId() after the insert.

What is the maximum database size in PostgreSQL?

PostgreSQL has no practical size limit. Instances with multiple terabytes of data are common with proper partitioning and indexing.

How do I migrate from MySQL to PostgreSQL?

Use tools like pgloader to automate the migration. Be aware of differences in auto-increment (SERIAL vs AUTO_INCREMENT), quoting, and function names.

Does PostgreSQL support full-text search in multiple languages?

Yes. PostgreSQL includes dictionaries for many languages. Configure the language in to_tsvector() and plainto_tsquery().

Conclusion

PostgreSQL is a powerful, feature-rich database that pairs excellently with PHP. Its strong SQL standards compliance, advanced indexing, JSON support, and full-text search make it suitable for applications of any scale.

Start by installing PostgreSQL and connecting with PDO. Use prepared statements for all queries. Explore advanced features like JSONB and full-text search as your application’s needs grow.

With proper configuration and security practices, PostgreSQL provides a reliable foundation for your PHP applications.