Laravel and PostgreSQL: Advanced Techniques for Full-Text Search, Window Functions, and CTEs in 2026

Recursive CTEs for Hierarchies

Suppose we have a categories table with a parent_id field. We need to retrieve all subcategories for a given tree node:

// app/Services/CategoryService.php

public function getSubtree(int $rootId): \Illuminate\Support\Collection
{
    $sql = << ' || c.name
            FROM categories c
            INNER JOIN category_tree ct ON ct.id = c.parent_id
        )
        SELECT * FROM category_tree ORDER BY depth, name
    SQL;

    return collect(DB::select($sql, ['root_id' => $rootId]));
}

Recursive CTEs in PostgreSQL work via UNION ALL: the first part is the base case, the second is the recursive step that references the CTE itself. Depth is limited by the max_recursion_depth parameter (default: 100).

Using the staudenmeir/laravel-cte Package

To integrate CTEs with Eloquent syntax, install the package:

composer require staudenmeir/laravel-cte

Once installed, the withExpression() method becomes available in models and the Builder:

use Staudenmeir\LaravelCte\Facades\DB as CteDB;

$results = CteDB::table('orders')
    ->withExpression('monthly_stats', function ($query) {
        $query->from('orders')
            ->selectRaw("DATE_TRUNC('month', created_at) as month, SUM(total) as revenue")
            ->groupByRaw("DATE_TRUNC('month', created_at)");
    })
    ->from('monthly_stats')
    ->orderBy('month')
    ->get();

Full-Text Search with PostgreSQL in Laravel

PostgreSQL's built-in full-text search is vastly more powerful than LIKE '%query%': it understands morphology, stop words, relevance ranking, and supports complex Boolean operators.

Creating a tsvector Index

Let's start with a migration that adds an FTS index:

// database/migrations/2026_01_01_000000_add_fts_to_articles.php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;

return new class extends Migration
{
    public function up(): void
    {
        // Add a generated column to store the tsvector
        DB::statement("
            ALTER TABLE articles
            ADD COLUMN search_vector tsvector
            GENERATED ALWAYS AS (
                to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))
            ) STORED
        ");

        // GIN index for fast searching
        DB::statement("
            CREATE INDEX articles_search_vector_idx
            ON articles USING GIN (search_vector)
        ");
    }

    public function down(): void
    {
        DB::statement('DROP INDEX IF EXISTS articles_search_vector_idx');
        DB::statement('ALTER TABLE articles DROP COLUMN IF EXISTS search_vector');
    }
};

Searching with tsquery and websearch_to_tsquery

The websearch_to_tsquery function (available since PostgreSQL 11) converts user input in web-search format into a valid tsquery, protecting against injection and supporting AND, OR, NOT operators and exact phrases in quotes.

// app/Services/ArticleSearchService.php

use Illuminate\Support\Facades\DB;
use Illuminate\Pagination\LengthAwarePaginator;

public function search(string $query, int $perPage = 15): LengthAwarePaginator
{
    $sanitized = trim($query);

    return DB::table('articles as a')
        ->select([
            'a.id',
            'a.title',
            'a.slug',
            'a.published_at',
            DB::raw("ts_rank(a.search_vector, websearch_to_tsquery('english', ?)) AS relevance"),
            DB::raw("ts_headline('english', a.body, websearch_to_tsquery('english', ?), 'MaxWords=50, MinWords=20, StartSel=, StopSel=') AS snippet"),
        ])
        ->join('users as u', 'u.id', '=', 'a.author_id')
        ->addSelect('u.name as author_name')
        ->whereRaw(
            "a.search_vector @@ websearch_to_tsquery('english', ?)",
            [$sanitized]
        )
        ->where('a.status', 'published')
        ->orderByDesc('relevance')
        ->addBinding([$sanitized, $sanitized], 'select')
        ->paginate($perPage);
}

The ts_headline function automatically generates a snippet with highlighted matches — very handy for displaying search results. The StartSel and StopSel parameters let you specify HTML tags for highlighting.

Multilingual Search and Custom Dictionaries

For projects with multiple languages, you can store the search configuration in a separate field or use dynamic configuration selection:

// Search with dynamic language configuration selection

public function searchMultilang(string $query, string $lang = 'english'): array
{
    $allowedConfigs = ['russian', 'english', 'simple'];
    $config = in_array($lang, $allowedConfigs) ? $lang : 'simple';

    $sql = "SELECT id, title,
                ts_rank(
                    to_tsvector(?, title || ' ' || body),
                    websearch_to_tsquery(?, ?)
                ) AS rank
            FROM articles
            WHERE to_tsvector(?, title || ' ' || body) @@ websearch_to_tsquery(?, ?)
            AND status = 'published'
            ORDER BY rank DESC
            LIMIT 50";

    return DB::select($sql, [$config, $config, $query, $config, $config, $query]);
}

Caching Complex Query Results with Redis

Complex analytical queries with CTEs and window functions can take seconds to execute. Redis is an excellent fit for caching such results with a reasonable TTL.

// app/Services/AnalyticsService.php

use Illuminate\Support\Facades\Cache;
use Illuminate\Support\Facades\DB;

public function getRevenueReport(string $period = '30days'): array
{
    $cacheKey = "analytics:revenue:{$period}:" . now()->format('Y-m-d-H');

    return Cache::store('redis')->remember($cacheKey, 3600, function () use ($period) {
        $interval = match ($period) {
            '7days' => '7 days',
            '90days' => '90 days',
            default => '30 days',
        };

        $sql = <<= NOW() - INTERVAL ':interval'
                AND status = 'completed'
                GROUP BY DATE_TRUNC('day', created_at)
            ),
            moving_avg AS (
                SELECT
                    day,
                    revenue,
                    orders_count,
                    AVG(revenue) OVER (
                        ORDER BY day
                        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
                    ) AS moving_avg_7d
                FROM daily_revenue
            )
            SELECT * FROM moving_avg ORDER BY day
        SQL;

        // Safe interpolation only for whitelisted values
        $safeSql = str_replace(':interval', $interval, $sql);

        return DB::select($safeSql);
    });
}

To invalidate the cache when data changes, use Redis tags:

// Tagged cache invalidation

// When a new order is created:
Cache::store('redis')->tags(['analytics', 'revenue'])->flush();

// When caching:
return Cache::store('redis')->tags(['analytics', 'revenue'])->remember(
    'revenue_report_30d',
    3600,
    fn() => $this->buildRevenueQuery()
);

Note: tagged caching requires Redis or Memcached — the file driver does not support tags.

Query Optimization and Analysis with EXPLAIN ANALYZE

Writing a query is half the battle. Making sure it runs efficiently is the other half. PostgreSQL provides the powerful EXPLAIN ANALYZE tool, which shows the actual execution plan of a query.

// app/Console/Commands/ExplainQuery.php

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

class ExplainQuery extends Command
{
    protected $signature = 'db:explain {--format=text}';

    public function handle(): void
    {
        $sql = <<option('format'));
        $explain = DB::select("EXPLAIN (ANALYZE, BUFFERS, FORMAT {$format}) {$sql}");

        foreach ($explain as $row) {
            $this->line($row->{'QUERY PLAN'} ?? json_encode($row));
        }
    }
}

Key metrics to watch in the EXPLAIN ANALYZE output:

• Seq Scan vs Index Scan — a sequential scan on large tables signals that no suitable index exists.
• actual time — the real execution time for each step in the plan.
• rows — compare the planner's estimate with the actual row count: a large discrepancy indicates stale statistics.
• Buffers: shared hit/read — the number of PostgreSQL cache hits versus disk reads.

Run ANALYZE articles; regularly after bulk inserts to update statistics for the query planner.

Practical Case: Analytical Query with CTEs + Window Functions + FTS

Let's combine all the techniques we've covered into a realistic example. The task: build an analytics page for a blog editor — a list of articles with an FTS filter, relevance ranking, view statistics for a given period, and comparison with the previous period.

// app/Services/ContentAnalyticsService.php

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Cache;

public function getContentAnalytics(string $searchQuery = '', int $days = 30): array
{
    $cacheKey = 'content:analytics:' . md5($searchQuery . $days) . ':' . now()->format('Y-m-d-H');

    return Cache::store('redis')->remember($cacheKey, 1800, function () use ($searchQuery, $days) {

        $hasFts = !empty(trim($searchQuery));

        // FTS condition: if query is empty — skip relevance filtering
        $ftsSelect = $hasFts
            ? "ts_rank(a.search_vector, websearch_to_tsquery('english', :query)) AS fts_rank"
            : '0 AS fts_rank';

        $ftsWhere = $hasFts
            ? "AND a.search_vector @@ websearch_to_tsquery('english', :query2)" : '';

        $sql = <<= NOW() - INTERVAL ':days days' THEN 1 ELSE 0 END) AS views_current,
                    SUM(CASE WHEN viewed_at < NOW() - INTERVAL ':days days'
                        AND viewed_at >= NOW() - INTERVAL ':days2 days' THEN 1 ELSE 0 END) AS views_prev
                FROM page_views
                WHERE viewed_at >= NOW() - INTERVAL ':days3 days'
                GROUP BY article_id
            ),
            article_stats AS (
                SELECT
                    a.id,
                    a.title,
                    a.slug,
                    a.published_at,
                    a.author_id,
                    $ftsSelect,
                    COALESCE(av.views_current, 0) AS views_current,
                    COALESCE(av.views_prev, 0) AS views_prev,
                    ROUND(
                        (COALESCE(av.views_current, 0) - COALESCE(av.views_prev, 0))::numeric
                        / NULLIF(COALESCE(av.views_prev, 0), 0) * 100, 1
                    ) AS views_growth_percent
                FROM articles a
                LEFT JOIN article_views av ON av.article_id = a.id
                WHERE a.status = 'published'
                $ftsWhere
            ),
            ranked_articles AS (
                SELECT
                    *,
                    RANK() OVER (ORDER BY views_current DESC) AS views_rank,
                    RANK() OVER (ORDER BY fts_rank DESC) AS relevance_rank,
                    LAG(views_current) OVER (ORDER BY published_at) AS prev_article_views
                FROM article_stats
            )
            SELECT
                ra.*,
                u.name AS author_name,
                -- Combined score: relevance + popularity
                ROUND(
                    (0.6 * (1.0 / NULLIF(relevance_rank, 0)) +
                     0.4 * (1.0 / NULLIF(views_rank, 0))) * 1000, 2
                ) AS combined_score
            FROM ranked_articles ra
            JOIN users u ON u.id = ra.author_id
            ORDER BY combined_score DESC
            LIMIT 50
        SQL;

        // Safe substitution of numeric parameters (whitelist)
        $daysInt = (int) $days;
        $safeSql = str_replace(
            [':days days', ':days2 days', ':days3 days'],
            ["{$daysInt} days", ($daysInt * 2) . " days", ($daysInt * 2) . " days"],
            $sql
        );

        $bindings = [];
        if ($hasFts) {
            $bindings = ['query' => $searchQuery, 'query2' => $searchQuery];
        }

        $results = $bindings
            ? DB::select($safeSql, $bindings)
            : DB::select($safeSql);

        return [
            'articles' => $results,
            'period_days' => $daysInt,
            'search_query' => $searchQuery,
            'generated_at' => now()->toIso8601String(),
        ];
    });
}

This query demonstrates the real synergy of three techniques:

1. CTE article_views — aggregates views across two periods for subsequent comparison.
2. CTE article_stats — joins article data with view counts and FTS ranking.
3. CTE ranked_articles — applies RANK and LAG window functions to the already-filtered result set.
4. Final SELECT — computes a combined score and joins in author data.
5. Redis cache — prevents repeated execution of the heavy query.

Conclusion

Eloquent and Query Builder are great tools for CRUD operations. But real analytical tasks, hierarchical structures, and full-text search require going beyond their boundaries. PostgreSQL provides a rich arsenal: window functions for calculating rankings and trends, recursive CTEs for working with trees, and built-in FTS with morphology and relevance ranking.

In combination with Laravel, these capabilities are accessible through DB::select(), DB::statement(), and packages like staudenmeir/laravel-cte. Add Redis caching and regular plan analysis via EXPLAIN ANALYZE — and your application will reach a new level of performance and query expressiveness in 2026.