7 ms·

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

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.

andrewstuart2 11y ago
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. [1] https://en.wikipedia.org/wiki/Database_normalization https://en.wikipedia.org/wiki/Database_normalization

andrewstuart2 11y ago

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. [1] https://en.wikipedia.org/wiki/Database_normalization https://en.wikipedia.org/wiki/Database_normalization