1

I have a relatively big postgresql table (timescaleDB), 200GB ~ 1B rows, index by timestamp and id. I realize a lot of times, my very task is only handled by 1 process. I believe my query can be split up into sub-queries and use python to create new connection for every sub-query. For example, the simplest query is

select * from db

I plan to split it up into

select * from db where timestamp > '2021-01-01' and timestamp <= '2021-02-01' 
select * from db where timestamp > '2021-02-01' and timestamp <= '2021-03-01' 
...
select * from db where timestamp > '2021-09-01' and timestamp <= '2021-10-01' 

I believe this could speed up right? I am also considering insert into the same db from multiple connection. However, this time, I am a bit unsure... Would I create some kind of lock so that it could not speed up?

The complexity involved is that, the insert is dependent on the db itself...

Create View view_db as select timestamp+'0.01s'::interval, id, a+random() as a from db 

My parallelized insert then looks like

insert into db select * from view_db where timestamp > '2021-01-01' and timestamp <= '2021-02-01' 
insert into db select * from view_db where timestamp > '2021-02-01' and timestamp <= '2021-03-01' 
...
insert into db select * from view_db where timestamp > '2021-09-01' and timestamp <= '2021-10-01' 

Would my multiple connection speed up in this case (or would it lock the table so that there is no parallelization benefit)? The last myth to me would be what if they have conflict. Example,

insert into db select * from view_db where timestamp > '2021-01-01' and timestamp <= '2021-02-01' 
insert into db select * from view_db where timestamp > '2021-01-15' and timestamp <= '2021-02-15' 
insert into db select * from view_db where timestamp > '2021-02-01' and timestamp <= '2021-03-01' 
insert into db select * from view_db where timestamp > '2021-02-15' and timestamp <= '2021-03-15' 
...
insert into db select * from view_db where timestamp > '2021-09-01' and timestamp <= '2021-10-01' 

Then, everything now is definitely not perfectly parallelizable... What will happen in this case?

So, in summary, my questions are

1, would multiple connection select speed up query

2, would non-conflicting multiple connection insert speed up insert

3, what will happen in conflicting multiple connection insert? Is my insert still speed up?

Thanks!

2 Answers 2

5

Due to parallel query introduced in PostgreSQL 9.6, your SELECT will automatically be parallelized, so you won't gain anything by using multiple connections. You can override the default degree of parallelization by setting the parallel_workers storage parameter on the table.

Concurrent inserts can run in parallel. If there are conflicts, like unique constraint violations, one of the inserts will fail. You can avoid that by using the ON CONFLICT DO NOTHING/UPDATE clause of INSERT.

3

You should definitely check the tsbs project: https://github.com/timescale/tsbs/

It can help you to do this by configuring the --workers param in the tsdb_load command. You can apply the parameter while running queries with the tsbs_run_queries command to confirm how it will behave.

In my benchmarks, generally, a 16 CPU server works well with 12 to 24 parallel workers. Running a few benchmarks you can find the right balance for your server.

If you need a strong computing system parallelizing hardcore queries you should try multi-node with distributed hypertables.

Also, consider that hypertables are doing these parallel scans behind the scenes. So, if you have more cores, PostgreSQL will benefit from it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.