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

I do not need to do insert updates from many threads. I want to do it from
one thread.

My current MySQL architecture is that I have a table with same layout as the
main one, to hold new and updated objects.

When there is enough objects, I begin a big INSERT SELECT ... ON DUPLICATE
KEY UPDATE and stuff that into the master table.

i

On Tue, Sep 13, 2011 at 1:11 PM, Marti Raudsepp <marti(at)juffo(dot)org> wrote:

> On Tue, Sep 13, 2011 at 19:34, Robert Klemme <shortcutter(at)googlemail(dot)com>
> wrote:
> > I don't think so. You only need to catch the error (see attachment).
> > Or does this create a sub transaction?
>
> Yes, every BEGIN/EXCEPTION block creates a subtransaction -- like a
> SAVEPOINT it can roll back to in case of an error.
>
> > 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.
>
> In a mass-loading application you can often divide the work between
> threads in a manner that doesn't cause conflicts.
>
> For example, if the unique key is foobar_id and you have 4 threads,
> thread 0 will handle rows where (foobar_id%4)=0, thread 1 takes
> (foobar_id%4)=1 etc. Or potentially hash foobar_id before dividing the
> work.
>
> I already suggested this in my original post.
>
> Regards,
> Marti
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>