Question - Hypertables on Timefield vs any other incremental field

[luvpreetsingh]

Using Postgresql 10.5 and latest Timescaledb.

This is more of a question than a bug.

When people talk about timeseries databases, I, and many others, have this view that time based queries will be served faster.

It is written in docs that hypertable can be created on any incremental field, not only on a timefield.

My question:

If I create a hypertable on the id field, and id is primary key.

SELECT create_hypertable('api_iot_data', 'id', chunk_time_interval=86400);

And I query on the timefield,

Select * from api_iot_data where timefield >= '2018-10-31 00:00'

Would my query be as fast as if I had instead created a hypertable on the timefield?

More on why this came to my mind:

I was trying to create a hypertable on my simple table where id was the primary key.

When I tried to create the hypertable, it failed,

db=# SELECT create_hypertable('test', 'timefield');
ERROR:  cannot create a unique index without the column "timefield" (used in partitioning)

So, I restarted and this time, I made my timefield as the primary key, and again tried to create hypertable, and it worked.

But if I create timefield as my primay key, too many IntegrityError started coming as many rows were being written at the same time.

[mfreed]

Hi @luvpreetsingh I think we answered this in slack, but for the record: If you partition by one field (like id), but commonly query just by timefield, the planner will need to execute the query against every partition, because it know longer has time as a dimension with which to "prune" partitions.

As to your second question, you can only define a UNIQUE column (which a primary key is) on either your partitioning key or a composite that includes your partitioning keys as their prefix.

But you