PostgreSQL for Laravel: Setup Tips

Tip: Use pg_hba.conf for Access Control

Don't just open up all connections. Configure pg_hba.conf:

# Only local connections
local   all   all   md5
host    all   all   127.0.0.1/32   md5

Gotcha: PostgreSQL is Case-Sensitive for Identifiers

SELECT * FROM "Users"; -- Different from "users"

Laravel quotes identifiers, so this usually isn't an issue. But raw queries can bite you.

Tip: Use pg_stat_statements for Query Monitoring

CREATE EXTENSION pg_stat_statements;
SELECT query, calls, total_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

Shows your slowest queries.

Gotcha: SERIAL vs BIGSERIAL

Laravel uses BIGSERIAL for auto-increment IDs. Don't change this — SERIAL maxes out at 2 billion rows.

Tip: Connection Pooling with PgBouncer

For 100+ concurrent connections, use PgBouncer:

pool_mode = transaction
max_client_conn = 200
default_pool_size = 20

Gotcha: ON CONFLICT for Upserts

Post::updateOrCreate(
    ['slug' => $slug],
    ['title' => $title]
);

Generates INSERT ... ON CONFLICT under the hood.

Tip: Schema Search Path

'schema' => 'public,app',

Search multiple schemas without qualifying table names.

Gotcha: PostgreSQL Doesn't Support IF NOT EXISTS for Columns

Use raw SQL or check before adding:

if (! Schema::hasColumn('posts', 'slug')) {
    Schema::table('posts', fn($t) => $t->string('slug'));
}

Tip: EXPLAIN (ANALYZE, BUFFERS) Is Your Best Friend

For query debugging, always use EXPLAIN (ANALYZE, BUFFERS) instead of plain EXPLAIN. The BUFFERS option shows hit/miss rates for every node, revealing whether your indexes are actually in memory.

Tip: Partial Indexes Are Underutilized

CREATE INDEX ON orders (status) WHERE status = 'pending' creates a tiny index that covers only the rows your query needs. It's faster to scan and cheaper to maintain than a full-column index.

Gotcha: NULL Sorting Is Non-Obvious

By default, NULLs sort AFTER non-null values in ascending order. ORDER BY col DESC puts NULLs FIRST. Use NULLS LAST or NULLS FIRST to be explicit.

Senior Insight

The most common PostgreSQL setup mistake I encounter is inadequate shared_buffers configuration. Teams leave it at the default 128MB, then wonder why their database uses only a fraction of available RAM. As a rule of thumb, set shared_buffers to 25% of total RAM on a dedicated database server. But here's what I learned the hard way: setting it above 8GB on Linux requires kernel.shmmax adjustment, otherwise PostgreSQL refuses to start.

Source: pganalyze Blog (https://pganalyze.com/blog), PostgreSQL Docs (https://www.postgresql.org/docs/current/), Crunchy Data Blog (https://www.crunchydata.com/blog)