{"id":74953,"date":"2020-03-22T08:42:22","date_gmt":"2020-03-22T05:42:22","guid":{"rendered":"https:\/\/prohoster.info\/blog\/administrirovanie\/dba-gramotno-organizovyvaem-sinhronizaczii-i-importy"},"modified":"2020-03-22T08:42:22","modified_gmt":"2020-03-22T05:42:22","slug":"dba-gramotno-organizovyvaem-sinhronizaczii-i-importy","status":"publish","type":"post","link":"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/dba-gramotno-organizovyvaem-sinhronizaczii-i-importy","title":{"rendered":"DBA: we efficiently organize synchronizations and imports","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

When dealing with complex processing of large datasets (various ETL processes<\/a><\/noindex>: imports, conversions, and synchronization with an external source), the need often arises to temporarily 'remember' and quickly process<\/b> something substantial.<\/p>\n

The typical task of this kind usually sounds something like this: \"Here, the accounting department exported from the client bank<\/a><\/noindex> the latest incoming payments, we need to quickly upload them to the site and link them to invoices\"<\/i><\/p>\n

However, when the volume of this 'something' starts to be measured in hundreds of megabytes, and the service must continue to operate with the database in 24\/7 mode, many side effects arise that will complicate your life.
\n\"DBA:
\nTo cope with them in PostgreSQL (and not just there), certain optimization features can be used that will allow for faster processing with less resource consumption.
\n<\/a><\/noindex><\/p>\n

1. Where to load?<\/h2>\n

\nFirst, let's determine where we can load the data we want to 'process'.<\/p>\n

1.1. Temporary tables (TEMPORARY TABLE)<\/h3>\n

\nIn principle, for PostgreSQL, temporary tables are just like any other tables. Therefore, superstitions like \"everything is stored only in memory, and it can run out\"<\/b><\/i>are incorrect. However, there are several significant differences.<\/p>\n

A separate 'namespace' for each connection to the database<\/h4>\n

\nIf two connections try to simultaneously execute CREATE TABLE x<\/code>, then someone will definitely get a uniqueness violation error<\/b> of database objects.<\/p>\n

However, if both try to execute CREATE TEMPORARY<\/b> TABLE x<\/code>, then both will do it successfully, and each will receive its own instance<\/b> of the table. There will be nothing in common between them.<\/p>\n

'Self-destruction' upon disconnection<\/h4>\n

\nWhen a connection is closed, all temporary tables are automatically deleted, so there's no point in manually executing DROP TABLE x<\/code> unless\u2026<\/p>\n

If you are working through pgbouncer in transaction mode<\/b>, then the database still considers that the connection is still active, and that temporary table still exists in it.<\/p>\n

Therefore, trying to create it again from another connection to pgbouncer will lead to an error. But this can be bypassed by using CREATE TEMPORARY TABLE IF NOT EXISTS<\/b> x<\/code>.<\/p>\n

In fact, it's better not to do that, as you might unexpectedly discover leftover data from the previous owner. Instead, it's much better to read the manual and see that when creating a table, you have the option to specify ON COMMIT DROP<\/b><\/code> \u2014 meaning that upon completing the transaction, the table will be automatically deleted.<\/p>\n

Non-replication<\/h4>\n

\nDue to being tied to a specific connection, temporary tables are not replicated. However, this eliminates the need for double data writing<\/b> in heap + WAL, so INSERT\/UPDATE\/DELETE operations on it are significantly faster.<\/p>\n

But since a temporary table is still 'almost ordinary,' it cannot be created on a replica either. At least, not for now, although the corresponding patch has been around for a long time.<\/p>\n

1.2. Unlogged Tables (UNLOGGED TABLE)<\/h3>\n

\nBut what if, for instance, you have some bulky ETL process that cannot be realized within a single transaction, and you do have pgbouncer in transaction mode<\/b>?..<\/p>\n

Or if the data flow is so large that the bandwidth of a single connection<\/b> to the DB (i.e., one process on the CPU) is insufficient?..<\/p>\n

Or if part of the operations are going asynchronously<\/b> in different connections?..<\/p>\n

Here, there\u2019s only one option \u2014 temporarily creating a non-temporary table<\/b>. A pun, right? Meaning:<\/p>\n