PostgreSQL Deep Dive — JSONB, Arrays, Extensions

PostgreSQL — গভীরতর আলোচনা

Read: ~55 min Advanced 16 practice problems Live SQLite runner

1. Why PostgreSQL Is Special

PostgreSQL — usually shortened to Postgres — is the world's most technically advanced open-source relational database. Where MySQL aims to be the web's default workhorse, Postgres aims to be the research-grade engine that also runs production: full ACID, strict standards conformance, extensible types, extensible indexes, extensible everything. If a database paper publishes a new feature, Postgres usually gets it within a few releases.

PostgreSQL হলো সবচেয়ে উন্নত open-source relational database। MySQL যেখানে web-এর জন্য সহজ ও দ্রুত workhorse, Postgres সেখানে গবেষণা-মানের একটি engine — কঠোর SQL standard, পূর্ণ ACID, এবং সবকিছু extend করা যায় (data type, index, function — সবই)। আজকের অধ্যায়ে আমরা সেই বিশেষ দিকগুলো দেখব যা Postgres-কে আলাদা করে।

In this module we cover the seven things a senior backend engineer must know about Postgres: advanced data types (JSONB, arrays, ranges, UUID, hstore), generated columns, PL/pgSQL functions, the extension ecosystem (PostGIS, pg_trgm, pgvector, pg_stat_statements), MVCC and the VACUUM story, and finally logical replication.

Honesty note — what runs in your browser Our in-page SQL runner uses SQLite. SQLite shares a lot of standard SQL with Postgres, so we will run SQLite-compatible demos for general concepts. But truly Postgres-only features (JSONB, GIN, PL/pgSQL, extensions) cannot run here — those code blocks are shown as psql.sh snippets without Run/Copy/Fiddle buttons. Try them on a real Postgres later (Supabase, Neon, or a local docker run postgres).

2. Data Types Beyond the Standard

Standard SQL gives you INT, TEXT, DATE, NUMERIC and a few cousins. Postgres gives you those and a whole catalog more — and they are first class: each has dedicated operators, functions and indexes.

Type	What it stores	Postgres-specific superpower
`JSONB`	Binary-encoded JSON document.	Indexable with `GIN`; operators `->`, `->>`, `@>`, `?`.
`TEXT[]` / array of any type	Native one-dimensional (or N-D) array.	Operators `@>` contains, `&&` overlaps; `UNNEST`.
`int4range`, `tstzrange`	A range of values with inclusive / exclusive bounds.	Range operators `&&` overlap, `@>` contains; `EXCLUDE` constraint.
`UUID`	128-bit universally unique identifier.	Native 16-byte storage, generated by `gen_random_uuid()`.
`hstore`	Flat key→value text map (older sibling of JSONB).	Compact, indexable, still used in legacy schemas.
`cidr`, `inet`, `macaddr`	Network addresses.	CIDR-aware operators (`<<`, `>>`).
`tsvector` / `tsquery`	Full-text search document and query.	Native FTS engine — no Elasticsearch needed for many workloads.

মূল কথা: Postgres-এর data type-গুলো শুধু data ধরে রাখে না, তাদের উপর সরাসরি query চালানোর জন্য অপারেটরও আছে। অর্থাৎ আপনি একটি column-এ পুরো JSON document রেখেও সেটার ভেতরের key দিয়ে indexable filter করতে পারবেন — এই ক্ষমতাই Postgres-কে document database (MongoDB) আর relational database-এর মাঝামাঝি একটি অনন্য জায়গায় রেখেছে।

2.1 JSONB in action

JSONB stores JSON in a parsed binary form — slower to insert than plain JSON text, but much faster to query and indexable. For schemaless or semi-structured data (audit logs, webhook payloads, product attributes), it is the standard choice.

psql.sh — JSONB

-- Daraz product catalog: every product has different attributes
CREATE TABLE product (
    id          BIGSERIAL PRIMARY KEY,
    sku         TEXT UNIQUE NOT NULL,
    name        TEXT NOT NULL,
    attrs       JSONB NOT NULL DEFAULT '{}'::jsonb,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

INSERT INTO product (sku, name, attrs) VALUES
 ('MOB-001','Galaxy A55','{"brand":"Samsung","ram_gb":8,"colors":["black","blue"],"price":52000}'),
 ('MOB-002','Redmi Note 13','{"brand":"Xiaomi","ram_gb":6,"colors":["white"],"price":24500}'),
 ('LAP-007','MacBook Air M3','{"brand":"Apple","ram_gb":16,"price":165000,"warranty_yrs":1}');

-- Operators
SELECT name, attrs->>'brand'        AS brand,             -- text
       (attrs->>'ram_gb')::int      AS ram_gb,            -- cast to int
       attrs->'colors'              AS colors_jsonb       -- still JSONB
FROM   product;

-- Containment (the most useful operator)
SELECT name FROM product
WHERE  attrs @> '{"brand":"Samsung"}';   -- "attrs contains this sub-document"

-- Existence of a key
SELECT name FROM product WHERE attrs ? 'warranty_yrs';

-- GIN index makes @> and ? queries fast on millions of rows
CREATE INDEX idx_product_attrs ON product USING GIN (attrs);

-- Update inside JSONB (atomic, no read-modify-write)
UPDATE product
SET    attrs = jsonb_set(attrs, '{price}', '49999')
WHERE  sku = 'MOB-001';

When JSONB, when columns? Use columns for fields you query in every request (price, status, foreign keys). Use JSONB for sparse / variable / per-category attributes (a phone has ram_gb, a book has author — putting both in one big columnar table is wasteful).

যেগুলো সব row-তে থাকে এবং প্রায় সব query-তে লাগে — সেগুলো column। যেগুলো কোনো row-তে আছে, কোনোটায় নেই — সেগুলো JSONB-তে রাখুন, GIN index দিয়ে দ্রুত search করুন।

2.2 Arrays

psql.sh — arrays

CREATE TABLE post (
    id     SERIAL PRIMARY KEY,
    title  TEXT,
    tags   TEXT[]                            -- array of TEXT
);
INSERT INTO post (title, tags) VALUES
 ('How bKash works',     ARRAY['fintech','bangladesh','payments']),
 ('Pathao ride pricing', ARRAY['ride','bangladesh','algorithms']),
 ('Postgres vs MySQL',   ARRAY['database','postgres','mysql']);

-- Array operators
SELECT title FROM post WHERE tags @> ARRAY['bangladesh'];   -- contains
SELECT title FROM post WHERE tags && ARRAY['mysql','redis']; -- overlaps (any common element)

-- Index by position (1-based!)
SELECT title, tags[1] AS first_tag FROM post;

-- Expand array → rows
SELECT id, unnest(tags) AS tag FROM post;

-- GIN index on the array
CREATE INDEX idx_post_tags ON post USING GIN (tags);

সতর্কতা: Postgres-এ array index 1 থেকে শুরু হয় — অনেক ভাষার মতো 0 নয়। tags[1] মানে প্রথম element। আর array-তে duplicate value রাখা চলে — tag system বানানোর সময় তা মাথায় রাখুন, না হলে একই tag বারবার ঢুকতে পারে।

2.3 Range types

A tstzrange ("timestamp-with-timezone range") perfectly models things like booking intervals, discount validity or employee tenure. Combined with the EXCLUDE constraint, Postgres can guarantee no two bookings overlap — at the schema level, no application code required.

psql.sh — range & EXCLUDE

CREATE EXTENSION IF NOT EXISTS btree_gist;

-- A meeting room booking system that physically cannot double-book
CREATE TABLE booking (
    id      SERIAL PRIMARY KEY,
    room    TEXT,
    period  TSTZRANGE NOT NULL,
    EXCLUDE USING GIST (
        room   WITH =,           -- same room
        period WITH &&           -- with overlapping time
    )
);

INSERT INTO booking (room, period) VALUES
 ('Conf-A', '[2026-05-10 10:00, 2026-05-10 11:00)');

-- This one OVERLAPS — Postgres rejects it at the database level
INSERT INTO booking (room, period) VALUES
 ('Conf-A', '[2026-05-10 10:30, 2026-05-10 11:30)');
-- ERROR: conflicting key value violates exclusion constraint

3. Generated Columns & PL/pgSQL Functions

3.1 Generated columns

A generated column is a column whose value is computed from other columns by the database itself. Postgres supports STORED generated columns (computed once on write, stored on disk, indexable).

psql.sh — generated

CREATE TABLE invoice (
    id         BIGSERIAL PRIMARY KEY,
    qty        INT NOT NULL,
    unit_price NUMERIC(10,2) NOT NULL,
    vat_pct    NUMERIC(4,2)  NOT NULL DEFAULT 7.5,
    -- These two are auto-computed every INSERT/UPDATE
    subtotal   NUMERIC(12,2) GENERATED ALWAYS AS (qty * unit_price) STORED,
    total      NUMERIC(12,2) GENERATED ALWAYS AS
                 (qty * unit_price * (1 + vat_pct/100)) STORED
);
INSERT INTO invoice (qty, unit_price) VALUES (3, 200);
SELECT * FROM invoice;
-- subtotal = 600.00, total = 645.00 — automatically

Generated column-এর সবচেয়ে বড় সুবিধা — হিসাবটি এক জায়গায়, schema-এ। যত application এই table-এ লেখুক, total সবসময় সঠিক হবে। আর STORED হওয়ায় তার উপর index-ও বসানো যায়।

3.2 Anonymous code blocks (`DO`)

Sometimes you want to run procedural logic once — a migration, a one-off cleanup. DO blocks let you do that without permanently creating a function.

psql.sh — DO block

DO $$
DECLARE
    rec RECORD;
    n   INT := 0;
BEGIN
    FOR rec IN SELECT id FROM product WHERE attrs->>'brand' IS NULL LOOP
        UPDATE product
        SET    attrs = attrs || '{"brand":"Unknown"}'::jsonb
        WHERE  id = rec.id;
        n := n + 1;
    END LOOP;
    RAISE NOTICE 'Patched % rows', n;
END $$;

3.3 `CREATE FUNCTION` in PL/pgSQL

For repeatable logic, package it as a function. PL/pgSQL is Postgres's native procedural language — full control flow, exceptions, and access to query results.

psql.sh — CREATE FUNCTION

-- bKash-like balance transfer with full transactional safety
CREATE OR REPLACE FUNCTION transfer_money(
    p_from   BIGINT,
    p_to     BIGINT,
    p_amount NUMERIC
) RETURNS TEXT
LANGUAGE plpgsql
AS $$
DECLARE
    src_balance NUMERIC;
BEGIN
    IF p_amount <= 0 THEN
        RAISE EXCEPTION 'Amount must be positive (got %)', p_amount;
    END IF;

    -- Lock the source row so two concurrent transfers can't double-spend
    SELECT balance INTO src_balance
    FROM   account
    WHERE  id = p_from
    FOR    UPDATE;

    IF src_balance < p_amount THEN
        RAISE EXCEPTION 'Insufficient balance: have %, need %', src_balance, p_amount;
    END IF;

    UPDATE account SET balance = balance - p_amount WHERE id = p_from;
    UPDATE account SET balance = balance + p_amount WHERE id = p_to;

    INSERT INTO ledger(from_id, to_id, amount) VALUES (p_from, p_to, p_amount);
    RETURN 'OK';
END $$;

-- Call it
SELECT transfer_money(1001, 1002, 500);

FOR UPDATE — the row lock that prevents double-spend Without FOR UPDATE, two concurrent transfers could both read balance = 1000, both send 800, and leave balance = 200 (instead of the correct -600 / rejection). The lock serializes the critical section. We will revisit this in the concurrency phase.

4. The Extension Ecosystem — Postgres's Killer Feature

An extension is a packaged bundle of functions, types and indexes that you load with one command: CREATE EXTENSION foo;. The Postgres extension catalog is what really separates it from MySQL — you can turn Postgres into a GIS engine, a vector database, or a full-text search engine without leaving SQL.

Extension	What it adds	Real-world use
`postgis`	Geometry / geography types, spatial indexes (`GiST`), 1000+ GIS functions.	Pathao "find drivers within 2 km", land-records.
`pg_trgm`	Trigram similarity → fast fuzzy `LIKE '%foo%'` and typo tolerance.	"Did you mean…" search, dedup name lists.
`pgvector`	The `vector` type and approximate-nearest-neighbour indexes.	RAG / semantic search / AI memory — what ChatGPT plugins use.
`pg_stat_statements`	Cumulative stats per query: total time, calls, mean, p95.	"Which query is killing prod?" — first place to look.
`uuid-ossp` / built-in `pgcrypto`	UUID generators (v1, v4, etc.)	Distributed primary keys.
`citext`	Case-insensitive text type.	Email columns, usernames.
`tablefunc`	`crosstab()` — pivot tables in SQL.	Reporting dashboards.

Extension হলো Postgres-এর "plugin"। একটি কমান্ড দিয়ে database-এ নতুন data type, function, এবং index যুক্ত করা যায়। postgis দিয়ে map application, pgvector দিয়ে AI semantic search, pg_trgm দিয়ে fuzzy text search — সবই পাবেন একই database-এ। আলাদা কোনো service চালাতে হবে না।

4.1 PostGIS — Pathao's secret sauce

psql.sh — PostGIS

CREATE EXTENSION postgis;

CREATE TABLE driver (
    id        BIGSERIAL PRIMARY KEY,
    name      TEXT,
    location  GEOGRAPHY(POINT, 4326)        -- WGS84 lat/lng
);
CREATE INDEX idx_driver_loc ON driver USING GIST (location);

INSERT INTO driver (name, location) VALUES
 ('Karim', ST_MakePoint(90.4125, 23.8103)::geography),  -- Gulshan, Dhaka
 ('Rahim', ST_MakePoint(90.3563, 23.7461)::geography),  -- Dhanmondi
 ('Sumon', ST_MakePoint(91.7832, 22.3569)::geography);  -- Chittagong

-- "All drivers within 3 km of a Banani pickup point"
SELECT name,
       ST_Distance(location,
                   ST_MakePoint(90.4070, 23.7937)::geography) AS dist_m
FROM   driver
WHERE  ST_DWithin(location,
                  ST_MakePoint(90.4070, 23.7937)::geography,
                  3000)
ORDER  BY dist_m;

4.2 pg_trgm — fuzzy search

psql.sh — pg_trgm

CREATE EXTENSION pg_trgm;

CREATE INDEX idx_product_name_trgm
  ON product USING GIN (name gin_trgm_ops);

-- Misspelled "samsng" still finds Samsung
SELECT name, similarity(name, 'samsng') AS sim
FROM   product
WHERE  name % 'samsng'         -- the % operator = "similar enough"
ORDER  BY sim DESC
LIMIT  5;

4.3 pgvector — your AI memory store

psql.sh — pgvector

CREATE EXTENSION vector;

CREATE TABLE doc_chunk (
    id      BIGSERIAL PRIMARY KEY,
    text    TEXT,
    embed   VECTOR(1536)        -- OpenAI ada-002 dimension
);

CREATE INDEX ON doc_chunk
  USING hnsw (embed vector_cosine_ops);

-- Find the 5 most similar chunks to a query embedding
SELECT id, text, embed <=> '[0.01, -0.02, ...]'::vector AS distance
FROM   doc_chunk
ORDER  BY embed <=> '[0.01, -0.02, ...]'::vector
LIMIT  5;

SQLite emulation hint SQLite has no native JSONB, but it does have a JSON1 extension with json_extract() and friends. For arrays, use a comma-separated TEXT column or a child table (the relational way). For vectors, the new sqlite-vec extension brings something similar to pgvector to embedded apps.

5. MVCC, Bloat, and the `VACUUM` Story

Postgres uses MVCC — Multi-Version Concurrency Control. Instead of locking rows on UPDATE, Postgres writes a new version of the row and marks the old one as dead. Readers always see a consistent snapshot; writers never block readers.

MVCC মানে — UPDATE করলে Postgres পুরোনো row-টি মুছে নতুন কিছু লেখে না; বরং row-এর একটি নতুন version তৈরি করে এবং পুরোনোটি "dead" হিসেবে চিহ্নিত করে। ফলে যাঁরা read করছেন তাঁদের block হতে হয় না। এই সুবিধার মূল্য — dead row জমে গিয়ে storage ফুলে যায়, যাকে বলে bloat।

Figure 41.1 — MVCC version chain ও dead tuple ধারণা।

VACUUM is the maintenance task that finds dead row versions no transaction can ever see again and frees their space. autovacuum runs it automatically, but on heavy-write tables it can fall behind and bloat sneaks in. The classic sign: a table is 10 GB on disk but a fresh dump & restore shrinks it to 2 GB — that 8 GB was bloat.

psql.sh — VACUUM & bloat

-- Investigate one table
SELECT relname,
       n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct
FROM   pg_stat_user_tables
ORDER  BY dead_pct DESC NULLS LAST
LIMIT  10;

-- Manual cleanup
VACUUM (VERBOSE, ANALYZE) account;

-- Aggressive — locks the table, fully rewrites it (use during maintenance windows)
VACUUM FULL account;

-- Tune autovacuum per-table (run more eagerly on hot tables)
ALTER TABLE account SET (autovacuum_vacuum_scale_factor = 0.05);

Common production incident A long-running transaction (e.g. a forgotten psql session in a developer's terminal) holds an old snapshot. Autovacuum cannot remove dead rows newer than that snapshot. After a few hours, every UPDATE-heavy table is bloated, query plans degrade, disk fills. Always monitor pg_stat_activity for sessions with state = 'idle in transaction'.

6. Logical Replication — Publications & Subscriptions

Postgres has two kinds of replication:

Physical (streaming) replication — byte-for-byte copy of WAL. Standby is identical to primary, can serve reads, can be promoted on failover. Whole-cluster.
Logical replication — row-level change feed (DECODED from WAL). You pick which tables to publish; subscriber can be a different Postgres major version, can apply triggers, can be only a subset.

Physical replication মানে পুরো cluster-এর exact copy — সমান version লাগবে। Logical replication মানে শুধু কিছু table-এর change feed পাঠানো — version আলাদা হলেও চলে, ETL-এর জন্য আদর্শ। বড় Postgres version upgrade-ও logical replication দিয়েই zero-downtime করা যায়।

psql.sh — logical replication

-- ON THE PUBLISHER (source)
ALTER SYSTEM SET wal_level = logical;     -- requires restart
SELECT pg_reload_conf();

CREATE PUBLICATION orders_pub
    FOR TABLE orders, order_item;

-- ON THE SUBSCRIBER (target)
CREATE SUBSCRIPTION orders_sub
    CONNECTION 'host=primary.db dbname=shop user=replicator password=...'
    PUBLICATION orders_pub;

-- Inspect status
SELECT subname, received_lsn, latest_end_lsn, latest_end_time
FROM   pg_stat_subscription;

✅ Logical replication wins (কখন)

Zero-downtime major version upgrade (PG 14 → PG 16).
Sending only some tables to a reporting warehouse.
Cross-region disaster recovery for a single app's data.
Change-Data-Capture into Kafka via wal2json / Debezium.

⚠️ Physical replication wins (কখন)

Read replicas serving large analytical scans.
Synchronous standby for strong durability.
Whole-cluster point-in-time recovery (PITR).
Lower CPU overhead — no logical decoding work.

7. A Runnable Demo — Postgres Concepts in SQLite

We cannot run JSONB or PostGIS in the browser, but we can run a Postgres-style concept demo in SQLite — a generated-column-like calculation expressed as a view, plus an array-as-JSON workaround. This proves the idea; the syntax differs from real Postgres.

virtual_generated.sql

-- SQLite has STORED generated columns since 3.31, but easier: a view.
CREATE VIEW invoice_full AS
SELECT id, qty, unit_price, vat_pct,
       qty * unit_price                          AS subtotal,
       ROUND(qty * unit_price * (1 + vat_pct/100), 2) AS total
FROM   invoice;

SELECT * FROM invoice_full;

tags_workaround.sql

-- SQLite has no array. We simulate "array contains 'bangladesh'"
-- with a LIKE check on a CSV column. (Slower, no index — fine for demo.)
SELECT title
FROM   post
WHERE  ',' || tags_csv || ',' LIKE '%,bangladesh,%';

-- The Postgres equivalent (don't run here, run on real PG):
--   SELECT title FROM post WHERE tags @> ARRAY['bangladesh'];

8. Glossary (শব্দকোষ)

Term	Meaning	বাংলায়
JSONB	Binary-encoded JSON; indexable.	Binary form-এ জমানো JSON; index করা যায়।
GIN index	Generalized Inverted Index — built for "contains" queries.	"Contains" / array / JSONB-র জন্য বিশেষ index।
PL/pgSQL	Postgres's procedural language for functions and triggers.	Postgres-এ function ও trigger লেখার ভাষা।
Extension	Plug-in package adding types, functions, or indexes.	নতুন type / function / index যোগ করার plugin।
MVCC	Multi-Version Concurrency Control — readers never block writers.	Reader ও writer যাতে একে অপরকে block না করে।
Bloat	Wasted disk space from dead row versions awaiting vacuum.	Dead row জমে গিয়ে storage ফোলা।
Logical replication	Row-level change feed; per-table; cross-version capable.	Row-level change feed; নির্বাচিত table; version-stage-mismatch চলে।

9. Practice Problems

A mix of pen-and-paper concept questions and short SQLite-runnable demos. Postgres-only syntax is given as text — try those on a real Postgres later.

কিছু প্রশ্ন pen-and-paper, কিছু runnable। Postgres-নির্ভর syntax-গুলো শুধু পড়ার জন্য; বাস্তব Postgres-এ পরে চালাবেন।

In one sentence, when would you choose JSONB over a child table?

কখন child table-এর বদলে JSONB বেছে নেবেন — এক বাক্যে?

✨ Show Answer

Answer: When the attributes are sparse, schema-variable, and not joined — e.g. per-product attributes that differ wildly between phones and books. If two rows always have the same shape and you join on the field, make it a real column or a child table.

যখন attribute-গুলো sparse এবং schema পরিবর্তনশীল — যেমন প্রতিটি product-এর জন্য আলাদা আলাদা spec — তখন JSONB। প্রতিটি row-এ একই shape থাকলে আলাদা column বা child table।
Why does COUNT(DISTINCT col) + array-style filtering both benefit from a GIN index?

GIN index কেন array-style filter-এ সাহায্য করে?

✨ Show Answer

Answer: A GIN index inverts the relationship: for every distinct value (or JSON key, or array element), it lists the rows containing it. So a query like tags @> ARRAY['bangladesh'] becomes a single lookup of "rows containing bangladesh" — O(log n) instead of a full scan.
Run a generated-column-style view in SQLite that shows price after 15% discount.

SQLite-এ একটি view বানান যা ১৫% ছাড়ের পর দাম দেখায়।
✨ Show Answer
ans3.sql
```
CREATE VIEW product_sale AS
SELECT id, name, price,
       ROUND(price * 0.85, 2) AS sale_price
FROM   product;
SELECT * FROM product_sale;
```
Write the Postgres syntax to add a generated column full_name = first_name || ' ' || last_name.

Postgres-এ full_name generated column যোগ করার syntax লিখুন।
✨ Show Answer
```
ALTER TABLE customer
ADD COLUMN full_name TEXT
GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED;
```
Explain in 2 sentences why MVCC creates "bloat".

দুই বাক্যে ব্যাখ্যা করুন — MVCC কেন bloat তৈরি করে।

✨ Show Answer

Answer: Every UPDATE or DELETE leaves the old row version on disk, marked dead, until VACUUM removes it. If writes are faster than VACUUM, dead versions pile up — the table occupies far more disk than its live data justifies.

প্রতিটি UPDATE/DELETE পুরোনো row-কে dead হিসেবে রেখে দেয়; VACUUM সেগুলো না সরালে disk-এ জমে গিয়ে bloat হয়।
A long-running transaction has been "idle in transaction" for 6 hours. Why is this a Postgres-specific emergency?

"idle in transaction" ৬ ঘন্টা ধরে — এটি Postgres-এ কেন emergency?

✨ Show Answer

Answer: Autovacuum cannot reclaim any row version newer than that transaction's snapshot, because the transaction could still need it. So during those 6 hours, every heavily-updated table accumulates dead rows. Performance degrades, disk fills, and queries get slower until you kill the transaction.
Which extension would you use for "find drivers within 2 km of this lat/lng"?

"এই lat/lng থেকে ২ km-এর মধ্যে driver খুঁজুন" — কোন extension?

✨ Show Answer

Answer: postgis — using geography type and ST_DWithin(loc, point, 2000) with a GiST index on the location column.
For a "did you mean" search box on Bangla product names, which extension fits best and why?

"did you mean" search-এর জন্য কোন extension?

✨ Show Answer

Answer: pg_trgm — it builds trigrams (3-character sliding windows) of the text and provides a similarity score, so misspelled or partial inputs still match. A GIN index with gin_trgm_ops makes the queries fast.
For LLM "memory" / RAG, which extension and which index type?

LLM memory / RAG-এর জন্য কোন extension ও কোন index?

✨ Show Answer

Answer: pgvector with an HNSW (or IVFFLAT) index. HNSW gives better recall and is now the default for most embeddings workloads.
Run this SQLite demo simulating Postgres's jsonb_extract: from a JSON column, return brand for each product.

SQLite-এ JSON column থেকে brand বের করুন।
✨ Show Answer
ans10.sql
```
-- SQLite's json_extract is similar to Postgres ->>
SELECT name,
       json_extract(attrs, '$.brand')  AS brand,
       json_extract(attrs, '$.ram_gb') AS ram_gb
FROM   product;
```
The Postgres equivalent uses attrs->>'brand'.
Why must you set wal_level = logical before creating a publication?

Logical replication-এর আগে wal_level = logical কেন দরকার?

✨ Show Answer

Answer: The default WAL records only enough info for crash recovery, not full row images. Logical decoding needs the row's old/new values, which are written only at wal_level = logical. Setting it requires a server restart.
Difference between VACUUM and VACUUM FULL in one line each.

VACUUM ও VACUUM FULL — পার্থক্য?

✨ Show Answer

VACUUM — concurrent, marks dead-row space reusable inside the existing files; does not return space to the OS.
VACUUM FULL — exclusive lock, fully rewrites the table into a new compact file; does return space to the OS, but blocks all access while running.
Show the Postgres SQL to find rows where a JSONB column has the key warranty_yrs.

JSONB-তে warranty_yrs key আছে — এমন row বের করুন।
✨ Show Answer
```
SELECT name FROM product WHERE attrs ? 'warranty_yrs';
```
? operator JSONB-তে key অস্তিত্ব check করে।
A team uses SELECT * FROM big_table WHERE name LIKE '%mim%'. The query is slow. Which extension + index type would you suggest?

Leading % থাকার কারণে B-tree কাজ করছে না — কী suggest করবেন?

✨ Show Answer

Answer: Install pg_trgm and add a GIN index: CREATE INDEX idx_name_trgm ON big_table USING GIN (name gin_trgm_ops);. Now both LIKE '%mim%' and the similarity operator % can use the index.
Why is the EXCLUDE constraint with tstzrange safer than checking overlap in application code?

Application code-এ overlap check করার তুলনায় EXCLUDE কেন বেশি নিরাপদ?

✨ Show Answer

Answer: Application checks have a race window: two concurrent requests both read "no conflict" then both insert. The database constraint atomically tests-and-inserts under a row/range lock, so even at full concurrency overlap is impossible. Defense in the data layer outlasts every refactor.
If pg_stat_statements shows one query takes 80% of total DB time, what's your first action?

pg_stat_statements-এ একটি query DB-এর 80% সময় খাচ্ছে — প্রথম action?

✨ Show Answer

Answer: Run EXPLAIN (ANALYZE, BUFFERS) on it to see the actual plan. Look for sequential scans on big tables, missing indexes, bad join orders or huge sort operations. Index, rewrite, or add a covering column accordingly. Re-measure with pg_stat_statements after each change.

Summary — Module 41

PostgreSQL is the open-source database with the deepest type system and the richest extension catalog. JSONB + GIN brings document-store flexibility into a relational engine; arrays and ranges remove whole categories of bookkeeping tables; generated columns and PL/pgSQL push business logic safely into the database; extensions (PostGIS, pg_trgm, pgvector, pg_stat_statements) replace whole separate services. Internally, MVCC gives lock-free reads at the cost of bloat — keep an eye on VACUUM and idle-in-transaction sessions. Finally, logical replication is your zero-downtime upgrade and CDC story. Master these and you can run Postgres for almost any workload short of true OLAP-at-PB-scale.

PostgreSQL-এর শক্তি সাতটি জায়গায়: উন্নত data type (JSONB, array, range, UUID), generated column, PL/pgSQL function, extension ecosystem (PostGIS / pg_trgm / pgvector / pg_stat_statements), MVCC, VACUUM, এবং logical replication। এই সাতটি ভালোভাবে আয়ত্তে আসলে প্রায় যেকোনো বাস্তব backend-এর database-চাহিদা Postgres একাই মেটাতে পারে।

Next Module → MySQL Deep Dive — InnoDB internals, replication topologies and partitioning.

Prev: Module 40 Next: MySQL Deep Dive

1. Why PostgreSQL Is Special

2. Data Types Beyond the Standard

2.1 JSONB in action

2.2 Arrays

2.3 Range types

3. Generated Columns & PL/pgSQL Functions

3.1 Generated columns

3.2 Anonymous code blocks (DO)

3.3 CREATE FUNCTION in PL/pgSQL

4. The Extension Ecosystem — Postgres's Killer Feature

4.1 PostGIS — Pathao's secret sauce

4.2 pg_trgm — fuzzy search

4.3 pgvector — your AI memory store

5. MVCC, Bloat, and the VACUUM Story

6. Logical Replication — Publications & Subscriptions

✅ Logical replication wins (কখন)

⚠️ Physical replication wins (কখন)

7. A Runnable Demo — Postgres Concepts in SQLite

8. Glossary (শব্দকোষ)

9. Practice Problems

Summary — Module 41

3.2 Anonymous code blocks (`DO`)

3.3 `CREATE FUNCTION` in PL/pgSQL

5. MVCC, Bloat, and the `VACUUM` Story