Re: Template for schema? (as opposed to databases)

Hi Dominique:

On Tue, 11 Jan 2022 at 17:10, Dominique Devienne <ddevienne(at)gmail(dot)com> 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.