Closed tantaman closed 7 months ago
The problem is in backfill.rs
Simple test case:
def test_commit_alter_perf():
c = connect(":memory:")
c.execute("CREATE TABLE issue (id INTEGER PRIMARY KEY NOT NULL, title TEXT, owner TEXT, status INTEGER, priority INTEGER)")
c.execute("SELECT crsql_as_crr('issue')")
c.commit()
start_time = time.time()
for i in range(10_000):
c.execute("INSERT INTO issue (title, owner, status, priority) VALUES ('title', 'owner', 1, 1)")
c.commit()
end_time = time.time()
print(f"insert time: {end_time - start_time}")
start_time = time.time()
c.execute("SELECT crsql_begin_alter('issue')")
c.execute("SELECT crsql_commit_alter('issue')")
end_time = time.time()
print(f"no alter alter time: {end_time - start_time}")
start_time = time.time()
c.execute("SELECT crsql_begin_alter('issue')")
c.execute("ALTER TABLE issue ADD COLUMN description TEXT")
c.execute("SELECT crsql_commit_alter('issue')")
end_time = time.time()
print(f"alter add col time: {end_time - start_time}")
Timing alter with backfill:
tests/test_commit_alter_perf.py insert time: 0.11704802513122559
no alter alter time: 1.5357089042663574
alter add col time: 1.5406219959259033
Timing alter without backfill:
tests/test_commit_alter_perf.py insert time: 0.12140798568725586
no alter alter time: 0.015674829483032227
alter add col time: 0.015607118606567383
Skipping backfill was a 10x improvement.
specifically, create_clock_rows_from_stmt
is super slow in backfill.rs
Without that call the alter takes:
tests/test_commit_alter_perf.py insert time: 0.12377500534057617
no alter alter time: 0.024814128875732422
alter add col time: 0.0249788761138916
https://discord.com/channels/989870439897653248/989870440585494530/1176604709549768796