pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgres INSERT performance and scalability
8+ messages / 5 participants
[nested] [flat]
* Postgres INSERT performance and scalability
@ 2011-09-19 22:11 Igor Chudov <ichudov@gmail.com>
2011-09-19 23:32 ` Re: Postgres INSERT performance and scalability Scott Marlowe <scott.marlowe@gmail.com>
2011-09-20 00:53 ` Re: Postgres INSERT performance and scalability Stephen Frost <sfrost@snowman.net>
0 siblings, 2 replies; 8+ messages in thread
From: Igor Chudov @ 2011-09-19 22:11 UTC (permalink / raw)
To: pgsql-performance
Let's say that I want to do INSERT SELECT of 1,000 items into a table. The
table has some ints, varchars, TEXT and BLOB fields.
Would the time that it takes, differ a great deal, depending on whether the
table has only 100,000 or 5,000,000 records?
Thanks
i
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Postgres INSERT performance and scalability
2011-09-19 22:11 Postgres INSERT performance and scalability Igor Chudov <ichudov@gmail.com>
@ 2011-09-19 23:32 ` Scott Marlowe <scott.marlowe@gmail.com>
2011-09-20 01:11 ` Re: Postgres INSERT performance and scalability Igor Chudov <ichudov@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Scott Marlowe @ 2011-09-19 23:32 UTC (permalink / raw)
To: Igor Chudov <ichudov@gmail.com>; +Cc: pgsql-performance
On Mon, Sep 19, 2011 at 4:11 PM, Igor Chudov <ichudov@gmail.com> wrote:
> Let's say that I want to do INSERT SELECT of 1,000 items into a table. The
> table has some ints, varchars, TEXT and BLOB fields.
> Would the time that it takes, differ a great deal, depending on whether the
> table has only 100,000 or 5,000,000 records?
Depends. Got any indexes? The more indexes you have to update the
slower it's gonna be. You can test this, it's easy to create test
rows like so:
insert into test select generate_series(1,10000);
etc. There's lots of examples floating around on how to do that. So,
make yourself a couple of tables and test it.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Postgres INSERT performance and scalability
2011-09-19 22:11 Postgres INSERT performance and scalability Igor Chudov <ichudov@gmail.com>
2011-09-19 23:32 ` Re: Postgres INSERT performance and scalability Scott Marlowe <scott.marlowe@gmail.com>
@ 2011-09-20 01:11 ` Igor Chudov <ichudov@gmail.com>
2011-09-20 01:15 ` Re: Postgres INSERT performance and scalability Stephen Frost <sfrost@snowman.net>
0 siblings, 1 reply; 8+ messages in thread
From: Igor Chudov @ 2011-09-20 01:11 UTC (permalink / raw)
To: pgsql-performance
On Mon, Sep 19, 2011 at 6:32 PM, Scott Marlowe <scott.marlowe@gmail.com>wrote:
> On Mon, Sep 19, 2011 at 4:11 PM, Igor Chudov <ichudov@gmail.com> wrote:
> > Let's say that I want to do INSERT SELECT of 1,000 items into a table.
> The
> > table has some ints, varchars, TEXT and BLOB fields.
> > Would the time that it takes, differ a great deal, depending on whether
> the
> > table has only 100,000 or 5,000,000 records?
>
> Depends. Got any indexes? The more indexes you have to update the
> slower it's gonna be. You can test this, it's easy to create test
> rows like so:
>
> insert into test select generate_series(1,10000);
>
> etc. There's lots of examples floating around on how to do that. So,
> make yourself a couple of tables and test it.
>
Well, my question is, rather, whether the time to do a bulk INSERT of N
records into a large table, would take substantially longer than a bulk
insert of N records into a small table. In other words, does the populating
time grow as the table gets more and more rows?
i
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Postgres INSERT performance and scalability
2011-09-19 22:11 Postgres INSERT performance and scalability Igor Chudov <ichudov@gmail.com>
2011-09-19 23:32 ` Re: Postgres INSERT performance and scalability Scott Marlowe <scott.marlowe@gmail.com>
2011-09-20 01:11 ` Re: Postgres INSERT performance and scalability Igor Chudov <ichudov@gmail.com>
@ 2011-09-20 01:15 ` Stephen Frost <sfrost@snowman.net>
0 siblings, 0 replies; 8+ messages in thread
From: Stephen Frost @ 2011-09-20 01:15 UTC (permalink / raw)
To: Igor Chudov <ichudov@gmail.com>; +Cc: pgsql-performance
* Igor Chudov (ichudov@gmail.com) wrote:
> Well, my question is, rather, whether the time to do a bulk INSERT of N
> records into a large table, would take substantially longer than a bulk
> insert of N records into a small table. In other words, does the populating
> time grow as the table gets more and more rows?
Oh, in that regard, the answer would generally be 'no'. PostgreSQL
maintains a table known as the 'free space map', where it keeps track of
where there is 'free space' to insert data into a table. As someone
else mentioned, if there's a lot of indexes then it's possible that the
increased depth in the index due to the larger number of tuples might
mean the larger table is slower, but I don't think it'd make a huge
difference, to be honest...
Are you seeing that behavior? There's nothing like testing it to see
exactly what happens, of course..
Thanks,
Stephen
Attachments:
signature.asc (application/pgp-signature, 197B) download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Postgres INSERT performance and scalability
2011-09-19 22:11 Postgres INSERT performance and scalability Igor Chudov <ichudov@gmail.com>
@ 2011-09-20 00:53 ` Stephen Frost <sfrost@snowman.net>
2011-09-20 01:21 ` Re: Postgres INSERT performance and scalability Jon Nelson <jnelson+pgsql@jamponi.net>
1 sibling, 1 reply; 8+ messages in thread
From: Stephen Frost @ 2011-09-20 00:53 UTC (permalink / raw)
To: Igor Chudov <ichudov@gmail.com>; +Cc: pgsql-performance
Igor,
* Igor Chudov (ichudov@gmail.com) wrote:
> Would the time that it takes, differ a great deal, depending on whether the
> table has only 100,000 or 5,000,000 records?
Yes, because PostgreSQL is going to copy the data. If you don't need or
want it to be copied, just use a view. I've never heard of any
relational database implementing 'copy on write' type semantics, if
that's what you're asking about. Databases, unlike applications with
code in memory that's constantly copied, are typically focused around
minimizing duplication of data (since it all has to end up on disk at
some point). Not much point in having the overhead of COW for that kind
of environment, I wouldn't think.
Thanks,
Stephen
Attachments:
signature.asc (application/pgp-signature, 197B) download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Postgres INSERT performance and scalability
2011-09-19 22:11 Postgres INSERT performance and scalability Igor Chudov <ichudov@gmail.com>
2011-09-20 00:53 ` Re: Postgres INSERT performance and scalability Stephen Frost <sfrost@snowman.net>
@ 2011-09-20 01:21 ` Jon Nelson <jnelson+pgsql@jamponi.net>
2011-09-20 01:32 ` Re: Postgres INSERT performance and scalability Stephen Frost <sfrost@snowman.net>
2011-09-20 05:04 ` Re: Postgres INSERT performance and scalability Craig Ringer <ringerc@ringerc.id.au>
0 siblings, 2 replies; 8+ messages in thread
From: Jon Nelson @ 2011-09-20 01:21 UTC (permalink / raw)
To: ; +Cc: pgsql-performance
On Mon, Sep 19, 2011 at 7:53 PM, Stephen Frost <sfrost@snowman.net> wrote:
> Igor,
>
> * Igor Chudov (ichudov@gmail.com) wrote:
>> Would the time that it takes, differ a great deal, depending on whether the
>> table has only 100,000 or 5,000,000 records?
>
> Yes, because PostgreSQL is going to copy the data. If you don't need or
> want it to be copied, just use a view. I've never heard of any
> relational database implementing 'copy on write' type semantics, if
> that's what you're asking about. Databases, unlike applications with
> code in memory that's constantly copied, are typically focused around
> minimizing duplication of data (since it all has to end up on disk at
> some point). Not much point in having the overhead of COW for that kind
> of environment, I wouldn't think.
Isn't the WAL basically COW?
--
Jon
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Postgres INSERT performance and scalability
2011-09-19 22:11 Postgres INSERT performance and scalability Igor Chudov <ichudov@gmail.com>
2011-09-20 00:53 ` Re: Postgres INSERT performance and scalability Stephen Frost <sfrost@snowman.net>
2011-09-20 01:21 ` Re: Postgres INSERT performance and scalability Jon Nelson <jnelson+pgsql@jamponi.net>
@ 2011-09-20 01:32 ` Stephen Frost <sfrost@snowman.net>
1 sibling, 0 replies; 8+ messages in thread
From: Stephen Frost @ 2011-09-20 01:32 UTC (permalink / raw)
To: Jon Nelson <jnelson+pgsql@jamponi.net>; +Cc: pgsql-performance
* Jon Nelson (jnelson+pgsql@jamponi.net) wrote:
> Isn't the WAL basically COW?
eh..? No.. The WAL is used to record what changes are made to the
various files in the database, it certainly isn't an kind of
"copy-on-write" system, where we wait until a change is made to data
before copying it..
If you INSERT .. SELECT, you're going to get the real data in the WAL,
and also in the heap of the new table..
Thanks,
Stephen
Attachments:
signature.asc (application/pgp-signature, 197B) download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Postgres INSERT performance and scalability
2011-09-19 22:11 Postgres INSERT performance and scalability Igor Chudov <ichudov@gmail.com>
2011-09-20 00:53 ` Re: Postgres INSERT performance and scalability Stephen Frost <sfrost@snowman.net>
2011-09-20 01:21 ` Re: Postgres INSERT performance and scalability Jon Nelson <jnelson+pgsql@jamponi.net>
@ 2011-09-20 05:04 ` Craig Ringer <ringerc@ringerc.id.au>
1 sibling, 0 replies; 8+ messages in thread
From: Craig Ringer @ 2011-09-20 05:04 UTC (permalink / raw)
To: Jon Nelson <jnelson+pgsql@jamponi.net>; +Cc: pgsql-performance
On 09/20/2011 09:21 AM, Jon Nelson wrote:
> Isn't the WAL basically COW?
Nope, it's a lot more like a filesystem journal - though it includes all
data, not just metadata like filesystem journals usually do.
Now, you could argue that PostgreSQL uses a copy-on-write like system to
maintain row versions so that already-running statements (or
SERIALIZABLE transactions) don't see data from the future and to
maintain rollback data for uncommitted transactions. It's the *new* data
that gets written to the WAL, though, not the old data.
(OK, if you have full_page_writes enabled you might get a mix of old and
new data written to WAL, but that's an implementation detail).
--
Craig Ringer
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2011-09-20 05:04 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-09-19 22:11 Postgres INSERT performance and scalability Igor Chudov <ichudov@gmail.com>
2011-09-19 23:32 ` Scott Marlowe <scott.marlowe@gmail.com>
2011-09-20 01:11 ` Igor Chudov <ichudov@gmail.com>
2011-09-20 01:15 ` Stephen Frost <sfrost@snowman.net>
2011-09-20 00:53 ` Stephen Frost <sfrost@snowman.net>
2011-09-20 01:21 ` Jon Nelson <jnelson+pgsql@jamponi.net>
2011-09-20 01:32 ` Stephen Frost <sfrost@snowman.net>
2011-09-20 05:04 ` Craig Ringer <ringerc@ringerc.id.au>
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