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:

  • Objects
  • 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..)

  • 2
    I highly recommend you run benchmarks before you attempt a "non-standard" configuration. Usually, I find "normal" to be "fast enough". – Michael Kutz Jan 1 at 12:49
  • @MichaelKutz that's difficult, since I wouldn't have much control over the end user configuration. i.e. they may have 5400RPM magnetic hard drives or other low performing hardware. Regardless of the hardware performance, I'd like to have the system perform as well as possible for the hardware. Splitting storage IOPS across multiple drives seems like it should be quite common for databases. – BevanWeiss Jan 1 at 15:18
  • Pretty much all database servers use storage systems that are striped and mirrored, where the striping and mirroring is handled by the storage system itself. Trying to balance an IO load by manually distributing data over multiple spindles is very much 20th century. – Albert Godfrind Jan 1 at 15:57
  • 1
    "Splitting storage IOPS across multiple drives" is done through RAID. "Perform as well as possible" will require the HW owner to meet a certain level of IOPS requirements (which SW vendor provides based on benchmarks). From what I've seen, "Split across disks" is done for data management purposes like Information Lifecycle Management (ILM), not IOPS performance. – Michael Kutz Jan 1 at 16:03
  • @AlbertGodfrind, I don't believe that 'RAID' is the answer here. Within the database there are varying 'types' of data, some of which is critical and must be reliable at all cost (config), other data is less important (historical data even less so) but has greater performance requirements. The answer simply can't be RAID to the highest reliability requirement AND to the highest performance requirement. That is wasteful and would not be acceptable from a cost perspective. – BevanWeiss Jan 2 at 0:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.