Re: Is PostgreSQL for this?

2006/12/27, lopezf(at)adinet(dot)com(dot)uy <lopezf(at)adinet(dot)com(dot)uy>:
> Hi everybody,
> I'm looking for a database system for a SCADA system. The major
> probles I think it's on performance because the application it's going
> to poll about 4k variables per second from hardware and has to register
> the values on the procces table.
> I heard that PostgreSQL provides a bulk loading mechanism called COPY,
> which takes tab-delimited or CSV input from a file. Where COPY can be
> used instead of hundreds or thousands of INSERTS, it can cut execution
> time.
> I'm less than a novice so I'll thank any piece of advice.

I believe you could easily simulate the load in a small
fake-SCADA-program and see how the hardware at your disposal handles
it with postgresql, a different RDBMS or simply a flat file. Make a
small program which will generate a set of 4k random values and send
them asynchronously over the network to your data acquisition
application which should store the data in the database. Measure how
fast you can send the data and still record everything.

If data acquisition speed is your primary concern (as it seems to be),
you might want to use a simple .csv file: you'll probably beat the
performance of any database management system. You could periodically
move the saved data from the .csv files into a database (say,
postgresql) where you could (I assume) analyze it. You might want to
use a separate machine for the database management system so as to
remove any unnecessary CPU and I/O disturbances from the primary data
storage machine.
I don't think your load (32 kBps if your variables are double
precision float values) is a challenge, but running any kind of
analysis on a basically real-time-response-constrained machine might
cost you data losses and I don't know if you can afford those.

Cheers,
t.n.a.