5

My broader question here has to do with when it is appropriate to use HStore vs. multiple tables vs. one table for storing document-like objects.

Now, for my localized example: I’m designing a database structure for holding experimental results. My first idea was to have separate tables for each experiment. This would look like:

Multiple tables

However, this would require a lot of JOINs across tables since it would be fairly common for end-users to want to look for data across multiple experiments. (Side note: maybe a good use case for inheritance?). I thought I could avoid this by having a monolithic experiments table like this:

Monolithic table

That would certainly eliminate the need to have JOINs but I feel like it would be logical to have individual experiments be separate in some way. Also, it will be very common that one experiment is requested in its entirety. This is why I’m considering using HStore kind of like this:

HStore

This appeals to me because each individual result set is very document-like, but I’m afraid that I’ll be running into the same issues that the multiple tables approach would bring.

Other considerations and thoughts that may be relevant:

  • I’ve thought about having additional tables for storing meta-data/annotations relating to individual data points in individual experiments. Some of this would fit very nicely into the HStore experiments table.
  • Different users will be inputting experimental data, I would use schema for this
  • There will be different experiment types – I think the best way to handle this would be storing these in entirely different databases

I should also mention that I have very limited experience with PostgreSQL, but I’m already delighted at what it has to offer over MySQL!

Improve this question
2
  • Do all experiments share the same attributes? (foo, bar in your example). If yes, then the second table is the best way to go. hstore is only useful if you have different attributes in each row of a table.
    – a_horse_with_no_name
    May 22, 2014 at 8:42
  • All experiments of one type share the same attributes now.
    – Radu
    May 22, 2014 at 21:54

1 Answer 1

Reset to default
1

That would certainly eliminate the need to have JOINs but I feel like it would be logical to have individual experiments be separate in some way. Also, it will be very common that one experiment is requested in its entirety

Both of those are good arguments for using table partitioning. PostgreSQL implements this with inheritance and constraint exclusion.

I’ve thought about having additional tables for storing meta-data/annotations relating to individual data points in individual experiments. Some of this would fit very nicely into the HStore experiments table

Nothing stops you having an hstore field for annotations, or a separate join table of hstore fields for them.

Different users will be inputting experimental data, I would use schema for this

Why? Surely the same argument applies, that you'll want to do cross-user aggregation too?

I'd just partition by experiment and have the user ID as part of the table's key.

There will be different experiment types – I think the best way to handle this would be storing these in entirely different databases

Why?

Do you ever think you might need to query across them or aggregate them? If so, do not store them in different DBs.

If they store similar kinds of data, just have them in the main table, and have some optional columns.

If they store mostly different kinds of data (mostly or entirely different columns) then use different tables in the same DB.

Improve this answer
4
  • First off, thank you for the point by point answer. So I take it you would advise not going the hstore route? It does seem like inheritance and constraint exclusion would provide a pretty elegant solution but I'm not sure I have a field to partition on so neatly. I think a trigger may be appropriate to make things simpler on the application side. You're right about the schema for different users. I guess my issue here is I tend to want to separate things out more than is necessary to the point where it becomes a pain in the ass - as you've pointed out.
    – Radu
    May 22, 2014 at 21:53
  • I cant be more specific without knowing more about the data and workload. Data wise it depends on how static or variable the data points are - hstore is generally better than EAV or wide sparse tables if you would otherwise have to go that way. Workload wise it depends a bit on write-mostly vs update-heavy etc.
    – Craig Ringer
    May 23, 2014 at 0:02
  • Lets say each experiment will contain on the order of 10000 rows containing simple data types. The number of experiments will potentially be in the hundreds/thousands. Workload is heavily read oriented and updates would be rare. Write workload is fairly low. The current system is text files which are converted by a simple Perl script to some HTML. As you can imagine, that makes integrating results very difficult and basically any of the above solutions will be an improvement. Thanks again for your help.
    – Radu
    May 23, 2014 at 0:22
  • "10000 rows containing simple data types" gives no information about structure and thus row width and record format etc. In general I'd say you haven't shown anything that sounds like a good use case for hstore so far though.
    – Craig Ringer
    May 26, 2014 at 4:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.