[EMAIL PROTECTED] wrote: > well....The database and the applications accessing the database are all > located on the same machine, so distribution across multiple machines > doesn't apply here. The system is designed so that only one application > handles all the writes to the DB. Another application handles all the > reads, and there may be up to two instances of that application running at > any one time, so I guess that shows a small number of clients. When the > application that reads the DB data starts, it reads *all* the data in the > DB and ships it elsewhere.
I think either SQLite or PostgreSQL would be appropriate here. I'm guessing that SQLite will have the speed advantage in this particular case if you are careful in how you code it up. > > I anticipate 2 bottlenecks... > > 1. My anticipated bottleneck under postgres is that the DB-writing app. > must parse incoming bursts of data and store in the DB. The machine > sending this data is seeing a delay in processing. Debugging has shown > that the INSERTS (on the order of a few thousand) is where most of the time > is wasted. You will do well to gather your incoming data into a TEMP table then insert the whole wad into the main database all in one go using something like this: INSERT INTO maintable SELECT * FROM temptable; DELETE FROM temptable; Actually, this same trick might solve your postgresql performance problem and thus obviate the need to port your code. > > 2. The other bottleneck is data retrieval. My DB-reading application must > read the DB record-by-record (opens a cursor and reads one-by-one), build > the data into a message according to a system ICD, and ship it out. > postgres (postmaster) CPU usage is hovering around 85 - 90% at this time. > > The expansion of data will force me to go from a maximum 3400 row table to > a maximum of 11560. Unless each row is particularly large, this is not a very big database and should not present a problem to either SQLite or PostgreSQL. Unless you are doing some kind of strange join that you haven't told us about. If your data formatting takes a long time, the reader might block the writer in SQLite. The writer process will have to wait to do its write until the reader has finished. You can avoid this by making a copy of the data to be read into a temporary table before formatting it: CREATE TEMP TABLE outbuf AS SELECT * FROM maintable; SELECT * FROM outbuf; -- Do your formatting and sending DROP TABLE outbuf; Since PostgreSQL supports READ COMMITTED isolation by default, the writer lock will not be a problem there. But you will have the same issue on PosgreSQL if you select SERIALIZABLE isolation. SQLite only does SERIALIZABLE for database connections running in separate processes. -- D. Richard Hipp <[EMAIL PROTECTED]>