Postgres: Copying large subsets of data(10k+rows) from one schema to another for immediate use

postgresql sqlbulkcopy


Rows must be continuously pushed from one Postgres schema to another. The content is stored in one schema, while the copy of the data that the end user adds, modifies, and deletes for their unique usage resides in the other schema. As more material is uploaded, more content is consumed, and more content is altered by the end user, there are more than 10 million records and rising in each of the schema tables. Numerous thousand records from several tables to several tables in the other schema would need to be moved. The same database houses each of these schemas. To allow the end user to begin customizing as soon as they have selected the information, this data must be incredibly rapid and move constantly. I've looked at trigger replication and the COPY function, but neither seems to work as soon as required. Since customers utilize these tables often and extensively, I am unable to delete indexes before insert. I would be grateful for any suggestions.

6/13/2017 5:57:59 PM

Accepted Answer

The easiest approach to transfer a large number of tables from one schema of a database to another schema of the same database is to use the command SUBSTITUTE INTO...SELECT FROM, thus execute as a user who has access to both schema, for example:

INSERT INTO schema2.table_y ( field, field, field )
SELECT field, field, field
FROM schema1.table_y
WHERE user filter condition

Since you need to accomplish this across many tables, you can either run numerous insert statements using Writing CTEs in a single large query, or you can wrap it all up in one huge transaction.

That will really be the quickest method to get a complete set of the data. Even so, it will still need some time to insert thousands of rows across various tables and run the SELECT queries to retrieve the data. General queries and speed improvement are your options for cutting down on this time.

You might experiment with utilizing VIEWs, including updateable ones, if the modification is such that the user just need a live portion of the data rather than the whole set and no copy of the data is required. But it seems that you need a copy.

6/13/2017 8:31:02 PM

Related Questions

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow