pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Parallel (concurrent) inserts? 4+ messages / 3 participants [nested] [flat]
* Parallel (concurrent) inserts? @ 2012-05-25 22:04 Ivan Voras <ivoras@freebsd.org> 0 siblings, 2 replies; 4+ messages in thread From: Ivan Voras @ 2012-05-25 22:04 UTC (permalink / raw) To: pgsql-performance 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.)? ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Parallel (concurrent) inserts? @ 2012-05-25 23:13 Josh Berkus <josh@agliodbs.com> parent: Ivan Voras <ivoras@freebsd.org> 1 sibling, 0 replies; 4+ messages in thread From: Josh Berkus @ 2012-05-25 23:13 UTC (permalink / raw) To: pgsql-performance > 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 ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Parallel (concurrent) inserts? @ 2012-05-25 23:36 Jeff Janes <jeff.janes@gmail.com> parent: Ivan Voras <ivoras@freebsd.org> 1 sibling, 1 reply; 4+ messages in thread From: Jeff Janes @ 2012-05-25 23:36 UTC (permalink / raw) To: Ivan Voras <ivoras@freebsd.org>; +Cc: pgsql-performance 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 ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Parallel (concurrent) inserts? @ 2012-05-25 23:56 Ivan Voras <ivoras@freebsd.org> parent: Jeff Janes <jeff.janes@gmail.com> 0 siblings, 0 replies; 4+ messages in thread From: Ivan Voras @ 2012-05-25 23:56 UTC (permalink / raw) To: Jeff Janes <jeff.janes@gmail.com>; +Cc: pgsql-performance 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? ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2012-05-25 23:56 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2012-05-25 22:04 Parallel (concurrent) inserts? Ivan Voras <ivoras@freebsd.org> 2012-05-25 23:13 ` Josh Berkus <josh@agliodbs.com> 2012-05-25 23:36 ` Jeff Janes <jeff.janes@gmail.com> 2012-05-25 23:56 ` Ivan Voras <ivoras@freebsd.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox