Consider a few extra bytes per page for page headers, probably some row-level overhead I missed, and the size of the b-tree index created for your primary key, and that's about the size I'd expect, and without performance, precision, or some other losses, you're not going to see time series databases get much smaller for the same schemas.
It seems that float(p) with p undefined is treated as double. I need to double check my numbers. The storage test was done against 9.4 Indexes were turned off. Adding (Sensor, SampleTime DESC) index increases disk usage by 30%.
Not sure what you mean by precision or performance loss. TSDBs are way faster, I think it's a rather established fact because they're optimized for numeric array ingestion.
I'm not an expert on time series databases, but the main thing that I can imagine they do differently is data storage order. In SQL world, the physical order of the data is simply determined by the clustered index. If you were to want your data ordered by time, you'd make an index on a timestamp and cluster by that index [1].
Then if you want to get the most recent N entries, your SQL server is limited pretty much only by the disk read rate, because it's just reading pages in the order they're already stored physically on disk, rather than seeking based on a non-clustered index.
So that really just leaves the possibility of specialized caching or archiving logic as the primary benefit of a TSDB, as far as I can tell. To me, that's not likely worth the added complexity or maintenance costs until I'm processing a lot of transactions.
For the most part, though, that's because I already have a ton of SQL experience. And of course, I could be totally off-base, and I'd want to benchmark the two optimal solutions and see what the difference really is for a given use case.
My wording was misleading. It was 2.5 million observations (time|metric at long|float).
Row size on disk was 80 * 1024 * 1024 / 2500000/3 = 100 bytes. This is without any indexes. Looks like row overhead is quite substantial in PostgreSQL.
Well, the main reason for the overhead you mention is that SQL rows are constant size, and even with variable-size types (varchar) have a constant minimum size, even when some columns are null, which I think is where you're expecting different behavior.
This is not without reason. For one, it provides significant ordered lookup optimizations, since knowing that a row is N bytes long, you can simply skip to offset N * rowNumber on the disk.
So it's not system overhead, but rather the way you designed the table schema. Your table structure really ought to reflect the structure of data you expect it to store (single measurements at a time, in this case), unless you're doing warehousing or have access to sparse storage features (MSSQL, for one), which is exactly what I think you're expecting.
What I mean is, your schema should probably look like this:
Sensors table (probably smaller):
id serial: 4K
sensor_type (humidity, temperature, precipitation)
measurement_unit
location
etc.
Measurement table:
sensor int (FK) NOT NULL: 4B
value real NOT NULL: 4B
reading_taken timestamp NOT NULL: 8B
(No 1B null map since no fields are nullable)
Total measurement table row size: 16B
(plus indices, page overhead, and any overhead I'm unaware of as above)
This is more akin to the data structure you'd probably see in a TSDB and (sparse storage aside) is probably the most efficient format you can get. It's also more academically sound (normalized [1]), though that's often a mixed bag of tradeoffs past a certain point.
Schema:
Consider a few extra bytes per page for page headers, probably some row-level overhead I missed, and the size of the b-tree index created for your primary key, and that's about the size I'd expect, and without performance, precision, or some other losses, you're not going to see time series databases get much smaller for the same schemas.