Re: Postgres for a "data warehouse", 5-10 TB

On Tue, Sep 13, 2011 at 5:13 PM, Marti Raudsepp <marti(at)juffo(dot)org> wrote:
> On Tue, Sep 13, 2011 at 00:26, Robert Klemme <shortcutter(at)googlemail(dot)com> wrote:
>> In the case of PG this particular example will work:
>> 1. TX inserts new PK row
>> 2. TX tries to insert same PK row => blocks
>> 1. TX commits
>> 2. TX fails with PK violation
>> 2. TX does the update (if the error is caught)
>
> That goes against the point I was making in my earlier comment. In
> order to implement this error-catching logic, you'll have to allocate
> a new subtransaction (transaction ID) for EVERY ROW you insert.

I don't think so. You only need to catch the error (see attachment).
Or does this create a sub transaction?

> If
> you're going to be loading billions of rows this way, you will invoke
> the wrath of the "vacuum freeze" process, which will seq-scan all
> older tables and re-write every row that it hasn't touched yet. You'll
> survive it if your database is a few GB in size, but in the terabyte
> land that's unacceptable. Transaction IDs are a scarce resource there.

Certainly. But it's not needed as far as I can see.

> In addition, such blocking will limit the parallelism you will get
> from multiple inserters.

Yes, I mentioned the speed issue. But regardless of the solution for
MySQL's "INSERT..ON DUPLICATE KEY UPDATE" which Igor mentioned you
will have the locking problem anyhow if you plan to insert
concurrently into the same table and be robust.

Kind regards

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/