Lesson 5.2: Handling Massive Writes
Bulk Loading Strategies
Slow: Row-by-row inserts:
for row in data:
db.execute("INSERT INTO embeddings VALUES (%s, %s, %s)", row)
# 1M rows × 5ms = 5000 seconds (83 minutes!)
Fast: COPY command:
import csv
import io
# Write to CSV in memory
csv_buffer = io.StringIO()
writer = csv.writer(csv_buffer)
for row in data:
writer.writerow(row)
# Bulk load
csv_buffer.seek(0)
cursor.copy_expert("COPY embeddings FROM STDIN WITH CSV", csv_buffer)
# 1M rows in 10 seconds (500x faster!)
Faster: DROP indexes, then rebuild:
BEGIN;
-- Drop indexes
DROP INDEX embeddings_vector_idx;
DROP INDEX embeddings_document_id_idx;
-- Bulk load
COPY embeddings FROM '/path/to/data.csv' WITH CSV;
-- Rebuild indexes
CREATE INDEX embeddings_vector_idx ON embeddings USING hnsw(vector vector_cosine_ops);
CREATE INDEX embeddings_document_id_idx ON embeddings(document_id);
COMMIT;
-- Total time: 60 seconds vs 300 seconds with indexes
Write Optimization Techniques
1. Batch commits:
# Bad: Commit each row
for row in data:
db.execute("INSERT ...")
db.commit() # Slow!
# Good: Batch commits
db.execute("BEGIN")
for i, row in enumerate(data):
db.execute("INSERT ...")
if i % 1000 == 0:
db.commit()
db.execute("BEGIN")
db.commit()
2. UNLOGGED tables (temporary data):
-- Skip WAL (Write-Ahead Log) - faster writes
CREATE UNLOGGED TABLE temp_import (
id BIGINT,
vector VECTOR(1536)
);
-- Load data fast
COPY temp_import FROM '/data.csv';
-- Process and move to logged table
INSERT INTO embeddings SELECT * FROM temp_import;
3. Parallel writes (multiple connections):
from concurrent.futures import ThreadPoolExecutor
def insert_chunk(chunk):
conn = get_db_connection()
conn.execute("COPY embeddings FROM STDIN", chunk)
# Split data into 10 chunks, insert in parallel
with ThreadPoolExecutor(max_workers=10) as executor:
executor.map(insert_chunk, data_chunks)
Key Takeaways
- COPY is 500x faster than row-by-row INSERT for bulk loads
- Drop indexes before bulk inserts, rebuild after
- Batch commits reduce WAL overhead
- UNLOGGED tables skip durability for temporary staging data
- Parallel writes use multiple connections for throughput
- ML embedding generation often requires bulk loading millions of vectors
التالي · الدرس 30 من 45
Real-Time Ingestion from ML Systems
قراءة 1 دقيقة
أنهيت 29 من 45 درسًا

اسأل Byte عن هذا الدرس
محادثة خاصة حول هذا الدرس. قرأ Byte الدرس كاملًا، ويجيب خلال ثوانٍ، ويُظهر القسم الذي تأتي منه كل إجابة. يمكنك السؤال بأي لغة.
الأسئلة والأجوبة
أسئلة عامة حول هذا الدرس. يجيب AI Tutor فورًا بلغتك، ويراجع فريق FreeAcademy الإجابات، ويمكن لكل المتعلمين قراءتها.

