Home New Trending Search
About Privacy Terms
#
#JSONB
Posts tagged #JSONB on Bluesky
Carolina Code Conference
Carolina Code Conference
@codescarolina.bsky.social
2 weeks ago
PostgreSQL JSONB: Query & Index Your Data! #shorts
PostgreSQL JSONB: Query & Index Your Data! #shorts Discover how PostgreSQL's JSONB field type can simplify handling and querying JSON data within your database. Learn how it indexes these fields for efficient data retrieval. #PostgreSQL #JSONB #database #query #indexing

FYI: PostgreSQL JSONB: Query & Index Your Data! #shorts: Discover how PostgreSQL's JSONB field type can simplify handling and querying JSON data within your database. Learn how it indexes these fields for efficient data retrieval. #PostgreSQL #JSONB #database #query #indexing

0 0 0 0
Carolina Code Conference
Carolina Code Conference
@codescarolina.bsky.social
1 month ago
PostgreSQL JSONB: Query & Index Your Data! #shorts
PostgreSQL JSONB: Query & Index Your Data! #shorts Discover how PostgreSQL's JSONB field type can simplify handling and querying JSON data within your database. Learn how it indexes these fields for efficient data retrieval. #PostgreSQL #JSONB #database #query #indexing

ICYMI: PostgreSQL JSONB: Query & Index Your Data! #shorts: Discover how PostgreSQL's JSONB field type can simplify handling and querying JSON data within your database. Learn how it indexes these fields for efficient data retrieval. #PostgreSQL #JSONB #database #query #indexing

2 0 0 0
Carolina Code Conference
Carolina Code Conference
@codescarolina.bsky.social
1 month ago
PostgreSQL JSONB: Query & Index Your Data! #shorts
PostgreSQL JSONB: Query & Index Your Data! #shorts Discover how PostgreSQL's JSONB field type can simplify handling and querying JSON data within your database. Learn how it indexes these fields for efficient data retrieval. #PostgreSQL #JSONB #database #query #indexing

PostgreSQL JSONB: Query & Index Your Data! #shorts: Discover how PostgreSQL's JSONB field type can simplify handling and querying JSON data within your database. Learn how it indexes these fields for efficient data retrieval. #PostgreSQL #JSONB #database #query #indexing

1 0 0 0
PostgreSQL
PostgreSQL
@postgresql.activitypub.awakari.com.ap.brid.gy
5 months ago
Post image

Нормализация vs Денормализация: Mongo, Postgres и реальная жизнь Нормализация vs Денормализация: Mongo, Postgres и реальна...

#нормализация #денормализация #jsonb #агрегаты #DDD #object-relational #mismatch #ORM #грабли #Polyglot #persistence

Origin | Interest | Match

0 0 0 0
Sven Geggus
Sven Geggus
@giggls.bsky.geggus.net
7 months ago

Is it possible to create an index for a given key in a #PostgreSQL #jsonb column to speed up such a query?
select tags from myobjects where tags ->> 'country'='de';

0 0 0 0
PostgreSQL
PostgreSQL
@postgresql.activitypub.awakari.com.ap.brid.gy
7 months ago
Post image

Расширение jsquery для PostgreSQL — точные и быстрые выборки из JSONB Привет, Хабр! Это Антон Дятлов, инженер по защите ...

#selectel #postgresql #jsquery #sql #json #jsonb

Origin | Interest | Match

0 0 0 0
PostgreSQL
PostgreSQL
@postgresql.activitypub.awakari.com.ap.brid.gy
7 months ago
Awakari App

What does 'b' stand for in PostgreSQL's JSONB data type? – dba.stackexchange.com While not a technical question per se, I'm curious about the actual meaning of b in PostgreSQL'...

#postgresql #jsonb

Origin | Interest | Match

0 0 0 0
PostgreSQL
PostgreSQL
@postgresql.activitypub.awakari.com.ap.brid.gy
8 months ago
Awakari App

Why does a Json Path inequality over an array not work? – dba.stackexchange.com Given the following jsonb array, I want to filter it only for certain objects. [ { "id": "123" }, {...

#postgresql #json #jsonb #json-path

Origin | Interest | Match

0 0 0 0
Michael Ayres
Michael Ayres
@random-coder-1.bsky.social
9 months ago
screenshot of pg admin, two queries unioned together and the results. Note: using postgres 15 here. Queries: select jsonb_set('{"example": "1234"}'::jsonb, '{example}', to_jsonb(null::text)), 1 as record_number union select jsonb_set_lax('{"example": "1234"}'::jsonb, '{example}', to_jsonb(null::text)), 2 as record_number; `record_number` was added to show the ordering of the query results (nulls last by default). Query one, using jsonb_set, has a null result. Query two, using jsonb_set_lax, has the full result I was looking for, with a json null value for the property.

screenshot of pg admin, two queries unioned together and the results. Note: using postgres 15 here. Queries: select jsonb_set('{"example": "1234"}'::jsonb, '{example}', to_jsonb(null::text)), 1 as record_number union select jsonb_set_lax('{"example": "1234"}'::jsonb, '{example}', to_jsonb(null::text)), 2 as record_number; `record_number` was added to show the ordering of the query results (nulls last by default). Query one, using jsonb_set, has a null result. Query two, using jsonb_set_lax, has the full result I was looking for, with a json null value for the property.

Took me a bit to find how to make a null db value set into a json object in postgres.

`jsonb_set_lax` was added ~ pg v 13.

`jsonb_set` will make the entire object null if the value being set is null.

#pg #json #jsonb

0 0 0 0
Roger
Roger
@anyoldgeek.bsky.social
10 months ago

In the “might be useful someday” category, I’m now retrieving and storing #ansible facts in a #jsonb column in a new table in our #PostgreSQL system inventory #database.

1 0 0 0
Julien Jakubowski
Julien Jakubowski
@jaku.dev
10 months ago
Preview
PostgreSQL JSONB - Powerful Storage for Semi-Structured Data Object-oriented or relational? Why not both? Let's look on PostgreSQL JSONB column type and how it can help you to deliver your applications faster. We dived into how it's stored, when and why it can ...

Just read a great blog post covering PostgreSQL’s JSONB - from what it enables, to how you can use it effectively, plus tradeoffs to be aware of.

Super useful if you’re dealing with semi-structured data.
www.architecture-weekly.com/p/postgresql...

#PostgreSQL #JSONB #DevTips

3 1 0 0
PostgreSQL
PostgreSQL
@postgresql.activitypub.awakari.com.ap.brid.gy
10 months ago
Post image

PostgreSQL Antipatterns: создаем JSON из строки Я уже не раз подним...

habr.com/ru/companies/tensor/arti...

#postgresql #sql #sql #tips #and #tricks #json #jsonb

Event Attributes

0 1 0 0
DEV Community
DEV Community
@dev.to.web.brid.gy
10 months ago
Preview
PostgreSQL JSONB Indexing Limitations with B-Tree and GIN In a previous post, I highlighted a significant **limitation of relational databases: a One-to-Many relationship requires two tables** , yet no index can optimize access with selective predicates on both of them, resulting in **unnecessary rows that must be joined before elimination**. A comment from @dogacel suggested continuing with **PostgreSQL's JSON** approach, so here it is. SQL databases have evolved into general-purpose or converged databases capable of storing documents, like JSONB in PostgreSQL. However, they face challenges with indexing compared to document databases like MongoDB, which offer multi-key indexes for optimizing equality, sort, and range filtering. Let's explore JSONB documents and their indexing options. Instead of an Order Details table, I embed the details into the Orders table: create table orders( primary key(id) , id bigserial , country_id int , created_at timestamptz default clock_timestamp() , details jsonb ); I insert one million orders similar to the previous post: insert into orders (country_id, details) select (10 * random())::int as country_id, -- generate a random country_id ( select jsonb_agg( jsonb_build_object( 'line', gs, 'product_id', ((log(2, (1 + o.id * random())::numeric)::int)), 'quantity', (100 * random())::int ) ) from generate_series(1, 10) as gs -- create 10 lines per order ) as details from generate_series(1, 1000000) as o(id); -- generate 1,000,000 orders Here is an example of Order with its details: postgres=# select * from orders limit 1; -[ RECORD 1 ]-------------------------------------------------------- id | 1 country_id | 8 created_at | 2025-04-15 19:12:27.525731+00 details | [ {"line": 1, "quantity": 6, "product_id": 0}, {"line": 2, "quantity": 91, "product_id": 1}, {"line": 3, "quantity": 66, "product_id": 1}, {"line": 4, "quantity": 100, "product_id": 1}, {"line": 5, "quantity": 15, "product_id": 0}, {"line": 6, "quantity": 80, "product_id": 0}, {"line": 7, "quantity": 96, "product_id": 1}, {"line": 8, "quantity": 9, "product_id": 0}, {"line": 9, "quantity": 89, "product_id": 0}, {"line": 10, "quantity": 14, "product_id": 0} ] I want to run a simple query to retrieve the last ten orders from a specific country that contain a specific product: SELECT country_id, details FROM orders WHERE country_id = 1 AND details @> '[{"product_id": 15}]' ORDER BY created_at DESC LIMIT 10 ; Be aware of filters at various levels: on the order for country and date, and on the details for the product. With a normalized model and two tables, it was impossible to have one index covering all those filters. However, using MongoDB made it straightforward to create an index on "country_id", "order_details.product_id", and "created_at", resulting in only ten documents being read. ## Expression index on scalar values The normalized columns in the row can easily be indexed with a B-Tree: CREATE INDEX orders1 ON orders ("country_id", "created_at" desc) ; A similar B-Tree index can be created on the JSONB attributes as long as they are top-level, or sub-documents without repeating groups (without JSON array). However, it is impossible to create such index when the JSON path has an array because in PostgreSQL a B-Tree index cannot have multiple index key for the same row. Of course, it is possible to create any index, as there's no schema declared for the JSONB content: CREATE INDEX orders0 ON orders ( (("details"->>'product_id')::int) ) ; However, such index will never find a document where "product_id" is an array as it indexes a scalar expression. A query on the content of the array cannot use the index: postgres=# set enable_seqscan=off; SET postgres=# EXPLAIN (ANALYZE, COSTS OFF, BUFFERS, SUMMARY OFF) SELECT * from orders WHERE details @> '[{"product_id": 15}]' ; QUERY PLAN --------------------------------------------------------------------- Seq Scan on orders (actual time=38.631..1293.388 rows=446355 loops=1) Filter: (details @> '[{"product_id": 15}]'::jsonb) Rows Removed by Filter: 553645 Buffers: shared hit=16128 read=94984 ## Generalized Inverted Index (GIN) for arrays In order to index though an array, we need an inverted index and PostgreSQL has GIN: CREATE INDEX orders2 ON orders using GIN ( details ) ; This index is used for the predicate on the array: postgres=# set enable_seqscan=off; SET postgres=# EXPLAIN (ANALYZE, COSTS OFF, BUFFERS, SUMMARY OFF) SELECT * from orders WHERE details @> '[{"product_id": 15}]' ; QUERY PLAN ------------------------------------------------------------------------------------- Bitmap Heap Scan on orders (actual time=95.272..1196.876 rows=446355 loops=1) Recheck Cond: (details @> '[{"product_id": 15}]'::jsonb) Rows Removed by Index Recheck: 303886 Heap Blocks: exact=42718 lossy=65930 Buffers: shared hit=371 read=108648 written=3415 -> Bitmap Index Scan on orders2 (actual time=63.730..63.730 rows=499460 loops=1) Index Cond: (details @> '[{"product_id": 15}]'::jsonb) Buffers: shared hit=371 I disabled Seq Scan because this predicate alone is not selective enough, returning half of the rows, but it proves that the GIN index can be used. However, when I run the full query with additional WHERE and ORDER BY LIMIT filters, which are covered by the B-Tree index, the GIN index is no longer used: postgres=# EXPLAIN (ANALYZE, COSTS OFF, BUFFERS, SUMMARY OFF) SELECT country_id, details FROM orders WHERE country_id = 1 AND details @> '[{"product_id": 15}]' ORDER BY created_at DESC LIMIT 10 ; QUERY PLAN ------------------------------------------------------------------------------------- Limit (actual time=0.036..0.117 rows=10 loops=1) Buffers: shared hit=32 -> Index Scan using orders1 on orders (actual time=0.035..0.115 rows=10 loops=1) Index Cond: (country_id = 1) Filter: (details @> '[{"product_id": 15}]'::jsonb) Rows Removed by Filter: 38 Buffers: shared hit=32 To understand the reason, I remove the LIMIT clause: postgres=# EXPLAIN (ANALYZE, COSTS OFF, BUFFERS, SUMMARY OFF) SELECT country_id, details FROM orders WHERE country_id = 1 AND details @> '[{"product_id": 15}]' ORDER BY created_at DESC -- LIMIT 10 ; QUERY PLAN ------------------------------------------------------------------------------------------------------- Gather Merge (actual time=237.895..257.523 rows=44864 loops=1) Workers Planned: 2 Workers Launched: 2 Buffers: shared hit=158 read=61738, temp read=4584 written=4592 -> Sort (actual time=222.683..226.142 rows=14955 loops=3) Sort Key: created_at DESC Sort Method: external merge Disk: 12688kB Buffers: shared hit=158 read=61738, temp read=4584 written=4592 Worker 0: Sort Method: external merge Disk: 11616kB Worker 1: Sort Method: external merge Disk: 12368kB -> Parallel Bitmap Heap Scan on orders (actual time=74.822..211.433 rows=14955 loops=3) Recheck Cond: ((country_id = 1) AND (details @> '[{"product_id": 15}]'::jsonb)) Rows Removed by Index Recheck: 96641 Heap Blocks: exact=9701 lossy=11292 Buffers: shared hit=145 read=61733 -> BitmapAnd (actual time=78.189..78.190 rows=0 loops=1) Buffers: shared hit=145 read=613 -> Bitmap Index Scan on orders1 (actual time=11.496..11.497 rows=100362 loops=1) Index Cond: (country_id = 1) Buffers: shared read=387 -> Bitmap Index Scan on orders2 (actual time=64.445..64.445 rows=499460 loops=1) Index Cond: (details @> '[{"product_id": 15}]'::jsonb) Buffers: shared hit=145 read=226 PostgreSQL can utilize both indexes, but it requires a Bitmap Scan to combine them, which does not preserve the B-Tree index order. Consequently, it must perform a Sort operation that reads all rows before returning the first one, making it unsuitable for the LIMIT clause due to excessive row reading. ## Composite B-Tree + GIN with extension With the `btree_gin` extension available in contrib, I can create a single index that is supposed to combine the advantages of B-Tree and GIN indexes: postgres=# CREATE EXTENSION BTREE_GIN; CREATE EXTENSION postgres=# CREATE INDEX orders3 ON orders using GIN (country_id , details, created_at); CREATE INDEX Running it without LIMIT shows that it doesn't solve the problem: postgres=# EXPLAIN (ANALYZE, COSTS OFF, BUFFERS, SUMMARY OFF) SELECT country_id, details FROM orders WHERE country_id = 1 AND details @> '[{"product_id": 15}]' ORDER BY created_at DESC -- LIMIT 10 ; QUERY PLAN --------------------------------------------------------------------------------------------------- Gather Merge (actual time=123.675..143.726 rows=44864 loops=1) Workers Planned: 2 Workers Launched: 2 Buffers: shared hit=237 read=40117, temp read=4585 written=4594 -> Sort (actual time=109.979..113.574 rows=14955 loops=3) Sort Key: created_at DESC Sort Method: external merge Disk: 12456kB Buffers: shared hit=237 read=40117, temp read=4585 written=4594 Worker 0: Sort Method: external merge Disk: 11720kB Worker 1: Sort Method: external merge Disk: 12504kB -> Parallel Bitmap Heap Scan on orders (actual time=18.096..98.799 rows=14955 loops=3) Recheck Cond: ((country_id = 1) AND (details @> '[{"product_id": 15}]'::jsonb)) Rows Removed by Index Recheck: 1760 Heap Blocks: exact=13486 Buffers: shared hit=226 read=40110 -> Bitmap Index Scan on orders3 (actual time=25.197..25.197 rows=50144 loops=1) Index Cond: ((country_id = 1) AND (details @> '[{"product_id": 15}]'::jsonb)) Buffers: shared hit=226 read=251 Only one index was scanned, with all conditions in its Index Cond, but it is still a Bitmap Scan which doesn't preserve the index key order, because that's how PostgreSQL inverted index works. A GIN index, even with the BTREE_GIN extension, is not an equivalent to MongoDB multi-key indexes which have their index entries ordered. Even if my new index has all columns, it is not used by the pagination query: postgres=# EXPLAIN (ANALYZE, COSTS OFF, BUFFERS, SUMMARY OFF) SELECT country_id, details FROM orders WHERE country_id = 1 AND details @> '[{"product_id": 15}]' ORDER BY created_at DESC LIMIT 10 ; QUERY PLAN ------------------------------------------------------------------------------------- Limit (actual time=0.034..0.117 rows=10 loops=1) Buffers: shared hit=32 -> Index Scan using orders1 on orders (actual time=0.034..0.115 rows=10 loops=1) Index Cond: (country_id = 1) Filter: (details @> '[{"product_id": 15}]'::jsonb) Rows Removed by Filter: 38 Buffers: shared hit=32 If I drop the B-Tree index, the B-Tree GIN is used, but not efficient as it has to read and sort all rows before returning the ten row for the result: postgres=# drop index orders1; DROP INDEX postgres=# EXPLAIN (ANALYZE, COSTS OFF, BUFFERS, SUMMARY OFF) SELECT country_id, details FROM orders WHERE country_id = 1 AND details @> '[{"product_id": 15}]' ORDER BY created_at DESC LIMIT 10 ; QUERY PLAN --------------------------------------------------------------------------------------------------- Limit (actual time=215.701..215.704 rows=10 loops=1) Buffers: shared hit=229 read=40107 written=5 -> Sort (actual time=215.700..215.701 rows=10 loops=1) Sort Key: created_at DESC Sort Method: top-N heapsort Memory: 46kB Buffers: shared hit=229 read=40107 written=5 -> Bitmap Heap Scan on orders (actual time=31.466..204.149 rows=44864 loops=1) Recheck Cond: ((country_id = 1) AND (details @> '[{"product_id": 15}]'::jsonb)) Rows Removed by Index Recheck: 5280 Heap Blocks: exact=39859 Buffers: shared hit=229 read=40107 written=5 -> Bitmap Index Scan on orders3 (actual time=24.544..24.544 rows=50144 loops=1) Index Cond: ((country_id = 1) AND (details @> '[{"product_id": 15}]'::jsonb)) Buffers: shared hit=229 read=248 Seeing all conditions in the Index Cond is not sufficient, especially with GIN indexes because they return false positives that must be rechecked later. Even if you don't have an ORDER BY LIMIT, the scan itself reads more rows than necessary, as reported by Rows Removed by Index Recheck. ## Conclusion PostgreSQL's JSONB with GIN indexing allows for schema-on-read capabilities within a schema-on-write relational model. While it enables document storage, it does not convert PostgreSQL into a document database. Although GIN indexes efficiently handle equality predicates on array items, they are less effective for range or sorting operations compared to MongoDB's multi-key indexes, which support equality, sorting, and range queries transparently. You may use JSON datatypes to provide more flexibility to the relational model, but SQL databases should be used as relational databases because their engine is optimized for it. Always check the execution plan to avoid surprises.
0 0 0 0
Forem
Forem
@forem.com.web.brid.gy
10 months ago
Preview
PostgreSQL JSONB Indexing Limitations with B-Tree and GIN In a previous post, I highlighted a significant **limitation of relational databases: a One-to-Many relationship requires two tables** , yet no index can optimize access with selective predicates on both of them, resulting in **unnecessary rows that must be joined before elimination**. A comment from @dogacel suggested continuing with **PostgreSQL's JSON** approach, so here it is. SQL databases have evolved into general-purpose or converged databases capable of storing documents, like JSONB in PostgreSQL. However, they face challenges with indexing compared to document databases like MongoDB, which offer multi-key indexes for optimizing equality, sort, and range filtering. Let's explore JSONB documents and their indexing options. Instead of an Order Details table, I embed the details into the Orders table: create table orders( primary key(id) , id bigserial , country_id int , created_at timestamptz default clock_timestamp() , details jsonb ); I insert one million orders similar to the previous post: insert into orders (country_id, details) select (10 * random())::int as country_id, -- generate a random country_id ( select jsonb_agg( jsonb_build_object( 'line', gs, 'product_id', ((log(2, (1 + o.id * random())::numeric)::int)), 'quantity', (100 * random())::int ) ) from generate_series(1, 10) as gs -- create 10 lines per order ) as details from generate_series(1, 1000000) as o(id); -- generate 1,000,000 orders Here is an example of Order with its details: postgres=# select * from orders limit 1; -[ RECORD 1 ]-------------------------------------------------------- id | 1 country_id | 8 created_at | 2025-04-15 19:12:27.525731+00 details | [ {"line": 1, "quantity": 6, "product_id": 0}, {"line": 2, "quantity": 91, "product_id": 1}, {"line": 3, "quantity": 66, "product_id": 1}, {"line": 4, "quantity": 100, "product_id": 1}, {"line": 5, "quantity": 15, "product_id": 0}, {"line": 6, "quantity": 80, "product_id": 0}, {"line": 7, "quantity": 96, "product_id": 1}, {"line": 8, "quantity": 9, "product_id": 0}, {"line": 9, "quantity": 89, "product_id": 0}, {"line": 10, "quantity": 14, "product_id": 0} ] I want to run a simple query to retrieve the last ten orders from a specific country that contain a specific product: SELECT country_id, details FROM orders WHERE country_id = 1 AND details @> '[{"product_id": 15}]' ORDER BY created_at DESC LIMIT 10 ; Be aware of filters at various levels: on the order for country and date, and on the details for the product. With a normalized model and two tables, it was impossible to have one index covering all those filters. However, using MongoDB made it straightforward to create an index on "country_id", "order_details.product_id", and "created_at", resulting in only ten documents being read. ## Expression index on scalar values The normalized columns in the row can easily be indexed with a B-Tree: CREATE INDEX orders1 ON orders ("country_id", "created_at" desc) ; A similar B-Tree index can be created on the JSONB attributes as long as they are top-level, or sub-documents without repeating groups (without JSON array). However, it is impossible to create such index when the JSON path has an array because in PostgreSQL a B-Tree index cannot have multiple index key for the same row. Of course, it is possible to create any index, as there's no schema declared for the JSONB content: CREATE INDEX orders0 ON orders ( (("details"->>'product_id')::int) ) ; However, such index will never find a document where "product_id" is an array as it indexes a scalar expression. A query on the content of the array cannot use the index: postgres=# set enable_seqscan=off; SET postgres=# EXPLAIN (ANALYZE, COSTS OFF, BUFFERS, SUMMARY OFF) SELECT * from orders WHERE details @> '[{"product_id": 15}]' ; QUERY PLAN --------------------------------------------------------------------- Seq Scan on orders (actual time=38.631..1293.388 rows=446355 loops=1) Filter: (details @> '[{"product_id": 15}]'::jsonb) Rows Removed by Filter: 553645 Buffers: shared hit=16128 read=94984 ## Generalized Inverted Index (GIN) for arrays In order to index though an array, we need an inverted index and PostgreSQL has GIN: CREATE INDEX orders2 ON orders using GIN ( details ) ; This index is used for the predicate on the array: postgres=# set enable_seqscan=off; SET postgres=# EXPLAIN (ANALYZE, COSTS OFF, BUFFERS, SUMMARY OFF) SELECT * from orders WHERE details @> '[{"product_id": 15}]' ; QUERY PLAN ------------------------------------------------------------------------------------- Bitmap Heap Scan on orders (actual time=95.272..1196.876 rows=446355 loops=1) Recheck Cond: (details @> '[{"product_id": 15}]'::jsonb) Rows Removed by Index Recheck: 303886 Heap Blocks: exact=42718 lossy=65930 Buffers: shared hit=371 read=108648 written=3415 -> Bitmap Index Scan on orders2 (actual time=63.730..63.730 rows=499460 loops=1) Index Cond: (details @> '[{"product_id": 15}]'::jsonb) Buffers: shared hit=371 I disabled Seq Scan because this predicate alone is not selective enough, returning half of the rows, but it proves that the GIN index can be used. However, when I run the full query with additional WHERE and ORDER BY LIMIT filters, which are covered by the B-Tree index, the GIN index is no longer used: postgres=# EXPLAIN (ANALYZE, COSTS OFF, BUFFERS, SUMMARY OFF) SELECT country_id, details FROM orders WHERE country_id = 1 AND details @> '[{"product_id": 15}]' ORDER BY created_at DESC LIMIT 10 ; QUERY PLAN ------------------------------------------------------------------------------------- Limit (actual time=0.036..0.117 rows=10 loops=1) Buffers: shared hit=32 -> Index Scan using orders1 on orders (actual time=0.035..0.115 rows=10 loops=1) Index Cond: (country_id = 1) Filter: (details @> '[{"product_id": 15}]'::jsonb) Rows Removed by Filter: 38 Buffers: shared hit=32 To understand the reason, I remove the LIMIT clause: postgres=# EXPLAIN (ANALYZE, COSTS OFF, BUFFERS, SUMMARY OFF) SELECT country_id, details FROM orders WHERE country_id = 1 AND details @> '[{"product_id": 15}]' ORDER BY created_at DESC -- LIMIT 10 ; QUERY PLAN ------------------------------------------------------------------------------------------------------- Gather Merge (actual time=237.895..257.523 rows=44864 loops=1) Workers Planned: 2 Workers Launched: 2 Buffers: shared hit=158 read=61738, temp read=4584 written=4592 -> Sort (actual time=222.683..226.142 rows=14955 loops=3) Sort Key: created_at DESC Sort Method: external merge Disk: 12688kB Buffers: shared hit=158 read=61738, temp read=4584 written=4592 Worker 0: Sort Method: external merge Disk: 11616kB Worker 1: Sort Method: external merge Disk: 12368kB -> Parallel Bitmap Heap Scan on orders (actual time=74.822..211.433 rows=14955 loops=3) Recheck Cond: ((country_id = 1) AND (details @> '[{"product_id": 15}]'::jsonb)) Rows Removed by Index Recheck: 96641 Heap Blocks: exact=9701 lossy=11292 Buffers: shared hit=145 read=61733 -> BitmapAnd (actual time=78.189..78.190 rows=0 loops=1) Buffers: shared hit=145 read=613 -> Bitmap Index Scan on orders1 (actual time=11.496..11.497 rows=100362 loops=1) Index Cond: (country_id = 1) Buffers: shared read=387 -> Bitmap Index Scan on orders2 (actual time=64.445..64.445 rows=499460 loops=1) Index Cond: (details @> '[{"product_id": 15}]'::jsonb) Buffers: shared hit=145 read=226 PostgreSQL can utilize both indexes, but it requires a Bitmap Scan to combine them, which does not preserve the B-Tree index order. Consequently, it must perform a Sort operation that reads all rows before returning the first one, making it unsuitable for the LIMIT clause due to excessive row reading. ## Composite B-Tree + GIN with extension With the `btree_gin` extension available in contrib, I can create a single index that is supposed to combine the advantages of B-Tree and GIN indexes: postgres=# CREATE EXTENSION BTREE_GIN; CREATE EXTENSION postgres=# CREATE INDEX orders3 ON orders using GIN (country_id , details, created_at); CREATE INDEX Running it without LIMIT shows that it doesn't solve the problem: postgres=# EXPLAIN (ANALYZE, COSTS OFF, BUFFERS, SUMMARY OFF) SELECT country_id, details FROM orders WHERE country_id = 1 AND details @> '[{"product_id": 15}]' ORDER BY created_at DESC -- LIMIT 10 ; QUERY PLAN --------------------------------------------------------------------------------------------------- Gather Merge (actual time=123.675..143.726 rows=44864 loops=1) Workers Planned: 2 Workers Launched: 2 Buffers: shared hit=237 read=40117, temp read=4585 written=4594 -> Sort (actual time=109.979..113.574 rows=14955 loops=3) Sort Key: created_at DESC Sort Method: external merge Disk: 12456kB Buffers: shared hit=237 read=40117, temp read=4585 written=4594 Worker 0: Sort Method: external merge Disk: 11720kB Worker 1: Sort Method: external merge Disk: 12504kB -> Parallel Bitmap Heap Scan on orders (actual time=18.096..98.799 rows=14955 loops=3) Recheck Cond: ((country_id = 1) AND (details @> '[{"product_id": 15}]'::jsonb)) Rows Removed by Index Recheck: 1760 Heap Blocks: exact=13486 Buffers: shared hit=226 read=40110 -> Bitmap Index Scan on orders3 (actual time=25.197..25.197 rows=50144 loops=1) Index Cond: ((country_id = 1) AND (details @> '[{"product_id": 15}]'::jsonb)) Buffers: shared hit=226 read=251 Only one index was scanned, with all conditions in its Index Cond, but it is still a Bitmap Scan which doesn't preserve the index key order, because that's how PostgreSQL inverted index works. A GIN index, even with the BTREE_GIN extension, is not an equivalent to MongoDB multi-key indexes which have their index entries ordered. Even if my new index has all columns, it is not used by the pagination query: postgres=# EXPLAIN (ANALYZE, COSTS OFF, BUFFERS, SUMMARY OFF) SELECT country_id, details FROM orders WHERE country_id = 1 AND details @> '[{"product_id": 15}]' ORDER BY created_at DESC LIMIT 10 ; QUERY PLAN ------------------------------------------------------------------------------------- Limit (actual time=0.034..0.117 rows=10 loops=1) Buffers: shared hit=32 -> Index Scan using orders1 on orders (actual time=0.034..0.115 rows=10 loops=1) Index Cond: (country_id = 1) Filter: (details @> '[{"product_id": 15}]'::jsonb) Rows Removed by Filter: 38 Buffers: shared hit=32 If I drop the B-Tree index, the B-Tree GIN is used, but not efficient as it has to read and sort all rows before returning the ten row for the result: postgres=# drop index orders1; DROP INDEX postgres=# EXPLAIN (ANALYZE, COSTS OFF, BUFFERS, SUMMARY OFF) SELECT country_id, details FROM orders WHERE country_id = 1 AND details @> '[{"product_id": 15}]' ORDER BY created_at DESC LIMIT 10 ; QUERY PLAN --------------------------------------------------------------------------------------------------- Limit (actual time=215.701..215.704 rows=10 loops=1) Buffers: shared hit=229 read=40107 written=5 -> Sort (actual time=215.700..215.701 rows=10 loops=1) Sort Key: created_at DESC Sort Method: top-N heapsort Memory: 46kB Buffers: shared hit=229 read=40107 written=5 -> Bitmap Heap Scan on orders (actual time=31.466..204.149 rows=44864 loops=1) Recheck Cond: ((country_id = 1) AND (details @> '[{"product_id": 15}]'::jsonb)) Rows Removed by Index Recheck: 5280 Heap Blocks: exact=39859 Buffers: shared hit=229 read=40107 written=5 -> Bitmap Index Scan on orders3 (actual time=24.544..24.544 rows=50144 loops=1) Index Cond: ((country_id = 1) AND (details @> '[{"product_id": 15}]'::jsonb)) Buffers: shared hit=229 read=248 Seeing all conditions in the Index Cond is not sufficient, especially with GIN indexes because they return false positives that must be rechecked later. Even if you don't have an ORDER BY LIMIT, the scan itself reads more rows than necessary, as reported by Rows Removed by Index Recheck. ## Conclusion PostgreSQL's JSONB with GIN indexing allows for schema-on-read capabilities within a schema-on-write relational model. While it enables document storage, it does not convert PostgreSQL into a document database. Although GIN indexes efficiently handle equality predicates on array items, they are less effective for range or sorting operations compared to MongoDB's multi-key indexes, which support equality, sorting, and range queries transparently. You may use JSON datatypes to provide more flexibility to the relational model, but SQL databases should be used as relational databases because their engine is optimized for it. Always check the execution plan to avoid surprises.
0 0 0 0
Federico Campoli
Federico Campoli
@pgdba.org
11 months ago
Preview
Leveraging PostgreSQL 17's JSONB for Enhanced Data Modeling and Performance JSONB has been part of PostgreSQL since the ancient version 9.4. PostgreSQL 17's lates JSONB improvements provides a flexible and efficient way to store…

The Slides from my talk at the Incontro DevOps 2025 in Bologna are now available!
Check out the JSONB capabilities in PostgreSQL 17 here!

speakerdeck.com/the4thdoctor...

#PostgreSQL #JSONB #DevOps #IncontroDevOps #Bologna #Databases

4 1 0 0
PostgreSQL
PostgreSQL
@postgresql.activitypub.awakari.com.ap.brid.gy
1 year ago
Post image

Использование JSONB-полей вместо EAV в PostgreSQL На одной из ...

habr.com/ru/companies/sigma/artic...

#postgresql #jsonb #sql #eav #crm #gin

Event Attributes

0 0 0 0
PostgreSQL
PostgreSQL
@postgresql.activitypub.awakari.com.ap.brid.gy
1 year ago
Post image

Использование JSONB-полей вместо EAV в PostgreSQL На одной из ...

habr.com/ru/companies/sigma/artic...

#postgresql #jsonb #sql #eav #crm #gin

Event Attributes

0 0 0 0
PostgreSQL
PostgreSQL
@postgresql.activitypub.awakari.com.ap.brid.gy
1 year ago
Original post on habr.com

Работа с колонками типа JSON в PostgreSQL (на примере исполь...

habr.com/ru/articles/883884/

#PostgreSQL #json #jsonb #jsonb_to_record #jsonb_to_recordset #json_array_elements […]

[Original post on habr.com]

0 0 0 0
PostgreSQL
PostgreSQL
@postgresql.activitypub.awakari.com.ap.brid.gy
1 year ago
Awakari App

Check jsonb array element existence in spring data jpa native query I have to check if there is i...

stackoverflow.com/questions/79412290/check...

#java #sql #postgresql #spring-data-jpa #jsonb

Event Attributes

0 0 0 0
PostgreSQL
PostgreSQL
@postgresql.activitypub.awakari.com.ap.brid.gy
1 year ago
Awakari App

check jsonb array element existence in spring data jpa native query I have to check if there is i...

stackoverflow.com/questions/79412290/check...

#java #postgresql #spring-data-jpa #jsonb

Event Attributes

1 1 0 0
PostgreSQL
PostgreSQL
@postgresql.activitypub.awakari.com.ap.brid.gy
1 year ago
Awakari App

Find row where an exact JSON key value pair exists I have a table in PostgreSQL that contains two...

stackoverflow.com/questions/79407784/find-...

#postgresql #jsonb

Event Attributes

0 0 0 0
PostgreSQL
PostgreSQL
@postgresql.activitypub.awakari.com.ap.brid.gy
1 year ago
Awakari App

PostgreSQL - Find row where an exact JSON key value pair exists I have a table in PostgreSQL that...

stackoverflow.com/questions/79407784/postg...

#postgresql #jsonb

Event Attributes

0 0 0 0
PostgreSQL
PostgreSQL
@postgresql.activitypub.awakari.com.ap.brid.gy
1 year ago
Awakari App

How do you remove an object from an array stored in a JSONB column where the object property is e...

stackoverflow.com/questions/79404083/how-d...

#postgresql #jsonb

Event Attributes

0 0 0 0
PostgreSQL
PostgreSQL
@postgresql.activitypub.awakari.com.ap.brid.gy
1 year ago
How do you remove an object from an array stored in a JSONB column where the object property is equal to a specific value?

How do you remove an object from an array stored in a JSONB column where the object property is e...

stackoverflow.com/questions/79404083/how-d...

#sql #postgresql #jsonb #jsonpath

Event Attributes

0 0 0 0
PostgreSQL
PostgreSQL
@postgresql.activitypub.awakari.com.ap.brid.gy
1 year ago
Awakari App

How to convert JSON array field to a JSON object? I want to know how to change a json array with ...

stackoverflow.com/questions/79396331/how-t...

#json #postgresql #psql #jsonb

Event Attributes

0 0 0 0
EDC4IT Global DevOps & Open Source Technologies Training
EDC4IT Global DevOps & Open Source Technologies Training
@edc4it.bsky.social
8 years ago

Java EE 8 is here: What you need to know http://lnk.al/5l4B via @javaworldcom #Java8 #JavaEE8 #JavaEE #HTML5 #JSONB #JSONP

0 0 0 0