pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedDispatch-Merge pattern
2+ messages / 2 participants
[nested] [flat]
* Dispatch-Merge pattern
@ 2007-03-13 13:36 James Riordan <james.riordan@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: James Riordan @ 2007-03-13 13:36 UTC (permalink / raw)
To: pgsql-performance
Howdy-
I am currently using PostgreSQL to store and process a high-bandwidth
event stream. I do not need old events but the delete and vacuum does
not terminate due to the large number of events being inserted (it
just pushes me over the tipping point of where the machine can keep up
with the events).
I ended up implementing a scheme where a trigger is used to redirect
the events (round robin based on time) to a series of identically
structured tables. I can then use TRUNCATE older tables rather than
DELETE and VACUUM (which is a significant speed up).
It worked out pretty well so thought post the idea to find out if
- it is stupid way of doing things and there is a correct database
abstraction for doing this
or
- it is a reasonable way of solving this problem and might be of use
to other folks using rdbs as event processing
I then use a view to merge the tables. Obviously update would be a
problem for my purposes, and I suppose a lot of event processing, it
isn't an issue.
Either way, details are at:
http://unsyntax.net/james/blog/tools+and+programming/2007/03/08/Dispatch-Merge-Database-Pattern
Cheers,
James
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Dispatch-Merge pattern
@ 2007-03-15 15:58 Steve Atkins <steve@blighty.com>
parent: James Riordan <james.riordan@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Steve Atkins @ 2007-03-15 15:58 UTC (permalink / raw)
To: pgsql-performance
On Mar 13, 2007, at 6:36 AM, James Riordan wrote:
> Howdy-
>
> I am currently using PostgreSQL to store and process a high-bandwidth
> event stream. I do not need old events but the delete and vacuum does
> not terminate due to the large number of events being inserted (it
> just pushes me over the tipping point of where the machine can keep up
> with the events).
>
> I ended up implementing a scheme where a trigger is used to redirect
> the events (round robin based on time) to a series of identically
> structured tables. I can then use TRUNCATE older tables rather than
> DELETE and VACUUM (which is a significant speed up).
>
> It worked out pretty well so thought post the idea to find out if
>
> - it is stupid way of doing things and there is a correct database
> abstraction for doing this
>
> or
>
> - it is a reasonable way of solving this problem and might be of use
> to other folks using rdbs as event processing
>
> I then use a view to merge the tables. Obviously update would be a
> problem for my purposes, and I suppose a lot of event processing, it
> isn't an issue.
>
> Either way, details are at:
> http://unsyntax.net/james/blog/tools+and+programming/2007/03/08/
> Dispatch-Merge-Database-Pattern
I can't reach that URL, but from what you say it sounds like you've
re-invented table partitioning.
http://www.postgresql.org/docs/8.2/static/ddl-partitioning.html
If you do it the postgresql way you can also take advantage of
constraint exclusion, to speed up some selects on the set of
partitioned tables, and inheritance means you don't have to
maintain the union view yourself.
Cheers,
Steve
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2007-03-15 15:58 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-03-13 13:36 Dispatch-Merge pattern James Riordan <james.riordan@gmail.com>
2007-03-15 15:58 ` Steve Atkins <steve@blighty.com>
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