I have thousands of tables need to be migrated and merged from multiple PostgreSQL DBs to a single one(at same machine). I’m trying to improve the performance of the process.
I do all things in a C++ program by means of multi-threads where every thread has its own connections to the source DBs and the destination, but I think we can discuss only at SQL layer instead of C++.
the definitions of the destination tables are very similar with the source.
I’m absolutely sure there is no primary key conflictions within all the data.
Can I do things below?
- Disable all triggers;
- Unlog the destination tables(ALTER TABLE <table_name> SET UNLOGGED);
- Drop all the PKs/Indexes, and re-create them after the process
And how to Disable all triggers(not only influence a single session or single table)?
This is the definition of the core table, and others very similary.
CREATE TABLE tab_name( tick_time TIMESTAMPTZ(6) NOT NULL, nano_secs INT2 NOT NULL DEFAULT 0, trade_day DATE NOT NULL, other data, PRIMARY KEY ( tick_time, nano_secs ) ); CREATE INDEX idx_tab_name_trade_day ON tab_name(trade_day);
And this is the core SQL statement that do the merging:
INSERT INTO public.tab_name( fields list ) SELECT <fields list> FROM source1.tab_name;