8 ms·

We do similar, although lean into our strict "every table as a sequence" and "all FKs are deferred" conventions and only issue DELETEs for tables that actually

by stephen 2mo ago

We do similar, although lean into our strict "every table as a sequence" and "all FKs are deferred" conventions and only issue DELETEs for tables that actually were inserted by the test https://github.com/joist-orm/joist-orm/blob/16cc73f148b6f962fac83bf7470520fe09d2be48/packages/codegen/src/generateFlushFunction.ts#L88 https://github.com/joist-orm/joist-orm/blob/16cc73f148b6f962... I forgot the speedup this got us on a 400-500 table schema, but it was noticeable -- curious if you could do the same / what the perf impact would be.

ltbarcly3 2mo ago
tracking the table inserted to isn't reliable without some kind of trigger based registry as it requires all db interactions to go through some kind of orm or something which we don't do because it's a bad thing to do. Sometimes CTE's that modify stuff are 1000x faster than the alternative and it's hard to track what is doing modifications vs not. We do track at the psycopg2 level whether a query has INSERT in it somewhere, which is a pretty good heuristic. But lets say we just always cleared all the tables: 5ms per test * 6000 tests == 30s, across 15 test processes it is 2s of overhead to the test run. Meh. You are better off auditing your test setup functions that get reused (create_test_user etc) for how many queries they do, you might find that your overall test setup spends 20% of it's runtime creating users. When I did this I found that 50% of our test runtime was processing stack traces in logging statements (to show where in the code it was being logged from), modifying it to only put tracebacks on INFO and above cut our total testing time by almost 50%.
- stephen 2mo ago
  > tracking the table ... isn't reliable without [trigger] or [orm] We use sequences, which is neither of those, and has been very reliable for us. I included the disclaimer that you need a schema that follows strict sequence name => table name conventions, but that's what we have. > 20% of runtime creating users Right -- we also have ~2-3 "stable" rows of users that every test needs, so we can skip re-creating for each test.
  - ltbarcly3 2mo ago
    Using sequences is clever if you always increment a sequence for every insert, but again my analysis says that unless your table clearing is much slower than mine it's hardly worth it to check, in fact checking the value of every sequence can't take much less than 5ms which is how long it takes to clear every table if empty but is less general.

by stephen 2mo ago

ltbarcly3 2mo ago

tracking the table inserted to isn't reliable without some kind of trigger based registry as it requires all db interactions to go through some kind of orm or something which we don't do because it's a bad thing to do. Sometimes CTE's that modify stuff are 1000x faster than the alternative and it's hard to track what is doing modifications vs not. We do track at the psycopg2 level whether a query has INSERT in it somewhere, which is a pretty good heuristic. But lets say we just always cleared all the tables: 5ms per test * 6000 tests == 30s, across 15 test processes it is 2s of overhead to the test run. Meh. You are better off auditing your test setup functions that get reused (create_test_user etc) for how many queries they do, you might find that your overall test setup spends 20% of it's runtime creating users. When I did this I found that 50% of our test runtime was processing stack traces in logging statements (to show where in the code it was being logged from), modifying it to only put tracebacks on INFO and above cut our total testing time by almost 50%.

stephen 2mo ago
> tracking the table ... isn't reliable without [trigger] or [orm] We use sequences, which is neither of those, and has been very reliable for us. I included the disclaimer that you need a schema that follows strict sequence name => table name conventions, but that's what we have. > 20% of runtime creating users Right -- we also have ~2-3 "stable" rows of users that every test needs, so we can skip re-creating for each test.
- ltbarcly3 2mo ago
  Using sequences is clever if you always increment a sequence for every insert, but again my analysis says that unless your table clearing is much slower than mine it's hardly worth it to check, in fact checking the value of every sequence can't take much less than 5ms which is how long it takes to clear every table if empty but is less general.