4 ms·
> If you're dealing with event or time series data—whether > user messages, logs, web analytics, or connected device > events—scaling out to both store and an
by rodionos 11y ago
> If you're dealing with event or time series data—whether
> user messages, logs, web analytics, or connected device
> events—scaling out to both store and analyze terabytes of
> data becomes trivial with Citus Cloud
Speaking of time series use cases. PostgreSQL uses 30 to 100 bytes per long/float tuple, depending on schema. It's hard to see how it can it be competitive with TSDBs that are often in the single byte digits these days?
- andrewstuart2 11y agoAre you referring to something different than the double precision floating point on the types page [1]? Because according to the docs, it uses 8 bytes, in compliance with IEEE 754. [1] http://www.postgresql.org/docs/9.5/static/datatype-numeric.html http://www.postgresql.org/docs/9.5/static/datatype-numeric.h...
- rodionos 11y agoTable schema, with indexes disabled: > CREATE TABLE SensorData ( Sensor INT NOT NULL, FOREIGN KEY (Sensor) REFERENCES Sensors(Id), Humidity FLOAT, Precipitation FLOAT, Temperature FLOAT, SampleTime TIMESTAMP(3) NOT NULL, PRIMARY KEY (Sensor, SampleTime) ); Size on disk 80MB after 2.5 mln inserts {sensor,temperature,humidity,precipitation}. SELECT pg_size_pretty(pg_total_relation_size('"measurements"'))
- andrewstuart2 11y ago80MB * 1024 * 1024 / 2500000 rows = 33 bytes/row Schema: Sensor: 4B Humidity: 4B Precipitation: 4B Temperature: 4B Timestamp: 8B Null fields bitmap: 1B (< 8 fields) Total: 4 * 4 + 8 + 1 = 24 Bytes. 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.
- ddorian43 11y agoRow overhead is 24 bytes + alignment/padding.
- rodionos 11y agoIt 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.
- andrewstuart2 11y agoI'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. [1] http://www.postgresql.org/docs/9.5/static/sql-cluster.html http://www.postgresql.org/docs/9.5/static/sql-cluster.html
- rodionos 11y agoMy 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.