What is an Upsert?

The upsert method is a combination of the update and insert methods. You may have come across this method before when working with a relational database management system (RDMS). Note that although it also uses the upsert method, LUSID uses a bi-temporal event store rather than relying on a RDMS. 

The upsert method inserts a record if it does not exist and updates a record if it does exist. It guarantees that each record will either be inserted or updated regardless of the state of the system and any concurrent activity. 

Why Upsert?

For some of the LUSID APIs such as the instrument API, maintaining an instrument master of hundreds of thousands if not millions of securities is challenging with just the update and insert methods. 

At such a large scale using just the update and insert methods requires a lot of logic above the API to handle errors caused by trying to upload duplicates and trying to update records which don't exist. 

To overcome this and ensure that the data inside LUSID accurately represents the state of your your other systems we have chosen to use the upsert method instead.