Postgresql – How to solve this complex insertion problem

amazon-rdspostgresql

In our database we store policycase records. A policycase has a 1:1 relation to a (requested) policy record. The policy record has a 1:n relationship to policy_map. Where policy_map is a key, value table.

When a policycase is changed from our user application, the complete policycase record and all it's child records are copied and stored again. At least, that was the requirement. Due to a bug only a small number of the policy_map records are copied to the new policycase.

We want to execute an insert query that creates the missing policy_map records for the copied record, when they do not exist.

The old policycase can be recognized from a status HISTORICAL and the copied policycase from a status ARCHIVED. Both policycases have the same uniqueid field.

So the following select query gives me the policy_map records for the historical and the archived (copied) policycase records for a certain uniqueid:

SELECT 
    policycase.status,
    policy.id,
    policy.uniqueid, 
    policy_map.map_key, 
    policy_map.map_value 
FROM policycase 
INNER JOIN policy ON policy.id = policycase.requested_id
INNER JOIN policy_map ON policy_map.policy_id = policy.id
WHERE policy.uniqueid = '91157235'

The result of this query is something like:

+------------+--------+----------+--------------------------------+-----------+
|   status   |   id   | uniqueid |            map_key             | map_value |
+------------+--------+----------+--------------------------------+-----------+
| HISTORICAL | 438957 | 91157235 | isPropertyOwner                | TRUE      |
| HISTORICAL | 438957 | 91157235 | livingAtProperty               | TRUE      |
| HISTORICAL | 438957 | 91157235 | stateOfMaintenance             | GOED      |
| HISTORICAL | 438957 | 91157235 | stateOfMaintenanceRoof         | 0         |
| HISTORICAL | 438957 | 91157235 | unsecuredGuaranteeHouseContent | J         |
| HISTORICAL | 438957 | 91157235 | unsecuredGuaranteeHouseFire    | J         |
| ARCHIVED   | 451423 | 91157235 | isPropertyOwner                | TRUE      |
| ARCHIVED   | 451423 | 91157235 | livingAtProperty               | TRUE      |
| ARCHIVED   | 451423 | 91157235 | stateOfMaintenance             | GOED      |
+------------+--------+----------+--------------------------------+-----------+

In the table above, the map records

stateOfMaintenanceRoof
unsecuredGuaranteeHouseContent
unsecuredGuaranteeHouseFire

need to be copied to the policy_map table, related to the ARCHIVED policycase. That needs to be done for every uniqueid in the database.

How would a query or function that accomplishes this look?

We are running Postgres 9.6 on Amazon RDS.

Best Answer

Setup:

create table policycase
(requested_id int
,status       varchar(30));

insert into policycase values (438957,'HISTORICAL');
insert into policycase values (451423,'ARCHIVED');

create table policy
(id           int
,uniqueid     varchar(10));

insert into policy values (438957, '91157235');
insert into policy values (451423, '91157235');

create table policy_map
(policy_id    int
,map_key      varchar(100)
,map_value    varchar(30));

insert into policy_map values (438957, 'isPropertyOwner'               , 'TRUE');
insert into policy_map values (438957, 'livingAtProperty'              , 'TRUE');
insert into policy_map values (438957, 'stateOfMaintenance'            , 'GOED');
insert into policy_map values (438957, 'stateOfMaintenanceRoof'        , '0');
insert into policy_map values (438957, 'unsecuredGuaranteeHouseContent', 'J');
insert into policy_map values (438957, 'unsecuredGuaranteeHouseFire'   , 'J');

insert into policy_map values (451423, 'isPropertyOwner'               , 'TRUE');
insert into policy_map values (451423, 'livingAtProperty'              , 'TRUE');
insert into policy_map values (451423, 'stateOfMaintenance'            , 'GOED');

Before insert:

SELECT 
    policycase.status,
    policy.id,
    policy.uniqueid, 
    policy_map.map_key, 
    policy_map.map_value 
FROM policycase 
INNER JOIN policy ON policy.id = policycase.requested_id
INNER JOIN policy_map ON policy_map.policy_id = policy.id
WHERE policy.uniqueid = '91157235'
order by policy.uniqueid,policy.id,policy_map.map_key;

status     |     id | uniqueid | map_key                        | map_value
---------- | ------ | -------- | ------------------------------ | ---------
HISTORICAL | 438957 | 91157235 | isPropertyOwner                | TRUE     
HISTORICAL | 438957 | 91157235 | livingAtProperty               | TRUE     
HISTORICAL | 438957 | 91157235 | stateOfMaintenance             | GOED     
HISTORICAL | 438957 | 91157235 | stateOfMaintenanceRoof         | 0        
HISTORICAL | 438957 | 91157235 | unsecuredGuaranteeHouseContent | J        
HISTORICAL | 438957 | 91157235 | unsecuredGuaranteeHouseFire    | J        
ARCHIVED   | 451423 | 91157235 | isPropertyOwner                | TRUE     
ARCHIVED   | 451423 | 91157235 | livingAtProperty               | TRUE     
ARCHIVED   | 451423 | 91157235 | stateOfMaintenance             | GOED

The proposed INSERT:

insert into policy_map

select p2.id,
       pm1.map_key,
       pm1.map_value

from   policycase pc1
join   policy p1
on     pc1.requested_id = p1.id

join   policy_map pm1
on     pm1.policy_id = p1.id

-- join with p2/pc2 to obtain id for ARCHIVED rows
join   policy p2
on     p2.uniqueid  = p1.uniqueid

join   policycase pc2
on     pc2.requested_id = p2.id

where  p1.uniqueid = '91157235'
and    pc1.status  = 'HISTORICAL'
and    pc2.status  = 'ARCHIVED'

and    not exists(select 1
                  from   policycase pcx
                  join   policy px
                  on     pcx.requested_id = px.id

                  join   policy_map pmx
                  on     pmx.policy_id    = px.id

                  where  px.uniqueid      = p1.uniqueid
                  and    pcx.status       = 'ARCHIVED'

                  and    pmx.map_key      = pm1.map_key
                  and    pmx.map_value    = pm1.map_value);

NOTE: There may be a way to eliminate some of the joins but I'll leave it like this for now as it's easier (for me) to understand what's going oin.

After insert:

SELECT 
    policycase.status,
    policy.id,
    policy.uniqueid, 
    policy_map.map_key, 
    policy_map.map_value 
FROM policycase 
INNER JOIN policy ON policy.id = policycase.requested_id
INNER JOIN policy_map ON policy_map.policy_id = policy.id
WHERE policy.uniqueid = '91157235'
order by policy.uniqueid,policy.id,policy_map.map_key;

status     |     id | uniqueid | map_key                        | map_value
---------- | ------ | -------- | ------------------------------ | ---------
HISTORICAL | 438957 | 91157235 | isPropertyOwner                | TRUE     
HISTORICAL | 438957 | 91157235 | livingAtProperty               | TRUE     
HISTORICAL | 438957 | 91157235 | stateOfMaintenance             | GOED     
HISTORICAL | 438957 | 91157235 | stateOfMaintenanceRoof         | 0        
HISTORICAL | 438957 | 91157235 | unsecuredGuaranteeHouseContent | J        
HISTORICAL | 438957 | 91157235 | unsecuredGuaranteeHouseFire    | J        
ARCHIVED   | 451423 | 91157235 | isPropertyOwner                | TRUE     
ARCHIVED   | 451423 | 91157235 | livingAtProperty               | TRUE     
ARCHIVED   | 451423 | 91157235 | stateOfMaintenance             | GOED     
ARCHIVED   | 451423 | 91157235 | stateOfMaintenanceRoof         | 0        
ARCHIVED   | 451423 | 91157235 | unsecuredGuaranteeHouseContent | J        
ARCHIVED   | 451423 | 91157235 | unsecuredGuaranteeHouseFire    | J

And here's a dbfiddle

Related Solutions

Amazon-rds – IOPS on Amazon RDS

will select query count as 1 IOP or would it equal the number of records being returned?

No. Assuming that Amazon accounts IOPs accurately (after all, they are virtual machines), there are some abstractions between SQL and disk IO operations:

SQL Operations: can be seen with the com_select, com_insert, etc. counters on SHOW GLOBAL STATUS. Please note that if you have a cache like the query_cache, close to no disk operations will be done (because results will be returned directly from memory).
Handler ("row") operations: can be seen with the 'Handler_%'counters on SHOW GLOBAL STATUS
Engine operations: reading a single row, or inserting a single row (Handler_write) will be done differently for each engine, implying MySQL buffering or not. For example, for InnoDB you can check the GLOBAL STATUS variables for the read request to the buffer pool versus the disk read requests. A single row read may require several reads on indexes and different versions of the rows in the UNDO history before being returned.
Filesystem cache: data that is frequently read or written will usually be cached on memory by the operating system. This will speed up subsequent reads and writes by avoiding actual disk IO (specially on places like Amazon, where de IO can be on a local disk or over the network with non-deterministic response).
Disk IO: even when dealing with actual IO, writes are usually done in blocks.

I have not gone in detail about MySQL and OS internals, and even without doing that you can see that things are not obvious- in fact reducing IO and knowing why there is so much of it in some cases is one of the most important fields for database optimization.

An engine like InnoDB will require all of its pages to be written 3 or 4 times on insertion (one on the transaction log, one on the actual tablespace, one on the double write buffer and optionally, on the binary log)- and this is a simplification- it does not have into account index updates, metadata, statistics, etc. The best way to know how many IOPS you will need is to test on a particular setup. Even with some fake smaller tests it will be more reliable than tying to theorise. Caching both at OS and DB level will make the numbers dramatically different.

For example, the other day I inserted dozens of millions of records at 200.000 rows/s with LOAD DATA (no SQL overhead) because I had enough memory to write almost exclusively to the InnoDB buffer pool. It took several minutes for the disk to synchronise with the memory contents, though.

PostgreSQL – How to Select Rows and Add Their Previous Rows

This is exactly what the LAG() window function does, available since version 8.4:

SELECT id, name, date, 
       LAG(name) OVER (PARTITION BY id ORDER BY date) AS previous_name, 
       LAG(date) OVER (PARTITION BY id ORDER BY date) AS previous_date
FROM table_name ;

There is also the option of using a LATERAL join (available from 9.3). Might be better if you want all the columns (of the previous row). This assumes that there is a UNIQUE constraint on (id, date):

SELECT t.id, t.name, t.date, 
       p.name AS previous_name, p.date AS previous_date
FROM table_name AS t
  LEFT JOIN LATERAL
    ( SELECT p.*
      FROM table_name AS p
      WHERE p.id = t.id
        AND p.date < t.date
      ORDER BY p.date DESC
      LIMIT 1
    ) AS p ON TRUE ;

An index on (id, date) would be helpful for both queries.

Best Answer

Related Solutions

Amazon-rds – IOPS on Amazon RDS

PostgreSQL – How to Select Rows and Add Their Previous Rows

Related Question