Re: Idea for improving speed of pg_restore

On Tue, 16 Sep 2003, Tom Lane wrote:

> Christopher Browne <cbbrowne(at)acm(dot)org> writes:
> > Restoring a database involves, for each table:
> > 1. Reading table data from the source file;
> > 2. Writing data to the database file for the table;
> > 3. After that, reading the database file data, and
> > 4. Writing the sorted bits to the index file.
> > 5. Along with all of this, HEFTY amounts of updates to WAL.
>
> An idea that Marc and Jan and I were kicking around last night was to
> offer a GUC option to disable writes to WAL. During initial data load,
> you might as well go back to initdb if you have any failure, so why
> bother with full ACID compliance? I'm not sure if the performance
> benefit would be great enough to make it worth equipping the system
> with such a large-caliber foot-gun, but it's something to think about.
>
> I tend to agree with your doubts about parallelizing index builds,
> but there may be scenarios where it's a win; it'd depend on your
> relative CPU and disk horsepower. (Consider fast disk and multiple
> not-so-fast CPUs; serial index builds can only use one of the CPUs.)
> Question is, is it a big enough win for enough people to make it worth
> supporting?

I'd say anything that improves postgresql's performance on medium to large
iron is worth at least trying. I imagine a 12 disk RAID5 with battery
backed RAID would likely be able to use parallelized index builds and even
loads.

Not so sure on whether the foot gun is a good idea. We already have .22
calibre foot gun (fsync) that makes for pretty big improvements in load
speed, and we see people all the time on General and Performance running
production servers with it turned off. You know as well as I do the
second we make WAL optional, some people are gonna start running
production servers with it.