PostgreSQL ikki tomonga: bazadan olish, bazaga yozish

5-bob 5 kod bloki 1572 so'z

Nega baza

Tahlilchining yetti talabidan to'rtinchisi: "Ma'lumotni bazaga yuklash". Bankda ma'lumot faqat Excelda yashamaydi. Uning asl manbai — ma'lumotlar bazasi. Excel fayl — ko'pincha shu bazadan bir paytda olingan eksport.

Demak Tezga ikki yo'nalish kerak:

• Bazadan olish: jadvalni yoki so'rov natijasini to'g'ridan-to'g'ri o'qish — Excel eksportini kutmasdan.
• Bazaga yozish: tozalangan natijani yangi jadval sifatida qaytarish.

Loyiha boshida bitta qaror qabul qilindi (qarorlar jurnalida D-12): PostgreSQL ham manba, ham manzil bo'ladi, lekin Tezning ichidagi engine DuckDB bo'lib qoladi. Sabab oddiy: DuckDB o'rnatishni talab qilmaydi — u Python kutubxonasi. PostgreSQL esa server. Tahlilchining kompyuteriga server o'rnatish o'rniga, Tez mavjud bazaga ulanadi.

Savol: DuckDB PostgreSQL bilan ikki tomonga ishlay oladimi, va qanchalik tez? Bu — spike 1b.

Spike qanday qurildi

Spike uchun tashlab yuboriladigan PostgreSQL ishlatildi: versiya 17.11, faqat shu kompyuterda, ishdan keyin o'chirildi. Uni boshqaradigan cluster.py dan bitta qator — server qanday ishga tushirilgani:

import ast

POSTGRES = Path("/mnt/c/Users/Admin/Desktop/Offline/spikes/spike1_excel_read/postgres")

def funksiya(fayl, nom):
    manba = fayl.read_text(encoding="utf-8")
    tugun = next(t for t in ast.parse(manba).body if isinstance(t, ast.FunctionDef) and t.name == nom)
    return ast.get_source_segment(manba, tugun)

print(funksiya(POSTGRES / "cluster.py", "start"))

def start(args):
    options = f"-p {args.port} -c listen_addresses=127.0.0.1 -c unix_socket_directories=''"
    log = str(Path(args.data_dir) / "server.log")
    subprocess.run([_tool(args, "pg_ctl"), "-D", args.data_dir, "-l", log, "-o", options, "-w", "start"], check=True)

listen_addresses=127.0.0.1 — server faqat shu kompyuterdan ulanishni qabul qiladi. unix_socket_directories='' — boshqa kirish yo'li ham yopiq. Spike da parol yo'q edi (trust) — bu faqat tashlab yuboriladigan sinov serveri uchun maqbul. Bankdagi haqiqiy bazada bunday narsa bo'lmaydi.

Ma'lumotni ko'chiradigan kod — transfer.py. Uning eng muhim to'rt funksiyasi:

for nom in ("open_duckdb", "push_attach", "pull_attach", "aggregate_remote"):
    print(funksiya(POSTGRES / "transfer.py", nom))
    print()

def open_duckdb(source_csv, dsn):
    con = duckdb.connect()
    con.execute("CREATE TABLE t AS SELECT * FROM read_csv(?)", [source_csv])
    con.execute("LOAD postgres")
    con.execute(f"ATTACH '{dsn}' AS pg (TYPE postgres)")
    return con

def push_attach(con, dsn, table):
    _drop_remote(con, table)
    con.execute(f"CREATE TABLE pg.{quote(table)} AS SELECT * FROM t")

def pull_attach(con, table):
    con.execute("DROP TABLE IF EXISTS back")
    con.execute(f"CREATE TABLE back AS SELECT * FROM pg.{quote(table)}")

def aggregate_remote(con, table, group_by, value):
    query = (
        f"SELECT {quote(group_by)}, count(*), sum({quote(value)}) "
        f"FROM pg.{quote(table)} GROUP BY 1"
    )
    return con.execute(query).fetchall()

Butun "ko'prik" — bitta ATTACH buyrug'i. Undan keyin PostgreSQL jadvali DuckDB uchun oddiy jadvalga aylanadi: pg.jadval_nomi. Yozish — CREATE TABLE pg.x AS SELECT .... O'qish — SELECT ... FROM pg.x. Alohida drayver kodi, alohida format yo'q.

Taqqoslash uchun ikkinchi yo'l ham o'lchandi: psycopg kutubxonasi orqali PostgreSQL ning COPY buyrug'i — Pythonda ma'lumotni PostgreSQL ga yuklashning odatiy tez usuli.

Natija

Sinov ma'lumoti — 4-bobdagi 400 000 qatorli tranzaksiyalar jadvali. Bu spike kitobni yig'ish paytida qayta ishga tushirilmaydi: buning uchun PostgreSQL serverini ishga tushirish kerak, biz esa kitob yig'ilayotganda server ko'tarmaymiz. Shuning uchun spike ning saqlangan natijasini o'qiymiz — o'zgartirmasdan:

print((POSTGRES.parent / "results_postgres_wsl.md").read_text(encoding="utf-8"))

rows in source: 400,000
| Step | Seconds (median) | Check |
|---|---|---|
| A1 push: DuckDB postgres extension | 0.67 | pass |
| A2 push: psycopg COPY | 0.82 | pass |
| B1 pull: full table into DuckDB | 0.17 | pass |
| B2 query: group and total on PostgreSQL table | 0.06 | pass |

400 000 qatorni yozish — bir soniyadan kam. O'qish — undan ham tez. Va DuckDB ning kengaytmasi psycopg + COPY dan tezroq chiqdi, holbuki u kamroq kod talab qiladi. Xulosa: ko'chirish uchun psycopg umuman kerak emas.

Har qatorda "pass" yozuvi bor. U nimani anglatadi — buni alohida ko'rib chiqishimiz kerak.

"pass" nimani tekshiradi

Har bir ko'chirishdan keyin spike ikkala tomonning fingerprint (barmoq izi) ni olib solishtiradi: qatorlar soni, har bir ustunda bo'sh bo'lmagan qiymatlar soni va har bir sonli ustunning yig'indisi. Funksiyaning o'zi:

for nom in ("fingerprint", "_equal", "same"):
    print(funksiya(POSTGRES / "transfer.py", nom))
    print()

def fingerprint(con, relation):
    columns = con.execute(f"DESCRIBE {relation}").fetchall()
    parts = ["count(*)"]
    for name, kind, *_ in columns:
        parts.append(f"count({quote(name)})")
        if kind.split("(")[0] in NUMERIC_TYPES:
            parts.append(f"sum({quote(name)})::DOUBLE")
    return con.execute(f"SELECT {', '.join(parts)} FROM {relation}").fetchone()

def _equal(a, b, tolerance):
    if isinstance(a, float) or isinstance(b, float):
        return a is not None and b is not None and math.isclose(a, b, rel_tol=tolerance)
    return a == b

def same(left, right, tolerance=1e-9):
    return len(left) == len(right) and all(_equal(a, b, tolerance) for a, b in zip(left, right))

G'oya yaxshi: agar bitta qator yo'qolsa, soni o'zgaradi; agar bitta qiymat buzilsa, yig'indi o'zgaradi. Lekin biz bu kitobni yozayotganda barmoq izini sinab ko'rdik — ataylab buzilgan nusxalar bilan. Sinov uchun PostgreSQL kerak emas: barmoq izi DuckDB ichida olinadi, va biz transfer.py ning o'z funksiyalarini ishlatamiz. Ma'lumot — 3-bobdagi sintetik kredit jadvali:

import sys
import duckdb

sys.path.insert(0, str(POSTGRES))
import transfer

con = duckdb.connect()
con.execute(f"create table asl as select * from read_csv('{DATA / 'loans.csv'}')")
asl = transfer.fingerprint(con, "asl")
jami = con.execute("select sum(qoldiq_uzs) from asl").fetchone()[0]
print(f"qatorlar: {asl[0]:,}, barmoq izidagi sonlar: {len(asl)}")
print(f"qoldiq_uzs ustunining yig'indisi: {jami:,.2f} so'm")
print()

def nusxa(nom, ifoda):
    con.execute(f"create or replace table {nom} as select * replace ({ifoda} as qoldiq_uzs) from asl")
    return transfer.same(asl, transfer.fingerprint(con, nom))

print(f"o'zgarishsiz nusxa:                  {nusxa('n0', 'qoldiq_uzs')}")
for farq in (0.01, 1000, 10000):
    natija = nusxa("n1", f"case when kredit_raqami = 500001 then qoldiq_uzs + {farq} else qoldiq_uzs end")
    print(f"bitta qator {farq:>9,} so'mga buzilgan: {natija}")
almashgan = nusxa("n2", """case kredit_raqami
    when 500001 then (select qoldiq_uzs from asl where kredit_raqami = 500002)
    when 500002 then (select qoldiq_uzs from asl where kredit_raqami = 500001)
    else qoldiq_uzs end""")
print(f"ikki qatorning qiymati almashgan:    {almashgan}")

qatorlar: 20,000, barmoq izidagi sonlar: 32
qoldiq_uzs ustunining yig'indisi: 8,552,858,333,854.45 so'm

o'zgarishsiz nusxa:                  True
bitta qator      0.01 so'mga buzilgan: True
bitta qator     1,000 so'mga buzilgan: True
bitta qator    10,000 so'mga buzilgan: False
ikki qatorning qiymati almashgan:    True

True — barmoq izi "bir xil" degani. Natijani o'qing:

• O'zgarishsiz nusxa — True. To'g'ri.
• Bitta qator 0.01 so'mga buzilgan — True. Buzilish sezilmadi.
• Bitta qator 1 000 so'mga buzilgan — True. Sezilmadi.
• Bitta qator 10 000 so'mga buzilgan — False. Ushlandi.
• Ikki qatorning qiymati almashgan — True. Yig'indi o'zgarmaydi, demak hech qachon sezilmaydi.

Sababi yuqoridagi ikki funksiyada: same standart holatda tolerance=1e-9 oladi, _equal esa uni math.isclose ga nisbiy farq sifatida beradi. Ya'ni sonlar milliarddan bir nisbiy farq bilan solishtiriladi. Bu kichik tuyuladi. Lekin yig'indi trillionlab so'm bo'lsa, milliarddan biri — minglab so'm. Bitta qatordagi shuncha xato "pass" bo'lib o'tadi.

Bu bizning xatoimiz. Spike 1b ning "pass" natijalari biz o'ylagandan zaifroq tekshiruvga asoslangan. Biz uni xatolar jurnaliga 17-raqam bilan yozdik va qoida qo'shdik:

Bu topilma spike ning asosiy javobini o'zgartirmaydi: DuckDB PostgreSQL bilan ikki tomonga ishlaydi va tez. Lekin "hamma tekshiruv o'tdi" degan jumla endi kitobda izohsiz turmaydi.

Bankda bu qanday bo'ladi

Bu yerda halol bo'lish kerak: Tezning bugungi kodida PostgreSQL ulagichi yo'q. Bor narsa — spike va qaror.

Ulagich qurilganda u shunday qoidalar bilan ishlaydi (plandagi talablar):

• Bazadan faqat o'qish. Standart holatda ulanish faqat o'qish uchun. Tez bank bazasidagi hech narsani o'zgartirmaydi.
• Bazaga yozish — faqat yangi jadvalga, oldindan ko'rsatib va foydalanuvchi tasdig'idan keyin. Mavjud jadval ustidan yozilmaydi.
• Tekshiruv — yuqoridagi saboq bilan: aniq qiymatlar bo'yicha.

Va eng katta ochiq savol (Q-13): bank qaysi bazadan foydalanadi? PostgreSQL, Oracle yoki MS SQL — biz hali bilmaymiz. Agar Oracle yoki MS SQL bo'lsa, bu spike ning javobi to'g'ridan-to'g'ri qo'llanmaydi va yangi o'lchov kerak bo'ladi. Shuningdek, bank IT bo'limi Tezga bazani to'g'ridan-to'g'ri o'qishga ruxsat beradimi — buni ham faqat bank aytadi.

Xulosa

• Tez PostgreSQL bilan ikki tomonga ishlashi kerak: manba sifatida o'qish va natijani yozish. Ichki engine DuckDB bo'lib qoladi (D-12).
• DuckDB ning postgres kengaytmasi bitta ATTACH bilan ikkala yo'nalishni ham qildi va psycopg + COPY dan tezroq chiqdi. 400 000 qator bir soniyadan kam.
• Spike dagi "pass" tekshiruvi zaif chiqdi: u bitta qatordagi minglab so'mlik xatoni va almashgan qiymatlarni sezmaydi. Bu xatolar jurnaliga yozildi (17).
• Ulagich hali kodda yo'q. Bank qaysi bazani ishlatishi va ruxsat berishi — ochiq savol.

Keyingi bobda ikkinchi qismning eng muhim o'lchoviga o'tamiz: model oddiy kompyuterda, CPU da qanchalik tez javob beradi.