pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedLarge journal as psql table. Good idea? Triggering.
3+ messages / 3 participants
[nested] [flat]
* Large journal as psql table. Good idea? Triggering.
@ 2007-04-23 23:12 Bryce Nesbitt <bryce1@obviously.com>
0 siblings, 1 reply; 3+ messages in thread
From: Bryce Nesbitt @ 2007-04-23 23:12 UTC (permalink / raw)
To: pgsql-sql
Dear Postgres folks;
I'm considering using a postgres table for something that could be done
with a flat file. Is this a good idea?
I have events on a machine "A", which need to be sent by an SMS/Cell
Phone modem that's on a totally different machine "B". Potentially this
is a job for a flat file FIFO.
But I'm thinking that maybe it's a job for a database table. Each new
row would be written with a status (10="new"). And that the modem
process would poll for new rows. Problem is there will be lots of rows,
but only a trivial few will be "new". The huge index file and the
polling seem like a drag on the database, unless there is a way to optimize.
On the good side, this scheme handles building the FIFO and the IPC
(interprocess communication). Is is a good approach, using postgres as
a glorified FIFO? Should the DB on machine "A" or "B"? Is there a way
to trigger or lower the polling overhead (in this application the delay
is user visible). The volume is low (a few messages a minute tops).
Bryce
^ permalink raw reply [nested|flat] 3+ messages in thread
* Large journal as psql table. Good idea? Triggering.
@ 2007-04-23 23:27 Phillip Smith <phillip.smith@weatherbeeta.com.au>
0 siblings, 0 replies; 3+ messages in thread
From: Phillip Smith @ 2007-04-23 23:27 UTC (permalink / raw)
To: pgsql-sql
If it's going to be too big for a database, then it's going to be worse
using flat-files on a disk :)
I'd suggest putting it in a database, and have 2 tables:
1) "New" messages to be sent
2) Archive messages
That way the polling machine only has to wait for the database to scan the
"New" table, then move them in to the Archive table once it has sent them.
Depending how many records you're actually looking at, one table with an
index would probably be fine :)
~p
-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Bryce Nesbitt
Sent: Tuesday, 24 April 2007 09:12
I'm considering using a postgres table for something that could be done
with a flat file. Is this a good idea?
I have events on a machine "A", which need to be sent by an SMS/Cell
Phone modem that's on a totally different machine "B". Potentially this
is a job for a flat file FIFO.
But I'm thinking that maybe it's a job for a database table. Each new
row would be written with a status (10="new"). And that the modem
process would poll for new rows. Problem is there will be lots of rows,
but only a trivial few will be "new". The huge index file and the
polling seem like a drag on the database, unless there is a way to optimize.
*******************Confidentiality and Privilege Notice*******************
The material contained in this message is privileged and confidential to
the addressee. If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.
Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Large journal as psql table. Good idea? Triggering.
@ 2007-04-24 00:56 chester c young <chestercyoung@yahoo.com>
parent: Bryce Nesbitt <bryce1@obviously.com>
0 siblings, 0 replies; 3+ messages in thread
From: chester c young @ 2007-04-24 00:56 UTC (permalink / raw)
To: Bryce Nesbitt <bryce1@obviously.com>; +Cc: pgsql-sql
> But I'm thinking that maybe it's a job for a database table. Each
> new
> row would be written with a status (10="new"). And that the modem
> process would poll for new rows. Problem is there will be lots of
> rows,
> but only a trivial few will be "new". The huge index file and the
> polling seem like a drag on the database, unless there is a way to
> optimize.
create index <name> on <table>( <id> ) where status = 'new';
this index will be very fast.
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-04-24 00:56 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-04-23 23:12 Large journal as psql table. Good idea? Triggering. Bryce Nesbitt <bryce1@obviously.com>
2007-04-24 00:56 ` chester c young <chestercyoung@yahoo.com>
2007-04-23 23:27 Large journal as psql table. Good idea? Triggering. Phillip Smith <phillip.smith@weatherbeeta.com.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