Quoting [EMAIL PROTECTED]: > > 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.
I would wrap the "bursts" in a transaction if you can (begin; and commit; statements) > > 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. > I do a simular thing in my application, what I do is to snapshot (copy) the database (A sqlite database is a single file) and then run my batch process against the copy. > The expansion of data will force me to go from a maximum 3400 row table to > a maximum of 11560. My tables are a simular size > > From what I gather in reading about SQLite, it seems to be better equipped > for performance. All my testing of the current system points to postgres > (postmaster) being my bottleneck. > > Jason Alburger > HID/NAS/LAN Engineer > L3/ATO-E En Route Peripheral Systems Support > 609-485-7225 > > > > [EMAIL PROTECTED] > > > 03/01/2006 09:54 To > AM sqlite-users@sqlite.org > cc > > Please respond to Subject > [EMAIL PROTECTED] Re: [sqlite] performance statistics > te.org > > > > > > > > > > [EMAIL PROTECTED] wrote: > > > > I am currently investigating porting my project from postgres to SQLite > due > > to anticipated performance issues > > > > I do not thing speed should really be the prime consideration > here. PostgreSQL and SQLite solve very different problems. > I think you should choose the system that is the best map to > the problem you are trying to solve. > > PostgreSQL is designed to support a large number of clients > distributed across multiple machines and accessing a relatively > large data store that is in a fixed location. PostgreSQL is > designed to replace Oracle. > > SQLite is designed to support a smaller number of clients > all located on the same host computer and accessing a portable > data store of only a few dozen gigabytes which is eaily copied > or moved. SQLite is designed to replace fopen(). > > Both SQLite and PostgreSQL can be used to solve problems outside > their primary focus. And so a high-end use of SQLite will > certainly overlap a low-end use of PostgreSQL. But you will > be happiest if you will use them both for what they were > originally designed for. > > If you give us some more clues about what your requirements > are we can give you better guidance about which database might > be the best choice. > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > >