Table Observations partitioning?

[ndevilleBE]

Dear,
I implemented the STA with a PostgreSQL database for our data which are en-route physico-chemical measurements of the Research vessel Belgica.
I have a large pool of historical data already imported in the db (100Gb). You can access the end-point here:
https://sensors.naturalsciences.be/sta/v1.1/

My goal is to automatically import the new measurement of the actual RV Belgica in the STA to allow clients a seamless access to all the data. BUT as the table is already large, importing using http POST operations becomes very slow. So I'm looking for some optimisations.
An idea would be to partition the Observation table, one partition for the historical data (not to be modified in the future) and one partition for the future data.
Is that a possibility? Will it creates issues with the STA API? Also, will it speeds up the different triggers which are partly responsible for the slow importing (such as datastreams min/max phenomenonTime)?
Another question is regarding the pooling settings. It is written in the documentation that pooling is possible but is it activated by default or are there some parameters to modify in the different configuration files of the application (and of tomcat)?

Thanks a lot for your answer, and please if you have some suggestions to keep the system fast with a large amount of data it would be most welcome!
Best regards,
Nicolas de VIlle

[hylkevds]

Partitioning the table is certainly possible. I don't expect it to make much difference when it comes to the write-speed, but it will depend a bit on what the bottleneck is when importing your data.
Partitioning helps by distributing read/write load on different physical disks, and possibly reducing the size of indexes required for queries, but I doubt those are a problem in your case.

I suspect a substantial increase in efficiency can be had by using batch processing for the import, reducing the many separate POST actions required.

The most important limitation for PostgreSQL is the speed of the disks. Network disks are usually very slow. Using SSDs close to the PostgreSQL server I can usually POST around 5000 Observations per second.

In your specific use-case it might also be possible to get a good improvement by merging the FoI and Location tables, since I suspect the data in those two is mostly the same.