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!