Обсуждение: Parallel (concurrent) inserts?
Hello, I'm wondering if there is ia document describing which guarantees (if any) PostgreSQL makes about concurrency for various operations? Speaking in general (i.e. IO can handle it, number of CPU cores and client threads is optimal), are fully concurrent operations (independant and non-blocking) possible for: 1) An unindexed table? 2) A table with 1+ ordinary (default btree) indexes? (are there constraints on the structure and number of indexes?) 3) A table with 1+ unique indexes? 4) A table with other objects on it (foreign keys, check constraints, etc.)?
> I'm wondering if there is ia document describing which guarantees (if > any) PostgreSQL makes about concurrency for various operations? Speaking > in general (i.e. IO can handle it, number of CPU cores and client > threads is optimal), are fully concurrent operations (independant and > non-blocking) possible for: Yes, there's quite a bit of documentation. Start here: http://www.postgresql.org/docs/9.1/static/mvcc.html > 1) An unindexed table? Yes. > 2) A table with 1+ ordinary (default btree) indexes? (are there > constraints on the structure and number of indexes?) Yes. > 3) A table with 1+ unique indexes? Yes. Note that locking on the unique index may degrade concurrent throughput of the input stream though, since we have to make sure you're not inserting two different rows with the same unique indexed value simulatenously. It's work as you expect, though. > 4) A table with other objects on it (foreign keys, check constraints, etc.)? Yes. Also concurrent autonumber (sequence) allocations work fine. In fact, PostgreSQL has no non-concurrent mode, unless you count temp tables. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
On Fri, May 25, 2012 at 3:04 PM, Ivan Voras <ivoras@freebsd.org> wrote: > Hello, > > I'm wondering if there is ia document describing which guarantees (if > any) PostgreSQL makes about concurrency for various operations? Speaking > in general (i.e. IO can handle it, number of CPU cores and client > threads is optimal), are fully concurrent operations (independant and > non-blocking) possible for: By "fully concurrent" do you mean that there is no detectable sub-linear scaling at all? I'm pretty sure that no such guarantees can be made. > 1) An unindexed table? For concurrent bulk loads, there was severe contention on generating WAL between concurrent bulk loaders. That is greatly improved in the upcoming 9.2 release. > 2) A table with 1+ ordinary (default btree) indexes? (are there > constraints on the structure and number of indexes?) This will be limited by contention for generating WAL. (Unless something else limits it first) > 3) A table with 1+ unique indexes? If more than one transaction attempts to insert the same value, one has to block until the other either commits or rollbacks. > 4) A table with other objects on it (foreign keys, check constraints, etc.)? Same as above. If they try to do conflicting things, they don't continue operating concurrently. Cheers, Jeff
On 26 May 2012 01:36, Jeff Janes <jeff.janes@gmail.com> wrote: > On Fri, May 25, 2012 at 3:04 PM, Ivan Voras <ivoras@freebsd.org> wrote: >> Hello, >> >> I'm wondering if there is ia document describing which guarantees (if >> any) PostgreSQL makes about concurrency for various operations? Speaking >> in general (i.e. IO can handle it, number of CPU cores and client >> threads is optimal), are fully concurrent operations (independant and >> non-blocking) possible for: > > By "fully concurrent" do you mean that there is no detectable > sub-linear scaling at all? I'm pretty sure that no such guarantees > can be made. > For concurrent bulk loads, there was severe contention on generating > WAL between concurrent bulk loaders. That is greatly improved in the > upcoming 9.2 release. I was thinking about major exclusive locks in the code paths which would block multiple clients operating on unrelated data (the same questions go for update operations). For example: if the free space map or whole index trees are exclusively locked, things like that. The WAL issue you mention seems exactly like what I was asking about, but are there any others?