PostgreSQL 16 for Laravel

PostgreSQL 16 delivers significant performance improvements and developer features. Laravel applications benefit from faster queries and enhanced JSONB operations. At ZIRA Software, PostgreSQL 16 improved query performance by 30% across our applications.

Key PostgreSQL 16 Features

Performance Improvements
├── Full-text search optimization
├── Parallel query execution
├── Improved COPY performance
└── Better query planner

Developer Features
├── SQL/JSON constructors
├── ANY_VALUE aggregate
├── Improved EXPLAIN output
└── Logical replication improvements

Installation and Upgrade

# Ubuntu/Debian
sudo apt install postgresql-16

# Docker
docker pull postgres:16

# Check version
psql -c "SELECT version();"

SQL/JSON Constructors in Laravel

// PostgreSQL 16 JSON constructors
$users = DB::table('users')
    ->selectRaw("
        JSON_OBJECT(
            'id': id,
            'name': name,
            'email': email,
            'profile': JSON_OBJECT(
                'avatar': avatar_url,
                'bio': bio
            )
        ) as user_json
    ")
    ->get();

// JSON_ARRAY for collections
$tags = DB::table('posts')
    ->selectRaw("
        id,
        title,
        JSON_ARRAY(
            SELECT name FROM tags
            WHERE tags.post_id = posts.id
        ) as tags
    ")
    ->get();

ANY_VALUE Aggregate

// PostgreSQL 16 - ANY_VALUE for non-grouped columns
$orders = DB::table('orders')
    ->select([
        'customer_id',
        DB::raw('SUM(total) as total_spent'),
        DB::raw('ANY_VALUE(customer_name) as customer_name'), // New in PG16
        DB::raw('COUNT(*) as order_count'),
    ])
    ->groupBy('customer_id')
    ->get();

// Before PG16, you needed:
// - Add customer_name to GROUP BY, or
// - Use MAX(customer_name) which was semantically incorrect

Improved Full-Text Search

// PostgreSQL 16 - Faster full-text search with parallel execution
$results = DB::table('articles')
    ->whereRaw("to_tsvector('english', title || ' ' || content) @@ plainto_tsquery('english', ?)", [$query])
    ->orderByRaw("ts_rank(to_tsvector('english', title || ' ' || content), plainto_tsquery('english', ?)) DESC", [$query])
    ->paginate(20);

// Create GIN index for better performance
// CREATE INDEX articles_search_idx ON articles
// USING GIN (to_tsvector('english', title || ' ' || content));

JSONB Subscripting Improvements

// Direct JSONB subscript access (faster in PG16)
$products = DB::table('products')
    ->whereRaw("(attributes['specs']['weight'])::numeric > ?", [10])
    ->selectRaw("
        id,
        name,
        attributes['specs']['dimensions'] as dimensions,
        attributes['pricing']['discount'] as discount
    ")
    ->get();

// Nested updates with subscripts
DB::table('products')
    ->where('id', $productId)
    ->update([
        'attributes' => DB::raw("
            jsonb_set(attributes, '{specs,updated_at}', to_jsonb(now()))
        "),
    ]);

Parallel Query Execution

// PostgreSQL 16 improves parallel query planning
// Ensure postgresql.conf is configured:
// max_parallel_workers_per_gather = 4
// parallel_tuple_cost = 0.01
// parallel_setup_cost = 1000

// Large aggregations benefit from parallelism
$stats = DB::table('orders')
    ->select([
        DB::raw('DATE_TRUNC(\'month\', created_at) as month'),
        DB::raw('SUM(total) as revenue'),
        DB::raw('COUNT(*) as order_count'),
        DB::raw('AVG(total) as avg_order'),
    ])
    ->where('created_at', '>=', now()->subYears(2))
    ->groupBy(DB::raw('DATE_TRUNC(\'month\', created_at)'))
    ->orderBy('month')
    ->get();

// Check if query uses parallel workers
// EXPLAIN (ANALYZE, BUFFERS) SELECT ...

Logical Replication Enhancements

-- PostgreSQL 16 - Replicate from standby servers
CREATE SUBSCRIPTION my_sub
CONNECTION 'host=standby dbname=mydb'
PUBLICATION my_pub
WITH (copy_data = true, origin = any);

-- Parallel COPY for faster initial sync
ALTER SUBSCRIPTION my_sub SET (parallel_copy = true);

Connection Pooling Improvements

// config/database.php - Leverage PG16 connection improvements
'pgsql' => [
    'driver' => 'pgsql',
    'host' => env('DB_HOST', '127.0.0.1'),
    'port' => env('DB_PORT', '5432'),
    'database' => env('DB_DATABASE', 'forge'),
    'username' => env('DB_USERNAME', 'forge'),
    'password' => env('DB_PASSWORD', ''),
    'charset' => 'utf8',
    'prefix' => '',
    'prefix_indexes' => true,
    'search_path' => 'public',
    'sslmode' => 'prefer',
    'options' => [
        PDO::ATTR_PERSISTENT => true, // Connection pooling
    ],
],

EXPLAIN Improvements

// PostgreSQL 16 - Better EXPLAIN output
$query = User::where('active', true)->toSql();

$explain = DB::select("EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) {$query}", [true]);

// New in PG16: More detailed JIT compilation stats
// "JIT": {
//   "Functions": 10,
//   "Options": { "Inlining": true, "Optimization": true },
//   "Timing": { "Generation": 1.234, "Optimization": 5.678 }
// }

Migration Considerations

// Check PostgreSQL version in migrations
public function up()
{
    $version = DB::selectOne("SELECT current_setting('server_version_num')::int as version");

    if ($version->version >= 160000) {
        // Use PostgreSQL 16 features
        DB::statement("
            CREATE INDEX articles_search_idx ON articles
            USING GIN (to_tsvector('english', title || ' ' || content))
        ");
    }
}

Conclusion

PostgreSQL 16 brings substantial performance improvements and developer features. Laravel applications benefit from faster JSONB operations, parallel execution, and enhanced full-text search.

Need database optimization? Contact ZIRA Software for PostgreSQL consulting.