PostgreSQL For Application Projects

PostgreSQL is a strong default database for serious PHP applications because it gives you reliable transactions, rich constraints, good indexing, JSONB when you truly need document-style data, and mature backup/restore tooling. This lesson is about using PostgreSQL as an application database, not treating it like a generic SQL box.

In a real project you normally separate the database server, the database, the schema, and the role that the PHP application uses. The application role should have only the permissions it needs. In development you might run PostgreSQL with Docker; in production you might use a managed service. Either way, the PHP code should connect through configuration, not hard-coded credentials.

Model important rules in the database as well as in PHP. Use primary keys, foreign keys, NOT NULL, UNIQUE, CHECK constraints, and indexes that match real queries. Store moments in time with TIMESTAMPTZ. Use transactions for multi-step changes. Use RETURNING when inserting rows and immediately needing generated values.

PostgreSQL also changes how you think operationally. You should know how migrations are applied, how long-running queries are found, how backups are made, and how a restore would be tested. A PHP developer does not need to be a full DBA, but they do need to avoid schema and query decisions that make the application fragile.

Connection Shape

PHP usually connects to PostgreSQL through PDO or a framework database layer.

<?php

declare(strict_types=1);

function postgresDsn(string $host, string $database, int $port = 5432): string
{
    return sprintf('pgsql:host=%s;port=%d;dbname=%s', $host, $port, $database);
}

echo postgresDsn('127.0.0.1', 'app') . PHP_EOL;

// Prints:
// pgsql:host=127.0.0.1;port=5432;dbname=app

Keep the username and password in environment-specific configuration or a secrets manager. Do not commit credentials.

Schema And Constraints

CREATE TABLE projects (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'active'
        CHECK (status IN ('active', 'paused', 'archived')),
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_projects_status_created_at
    ON projects (status, created_at DESC);

This table uses PostgreSQL-specific features deliberately: identity columns, TIMESTAMPTZ, a CHECK constraint, and an index that matches a real list query.

RETURNING

PostgreSQL can return generated values directly from an insert or update.

<?php

declare(strict_types=1);

$insertSql = 'INSERT INTO projects (name) VALUES (:name) RETURNING id';
$parameters = ['name' => 'Client portal'];

echo $insertSql . PHP_EOL;
echo json_encode($parameters, JSON_THROW_ON_ERROR) . PHP_EOL;

// Prints:
// INSERT INTO projects (name) VALUES (:name) RETURNING id
// {"name":"Client portal"}

With PDO, the code prepares the statement, executes it with parameters, and reads the returned ID. This is different from relying on a separate "last insert ID" call.

JSONB

PostgreSQL has strong JSON support through jsonb. It is useful for flexible metadata, provider payloads, audit snapshots, and integration data that varies by source.

CREATE TABLE webhook_events (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    provider TEXT NOT NULL,
    event_id TEXT NOT NULL,
    payload JSONB NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (provider, event_id)
);

Do not use JSONB because schema design feels inconvenient. Use normal columns for data you filter, join, validate, or report on regularly.

Transactions

Use transactions when several writes must succeed or fail together. In PHP this normally means $pdo->beginTransaction(), $pdo->commit(), and $pdo->rollBack(), or a framework transaction helper.

<?php

declare(strict_types=1);

function projectCreationStatements(): array
{
    return [
        'INSERT INTO projects (name) VALUES (:name) RETURNING id',
        'INSERT INTO audit_log (action, entity_id) VALUES (:action, :entity_id)',
    ];
}

print_r(projectCreationStatements());

// Prints:
// INSERT INTO projects (name) VALUES (:name) RETURNING id

Indexes And EXPLAIN

Indexes should match query patterns. A query like this benefits from the earlier (status, created_at DESC) index:

SELECT id, name, created_at
FROM projects
WHERE status = :status
ORDER BY created_at DESC
LIMIT 20;

Use EXPLAIN when checking whether PostgreSQL can use an index. Do not add indexes blindly; each index has storage and write costs.

Operations Awareness

Application developers should know enough PostgreSQL operations to avoid dangerous changes:

• Migrations can lock tables if written carelessly.
• Backups only matter if restores are tested.
• Long-running queries can block deploys or slow the app.
• Connection counts matter when PHP workers scale up.
• Roles and permissions should be least-privilege.

What To Check

Before moving on, make sure you can:

• Build a pgsql: PDO DSN.
• Recognise identity columns, TIMESTAMPTZ, CHECK, and RETURNING.
• Explain when JSONB is useful and when normal columns are better.
• Use transactions for related writes.
• Match indexes to real queries.
• Explain why migrations, backups, and restores are part of database work.