SQL vs NoSQL

SQL databases store data in tables with a fixed schema, query with SQL, and typically provide ACID transactions. NoSQL covers databases that use other data models and usually trade some relational features for scale or flexibility. See also SQL vs NoSQL.

Example 1: comparison table

Example 2: NoSQL families and when to use each

Example 3: ACID vs BASE

Details of ACID are in transactions and ACID; the consistency trade-off is in replication and CAP.

Example 4: schema-on-write vs schema-on-read

PostgreSQL can combine both: typed columns for stable fields and a jsonb column for variable ones.

Example 5: JSONB in PostgreSQL

jsonb stores parsed JSON in a binary format that supports indexing.

CREATE TABLE product_docs (
    id  serial PRIMARY KEY,
    doc jsonb NOT NULL
);
INSERT INTO product_docs (doc) VALUES
  ('{"name": "Laptop",  "price": 65000, "tags": ["electronics", "work"], "specs": {"ram_gb": 16}}'),
  ('{"name": "Mouse",   "price": 800,   "tags": ["electronics", "accessory"]}'),
  ('{"name": "Desk",    "price": 12000, "tags": ["furniture", "work"], "specs": {"material": "oak"}}');

SELECT id,
       doc ->> 'name'               AS name,
       (doc ->> 'price')::int       AS price,
       doc -> 'specs' ->> 'ram_gb'  AS ram_gb
FROM product_docs
ORDER BY id;

 id |  name  | price | ram_gb 
----+--------+-------+--------
  1 | Laptop | 65000 | 16
  2 | Mouse  |   800 | 
  3 | Desk   | 12000 | 
(3 rows)

Operators: -> returns jsonb, ->> returns text, #> and #>> follow a path. A missing key gives NULL, which is how the Mouse row has no ram_gb.

Example 6: containment and key-existence queries

SELECT doc ->> 'name' AS name
FROM product_docs
WHERE doc @> '{"tags": ["work"]}'
ORDER BY id;

  name  
--------
 Laptop
 Desk
(2 rows)

SELECT doc ->> 'name' AS name
FROM product_docs
WHERE doc ? 'specs'
ORDER BY id;

  name  
--------
 Laptop
 Desk
(2 rows)

Example 7: GIN index on JSONB

A GIN (generalized inverted) index maps each key and value to the rows containing it, so @> and ? avoid scanning the table. The demo uses a larger table.

CREATE TABLE events_json (id int, doc jsonb);
INSERT INTO events_json
SELECT g, jsonb_build_object('user', g % 1000, 'type', CASE WHEN g % 50 = 0 THEN 'purchase' ELSE 'view' END)
FROM generate_series(1, 50000) g;
CREATE INDEX events_json_gin ON events_json USING gin (doc);
ANALYZE events_json;

EXPLAIN (COSTS OFF)
SELECT count(*) FROM events_json WHERE doc @> '{"type": "purchase"}';

                            QUERY PLAN                            
------------------------------------------------------------------
 Aggregate
   ->  Bitmap Heap Scan on events_json
         Recheck Cond: (doc @> '{"type": "purchase"}'::jsonb)
         ->  Bitmap Index Scan on events_json_gin
               Index Cond: (doc @> '{"type": "purchase"}'::jsonb)
(5 rows)

SELECT count(*) FROM events_json WHERE doc @> '{"type": "purchase"}';

 count 
-------
  1000
(1 row)

For a single frequently queried field, a B-tree expression index is smaller and supports ranges and sorting:

CREATE INDEX events_json_user ON events_json (((doc ->> 'user')::int));
ANALYZE events_json;
EXPLAIN (COSTS OFF)
SELECT count(*) FROM events_json WHERE (doc ->> 'user')::int = 7;

                            QUERY PLAN                             
-------------------------------------------------------------------
 Aggregate
   ->  Bitmap Heap Scan on events_json
         Recheck Cond: (((doc ->> 'user'::text))::integer = 7)
         ->  Bitmap Index Scan on events_json_user
               Index Cond: (((doc ->> 'user'::text))::integer = 7)
(5 rows)

Example 8: polyglot persistence

Systems often use several stores, each for the data it handles best.

flowchart LR
    APP["Application"] --> PG[("PostgreSQL: orders, users")]
    APP --> RD[("Redis: cache, sessions")]
    APP --> ES[("Elasticsearch: search")]
    APP --> KF["Kafka: event log"]
    KF --> DW[("Warehouse: analytics")]
    PG -. "CDC" .-> KF

The cost is operational: more systems to run and keep consistent. Change data capture (CDC) or an outbox table keeps derived stores in sync with the source of truth.

Example 9: OLTP, OLAP, HTAP, and columnar storage

flowchart LR
    O[("OLTP database")] -->|"ETL / CDC"| W[("Data warehouse (columnar)")]
    W --> B["BI dashboards"]

Columnar storage keeps each column together. An aggregate such as SUM(amount) reads only that column, and similar values compress well. Row storage reads whole rows, which suits fetching one full record.

Example 10: caching layer and connection pooling

Cache stampede (many misses for the same key at once) is reduced with locks, request coalescing, or staggered TTLs. Redis and Memcached are common caches. Connection pooling (PgBouncer, HikariCP) limits database connections; see replication and CAP.

Interview decision guide

Interview points:

• State the access patterns, consistency needs, and scale before naming a database.
• NoSQL does not mean no transactions or no schema; the schema moves into the application.
• Relational databases scale reads with replicas and writes with partitioning and sharding, see partitioning and sharding.
• Denormalisation in NoSQL trades write cost and duplication for read speed.

Summary

• Relational databases give schemas, joins, and ACID transactions; NoSQL families (key-value, document, wide-column, graph, time-series) fit specific access patterns.
• ACID favours correctness; BASE favours availability and scale with eventual consistency.
• PostgreSQL jsonb with a GIN index handles flexible documents inside a relational database.
• OLTP uses row storage for transactions; OLAP uses columnar storage for analytics; polyglot systems combine stores.
• Start from access patterns and consistency needs, and default to relational when unsure.