Row and column format with PostgreSQL and TimescaleDB?

TimescaleDB and PostgreSQL Installation, Configuration, and Errors
, ,
1

I use Postgres 16 and 17. I read that TimescaleDB provides column compression. Would this mean that data in stored in column format? Do I necessarily need time to store in columnar format, please? I have not chosen the dataset to use so I do not know if it will contain any timestamps. How do I differentiate between PostgreSQL tables and hypertables (TimescaleDB tables)? I need to have two identical tables, one in row and another in column format.

TimescaleDB version: not installed
PostgreSQL version: 16, 17
Other software: pgAdmin 4
OS: Windows 11

2

Welcome Luca :wave:

You can use any table and just enable compression. The format will be changing internally without your intervenetion.

Can you elaborate a bit more? because you may not need to store it in this way, you must need to use some tools like crosstab to make the transformation to bring it in another format of representation.

3

Thank you for replying.

Can you elaborate a bit more?

I have a project where I have to compare performance between row and columnar data structures for OLAP data. By data structures I mean the way data files are structured.

You can use any table and just enable compression.

Would I have both structures available, or working with both side-by-side is not allowed, please?

you may not need to store it in this way, you must need to use some tools like crosstab to make the transformation to bring it in another format of representation

A crosstab is used to get aggregate values on categories of variables, right? If yes, then I do not believe I am searching for this functionality in this project.
Logically, the data will be shown in the same way, but physically it will be stored different (row and columnar).

4

Keep in mind this is all Timescaledb acting on the Postgresql internals. You define the table structure and use timescale to partition the table by the time column. You’ll use standard Postgres table queries and everything you need as it would be on a normal table.

5

I do not want to partition (maybe later on) as it is not the scope of the project. I would like to store the same table (or a copy of it) in row and column physical format, using pgAdmin and PostgreSQL. Does Timescale cater for this? If this is not something Timescale caters for, then it will not satisfy the requirement for the project. Time may not be something which features in the table. If it caters for this, how, please?