DEV Community: Aniketh Deshpande

Database Engines 101: How They Work, the Main Types, and How to Swap Them in PostgreSQL

Aniketh Deshpande — Mon, 28 Sep 2026 12:02:03 +0000

🧪 This article has a hands-on lab. You'll run PostgreSQL in Docker, store the same 5 million rows in
two different storage engines, crash the database on purpose, and measure what changes.
If you already know the theory, skip straight to the lab.

TL;DR: A database engine is the part of a database that turns "save this row" into bytes on disk and back again.
There's no single best engine. Row stores suit transactions, LSM trees suit heavy writes,
column stores suit analytics and in-memory engines suit speed.
PostgreSQL lets you choose per table with CREATE TABLE ... USING <engine>.

Part 1: How an engine works
Part 2: Types of engines
Part 3: The lab
Cheat sheet: pick an engine by use case

Part 1: How an engine works

When you run INSERT INTO users VALUES (1, 'alice', 1000), two very different pieces of software are involved:

   SQL text
      │
      ▼
 ┌──────────────────────────┐
 │  Query layer             │   parse → plan → execute
 │  "what do you want?"     │
 └────────────┬─────────────┘
              │  "store this row", "give me row X"
              ▼
 ┌──────────────────────────┐
 │  Storage engine          │   pages, rows, logs, versions
 │  "how do the bytes live?"│
 └────────────┬─────────────┘
              ▼
          disk / S3

This article is about the bottom box. The easiest way to see why it looks the way it does is to build the obvious version and watch it fail.

The naive engine: one row per line in a text file

with open("users.csv", "w") as f:
    for i in range(200_000):
        f.write(f"{i},user_{i:06d},{i * 7 % 100000}\n")

On my machine it failed three ways almost immediately:

Read cost depends on which row you want. Finding row N means counting N newlines. Row 0 took 0.25 ms and row 199,999 took 17.6 ms, 70x slower, on a file under 5 MB.
A row that grows overwrites its neighbour. The only safe fix is to rewrite everything after it. I changed 40 bytes and had to write 4.6 MB, a 121,667x write amplification.
A crash creates a row that never existed. Cut power halfway through replacing 42,alice,1000 with 42,bob,2000 and you get 42,bobce,1000. It parses fine, so nothing warns you.

Real engines solve these with four ideas.

Idea 1: Pages

Split every file into fixed-size blocks called pages. PostgreSQL uses 8 KB. Finding block 5,000 becomes plain arithmetic (5000 × 8192), so every read costs the same. The page becomes the unit of disk I/O, caching and crash recovery.

Idea 2: Slotted pages

Inside each page, a small slot array at the front points to rows stored at the back. The two regions grow toward each other:

byte 0     ┌────────────────────────────────┐
           │ Page header (24 bytes)         │
           ├────────────────────────────────┤
           │ Slot 1 │ Slot 2 │ Slot 3 │ ... │  ← slots grow DOWN ↓
           ├────────────────────────────────┤
           │          free space            │
           ├────────────────────────────────┤
           │ ... │ Row 3 │ Row 2 │ Row 1    │  ← rows grow UP ↑
byte 8192  └────────────────────────────────┘

A row's address is (page, slot), not a byte offset. So the engine can move rows around inside a page to reclaim space, and every index entry pointing at (5, 3) still works.

Idea 3: Checksums and the write-ahead log

Checksums detect damage: each page stores a checksum of its own bytes, so a half-written page fails verification instead of returning 42,bobce,1000.

The write-ahead log (WAL) repairs damage. Before touching a page, the engine appends a description of the change to a sequential log and flushes it to disk:

1. append "page 5, slot 3: balance 1000 → 2000" to the WAL  → fsync
2. change page 5 in memory
3. write page 5 to disk ... eventually

If the database crashes before step 3, recovery replays the log. You'll watch this happen in the lab.

Idea 4: MVCC

Instead of overwriting a row, write a new version and stamp each version with the transaction that created it (xmin) and the one that replaced it (xmax). Each transaction reads from a snapshot, so a long report keeps seeing the old balance while an update commits alongside it. Readers never block writers. The cost is that old versions pile up and must be cleaned out, which is what PostgreSQL's VACUUM does.

Part 2: Types of engines

Those four ideas describe PostgreSQL's default engine. Other engines make different trade-offs, depending on what they're optimised for.

Row-oriented store (B-tree + pages)

Whole rows are kept together in pages and updated in place, with B-tree indexes to find them. The classic design.

Great at: transactions, point lookups, frequent updates (OLTP).
Weak at: scanning a few columns across billions of rows, because it reads whole rows anyway.
Examples: PostgreSQL heap, MySQL InnoDB, SQLite, Oracle, SQL Server.

Log-structured merge tree (LSM)

Never update in place. Writes go to an in-memory buffer (plus a log for safety). When the buffer fills, it's flushed to disk as an immutable sorted file, and background compaction merges those files over time.

Great at: very heavy write and ingest rates, since every disk write is sequential.
Weak at: reads may check several files (Bloom filters help), and compaction uses background CPU and I/O.
Examples: RocksDB, LevelDB, Cassandra, ScyllaDB, MyRocks, CockroachDB's Pebble.

Column-oriented store

Store each column separately and compress it. A query that needs 2 of 12 columns reads only those 2.

Row store (one page)             Column store
┌──────────────────────────┐     id:      1   2   3   4  ...
│ 1 │ IN │ 12.50 │ chrome  │     country: IN  US  IN  DE ...  ← compresses
│ 2 │ US │  8.00 │ safari  │     amount:  12.5 8.0 3.2 ...      very well
│ 3 │ IN │  3.20 │ chrome  │     browser: ...
└──────────────────────────┘

Great at: analytics such as aggregates, dashboards and scans over huge tables (OLAP). Much smaller on disk.
Weak at: updating or deleting single rows, and fetching whole rows one at a time.
Examples: ClickHouse, DuckDB, Snowflake, BigQuery, Parquet files, Citus/Hydra columnar for PostgreSQL.

In-memory store

Keep everything in RAM. Durability comes from snapshots and append-only logs, or you give it up.

Great at: microsecond latency for caches, sessions, leaderboards and queues.
Weak at: RAM is expensive, and durability is weaker unless you configure it.
Examples: Redis, Memcached, MySQL MEMORY, VoltDB.

Pluggable engines

Some databases let you choose the engine per table. MySQL made this famous:

CREATE TABLE orders (...) ENGINE=InnoDB;
CREATE TABLE cache  (...) ENGINE=MEMORY;

PostgreSQL has had the same thing since version 12, through the Table Access Method API:

CREATE TABLE orders (...) USING heap;       -- the default row store
CREATE TABLE events (...) USING columnar;   -- added by an extension

It goes further than tables:

What you can swap	Syntax	Built in	Added by extensions
Table engine	`CREATE TABLE ... USING x`	`heap`	`columnar` (Citus, Hydra), `orioledb`
Index engine	`CREATE INDEX ... USING x`	`btree`, `hash`, `gin`, `gist`, `spgist`, `brin`	`hnsw`, `ivfflat` (pgvector)
Durability	`CREATE UNLOGGED TABLE`	skip the WAL entirely	—

Now let's plug some in.

Part 3: The lab

You need: Docker, and about 15 minutes. The table load takes about a minute.

We'll use the citusdata/citus image: stock PostgreSQL 17 with the columnar table engine already installed. We're only using its columnar engine, not its distributed features.

Step 0: Start PostgreSQL

docker run -d --name pg-engines -e POSTGRES_PASSWORD=pw citusdata/citus:13.0
docker exec -it pg-engines psql -U postgres

Inside psql, turn on timing:

\timing on

Step 1: See which engines are installed

PostgreSQL lists every engine in the pg_am catalog table ("am" = access method):

SELECT amname AS engine,
       CASE amtype WHEN 't' THEN 'table' ELSE 'index' END AS kind
FROM pg_am ORDER BY kind DESC, amname;

  engine  | kind
----------+-------
 columnar | table     ← added by the extension
 heap     | table     ← PostgreSQL's default
 brin     | index
 btree    | index
 gin      | index
 gist     | index
 hash     | index
 spgist   | index

Step 2: Same table, two engines

A realistic 12-column web-events table. The only difference between the two is the USING clause:

CREATE TABLE events_row (
  id bigint, ts timestamptz, user_id int, country text, amount numeric(10,2),
  device text, browser text, page_url text, referrer text,
  session_id uuid, status smallint, latency_ms int
) USING heap;

CREATE TABLE events_col (LIKE events_row) USING columnar;

Load 5 million rows of fake events into both:

INSERT INTO events_row
SELECT g,
       '2026-01-01'::timestamptz + g * interval '1 second',
       ((g::bigint * 7919) % 100000)::int,
       (ARRAY['IN','US','DE','BR','JP'])[1 + g % 5],
       (g % 10000) / 100.0,
       (ARRAY['mobile','desktop','tablet'])[1 + g % 3],
       (ARRAY['chrome','firefox','safari','edge'])[1 + g % 4],
       '/products/' || (g % 5000) || '/details?ref=campaign_' || (g % 97),
       'https://www.example-' || (g % 300) || '.com/search?q=item' || (g % 1000),
       md5(g::text)::uuid,
       (ARRAY[200,200,200,404,500])[1 + g % 5],
       (g % 900) + 20
FROM generate_series(1, 5000000) g;

INSERT INTO events_col SELECT * FROM events_row;
VACUUM ANALYZE events_row;
ANALYZE events_col;

Now compare their size on disk:

SELECT c.relname AS "table", a.amname AS engine,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS size
FROM pg_class c JOIN pg_am a ON a.oid = c.relam
WHERE c.relname LIKE 'events_%';

   table    |  engine  |  size
------------+----------+--------
 events_col | columnar | 127 MB
 events_row | heap     | 868 MB

Same data, 6.8x smaller. Storing each column together means similar values sit next to each other, and they compress well.

Step 3: Run an analytics query

Average order amount per country. BUFFERS shows how many 8 KB pages each engine had to read:

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF)
SELECT country, round(avg(amount), 2) FROM events_row GROUP BY country;

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF)
SELECT country, round(avg(amount), 2) FROM events_col GROUP BY country;

Trimmed output:

-- heap
 ->  Parallel Seq Scan on events_row
       Buffers: shared read=111093          ← 868 MB of pages
 Execution Time: 852 ms

-- columnar
 ->  Custom Scan (ColumnarScan) on events_col
       Columnar Projected Columns: country, amount
       Buffers: shared hit=3164 read=16     ← 25 MB of pages
 Execution Time: 1409 ms

The columnar engine read 35x fewer pages. Projected Columns: country, amount shows why: it skipped the other 10 columns entirely.

But look at the times. On my laptop, heap was still faster. The whole dataset fits in RAM, so reading pages is cheap. PostgreSQL also split the heap scan across 3 CPU cores, while this columnar engine scans on one.

💡 That's the real lesson: an engine is a bet on your bottleneck. Columnar wins when I/O is the limit, for
example tables bigger than RAM, network-attached cloud disks, or storage you pay for by the byte scanned.
When everything is already in memory, the difference shrinks, and a well-parallelised row store can win.

Step 4: Update one row

UPDATE events_row SET status = 500 WHERE id = 42;
UPDATE events_col SET status = 500 WHERE id = 42;

UPDATE 1
ERROR:  UPDATE and CTID scans not supported for ColumnarScan

The columnar engine refuses. Compressed column chunks can't be cheaply edited in place, so this engine is append-only. It suits event logs and history tables, not a users table.

Step 5: Turn durability down with `UNLOGGED`

The WAL from Part 1 has a cost. PostgreSQL lets you skip it per table:

CREATE TABLE          clicks_safe (LIKE events_row);
CREATE UNLOGGED TABLE clicks_fast (LIKE events_row);

SELECT pg_current_wal_lsn() AS before \gset
INSERT INTO clicks_safe SELECT * FROM events_row LIMIT 1000000;
SELECT pg_current_wal_lsn() AS middle \gset
INSERT INTO clicks_fast SELECT * FROM events_row LIMIT 1000000;
SELECT pg_current_wal_lsn() AS after \gset

SELECT pg_size_pretty(pg_wal_lsn_diff(:'middle', :'before')) AS wal_written_safe,
       pg_size_pretty(pg_wal_lsn_diff(:'after',  :'middle')) AS wal_written_fast;

 wal_written_safe | wal_written_fast
------------------+------------------
 199 MB           | 0 bytes

The unlogged insert wrote zero bytes of WAL and ran about 1.5x faster (1.5 s vs 2.3 s). Both tables hold 1,000,000 rows.

Step 6: Crash it on purpose

Quit psql (\q), then kill PostgreSQL the hard way. SIGKILL gives it no chance to clean up, like pulling the power cord:

docker kill -s KILL pg-engines
docker start pg-engines
docker logs pg-engines 2>&1 | grep -E "not properly shut down|redo"

LOG:  database system was not properly shut down; automatic recovery in progress
LOG:  redo starts at 0/57A9DD88
LOG:  redo done at 0/57A9DE58

That's the WAL replay from Part 1. Now reconnect and count:

SELECT (SELECT count(*) FROM clicks_safe) AS safe_rows,
       (SELECT count(*) FROM clicks_fast) AS fast_rows;

 safe_rows | fast_rows
-----------+-----------
   1000000 |         0

The unlogged table is empty. With no WAL to replay, PostgreSQL can't know whether its pages are consistent, so after a crash it truncates them. It's a good fit for staging tables and caches you can rebuild. It's a bad fit for anything you can't afford to lose.

Step 7: Swap index engines

Indexes are pluggable too. Put a B-tree and a BRIN index on the same timestamp column:

CREATE INDEX events_ts_btree ON events_row USING btree (ts);
CREATE INDEX events_ts_brin  ON events_row USING brin  (ts);

SELECT indexrelid::regclass AS index,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_index WHERE indexrelid::regclass::text LIKE 'events_ts_%';

      index      |  size
-----------------+--------
 events_ts_btree | 107 MB
 events_ts_brin  | 40 kB

The BRIN index is about 2,700x smaller. A B-tree stores one entry per row. BRIN ("Block Range INdex") stores only the min and max timestamp for each group of 128 pages. Since events arrive in time order, those ranges barely overlap, so the index can still skip most of the table.

Both indexes answer "give me Feb 1st" correctly (86,400 rows). BRIN is lossy: it returns whole page ranges, and PostgreSQL rechecks the rows, discarding 11,028 extras. If your data isn't physically ordered by that column, BRIN becomes useless and you're back to the B-tree.

Step 8: Change a table's engine later

You don't have to choose the engine forever. Since PostgreSQL 15, one command moves an existing table to a different engine:

SELECT pg_size_pretty(pg_total_relation_size('clicks_safe'));   -- 174 MB

ALTER TABLE clicks_safe SET ACCESS METHOD columnar;
SELECT pg_size_pretty(pg_total_relation_size('clicks_safe'));   -- 25 MB

ALTER TABLE clicks_safe SET ACCESS METHOD heap;                 -- and back again

This is the "plug and play" part. The table name, the columns and your queries stay the same. Only the engine underneath changes. The catch is that it rewrites the whole table and locks it while doing so, so plan it like a migration.

You can also change the default for new tables:

SET default_table_access_method = columnar;

Clean up

docker rm -f pg-engines

Cheat sheet: pick an engine by use case

Use case	Pick	Why
Users, orders, payments	`heap` + `btree`	Frequent updates, point lookups, full durability
Event logs, metrics history, audit trails	`columnar`	Append-only, scanned by a few columns, ~7x smaller
ETL staging, rebuildable caches	`UNLOGGED` heap	No WAL, faster writes. Emptied after a crash
Huge time-ordered tables, range filters	`brin` index	Kilobytes instead of 100+ MB
Full-text search, JSONB, arrays	`gin` index	Indexes the values inside a column
Maps and geometry (PostGIS)	`gist` index	Handles overlaps and nearest-neighbour queries
AI embeddings, similarity search	`hnsw` (pgvector)	Approximate nearest-neighbour search
Massive write-heavy key-value data	LSM engine (RocksDB, Cassandra)	Sequential writes and compaction
Sub-millisecond cache or session store	In-memory (Redis)	RAM speed; durability is optional

Want to go deeper?

I'm learning this by building one: LakePG is a from-scratch Python storage engine that writes byte-for-byte PostgreSQL-compatible pages, with slotted pages, checksums, tuples and MVCC. Its tests compare the pages it writes against a real PostgreSQL 17 server. It's a learning reimplementation of ideas from PostgreSQL, Neon and Databricks Lakebase, not an original design and not production software.

Your Python venv Is (Mostly) a Symlink — Here's Why That Matters

Aniketh Deshpande — Mon, 28 Sep 2026 05:17:36 +0000

TL;DR — On Linux and macOS, python3 -m venv .venv does not create a copy of Python.
The python inside your venv is a symlink that points back to the system interpreter.
The standard library isn't copied either. Only site-packages (your installed packages) really belongs to the venv.
So if the system Python gets upgraded, moved or deleted, your venv can break outright,
or keep running against a different Python without telling you.

The claim
Part 1 — See it with your own eyes (GCP VM / EC2 lab)
Part 2 — What does this mean? Pros, cons, and real-world impact
Part 3 — Building a venv that doesn't betray you
Cheat sheet

The claim

Most of us picture a virtual environment like this:

   What we THINK a venv is
   ───────────────────────

   .venv/
   ├── 🐍 a full private copy of Python
   ├── 📚 a full private copy of the standard library
   └── 📦 my packages

Here's what you actually get:

   What a venv ACTUALLY is (Linux/macOS default)
   ─────────────────────────────────────────────

   .venv/
   ├── bin/
   │   ├── python   ──symlink──►  python3
   │   ├── python3  ──symlink──►  /usr/bin/python3   ◄── the SYSTEM interpreter
   │   ├── pip                    (small script, absolute shebang)
   │   └── activate               (shell script)
   ├── lib/python3.12/site-packages/   ◄── the ONLY truly private part
   └── pyvenv.cfg                      ◄── "home = /usr/bin"  (a pointer back)

   Standard library (os, json, asyncio, ssl, ...)?  → still read from /usr/lib/python3.12

A venv is really an isolated site-packages folder, plus a pointer back to an interpreter that lives somewhere else.
I'll prove it.

Part 1 — See it with your own eyes

You can run this on any Linux box. A throwaway cloud VM is ideal because it's clean and you can wreck it without worrying.

Step 0: Get a VM

Option A — Google Cloud (GCE)

gcloud compute instances create venv-lab \
  --zone=us-central1-a \
  --machine-type=e2-micro \
  --image-family=ubuntu-2404-lts-amd64 \
  --image-project=ubuntu-os-cloud

gcloud compute ssh venv-lab --zone=us-central1-a

Option B — AWS (EC2)

aws ec2 run-instances \
  --image-id resolve:ssm:/aws/service/canonical/ubuntu/server/24.04/stable/current/amd64/hvm/ebs-gp3/ami-id \
  --instance-type t3.micro \
  --key-name <your-key-pair> \
  --security-group-ids <sg-allowing-ssh> \
  --tag-specifications 'ResourceType=instance,Tags=[{Key=Name,Value=venv-lab}]'

ssh -i <your-key>.pem ubuntu@<public-ip>

Once you're in, install the venv module (Ubuntu ships it as a separate package):

sudo apt update && sudo apt install -y python3-venv
python3 --version          # Python 3.12.x on Ubuntu 24.04

💡 The output below comes from Ubuntu 24.04 / Python 3.12. Other distros and versions show the same pattern, with different version numbers.

Experiment 1: Look inside `bin/`

cd ~
python3 -m venv demo
ls -la demo/bin/ | grep python

lrwxrwxrwx 1 ubuntu ubuntu   7 ... python -> python3
lrwxrwxrwx 1 ubuntu ubuntu  16 ... python3 -> /usr/bin/python3
lrwxrwxrwx 1 ubuntu ubuntu   7 ... python3.12 -> python3

The l at the start of each permission string and the -> arrows mean these are symlinks. None of them is a real binary.

Experiment 2: Follow the chain

readlink -f demo/bin/python
readlink -f /usr/bin/python3

/usr/bin/python3.12
/usr/bin/python3.12

They end at the same file. You can also check that the venv entry is just a link and not a hard copy:

stat -c '%i  %s bytes  %n' /usr/bin/python3.12 demo/bin/python3

16123164  6939856 bytes  /usr/bin/python3.12   ← the real binary (~7 MB)
18122480       16 bytes  demo/bin/python3      ← a 16-byte symlink

   Symlink resolution chain
   ────────────────────────

   demo/bin/python
        │  (symlink)
        ▼
   demo/bin/python3
        │  (symlink)
        ▼
   /usr/bin/python3
        │  (symlink, owned by the distro)
        ▼
   /usr/bin/python3.12   ◄── the one and only real interpreter binary

Experiment 3: Read `pyvenv.cfg`, the venv's "birth certificate"

cat demo/pyvenv.cfg

home = /usr/bin
include-system-site-packages = false
version = 3.12.3
executable = /usr/bin/python3.12
command = /usr/bin/python3 -m venv /home/ubuntu/demo

home = /usr/bin is the key line. At startup, Python sees this file and uses it to find its real home (and the standard library that comes with it).

Experiment 4: Ask Python where things come from

demo/bin/python - <<'EOF'
import sys, os, json
print("sys.executable :", sys.executable)
print("realpath       :", os.path.realpath(sys.executable))
print("sys.prefix     :", sys.prefix)        # the venv
print("sys.base_prefix:", sys.base_prefix)   # the REAL Python install
print("json stdlib    :", json.__file__)
EOF

sys.executable : /home/ubuntu/demo/bin/python
realpath       : /usr/bin/python3.12
sys.prefix     : /home/ubuntu/demo
sys.base_prefix: /usr
json stdlib    : /usr/lib/python3.12/json/__init__.py   ← NOT inside the venv!

There are two prefixes. sys.prefix is your venv, where packages get installed. sys.base_prefix is the system install, where the interpreter and the stdlib come from.

Experiment 5: Check the size

du -sh demo
ls demo/lib/python3.12/site-packages

12M   demo
pip  pip-24.0.dist-info

Almost all of those 12 MB are pip. The interpreter and the stdlib (tens of MB) aren't there, because they're borrowed.

Experiment 6: Move the venv and watch it break

head -1 demo/bin/pip
mv demo demo-moved
demo-moved/bin/pip --version

#!/home/ubuntu/demo/bin/python3
bash: demo-moved/bin/pip: /home/ubuntu/demo/bin/python3: bad interpreter: No such file or directory

Every console script (pip, pytest, black, and so on) has the absolute path of the venv baked into its shebang line. Venvs aren't relocatable.

mv demo-moved demo   # put it back

Experiment 7: Delete the base Python (the scary one)

We don't want to wreck the VM's real /usr/bin/python3, since apt and cloud-init depend on it. So we'll build a fake "system Python" in /opt, create venvs from it, then "uninstall" it.

# 1. Build a standalone copy of the interpreter + stdlib at /opt/fakepy
sudo mkdir -p /opt/fakepy/bin /opt/fakepy/lib
sudo cp /usr/bin/python3.12 /opt/fakepy/bin/
sudo cp -r /usr/lib/python3.12 /opt/fakepy/lib/
/opt/fakepy/bin/python3.12 -c "import sys; print(sys.prefix)"   # → /opt/fakepy

# 2. Create two venvs from it: default (symlinks) and --copies
/opt/fakepy/bin/python3.12 -m venv --without-pip ~/linked
/opt/fakepy/bin/python3.12 -m venv --without-pip --copies ~/copied
ls -l ~/linked/bin/python3.12     # → /opt/fakepy/bin/python3.12
ls -l ~/copied/bin/python3.12     # → a real 7 MB file

# 3. Simulate "the base Python was upgraded / uninstalled"
sudo mv /opt/fakepy /opt/fakepy.gone

Now run both:

~/linked/bin/python -c 'print("hello")'

bash: /home/ubuntu/linked/bin/python: No such file or directory

💥 The symlink is dangling, so the venv is dead.

~/copied/bin/python -c 'import sys, os; print(sys.base_prefix, os.__file__)'

/usr /usr/lib/python3.12/os.py

😱 The --copies venv did start, but pyvenv.cfg pointed to a home that no longer exists. Python fell back to its compiled-in prefix (/usr) and silently switched to a different standard library. Here the two happened to be identical, so nothing went wrong. On a real machine they could be different patch or minor versions, and you'd get strange ImportErrors or subtle bugs that nobody can explain.

Clean up:

sudo mv /opt/fakepy.gone /opt/fakepy   # or: sudo rm -rf /opt/fakepy.gone
rm -rf ~/linked ~/copied

And when you're finished with the lab, delete the VM:

gcloud compute instances delete venv-lab --zone=us-central1-a   # GCP
aws ec2 terminate-instances --instance-ids <instance-id>          # AWS

Part 2 — What does this mean?

How a venv actually boots

This comes from PEP 405, the spec that defines venv:

  $ .venv/bin/python app.py
            │
            ▼
  ┌──────────────────────────────────────┐
  │ Kernel follows symlinks              │
  │ .venv/bin/python → /usr/bin/python3.12│  ← executes the SYSTEM binary
  └──────────────────────────────────────┘
            │  (but argv[0] / sys.executable still says .venv/bin/python)
            ▼
  ┌──────────────────────────────────────┐
  │ Python looks for pyvenv.cfg next to  │
  │ (or one level above) sys.executable  │
  └──────────────────────────────────────┘
            │ found!
            ▼
  ┌──────────────────────────────────────┐        ┌──────────────────────────────┐
  │ sys.prefix      = .venv              │ ─────► │ .venv/lib/python3.12/        │
  │                                      │        │        site-packages/  📦     │
  │ sys.base_prefix = derived from       │        └──────────────────────────────┘
  │                   "home = /usr/bin"  │        ┌──────────────────────────────┐
  │                                      │ ─────► │ /usr/lib/python3.12/  📚      │
  └──────────────────────────────────────┘        │ (os, json, ssl, asyncio ...) │
                                                  └──────────────────────────────┘

So a venv is three things:

Piece	Where it lives	Owned by
Interpreter binary	`/usr/bin/python3.12` (symlinked)	🧑‍💼 The OS / package manager
Standard library	`/usr/lib/python3.12/` (referenced)	🧑‍💼 The OS / package manager
Third-party packages	`.venv/lib/python3.12/site-packages/`	🙋 You

⚠️ Being precise: "the venv is a symlink" is a handy shorthand, but the venv directory is real, and so is site-packages.
What's symlinked is the interpreter. The stdlib is borrowed through pyvenv.cfg and never linked or copied.
On Windows, venvs use a small copied python.exe launcher instead of a symlink, but it still reads pyvenv.cfg and depends on the base install in the same way.

Why did Python's designers do it this way?

On purpose. Venvs are meant to be cheap, fast and disposable, not self-contained deployments.

✅ Pros

Pro	Why it matters
Tiny	~12 MB (mostly pip) instead of ~40–100 MB per env. Having 30 projects doesn't mean 30 copies of CPython.
Fast to create	Creating a few symlinks takes milliseconds. Great for CI, tox and nox matrices.
Security patches for free	When `apt upgrade` ships a patched `python3.12` (say, an `ssl` or `zipfile` CVE fix), every venv picks it up with no rebuild.
One interpreter to trust	Only one binary sits on disk to audit, scan and patch.
Package isolation still works	Two projects can pin different versions of `requests` without conflict, which is the reason venvs exist.

❌ Cons

Con	What it looks like
Fragile to base changes	Base Python removed or upgraded → `No such file or directory` / `bad interpreter`.
Silent minor-version drift	Venv made with `python3 -m venv` links to `/usr/bin/python3`, not `python3.12`. If the distro repoints `python3` to 3.13, your venv starts running 3.13 and looks for `lib/python3.13/site-packages`, which doesn't exist. Result: `ModuleNotFoundError` for everything you installed.
Not relocatable	Absolute paths live in shebangs, `pyvenv.cfg` and `activate`. You can't `mv`, `cp -r`, rsync to another path, or bake it in one Docker stage and copy it to another path.
Not portable	You can't copy `.venv` to another machine unless the exact same Python exists at the exact same path.
Compiled wheels are tied to the base	C extensions (`numpy`, `psycopg`, `cryptography`) were built against a specific ABI (`cp312`). Swapping the base breaks them.
`--copies` is only half a fix	It copies the binary but still borrows the stdlib, and falls back silently if `home` disappears (see Experiment 7).

How it bites in real life

                   ┌──────────────────────────────┐
                   │   Your venv  (.venv/)        │
                   │   python3 → /usr/bin/python3 │
                   └───────────────┬──────────────┘
                                   │ depends on
                                   ▼
   ┌───────────────────────────────────────────────────────────────┐
   │            Base interpreter you DON'T control                 │
   └───────────────────────────────────────────────────────────────┘
      ▲               ▲                 ▲                   ▲
      │               │                 │                   │
 do-release-upgrade   brew upgrade   pyenv uninstall    Docker: copy venv
 22.04 → 24.04        python@3.12    3.11.4             into distroless /
 (3.10 → 3.12)        + brew cleanup                    alpine image
      │               │                 │                   │
      ▼               ▼                 ▼                   ▼
 ModuleNotFound   dangling link     dangling link      "no such file"
 (drift)          → venv dead       → venv dead        at container start

Some scenarios you may have hit already:

Ubuntu release upgrade. 22.04 ships Python 3.10 and 24.04 ships 3.12. After do-release-upgrade, venvs built with python3 -m venv start running 3.12 and all your packages are gone. Venvs built with python3.10 -m venv are dead links.
macOS + Homebrew. brew upgrade installs a new patch version, and brew cleanup deletes the old Cellar directory your venv pointed to. This is the classic "my venv broke overnight".
Cron jobs / systemd services on a VM. You set ExecStart=/opt/app/.venv/bin/python ... in a unit file. Months later, unattended upgrades or an OS image refresh change the base Python, and the service dies at 3 a.m.
Docker multi-stage builds. You build /opt/venv in python:3.12 and copy it into gcr.io/distroless/base. /usr/local/bin/python3 doesn't exist there, so the venv's symlink points nowhere.
Renaming a project folder. mv my-app my-app-v2 breaks pip, pytest and every other console script.
CI caching. You cache .venv between runs. The runner image updates its Python, the cache restores a venv linked to a Python that's gone, and the build fails in confusing ways.

Part 3 — Building a venv that doesn't betray you

The root problem isn't the symlink itself. It's that the venv depends on an interpreter you don't control. So the fix is to control the base interpreter, and to treat venvs as disposable.

                    Which venv strategy do I need?
                    ──────────────────────────────

                  ┌──────────────────────────────┐
                  │ Is this for local dev / CI ? │
                  └───────┬───────────────┬──────┘
                       yes│               │no (deploying / shipping)
                          ▼               ▼
         ┌──────────────────────┐   ┌───────────────────────────────┐
         │ Rule 1 + Rule 2      │   │ Must it run where Python may   │
         │ pinned base + lock   │   │ be missing/different?          │
         │ file, recreate often │   └──────┬─────────────────┬──────┘
         └──────────────────────┘       yes│                 │no
                                           ▼                 ▼
                        ┌──────────────────────────┐  ┌─────────────────────┐
                        │ Rule 4: ship the         │  │ Rule 3: Docker with │
                        │ interpreter too          │  │ venv at the SAME    │
                        │ (standalone Python /     │  │ path, SAME base img │
                        │  container / PyInstaller)│  └─────────────────────┘
                        └──────────────────────────┘

Rule 1 — Never build venvs on the distro's `python3`

The system Python exists for the OS (apt, cloud-init, gcloud/aws tooling), and the OS will upgrade it whenever it needs to. Install your own interpreter at a pinned, versioned path that only changes when you change it.

Option A: `uv` (fast and simple)

curl -LsSf https://astral.sh/uv/install.sh | sh
uv python install 3.12            # downloads a standalone CPython build
uv venv --python 3.12 .venv       # venv linked to *uv's* Python, not /usr/bin
uv pip install -r requirements.txt

Option B: `pyenv` (compiles from source)

curl -fsSL https://pyenv.run | bash      # then follow the shell setup it prints
pyenv install 3.12.7
~/.pyenv/versions/3.12.7/bin/python -m venv .venv

Either way, the venv is still a symlink, but now it points at an interpreter that belongs to you:

   BEFORE (fragile)                         AFTER (stable)
   ────────────────                         ──────────────
   .venv/bin/python3                        .venv/bin/python3
        │                                        │
        ▼                                        ▼
   /usr/bin/python3  ◄── apt owns this      ~/.pyenv/versions/3.12.7/bin/python3.12
        │                (changes whenever       ◄── YOU own this
        ▼                 the OS wants)              (changes only when you
   /usr/bin/python3.12                                pyenv uninstall it)

Option C: No extra tools? At least pin the minor version

python3.12 -m venv .venv      # ✅ links to python3.12 — no silent 3.12→3.13 drift
python3    -m venv .venv      # ❌ links to python3   — drifts when the distro repoints it

Check it:

ls -l .venv/bin/ | grep python
# python3.12 -> /usr/bin/python3.12   ✅ pinned

Rule 2 — Treat the venv as a cache, not a pet

Assume any venv can die, and make rebuilding it a one-liner:

# Lock exact versions
pip freeze > requirements.lock          # or: uv pip compile / poetry lock / pip-tools

# Rebuild from scratch any time
rm -rf .venv
python3.12 -m venv .venv
.venv/bin/pip install -r requirements.lock

Also:

Never commit .venv to git (add it to .gitignore).
Never mv a venv. Recreate it at the new path instead.
If you upgraded the base Python in place (same minor version), refresh the links with:

  python3.12 -m venv --upgrade .venv

Rule 3 — In Docker, keep the venv's world identical across stages

Multi-stage builds work fine as long as the final image has the same interpreter at the same path:

# ---------- build stage ----------
FROM python:3.12-slim AS build
RUN python -m venv /opt/venv
ENV PATH="/opt/venv/bin:$PATH"
COPY requirements.lock .
RUN pip install --no-cache-dir -r requirements.lock

# ---------- runtime stage ----------
FROM python:3.12-slim                 # ✅ SAME base → /usr/local/bin/python3.12 exists
COPY --from=build /opt/venv /opt/venv # ✅ SAME path → shebangs still valid
ENV PATH="/opt/venv/bin:$PATH"
COPY . /app
WORKDIR /app
CMD ["python", "main.py"]

   build stage (python:3.12-slim)          runtime stage (python:3.12-slim)
   ┌──────────────────────────────┐        ┌──────────────────────────────┐
   │ /usr/local/bin/python3.12 ◄─┐│        │ /usr/local/bin/python3.12 ◄─┐│
   │ /opt/venv/bin/python ───────┘│ COPY ► │ /opt/venv/bin/python ───────┘│
   │ /opt/venv/lib/.../site-pkgs  │        │ /opt/venv/lib/.../site-pkgs  │
   └──────────────────────────────┘        └──────────────────────────────┘
                 ✅ link target exists on both sides

❌ Don't copy /opt/venv into alpine (musl vs glibc), distroless (no Python at that path), or python:3.13-slim (wrong minor version).

Rule 4 — If Python might be missing at the destination, ship the interpreter

When the target machine may not have a matching Python at all (air-gapped servers, customer machines, minimal images), a venv is the wrong tool. Ship the interpreter along with it:

Approach	What you get
Container image	Interpreter, stdlib and venv frozen together. The most common answer.
python-build-standalone	A relocatable CPython tarball. Unpack it in `/opt/myapp/python`, build the venv from that Python, and ship the whole `/opt/myapp` directory.
`conda-pack`	Packs a full conda env (interpreter included) into a tarball you can relocate.
PyInstaller / Nuitka / shiv	Bundles your app and interpreter into a single executable or zipapp.

What about `--copies`?

python3.12 -m venv --copies .venv

   --symlinks (default on Linux/macOS)      --copies
   ───────────────────────────────────      ─────────────────────────────────
   bin/python3.12 → /usr/bin/python3.12     bin/python3.12   (real 7 MB copy)
   stdlib: borrowed from /usr/lib           stdlib: STILL borrowed from /usr/lib
   base removed → hard fail 💥              base removed → silent fallback 😶
   base patched → auto-upgrade ✅           base patched → stale binary +
                                                           new stdlib ⚠️ mismatch

--copies is useful when your filesystem can't do symlinks (some network mounts, Vagrant/VirtualBox shared folders, certain Windows setups). It is not a way to make a venv self-contained, because the stdlib still lives in the base install. In some ways it's worse: a symlinked venv fails loudly, while a copied one can keep running with a mismatched stdlib.

Bonus: a venv health check

Drop this in your dotfiles or CI to spot broken or drifted venvs before they cause trouble:

#!/usr/bin/env bash
# venv-doctor.sh — usage: ./venv-doctor.sh path/to/.venv [more venvs...]
for v in "$@"; do
  py="$v/bin/python"
  if [[ ! -e "$py" ]]; then
    echo "💀 $v: interpreter link is dangling → $(readlink -m "$py")"
    continue
  fi
  "$py" - "$v" <<'EOF'
import sys, os, re
venv = sys.argv[1]
cfg = open(os.path.join(venv, "pyvenv.cfg")).read()
home = re.search(r"^home\s*=\s*(.+)$", cfg, re.M).group(1).strip()
want = re.search(r"^version(?:_info)?\s*=\s*(\d+\.\d+)", cfg, re.M).group(1)
have = f"{sys.version_info.major}.{sys.version_info.minor}"
problems = []
if want != have:
    problems.append(f"version drift: created with {want}, now running {have}")
if not os.path.isdir(home):
    problems.append(f"home '{home}' no longer exists (stdlib fallback: {sys.base_prefix})")
print(("⚠️  " if problems else "✅ ") + venv + (": " + "; ".join(problems) if problems else f": OK (Python {have} → {os.path.realpath(sys.executable)})"))
EOF
done

$ ./venv-doctor.sh ~/proj-a/.venv ~/proj-b/.venv ~/old-thing/.venv
✅ /home/ubuntu/proj-a/.venv: OK (Python 3.12 → /home/ubuntu/.pyenv/versions/3.12.7/bin/python3.12)
⚠️  /home/ubuntu/proj-b/.venv: version drift: created with 3.10, now running 3.12
💀 /home/ubuntu/old-thing/.venv: interpreter link is dangling → /opt/fakepy/bin/python3.12

Cheat sheet

Question	Command
Is my venv's Python a symlink?	`ls -l .venv/bin/python*`
What does it really run?	`readlink -f .venv/bin/python`
Where does it think home is?	`cat .venv/pyvenv.cfg`
Venv vs base prefix	`.venv/bin/python -c 'import sys;print(sys.prefix, sys.base_prefix)'`
Where does the stdlib come from?	`.venv/bin/python -c 'import os;print(os.__file__)'`
Create a pinned venv	`python3.12 -m venv .venv`
Create a venv on a Python you own	`uv venv --python 3.12` / `~/.pyenv/versions/3.12.7/bin/python -m venv .venv`
Refresh links after base patch upgrade	`python3.12 -m venv --upgrade .venv`
Rebuild from scratch	`rm -rf .venv && python3.12 -m venv .venv && .venv/bin/pip install -r requirements.lock`

Wrapping up

   A venv is a lightweight overlay:

        ┌───────────────────────────────┐
        │  YOUR packages (site-packages)│  ◄── isolated, yours
        ├───────────────────────────────┤
        │  stdlib                       │  ◄── borrowed
        │  interpreter binary           │  ◄── symlinked
        └───────────────────────────────┘
                 base Python install

The symlink design makes venvs cheap, fast and auto-patched. That was a deliberate choice.
It also means a venv is only as stable as the interpreter it points to.
So: own your base interpreter (uv / pyenv / a pinned python3.X), lock your dependencies, treat venvs as disposable, and when you need portability, ship the interpreter, not just the venv.

Next time a venv "randomly" breaks after an upgrade, run ls -l .venv/bin/python first. There's a good chance the link is pointing at a Python that no longer exists.

Found this useful? Drop a ❤️ or tell me in the comments about your worst "my venv broke overnight" story.

Predicate Pushdown - Understanding Practically With An Example

Aniketh Deshpande — Wed, 17 Apr 2024 19:42:28 +0000

What is predicate pushdown?

The immediate theoretical answer that we get on searching is

Predicate pushdown is a query optimisation technique used in database technologies

Okay, I got to know that it is an optimisation technique. But I still did not understand...

How is the optimisation happening? 🤨
What is predicate? 🤔
What exactly is the meaning of pushed down here? 🤷‍♂️

I'm sure since you are also reading this article, even you have these questions in mind!

Now, lets explore this interesting topic practically in PySpark using explain()
(Similar phenomenon could be observed in relational databases as well)

1] Reading a csv file containing employee information.

emp = spark.read.format('csv')\
                .option('header', True)\
                .load('data/employee.csv')
emp.show()

emp.explain(mode="formatted")

Here, step (1) is related to read csv file

2] Lets do a group by and get number of employees on each department

df = emp.groupBy('deptID').count()
df.show()

df.explain(mode="formatted")

Here, step (2) is related to group by

3] Lets filter data for only dept number 10

dept_10 = df.filter(df.deptID == 10)
dept_10.show()

dept_10.explain(mode="formatted")

Here, we can see that step (2) is filtering and step (3) is grouping.

Now here is the catch,
Ideally if we go by the sequence of operations, grouping should be done first and then filtering.

However, the optimiser does filtering first and then grouping, because grouping is an expensive operation and it is optimal to filter first and then group data.

In the physical plan we see that filtering (predicate) is pushed down with respect to grouping. That is why it is called push down !

Write Through

Aniketh Deshpande — Fri, 22 Mar 2024 13:06:39 +0000

Write through cache is a simple to implement caching mechanism.

Here the newly arrived data is written into cache and as well as persisted into the disk or a database. Atomicity is maintained.

There are two ways to implement it:
1] The application writes data to cache and database simultaneously

2] The application writes data to cache and then the cache writes the data into the database.

Advantages:

Simple to implement.
Faster response times.
Data integrity because of atomic nature of write operation.
Lower latency for subsequent reads.

Disadvantages:

Cache pollution: Since every time the data is filled into cache, it can get filled with less frequently read data and more cache eviction which could introduce some latency.
Not suitable for write intensive scenarios as the write operations are slower compared to other methods because data needs to be written in cache as well as persistent storage everytime.

Database Caching Strategies

Aniketh Deshpande — Fri, 22 Mar 2024 12:43:08 +0000

We often face high latencies while fetching data from Database and are unable to meet SLA. Caching is one of the solutions to implement after DB query and table optimisations.

A Cache is used to store the data so that it can be delivered faster to the client as compared to persistent systems like database or disk.

Popular caching tools are:

There are multiple ways in which data can be written into and read from the cache. Let us explore the most prominently used methods or policies.

Cache Aside

Cache Aside or Lazy Loading is one of the cache write policies or strategies.

In cache aside method, the application is responsible for storing data into cache.
When the client sends request to the application, it looks for data in the cache.
If the data is found in the cache it is called as cache-hit. The data is fetched from cache and returned to client.
However, if the data is not found in the cache, also called as cache-miss, the application queries for data in the database, writes the data into cache and sends the response to the client.
Since we store data in cache only when it is necessary, this strategy is also called as Lazy-Loading.

Advantages:

Cost effective because, only the frequently accessed data is stored into cache.

Disadvantages:

The response time can be slow when there is cache miss, because, it involves many i/o operations to fetch data from DB and store it in cache.

In case the system can tolerate an initial delay and the same data is to be fetched repeatedly, then this mechanism works best.

Write through

Write through cache is a simple to implement caching mechanism.

Here the newly arrived data is written into cache and as well as persisted into the disk or a database. Atomicity is maintained.

There are two ways to implement it:
1] The application writes data to cache and database simultaneously

2] The application writes data to cache and then the cache writes the data into the database.

Advantages:

Faster response times.
Data integrity because of atomic nature of write operation.
Lower latency for subsequent reads.

Disadvantages:

Cache pollution: Since every time the data is filled into cache, it can get filled with less frequently read data and more cache eviction which could introduce some latency.
Not suitable for write intensive scenarios as the write operations are slower compared to other methods because data needs to be written in cache as well as persistent storage everytime.

In case the system cannot tolerate an initial delay and the writes are infrequent, then this mechanism works best.

Also if the number of records to be cached is also fixed, then this approach can provide the best of the results.

Thank you :)

Cache Aside

Aniketh Deshpande — Fri, 22 Mar 2024 12:41:36 +0000

Cache Aside or Lazy Loading is one of the cache write policies or strategies.

In cache aside method, the application is responsible for storing data into cache.
When the client sends request to the application, it looks for data in the cache.
If the data is found in the cache it is called as cache-hit. The data is fetched from cache and returned to client.
However, if the data is not found in the cache, also called as cache-miss, the application queries for data in the database, writes the data into cache and sends the response to the client.
Since we store data in cache only when it is necessary, this strategy is also called as Lazy-Loading.

Advantages:

The implementation is simple.

Disadvantages:

The response time can be slow when there is cache miss, because, it involves many io operations to fetch data from DB and store it in cache.

Shallow Copy Vs Deep Copy

Aniketh Deshpande — Sat, 29 Oct 2022 12:57:16 +0000

In our day to day development tasks, we come across the need to copy objects and perform various operations.

Python provides two important functions in the copy library - copy and deepcopy.

Let us understand the difference between the two and their respective use cases.

Shallow Copy - copy.copy(obj)

It makes a copy of the obj at the surface level.
It copies all the contents of the obj.

However, the point to be noted here is that, in case the contents of x are mutable, then, y has reference to the contents of x. Any modification to the contents of x would be reflected in y as well.

In this example we see that modifying the contents of x[1], also modified the contents of y[1].

Similarly, we see that modifying the contents of y, also modified the contents of x.

Deep Copy - copy.deepcopy(obj)

Deepcopy copies the object recursively. Recursively means, it copies the contents of the object and not merely its reference.
So we should use deep copy in case we need an independent copy of the contents of the obj.

In deepcopy, any changes to the contents of x do not effect y, unlike shallow copy.

Copying objects properly is a very important basic python concepts. This knowledge helps in building error free code wrt copying objects.

Thank you
Aniketh Deshpande

Dead Letter Queue

Aniketh Deshpande — Sun, 23 Oct 2022 11:50:55 +0000

This is going to be a very short introduction to dead letter queues.

What is a dead letter queue??

Dead letter queues are messages queues specifically deployed to holding messages that could not be delivered to their intended queues.
Messages sometimes fail to get delivered to their intended queues as they might be unavailable or the queue is full.
Popular message queue tools that support or do not support DLQ:
- AWS SQS (Simple Queue Service) supports DLQ.
- RabbitMQ (free and open source) also supports DLQ.
- Redis does not support DLQ.

Thanks for reading :)
DLQ is a very important component of a scalable and resilient software architecture. This article only provides an introduction to the concept and helps readers with useful links.

Following are some of the useful resources that give more indepth information.

I request the readers of this post to kindly add more helpful URLs in the comment section, or add your experiences using a DLQ in a real world project. I believe that would help beginners get even better idea about how to use this in their projects.

Thank You
Aniketh Deshpande

Change Data Capture - PostgreSQL

Aniketh Deshpande — Sat, 22 Oct 2022 14:58:18 +0000

Change Data Capture is the concept of recording the changes in the database table fields.

It is very helpful in use cases where we want to track creation, updation, deletion of records in the table.
We might want to use this information to make changes in other databases or notify customers or notify other services.
Example:
1. Save a copy of this data in a warehouse post transform.
2. Trigger notification service to notify users about this change.
3. Cache the data.

CDC In Postgres

Using Notify/Listen
- NOTIFY provides a mechanism for interprocess communication between the database and the service that is LISTENing to this notification channel.
- One or more services could be listening to this notification channel.
- Name of this channel is usually the name of the database. However the user is free to set suitable names for these channels.
- Any change in the table is captured by the DB and a trigger is initiated, which calls a function that formats the message to notify.
- This usually contains the table name and the payload string.
- The listening server registers to the channel and gets the message from the DB.
- The service can then use this message and perform operations on it.

Pros:

Simple to implement. Use a trigger and a function to notify. Implement a listen service.

Cons:

Weak reliability. There is always a risk of loss of message especially when the listening service is down. Messages in the queue do not persist.

Using Debezium
- Debezium is an open source tool used for capturing changes in the database tables based on the WAL (write ahead log).
- The tool provides connectors to connect to a variety of databases.
- The source connector is used to capture changes in the source database.
- The sync connector is used to sync data directly in the destination database.

Pros:

The changes are persistent as they can be streamed to kafka. Hence highly reliable.

Cons:

Debezium does not take into account changes in the schema, users need to update the schema changes. Otherwise there would be data loss.

Thank you
Aniketh Deshpande

DB Locking - Why and How?

Aniketh Deshpande — Fri, 21 Oct 2022 18:39:15 +0000

If you have sent concurrent requests to your DB to modify its contents, you would have come across a phenomenon called The Double Booking Problem!

Double Booking Problem arises when two or more threads read the same data point and one thread incorrectly overwrites the changes done by another thread resulting in inconsistency.

Lets see an example to get better understanding of it.
Suppose its a bus booking application.

User 1 and User 2, both read Seat_21 as available.
User 1 books the seat. However User 2 is unaware of this as he has read Seat_21 is available.
User 2 also books the seat. Now the seat info is overwritten and the seat is allotted to User 2.
Due to this phenomenon of double time booking of resources, it is named as The Double Booking Problem!

Following flow diagram gives clear idea about it.

In order to avoid this, we need to use locking.

Locking in DynamoDB

Let us see how we can lock dynamodb objects.

1. Optimistic Locking

The spotlight feature of optimistic locking is, it does not have a lock as such. Instead a version number is attached to the record and it is incremented whenever the record is updated.
Multiple users are allowed to read the document or record.
When the users try to commit their changes, the change related to the first request is accepted as the version numbers match in the record and in the commit request.
Subsequent users trying to commit their changes are sent a Validation Exception!
Based on this, the users can sync the updated record and make their changes and retry committing the changes.
Therefore, the double booking problem is eliminated.

2. Pessimistic Locking

In pessimistic locking mechanism, Locks are implemented. The record that is being updated is locked when a user starts a transaction.
Since the record is locked, the other users trying to update the record are notified that the record is locked and the read also fails.
This mechanism although equally effective, has an overhead of implementing locks.
Therefore, the double booking problem is eliminated.

Thanks for reading this blog.
Aniketh Deshpande
India

TimescaleDB Tablespaces

Aniketh Deshpande — Fri, 21 Oct 2022 04:59:45 +0000

Tablespace

Tablespace is a storage location where the actual data is stored.
The tuples belonging to the same table could be stored in different tablespaces.
Tablespaces are mainly used to separately store data of different priority in different kind of disk.
Example: Data of active users, or recent active customers can be stored in fast disk types like ssd or flash. Data that is old and unused frequently or archived, can be stored in a less expensive and slower data storage like HDD.

TimescaleDB

TimescaleDB is an open source time series database. It is extends PostgreSQL and supports most of the commands of postgres.
Docker Image:

docker pull timescale/timescaledb

TimescaleDB has a concept of hypertables and chunks. Hypertables are Postgresql tables, that partition the data into chunks.
The chunks are created based on primarily the time field.
The chunks older than certain date can be moved to a slow disk like HDD and the latest data which would be heavily used can be used in a fast disk like SSD and Flash.
The concept of tablespaces helps in here.
TSDB code to move chunks:

SELECT move_chunk(
  chunk => '_timescaledb_internal._hyper_1_4_chunk',
  destination_tablespace => 'tablespace_2',
  index_destination_tablespace => 'tablespace_3',
  reorder_index => 'conditions_device_id_time_idx',
  verbose => TRUE
);

For detailed information, use the following link: https://legacy-docs.timescale.com/v1.7/api#move_chunk

AWS Volumes

If the TSDB instance is hosted in a kubernetes cluster in AWS, the TSDB pod would be provided with an AWS Volume for persistent storage.
AWS supports the following volume classes. Based on speed and cost requirements, we can select the appropriate volumes for TSDB tablespaces.
AWS volume types: https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/ebs-volume-types.html

Thank you for reading the blog :)
Aniketh Deshpande

Redis Timeseries

Aniketh Deshpande — Thu, 20 Oct 2022 19:00:04 +0000

Redis is an amazing tool to cache data. It supports different data types to help us cache different kinds of data.

Following are the the data types supported as of Redis 7.0

Strings
Hashes
Lists
Sets
Sorted Sets
Timeseries

In this article, we shall focus mainly on caching timeseries data in redis.

We can cache timeseries data in the following ways:

1. Using the Redis-Timeseries extention

Save data using ADD KEY Timestamp Record
where, Key is the name of the timeseries.
Timestamp is the field used for sorting the elements.
Record is the field representing the value at the given timestamp.
Fetch records using range command. RANGE KEY FROM_TS TO_TS where from_ts and to_ts represent the upper bound and lower bound of the timestamp in the search space.

NOTE: the record field is of type decimal. It supports only numbers.

Therefore, it is very helpful for saving single value records and not lists or maps.
Example: stock values, moisture in soil etc.
However it is not possible to save lists or tuples.
In that case, we can make use of Sorted Sets.

2. Sorted Sets

ZADD KEY Timestamp RECORD.
Here, zadd is used to save data in sorted sets. Key is the series name. Timestamp is the field used for sorting. Record can be of type string. Hence we can save json strings in the record field.
To fetch data from sorted sets, use command ZRANGE FROM_TS TO_TS BYSCORE=TRUE.
Use by_score to get data based on timestamp and not index.

Docker Image For Redis Timeseries

docker pull redislabs/redistimeseries

Link: https://hub.docker.com/r/redislabs/redistimeseries

Thank you for reading the blog. Please suggest improvements and like the blog.

Aniketh Deshpande

DEV Community: Aniketh Deshpande

Database Engines 101: How They Work, the Main Types, and How to Swap Them in PostgreSQL

Table of Contents

Part 1: How an engine works

The naive engine: one row per line in a text file

Idea 1: Pages

Idea 2: Slotted pages

Idea 3: Checksums and the write-ahead log

Idea 4: MVCC

Part 2: Types of engines

Row-oriented store (B-tree + pages)

Log-structured merge tree (LSM)

Column-oriented store

In-memory store

Pluggable engines

Part 3: The lab

Step 0: Start PostgreSQL

Step 1: See which engines are installed

Step 2: Same table, two engines

Step 3: Run an analytics query

Step 4: Update one row

Step 5: Turn durability down with UNLOGGED

Step 6: Crash it on purpose

Step 7: Swap index engines

Step 8: Change a table's engine later

Clean up

Cheat sheet: pick an engine by use case

Want to go deeper?

Further reading

Your Python venv Is (Mostly) a Symlink — Here's Why That Matters

Table of Contents

The claim

Part 1 — See it with your own eyes

Step 0: Get a VM

Option A — Google Cloud (GCE)

Option B — AWS (EC2)

Experiment 1: Look inside bin/

Experiment 2: Follow the chain

Experiment 3: Read pyvenv.cfg, the venv's "birth certificate"

Experiment 4: Ask Python where things come from

Experiment 5: Check the size

Experiment 6: Move the venv and watch it break

Experiment 7: Delete the base Python (the scary one)

Part 2 — What does this mean?

How a venv actually boots

Why did Python's designers do it this way?

✅ Pros

❌ Cons

How it bites in real life

Part 3 — Building a venv that doesn't betray you

Rule 1 — Never build venvs on the distro's python3

Option A: uv (fast and simple)

Option B: pyenv (compiles from source)

Option C: No extra tools? At least pin the minor version

Rule 2 — Treat the venv as a cache, not a pet

Rule 3 — In Docker, keep the venv's world identical across stages

Rule 4 — If Python might be missing at the destination, ship the interpreter

What about --copies?

Bonus: a venv health check

Cheat sheet

Wrapping up

Predicate Pushdown - Understanding Practically With An Example

Write Through

Advantages:

Disadvantages:

Database Caching Strategies

Cache Aside

Advantages:

Disadvantages:

Write through

Advantages:

Disadvantages:

Cache Aside

Advantages:

Disadvantages:

Shallow Copy Vs Deep Copy

Shallow Copy - copy.copy(obj)

Deep Copy - copy.deepcopy(obj)

Dead Letter Queue

What is a dead letter queue??

Step 5: Turn durability down with `UNLOGGED`

Experiment 1: Look inside `bin/`

Experiment 3: Read `pyvenv.cfg`, the venv's "birth certificate"

Rule 1 — Never build venvs on the distro's `python3`

Option A: `uv` (fast and simple)

Option B: `pyenv` (compiles from source)

What about `--copies`?