Re: proposal: CREATE DATABASE vs. (partial) CHECKPOINT

>
>
>
> IMHO writing all the data into a WAL would be the cleanest solution.
>
> Also, what is a small database? I don't think a static value will work,
> because the sweet spot between the current approach (forcing two
> checkpoints) and writing everything in WAL depends on the amount of dirty
> buffers that need to be checkpointed. Which is mostly driven by the size
> of shared buffers and write activity - for small shared buffers and/or
> mostly-read workload, checkpoints are cheap, so the 'small database'
> threshold (when choosing the WAL approach) is much higher than for large
> shared buffers or write-heavy workloads.
>

So are you proposing having a heuristic based on the amount of data in
shared buffers and write activity? Do you have something in mind that works
for general workloads as well?

> So maybe if we could determine the amount of data to be checkpointed, and
> then base the decision on that, that'd work better? This would also have
> to take into account that writing into WAL is sequential, while
> checkpoints usually cause random writes all over the datafiles (which is
> more expensive).
>
> Another option might be forcing just a "spread" checkpoint, not the
> immediate one (which is what we do now). That would not fix the CREATE
> DATABASE duration (actually, it would make it longer), but it would lower
> the impact on other activity on the machine.
>
>
>
I believe this to be the cleanest way to reduce the amount of I/O
generated. If I understand correctly, the original problem you mentioned
was not the time CREATE DATABASE is taking but rather the amount of I/O
each one is generating.

This also leads me to think if it makes sense to explore group commits
around the creation of files for a new database (for a same backend, of
course). This might be on call, if the user knows he/she is going to create
a lot of databases in the near future and is fine with a large spike in I/O
at one go. Again, might be even more broken than the current scenario, but
depends on what the user wants...

Regards,

Atri

--
Regards,

Atri
*l'apprenant*