I'm just looking for some design options around Postgresql.
I'm looking at using Postgresql as the backend for a SCADA system that I'd like to develop, this essentially means that it would have a combination of both OLTP loads (for instantaneous state / config of system) as well as quite a lot of OLAP involving time series data.
One particular item that I'd be worried about is performance, and in particular around disk I/O performance.
I see that Postgresql allows for Tablespaces to be used to put tables on different storage volumes, is there any way to do this with particular columns of data also? My current schema approach would entail the following (exposed) tables:
- Historical Data
- Historical Events
For the Historical Data and Historical Events I'd ideally like to able to partition these across a number of different storage volumes, but not in time order, and instead perhaps by a hashing partition or similar. I think this wouldn't be a problem, although it appears that it would require the use of Inherited tables (i.e. the Historical Data / Events being hidden Parent tables, real tables inheriting from these along some hash of the ObjectId FK or similar).
It's the Objects table that I'm most unsure about. Each row would consist of something like:
- ObjectId (Unique, Primary Key)
- Security (ACL etc)
- Data (JSON(B) format)
- Config (JSON(B) format)
What I'd really like to be able to do is split out the Data column so that it's stored elsewhere. It will be write heavy, and I would prefer not to have that same disk I/O impacting on the Object Config.
Is there any nice way to do this in Postgresql? (or should I be normalising the schema to have Data into a separate table? just with FK against ObjectId..)