3 ms·

This is super cool! I run DevlRel @ Timescale, and I love seeing our community create well written posts like this! My initial reaction is that I think one of

This is super cool! I run DevlRel @ Timescale, and I love seeing our community create well written posts like this! My initial reaction is that I think one of the reasons you're seeing a hypertable being slower is almost certainly that it creates an index on the timestamp column by default. You don't have an index on your standard table which lets it go faster. You can use create_hypertable with create_default_indexes=>false to skip creating the index, or you can just drop the index before you ingest data. You'll eventually want that index - but it's best created after ingestion in a one-shot load like this. I'd also be interested in how the HDD you're reading data from is holding up in some of the highly parallel setups?

PolarizedPoutin 2y ago
Thank you for reading and for your kind words! Ah I did not know about the `create_default_indexes=>false` and that a time index is created by default for hypertables. I'll add a note to explain this! Also curious to benchmark inserting without the time index then creating it manually. Even with 32 workers I think the HDD was fine. I did monitor disk usage through btop and the SSD that Postgres lived on seemed to be more of a bottleneck than the HDD. So my conclusion was that a faster SSD for Postgres would be a better investment than moving the data from HDD to SSD.