Blog
Mert Özen's blog posts on software development, backend, frontend, and DevOps.
JSONB in PostgreSQL: Operators, SQL/JSON Functions, GIN Indexes and When Not to Use It
JSONB parses JSON once on write and stores it in a binary structure; it doesn't save disk, but it can be queried with @>, ? and jsonpath and indexed with GIN. On Bookstore's books.attributes column: json versus jsonb, operators, jsonb_set, 17's JSON_VALUE and JSON_TABLE, which queries jsonb_ops and jsonb_path_ops can serve, expression indexes, the TOAST trap where one counter costs 175 kB of WAL, bad estimates and EAV in JSON form.
PostgreSQL Full-Text Search: tsvector, tsquery, the Turkish Configuration, unaccent and pg_trgm
For Turkish search in PostgreSQL the turkish configuration is a reasonable start, but its stemmer isn't perfect: "kasabalar" can be found while "kasaba" isn't; prefix search, unaccent and pg_trgm close those gaps. tsvector/tsquery, websearch_to_tsquery, full-text search using the collation provider in 18 and the dotted/dotless i, a weighted STORED generated column with GIN, ts_rank, ts_headline, typo tolerance and when to move to Elasticsearch.