Advanced Laravel Query Builder Tips for PostgreSQL Databases

Why Go Beyond Basic Queries in Laravel with PostgreSQL

Laravel’s Query Builder is powerful out of the box—but PostgreSQL unlocks even more advanced capabilities if you know how to tap into them. From JSONB operations to full-text search and window functions, combining Laravel and PostgreSQL can supercharge your app’s performance and flexibility.

Clean Eloquent code is great. Clean + optimized PostgreSQL queries? Even better.

Key Takeaways

Use PostgreSQL-specific syntax inside Laravel’s Query Builder
Learn to query JSONB, arrays, full-text search, and CTEs
Leverage raw expressions safely and cleanly
Boost performance by reducing query complexity and joins

1. Using `whereJsonContains` with JSONB

PostgreSQL handles JSON like a champ. You can easily query JSONB columns:

Product::whereJsonContains('metadata->tags', 'featured')->get();

Or use raw expressions for advanced filters:

Product::whereRaw("metadata @> '{\"color\": \"red\"}'")
       ->get();

Use GIN indexes on JSONB for performance:

CREATE INDEX idx_products_metadata ON products USING GIN (metadata);

2. Leveraging Full-Text Search in Queries

PostgreSQL’s full-text search works great with raw expressions:

Post::whereRaw("search_vector @@ plainto_tsquery('english', ?)", [$query])->get();

Add ranking support:

Post::select('*', DB::raw("ts_rank(search_vector, plainto_tsquery('english', ?)) as rank"))
    ->whereRaw("search_vector @@ plainto_tsquery('english', ?)", [$query, $query])
    ->orderByDesc('rank')
    ->get();

3. Using PostgreSQL Arrays in Where Clauses

If you’re storing arrays (e.g., permissions, tags):

User::whereRaw("permissions @> ARRAY[?]::varchar[]", ['edit-posts'])->get();

Or:

User::whereRaw("'edit-posts' = ANY(permissions)")->get();

Index them with:

CREATE INDEX idx_users_permissions ON users USING GIN (permissions);

4. Working with Common Table Expressions (CTEs)

CTEs allow more complex queries like reporting, analytics, or recursive relationships.

DB::statement(<<<SQL
  WITH recent_posts AS (
    SELECT * FROM posts WHERE created_at >= now() - interval '30 days'
  )
  SELECT * FROM recent_posts ORDER BY created_at DESC;
SQL);

You can also use packages like staudenmeir/laravel-cte for a fluent CTE syntax in Laravel.

5. Window Functions with Laravel’s Raw Queries

PostgreSQL’s window functions like row_number(), rank(), dense_rank() are powerful for analytics and pagination.

Example:

DB::table('posts')
    ->select('user_id', DB::raw('ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) as row_num'))
    ->get();

Use this to rank posts, limit per group, or get top records within partitions.

6. Conditional Expressions with `CASE WHEN`

Use PostgreSQL’s conditional logic inline:

DB::table('orders')
    ->select('id', 'total', DB::raw("CASE WHEN total > 100 THEN 'high' ELSE 'low' END as tier"))
    ->get();

Great for creating categories or flags directly inside the DB.

7. PostgreSQL-Specific Functions in Selects

User::select('id', DB::raw("AGE(now(), created_at) AS account_age"))
    ->orderByDesc('account_age')
    ->get();

Or:

Product::select('name', DB::raw("jsonb_array_length(metadata->'variants') as variant_count"))
    ->get();

8. Use `EXPLAIN ANALYZE` for Query Debugging

Run SQL via pgAdmin or terminal to analyze performance:

EXPLAIN ANALYZE SELECT * FROM posts WHERE published = true;

Helps you detect slow queries, missing indexes, and row scan issues.

Summary Table: Advanced Query Builder Features

Feature	Laravel Support	PostgreSQL Power
JSONB Queries	✅ Yes (raw + helper)	✅ Full support
Full-Text Search	✅ (raw)	✅ Ranked & indexed
Arrays	✅ (raw)	✅ Flexible
CTEs	⚠️ Partial (use package)	✅ Full
Window Functions	✅ (raw)	✅ Essential for analytics
CASE Logic	✅ (raw)	✅ In-line tiering

Best Practices for Using PostgreSQL Features in Laravel

Always escape raw input—use bindings
Use GIN indexes for JSONB and arrays
Keep business logic in Laravel, but leverage DB power where needed
Use Laravel packages for CTE and advanced DB support
Profile and monitor complex queries regularly

FAQ

Can I use PostgreSQL’s full-text search without raw SQL? Not yet—use raw queries or a custom macro.

Are PostgreSQL arrays better than pivot tables? Depends. Arrays are flexible but not relational. Use pivot tables for true relationships.

Will using raw queries break Eloquent? No—but they bypass Eloquent features like model events or casts.

Should I use a package for complex queries? Yes. Tools like staudenmeir/laravel-cte help simplify CTEs and recursive queries.

Helpful Resources

Conclusion

PostgreSQL brings incredible depth to the Laravel Query Builder experience when used right. From handling complex filters in JSONB to ranking rows or filtering data with arrays and CTEs, you can unlock efficient, scalable query logic inside your database engine.

Laravel makes it approachable—and with a few raw expressions and helper packages, you can write expressive, advanced SQL logic without sacrificing readability or maintainability.

Whether you’re building analytics dashboards, flexible search engines, or API-heavy platforms, these PostgreSQL features give your Laravel app a serious edge.