[db] postgres index (gin, gist) in json data type

[heowc]

• https://www.postgresql.org/docs/current/textsearch-indexes.html
• https://www.postgresql.org/docs/current/gin.html

[heowc]

tsvector란?

• https://www.postgresql.org/docs/current/datatype-textsearch.html

[heowc]

docker run -d -it -p5432:5432 -e POSTGRES_PASSWORD=password postgres:14

[heowc]

json/jsonb란?

• https://www.postgresql.org/docs/current/datatype-json.html

[heowc]

SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector;

select to_tsvector('{"a": "b"}'::jsonb);
select * from ts_debug('{"a": "b"}');

select to_tsvector('{"a": "b", "c": "d"}'::jsonb);
select * from ts_debug('{"a": "b", "c": "d"}');

select to_tsvector('{"a": "b", "c": "b"}'::jsonb);
select * from ts_debug('{"a": "b", "c": "b"}');

select to_tsvector('["a","c"]'::jsonb);
select * from ts_debug('["a","c"]');

select to_tsvector('["a","a"]'::jsonb);
select * from ts_debug('["a","a"]');

[heowc]

https://pganalyze.com/blog/gin-index https://boiler.buzzni.com/2017/02/27/postgresql-json-indexing.html https://boiler.buzzni.com/2017/03/31/postgresql-relation-model.html https://scalegrid.io/blog/using-jsonb-in-postgresql-how-to-effectively-store-index-json-data-in-postgresql/ https://bitnine.net/blog-postgresql/postgresql-internals-jsonb-type-and-its-indexes/

[heowc]

• btree, gin 조합
• 없는 값 스캔 퍼포먼스
• extension
  • https://www.postgresql.org/docs/current/btree-gin.html

[heowc]

https://blog.goodusdata.com/197

[heowc]

https://www.postgresql.org/docs/current/functions-json.html