3 ms·

Noob question: Can you have time-series tables and regular Postgres tables in the same database? Is this distinction something that you decide on when you creat

Noob question: Can you have time-series tables and regular Postgres tables in the same database? Is this distinction something that you decide on when you create a table?

noctarius 5y ago
Yes you can have both in the same database. We actually use TimescaleDB exactly that way, well at least for now. We'll in the process of separating certain tables out, so make it possible to select a pg database based on async or sync replication. In terms of creating a Hypertable (TimescaleDB table) you create the normal table first and then transform it into a Hypertable.
- clarkbw 5y ago
  > ... make it possible to select a pg database based on async or sync replication Can you explain why you doing this? Is this application data vs. time-series?
  - takeda 5y ago
    Not op, but choosing async vs sync replication is typically done around guarantees. Async (the default mode) is generally preferred in most cases as it adds relatively small overhead. The problem with it, is that if something happens to master, the standby node might be behind (i.e. some data might be lost). Sync on the other hand won't return from the transaction until the change is replicated. For time series, generally async is acceptable, and even for regular data most people might also be fine with async (that's why it is the default). Sync is used if you absolutely aren't allowing any data loss. But because it waits for all nodes to confirm they wrote data to the disk writing will always be slower. The problem is that if you use physical replication it applies to all data in the database (the logical resource not the physical server), so my guess is that some of the non-time series data has higher durability requirements.
claytonjy 5y ago
Yes you can, and yes you make the decision table by table. 'CREATE TABLE` is the same in either case, then `create_hypertable` to make it a timeseries table https://docs.timescale.com/api/latest/hypertable/create_hypertable/ https://docs.timescale.com/api/latest/hypertable/create_hype...
GordonS 5y ago
Yes! It's a nice benefit of TimescaleDB being built on top of Postgres.
- pvorb 5y ago
  And it's the feature that sets Timescale apart from other, NoSQL, time-series databases. With Timescale you can have all your data in the same database and join regular tables against hypertables or do other useful stuff directly in the database.
deleted 5y ago
[deleted]
miohtama 5y ago
I have been using TimescaleDB with Python, SQLAlchemy for a mixed timeseries and normal relational workload. The only issue I have encountered so far is that pq_restore needs some special, easy, wrapping. TimescaleDB is just an extension to PSQL.

Noob question: Can you have time-series tables and regular Postgres tables in the same database? Is this distinction something that you decide on when you create a table?

noctarius 5y ago

Yes you can have both in the same database. We actually use TimescaleDB exactly that way, well at least for now. We'll in the process of separating certain tables out, so make it possible to select a pg database based on async or sync replication. In terms of creating a Hypertable (TimescaleDB table) you create the normal table first and then transform it into a Hypertable.

clarkbw 5y ago
> ... make it possible to select a pg database based on async or sync replication Can you explain why you doing this? Is this application data vs. time-series?
- takeda 5y ago
  Not op, but choosing async vs sync replication is typically done around guarantees. Async (the default mode) is generally preferred in most cases as it adds relatively small overhead. The problem with it, is that if something happens to master, the standby node might be behind (i.e. some data might be lost). Sync on the other hand won't return from the transaction until the change is replicated. For time series, generally async is acceptable, and even for regular data most people might also be fine with async (that's why it is the default). Sync is used if you absolutely aren't allowing any data loss. But because it waits for all nodes to confirm they wrote data to the disk writing will always be slower. The problem is that if you use physical replication it applies to all data in the database (the logical resource not the physical server), so my guess is that some of the non-time series data has higher durability requirements.

claytonjy 5y ago

Yes you can, and yes you make the decision table by table. 'CREATE TABLE` is the same in either case, then `create_hypertable` to make it a timeseries table https://docs.timescale.com/api/latest/hypertable/create_hypertable/ https://docs.timescale.com/api/latest/hypertable/create_hype...

GordonS 5y ago

Yes! It's a nice benefit of TimescaleDB being built on top of Postgres.

pvorb 5y ago
And it's the feature that sets Timescale apart from other, NoSQL, time-series databases. With Timescale you can have all your data in the same database and join regular tables against hypertables or do other useful stuff directly in the database.

deleted 5y ago

[deleted]

miohtama 5y ago

I have been using TimescaleDB with Python, SQLAlchemy for a mixed timeseries and normal relational workload. The only issue I have encountered so far is that pq_restore needs some special, easy, wrapping. TimescaleDB is just an extension to PSQL.