Pythonpartitioning_demo.py
Copy
1
import psycopg2
2
import random
3
from datetime import datetime, timedelta
4
 
5
# Connect to Postgres (adjust DSN)
6
conn = psycopg2.connect("host=localhost dbname=learndb user=postgres")
7
conn.autocommit = True
8
cur = conn.cursor()
9
 
10
# ── Range Partitioning by month ────────────────────────────────────
11
cur.execute("""
12
DROP TABLE IF EXISTS logs CASCADE;
13
 
14
CREATE TABLE logs (
15
    id         BIGSERIAL,
16
    level      TEXT NOT NULL,
17
    message    TEXT NOT NULL,
18
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
19
) PARTITION BY RANGE (created_at);
20
 
21
-- 2024 quarterly partitions
22
CREATE TABLE logs_2024_q1 PARTITION OF logs
23
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');
24
CREATE TABLE logs_2024_q2 PARTITION OF logs
25
    FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');
26
CREATE TABLE logs_2024_q3 PARTITION OF logs
27
    FOR VALUES FROM ('2024-07-01') TO ('2024-10-01');
28
CREATE TABLE logs_2024_q4 PARTITION OF logs
29
    FOR VALUES FROM ('2024-10-01') TO ('2025-01-01');
30
 
31
-- Default catch-all
32
CREATE TABLE logs_default PARTITION OF logs DEFAULT;
33
""")
34
 
35
# Insert rows across all quarters
36
base = datetime(2024, 1, 1)
37
rows = [
38
    (random.choice(["INFO", "WARN", "ERROR"]),
39
     f"Event {i}",
40
     base + timedelta(days=random.randint(0, 364)))
41
    for i in range(10_000)
42
]
43
cur.executemany(
44
    "INSERT INTO logs (level, message, created_at) VALUES (%s, %s, %s)",
45
    rows,
46
)
47
print("Inserted 10,000 log rows")
48
 
49
# Count per partition
50
for q, start, end in [("Q1", "2024-01-01", "2024-04-01"),
51
                       ("Q2", "2024-04-01", "2024-07-01"),
52
                       ("Q3", "2024-07-01", "2024-10-01"),
53
                       ("Q4", "2024-10-01", "2025-01-01")]:
54
    cur.execute("SELECT COUNT(*) FROM logs WHERE created_at BETWEEN %s AND %s", (start, end))
55
    count = cur.fetchone()[0]
56
    print(f"  {q}: {count} rows")
57
 
58
# Show partition pruning
59
cur.execute("EXPLAIN SELECT * FROM logs WHERE created_at BETWEEN '2024-04-01' AND '2024-06-30'")
60
plan = cur.fetchall()
61
for row in plan[:10]:
62
    print(row[0])
63
 
64
cur.close()
65
conn.close()
66