PostgreSQL Data Types – OID vs BYTEA in PostgreSQL 9.1

I managed to resolve this issue as follow (2nd considered solution - revisited):

All in a while cicle, for every file:

• select file_id , file_version, oid and put them in 3 variables
• perform a [lo_export] of pg_largeobjects that require oid a path+filename to be defined. The filename will be the concatenation of the file_id and file_version.
• during lo_import new OID will be generated. But using the filename defined above, I can perform the last stem:
• update all the rows in the files table in order to make them point to the new imported pg_largeobject records and mantain the relation consistecy

Not really enough information to give a good recommendation.

A few hundred queries a day is not much at all for Postgres, generally speaking.

As for queries, keep in mind that Postgres caches data in memory. As mentioned, do some study to learn about this caching and how to tune settings for Postgres. By default the settings are quite conservative, to favor erring in the side of safety and not overburdening the host server machine.

Adding a row every second means 86,400 per day, and 31.5 million per year. That is a serious number of rows, but many people handle such data with Postgres.

The key to fast queries here is in the indexing (assuming the RAM buffers issue is tuned). You've not described enough detail to help. If you are simply indexing a TIMESTAMP WITH TIME ZONE column, you may have no performance issues at all. Remember that with suitable hardware, tuned Postgres settings, and appropriate indexing, both your data and the relevant portions of your index will all be cached in RAM and very fast.

If you do decide to cache data in a redundant table, know that you can turn off the write-ahead log (WAL) on that table for faster performance as you do not care about its integrity.

Try some tests, running on hardware similar to your production system. Load up fake data to simulate real loads. Verify behavior with the EXPLAIN feature to make sure your queries act as you expect.

Another approach for performance is to have a background process perform the query and generate results every so many seconds (whatever your users’ tolerance for staleness of results). When user request arrives, your app uses the already determined result rather than performing a fresh query. This increases the burden on your database. And most of those queries are for nought given you have so few user requests per day. But the benefit is fastest possible response to user request. For Java, see ScheduledExecutorService.

Another option is to cache the data in your app rather than in the database if it easily fits in RAM. Java for example offers many kinds of collections and queues for such caching. This especially makes sense if the app gathering the data is also the app responding to the hundreds of requests per day. Use the database as history, but keep the recent data in memory. Keep three caches, one for each of your recent 1 minute, 5 minutes, and 15 minutes, so no queries needed at all, data ready to go at all times.