Re: No flamefest please, MySQL vs. PostgreSQL AGAIN

> > 3. Non-logged bulk inserts -- How much logging does COPY table FROM
> >    do? Is it comparable to a MySQL MyISAM table?
> I cannot imagine why you'd want non-logged inserts, unless you don't
> actually care about your data.

More or less, we don't. If we try to insert 1,000 entries, and our table
has now 1,000 entries more, then we're satisfied. If our database goes
down, we simply need to re-load. It's a data warehouse, after all, and
its source is the L bit of the ETL (extract/transform/load) process. All
the data we truly care about transactions for are on the OLTP databases.

To wit: atomicity? We don't care. Consistency? We don't care. Isolation?
We don't care. Durability? We care, but if it goes dead-mode before
buffers get flushed to disk, we've got all the data ready to load again.

Maybe I don't understand how to ask the question. I want to know how to
insert (say) 10M rows into a table quickly. I mean... VERY quickly.

Obviously the following 10M transactions are going to be slow:

        insert into tab values (1);
        insert into tab values (2);
        insert into tab values (3);
         . . .
        insert into tab values (10000000);

Would it be faster if I put a single transaction around that? Would it
be faster to do the following?

        copy tab (col) from stdin;
        1
        2
        3
         . . .
        10000000
        \.

> It should be noted though that as of
> 7.3, operations on TEMP tables don't do WAL logging; perhaps that
> would be of use to you.

It does sound useful, on new tables, to load into a temp table, then
rename the temp table as permanent table.

> [point-in-time recovery] might be there in 7.4 ... it ain't there today

Would it be better to hang out on hackers to find out about this?
Looking at archives, I see messages all the way back to July of last
year talking about it. I only recall seeing one message (from Bruce)
about it on this list.

--
Tim Ellis
Senior Database Architect and author, tedia2sql (http://tedia2sql.tigris.org)
If this helped you, http://svcs.affero.net/rm.php?r=philovivero