PostgreSQL JSONB in Laravel: Indexes & Casting | Mohamed Said [Skip to content](#main) [ ![](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MH.png) Mohamed SaidLaravel Backend Engineer ](https://www.msaied.com/public) - [Home](https://www.msaied.com/public) - [Projects](https://www.msaied.com/public/projects) - [Articles](https://www.msaied.com/public/articles) - [Certificates](https://www.msaied.com/public/certificates) - [About](https://www.msaied.com/public#about) [ Contact](https://www.msaied.com/public#contact) Menu Menu ---- Close - [HomeStart here](https://www.msaied.com/public) - [ProjectsCase studies](https://www.msaied.com/public/projects) - [ArticlesEngineering notes](https://www.msaied.com/public/articles) - [CertificatesCredentials](https://www.msaied.com/public/certificates) - [AboutHow I work](https://www.msaied.com/public#about) - [ContactGet in touch](https://www.msaied.com/public#contact) [Start a conversation](https://www.msaied.com/public#contact) [WhatsApp](https://wa.me/201094619204) [Email](mailto:hello@msaied.com) 1. [Home](https://www.msaied.com/public) 2. / 3. [Articles](https://www.msaied.com/public/articles) 4. / 5. PostgreSQL JSONB in Laravel: Indexing, Querying, and Casting Without the Chaos PostgreSQL JSONB in Laravel: Indexing, Querying, and Casting Without the Chaos =============================================================================== JSONB columns unlock flexible schemas, but without the right indexes and Eloquent integration they become a performance trap. Here is how to use them correctly in production Laravel apps. ![](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp) [Mohamed Said](https://www.msaied.com/public#person) Published 9 Aug 2026 · Updated 9 Aug 2026 · 3 min read ShareCopy linkCopied ![PostgreSQL JSONB in Laravel: Indexing, Querying, and Casting Without the Chaos](https://cdn.msaied.com/526/bc43aae3afe723f9a29f47820735edf5.png) On this page +1. [Why JSONB and Not Just JSON?](#why-jsonb-and-not-just-json) 2. [GIN Indexes: The Right Tool for JSONB](#gin-indexes-the-right-tool-for-jsonb) 3. [Querying JSONB from Eloquent](#querying-jsonb-from-eloquent) 4. [Generated Columns for Selective B-tree Indexes](#generated-columns-for-selective-b-tree-indexes) 5. [Eloquent Casts: Keeping PHP Types Honest](#eloquent-casts-keeping-php-types-honest) 6. [Avoiding the Silent Performance Traps](#avoiding-the-silent-performance-traps) 7. [Takeaways](#takeaways) Why JSONB and Not Just JSON? ---------------------------- PostgreSQL offers two JSON column types. `json` stores raw text and re-parses it on every read. `jsonb` stores a decomposed binary representation, supports indexing, and enables operator-based querying. For any column you will filter or index, always choose `jsonb`. ```sql -- migration $table->jsonb('meta')->nullable(); ``` --- GIN Indexes: The Right Tool for JSONB ------------------------------------- A plain B-tree index on a `jsonb` column is useless for containment queries. You need a **GIN** (Generalized Inverted Index) index. ```php // database/migrations/xxxx_add_gin_index_to_products.php public function up(): void { DB::statement( 'CREATE INDEX products_meta_gin ON products USING GIN (meta)' ); } ``` For queries that target a single known key path, a **GIN index with `jsonb_path_ops`** is smaller and faster: ```php DB::statement( 'CREATE INDEX products_meta_path_gin ON products USING GIN (meta jsonb_path_ops)' ); ``` Use `jsonb_path_ops` when you only need the `@>` containment operator. Use the default opclass when you also need `?`, `?|`, or `?&` key-existence operators. --- Querying JSONB from Eloquent ---------------------------- Laravel's query builder exposes `whereJsonContains`, `whereJsonLength`, and raw expressions for everything else. ```php // Containment — uses the GIN index Product::whereJsonContains('meta->tags', 'featured')->get(); // Key-path equality — add a generated column + B-tree for this pattern Product::whereJsonPath('meta', '$.status', '=', 'active')->get(); // Raw operator when you need full control Product::whereRaw("meta @> ?::jsonb", [json_encode(['tier' => 'pro'])])->get(); ``` > **Tip:** `whereJsonContains` emits the `@>` operator under the hood for PostgreSQL, so your GIN index will be hit. Verify with `EXPLAIN ANALYZE`. --- Generated Columns for Selective B-tree Indexes ---------------------------------------------- When you repeatedly filter on one stable key, a **generated (stored) column** plus a normal B-tree index beats a GIN index on cardinality-heavy data. ```php DB::statement( "ALTER TABLE products ADD COLUMN meta_status TEXT GENERATED ALWAYS AS (meta->>'status') STORED" ); DB::statement( 'CREATE INDEX products_meta_status_btree ON products (meta_status)' ); ``` Now `WHERE meta_status = 'active'` uses a tight B-tree scan instead of a GIN bitmap scan. --- Eloquent Casts: Keeping PHP Types Honest ---------------------------------------- Storing raw arrays is fine for prototypes, but production code deserves typed value objects. ```php // app/Casts/ProductMetaCast.php use Illuminate\Contracts\Database\Eloquent\CastsAttributes; class ProductMetaCast implements CastsAttributes { public function get($model, string $key, $value, array $attributes): ProductMeta { return ProductMeta::fromArray(json_decode($value, true) ?? []); } public function set($model, string $key, $value, array $attributes): string { return json_encode( $value instanceof ProductMeta ? $value->toArray() : $value ); } } ``` ```php // app/Models/Product.php protected $casts = [ 'meta' => ProductMetaCast::class, ]; ``` Your `ProductMeta` value object can enforce invariants, provide typed accessors, and keep business logic out of the model. --- Avoiding the Silent Performance Traps ------------------------------------- - **Never** use `->` or `->>` inside a `WHERE` without a supporting index or generated column — it triggers a sequential scan. - `whereJsonLength` does not use a GIN index; add a generated column if you filter by array length frequently. - Avoid storing deeply nested, frequently-updated structures in JSONB. Write amplification on updates is real. - Run `EXPLAIN (ANALYZE, BUFFERS)` — not just `EXPLAIN` — to confirm index usage and shared-buffer hits. --- Takeaways --------- - Always use `jsonb`, never `json`, for any column you will index or query. - GIN indexes with `jsonb_path_ops` are the default choice; fall back to the full opclass only when you need key-existence operators. - Generated stored columns + B-tree indexes outperform GIN for high-cardinality single-key filters. - Wrap JSONB columns in typed Eloquent casts to enforce invariants at the PHP layer. - Validate every JSONB query with `EXPLAIN (ANALYZE, BUFFERS)` before shipping to production. - [laravel](https://www.msaied.com/public/articles?search=laravel) - [postgresql](https://www.msaied.com/public/articles?search=postgresql) - [jsonb](https://www.msaied.com/public/articles?search=jsonb) - [eloquent](https://www.msaied.com/public/articles?search=eloquent) - [performance](https://www.msaied.com/public/articles?search=performance) Frequently asked questions --------------------------- Does `whereJsonContains` in Laravel use a GIN index on PostgreSQL?Yes. On PostgreSQL, `whereJsonContains` compiles to the `@>` containment operator, which is supported by a GIN index. Confirm with `EXPLAIN ANALYZE` to ensure the planner chooses the index over a sequential scan. When should I use a generated column instead of a GIN index for JSONB?Use a generated stored column with a B-tree index when you repeatedly filter on a single, stable JSONB key with high cardinality. B-tree lookups on a scalar column are faster and cheaper than GIN bitmap scans in those cases. Can I use PHP value objects as Eloquent casts for JSONB columns?Yes. Implement `CastsAttributes`, deserialize the JSON string into your value object in `get`, and serialize it back in `set`. This keeps type safety and business rules at the PHP layer without polluting the model. ![Mohamed Said](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp)About the author ---------------- [Mohamed Said](https://www.msaied.com/public#person)Senior Backend Engineer specializing in Laravel, scalable SaaS platforms, APIs, and cloud infrastructure. I build secure, high-performance web applications that help businesses grow. [About](https://www.msaied.com/public#about) [GitHub ↗](https://github.com/EG-Mohamed) [LinkedIn ↗](https://www.linkedin.com/in/msaiedm/) [WhatsApp ↗](https://wa.me/201094619204) [Email Address ↗](mailto:hello@msaied.com) [My CV ↗](https://drive.google.com/file/u/0/d/1MF20IPRJyzfy32mhEutjL5EpSls0w2Q8/view) [Previous articleFilament v4 Schema-Based Forms: Practical Patterns for the Unified Schema API](https://www.msaied.com/public/articles/filament-v4-schema-based-forms-practical-patterns-for-the-unified-schema-api) [Next articlePostgreSQL JSONB in Laravel: Indexing, Querying, and Casting Without the Bloat](https://www.msaied.com/public/articles/postgresql-jsonb-in-laravel-indexing-querying-and-casting-without-the-bloat) On this page ------------- 1. [Why JSONB and Not Just JSON?](#why-jsonb-and-not-just-json) 2. [GIN Indexes: The Right Tool for JSONB](#gin-indexes-the-right-tool-for-jsonb) 3. [Querying JSONB from Eloquent](#querying-jsonb-from-eloquent) 4. [Generated Columns for Selective B-tree Indexes](#generated-columns-for-selective-b-tree-indexes) 5. [Eloquent Casts: Keeping PHP Types Honest](#eloquent-casts-keeping-php-types-honest) 6. [Avoiding the Silent Performance Traps](#avoiding-the-silent-performance-traps) 7. [Takeaways](#takeaways) ### Have a technical challenge? Tell me what you’re building. I reply within two working days. [Start a conversation](https://www.msaied.com/public#contact) Related articles ----------------- [ ![](https://cdn.msaied.com/740/cce86edc21eddcbdd2f2454fadaf9c70.png) · 3 min read### The Pipeline Pattern in Laravel: Custom Pipelines Beyond Middleware 5 Oct 2026 ](https://www.msaied.com/public/articles/the-pipeline-pattern-in-laravel-custom-pipelines-beyond-middleware-1) [ ![](https://cdn.msaied.com/739/2d6897fdcdcf090613f96f72a64b8a78.png) · 4 min read### MySQL Full-Text Search in Laravel: Indexes, Relevance Scoring, and Boolean Mode 4 Oct 2026 ](https://www.msaied.com/public/articles/mysql-full-text-search-in-laravel-indexes-relevance-scoring-and-boolean-mode) [ ![](https://cdn.msaied.com/738/073696a3fefe18bec825beec5ac658f5.png) · 4 min read### Laravel Queue Rate-Limited Middleware: Throttling Jobs Without Losing Work 4 Oct 2026 ](https://www.msaied.com/public/articles/laravel-queue-rate-limited-middleware-throttling-jobs-without-losing-work) Have a technical challenge? ---------------------------- Tell me what you’re building. I reply within two working days. [Discuss your project ↗](https://www.msaied.com/public#contact) © 2026 Mohamed Said · Built with Laravel, meant to last.Senior Backend Engineer specializing in Laravel, scalable SaaS platforms, APIs, and cloud infrastructure. I build secure, high-performance web applications that help businesses grow. - [Home](https://www.msaied.com/public) - [Articles](https://www.msaied.com/public/articles) - [Certificates](https://www.msaied.com/public/certificates) - [GitHub](https://github.com/EG-Mohamed) - [LinkedIn](https://www.linkedin.com/in/msaiedm/) - [WhatsApp](https://wa.me/201094619204) - [Email Address](mailto:hello@msaied.com) - [My CV](https://drive.google.com/file/u/0/d/1MF20IPRJyzfy32mhEutjL5EpSls0w2Q8/view) - [Sitemap](https://www.msaied.com/public/sitemap.xml)