First steps in postgres

TimescaleDB and PostgreSQL
,
1

Hi to all,
I’m new with Timescale and working on creating a table with 60 columns. I’ve included the DDL below, and I’m wondering if there are any best practices I should be aware of or if anyone has suggestions on optimizing this structure. Additionally, I’m unsure about some of the permissions and the hypertable creation – any feedback would be appreciated!

CREATE TABLE dwh_conta.reading (
a_plus_frame varchar(500),
alarm int,
alarm_stat int,
…
qmin_time varchar(250),
read_date varchar(250),
read_date_time timestamp,
read_date_time_utc varchar(250),
read_time varchar(250),
…
wd_5_vol numeric
);

alter table dwh_conta.reading add primary key ( id, read_date_time );

GRANT CREATE, USAGE ON SCHEMA dwh_conta TO ga_balvi;
GRANT CREATE, USAGE ON SCHEMA dwh_conta TO goaigua_nifi;
GRANT UPDATE, TRUNCATE, REFERENCES, INSERT, DELETE, TRIGGER, SELECT ON TABLE dwh_conta.reading TO ga_balvi;
GRANT UPDATE, TRUNCATE, REFERENCES, INSERT, DELETE, TRIGGER, SELECT ON TABLE dwh_conta.reading TO goaigua_nifi;

SELECT create_hypertable(
‘dwh_conta.reading’,
‘read_date_time’,
migrate_data => true,
chunk_time_interval => INTERVAL ‘1 day’,
associated_schema_name => ‘dwh_conta’,
associated_table_prefix => ‘reading’
);

Thanks in advance!"

This way, you’re inviting the forum members to provide more targeted assistance or advice based on your specific concerns. Overall, your post is on the right track!

2

Hi @javier_aragon , you can use the same permissions as your regular table.

If you’re using 60 columns probably your data will end up going to TOAST. Take a look on the surprising impact it can bring.

Another thing that I noted is that you’re not optimizing the data types. Looking to all the columns that ends with _time or _date and they looks like a timestamptz but you’re still using varchar(250). Limiting varchar is not a good idea, check why.

You can also find some good instructions here: Timescale Tips: How to Optimize Your Ingest Rate

UPDATE: We also have a new blog post about it this week: Postgres TOAST vs. Timescale Compression