Hi Dominique: On Tue, 11 Jan 2022 at 17:10, Dominique Devienne <ddevienne@xxxxxxxxx> wrote: ... > Creating the first two "fixed" schemas is fast enough. 300ms, including populating the 2nd with a bunch of rows. > But creating the 3rd kind, which will be done many times, is not so fast, at 2.5 - 3s, which may seem fast, > but when you do it dozens of time in unit-tests, adds up quickly to slow running tests... First question would be, are you running a test server? Are you testing crashes of just application logic? For these I normally use wal_level=minimal, fsync=off, same as for full cluster restores ( it is faster to redo it if restore fails, reinitdb on test cases ), it normally gives quite a nice performance boost. ... > Right now, that implies quite a few round-trips to the server, about 1'100, but on the LAN with sub-ms latency; How much sub-ms? I mean, I have 0.74 pings to my router, but this would be .82s, a good chunk of your quoted 2-3s, (and I'm not sure if libpq can do all queries in one RTT ). ... > One idea I had, similar to the template-DB feature, would be to create 1 largish read-only schema at installation time > (i.e. not create only 2 but the 3 kinds of schemas), and have the ability to clone this 3rd (largish) kind entirely server-side. You could try to use do-blocks, or even create procedures, even parametrized. If all schemas are similar you could theoretically send a parametrized procedure to create one, send another to execute it N times, execute it, drop both. Basically, send some of your logic to the database. > So if there a way to close a schema easily in PostgreSQL? s/close/clone/, I assume. ... > Please do note this needs to be done from a libpq client, which has no direct access to the server (i.e. cannot use dump/restore solutions). pg_dump/pg_restore work with the same connection as libpq, I even suspect they are written using libpq ( but the need to use your own client may preclude using dump/restore ). Of course these are generalities for speeding up tsts. FOS.
