Improving performance of merging data between tables

I was wondering if anybody would have any ideas on how to improve certain operations that we are having.

PostgreSQL 9.3.3. Table layout:

main_table: PK, N key columns, M data columns. The key columns are bound in a unique key together. PK is pure sequence number. There are few separate non-unique indices on some of the key columns, to aid in querying the table data.

second_table: PK, main_table_PK_REF (declared as foreign key), Na key columns, Ma data columns. There are 0-inf (typically 0-10) entries in second_table that reference a single entry in main_table. PK is pure sequence number, and unique key for that table is (main_table_PK_REF + key columns). The only non-obvious thing here is that some of the unique indexes involve coalesce() function:

"uq_brkioevent" UNIQUE, btree (mainid, blockid, name, subid, (COALESCE(cname, ''::character varying)))

Those are there because we need to write the NULL into the tables, but unique indexes don't like NULL values (AFAIR).

There is one main_table, and 10 second_tables.

The amount of rows in main table right now is ~1.1M, second tables have about 1M-2M of rows. The growth of the main table is fixed amount of rows (~10) per hour.

Multiple (web) application nodes need to write data into this table. Most of the operations are modifying the data columns, rather than inserting new data. We had serious contention problems if we let all the application nodes write directly into the table. The writes involved using prepared transactions, the prepared transaction can only be reaped after other data stores are written to, and there is very high chance nodes will need to modify the same rows, so the locking was taking too long.

To relieve the contention, we have allocated each application node it's own set of tables that structurally are exactly like main/second tables. The application node will open a transaction, write all the data into its own tables, free of locks, and then call a pgsql function to merge the data from its tables into the common tables. There is typically relatively little data in the node tables (say within 100 rows in any table) before its merged into the common tables. Nodes would dump their data when there is something to dump, can be few times a second.

Recently, the operation that moves the data from the node tables into the common tables started being a real drain on the PostgreSQL server CPU. I assume this is probably due to the data set size reaching some critical mass. Things really got outta hand when we had to double the amount of application nodes to accommodate surge in application use.

The merging is done in the following manner.

Each main/second table has an associated PL/pgSQL function (merge_xxx) that takes in key and data values as arguments. It then, in an endless loop, tries to execute UPDATE statement (using math operations to update the data based on existing and input data values, using key data in the query part). If UPDATE statement set "found", then the function exists. Otherwise, the function tries to INSERT with key/data values. If that succeeds, function exists, else if unique_violation is thrown, loop continues.

On top of these individual functions, there is another PL/pgSQL function (merge_all). It uses "for ROW in select * from MAIN_NODE" outer loop, and within that loop it calls the merge_xxx for the main table, and then for each secondary table, does the same "for ROWx in select * from SECOND_NODE", adding WHERE clause to only pick up entries that correspond to the current main_node table entry that's being processed, calling merge_xxx for the corresponding secondary table. At the end of the outer loop, all data from node tables is removed (using DELETE). I will gladly provide pseudo-code, or even the function body is my explanation is unclear.

Besides "can somebody please look at this and let me know if I'm doing something utterly stupid", here are my questions.

1) How do I find out what exactly is consuming the CPU in a PL/pgSQL function? All I see is that the calls to merge_all() function take long time, and the CPU is high while this is going on.

2) Is there a better way to merge individual rows, except doing UPDATE/INSERT in a loop, and would that be CPU expensive?

3) Is there a better way to merge whole tables? However, note that I need to translate primary keys from node main table into the common main table, as they are used as foreign keys, hence the loops. I suspect the looping is CPU intensive.

Thank you,

  Pawel.