pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedmassive INSERT
2+ messages / 2 participants
[nested] [flat]
* massive INSERT
@ 2003-03-13 17:45 Kurt Overberg <kurt@hotdogrecords.com>
2003-03-14 00:20 ` Re: massive INSERT Greg Stark <gsstark@mit.edu>
0 siblings, 1 reply; 2+ messages in thread
From: Kurt Overberg @ 2003-03-13 17:45 UTC (permalink / raw)
To: pgsql-sql
Hi everyone!
I'm trying to maintain a 'membership' table in my postgres database.
Membership is determined by a potentially costly algorithm that
basically returns a series of member ids, where I insert those ids into
my membership table (called groups). Up to now, for speed purposes,
I've been deleting all users in a group, then re-building the group from
scratch. The tables look roughly like this:
id | integer | not null default
nextval('"xrefmgrp_id_seq"'::text)
membergroupid | integer | not null default 0
memberid | integer | not null default 0
There's a constraint on the table saying that (membergroupid,memberid)
needs to be UNIQUE.
...so before re-building a table, I do a:
delete from xrefmembergroup where membergroupid = 4 ;
...then blast the id's into the table:
insert into xrefmembergroup (membergroupid, memberid) select 4 as
membergroupid, member.id as memberid from member where <* huge complex
select*>
...I've found this to be faster then running the query, figuring out who
needs to be removed from the group, who needs to be added and whatnot.
The thing that I'm worried about is that this table is going to be
pretty big (potentially millions of rows), and everytime I rebuild this
table I lose (or at least invalidate) all my indexes. Is that the case?
Is constantly deleting then adding large numbers of rows from a table
really bad for performance? I'm worried this isn't going to scale well.
Anyone know of a better way to do this? Thoughts and comments would
be appreciated.
Thanks!
/kurt
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: massive INSERT
2003-03-13 17:45 massive INSERT Kurt Overberg <kurt@hotdogrecords.com>
@ 2003-03-14 00:20 ` Greg Stark <gsstark@mit.edu>
0 siblings, 0 replies; 2+ messages in thread
From: Greg Stark @ 2003-03-14 00:20 UTC (permalink / raw)
To: Kurt Overberg <kurt@hotdogrecords.com>; +Cc: pgsql-sql
Kurt Overberg <kurt@hotdogrecords.com> writes:
> ...I've found this to be faster then running the query, figuring out who needs
> to be removed from the group, who needs to be added and whatnot. The thing that
> I'm worried about is that this table is going to be pretty big (potentially
> millions of rows), and everytime I rebuild this table I lose (or at least
> invalidate) all my indexes. Is that the case? Is constantly deleting then
> adding large numbers of rows from a table really bad for performance? I'm
> worried this isn't going to scale well. Anyone know of a better way to do
> this? Thoughts and comments would be appreciated.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2003-03-14 00:20 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-13 17:45 massive INSERT Kurt Overberg <kurt@hotdogrecords.com>
2003-03-14 00:20 ` Greg Stark <gsstark@mit.edu>
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