Bulk update to Postgres with node js performance issue

Question

I'm facing performance issue while trying to do bulk update in PostgresDB. It's taking more than 180 seconds to update around 23000 records. PFB the code. I'm using pg-promise library. Is there anything I could do to improve the performance?

const pgp = require('pg-promise')();

const postgresDBConfig = {
    host: Config.postgresDBHost,
    port: Config.postgresDBPort,
    database: Constants.postgresDBName,
    user: Config.postgresDBUser,
    password: 'pswd'
};

export async function getTransactionDetails(): Promise<any> {

    return new Promise<any>(async function (resolve, reject) {
        try {
            let db = pgp(postgresDBConfig);
            db.connect();
            let query = "SELECT * FROM table_name";
            db.any(query)
                .then(data => {
                    console.log("Executed successfully::");
                    resolve(data);
                })
                .catch(error => {
                    console.log('ERROR:', error);
                })

        } catch (error) {
            log.error("Error::" + error);
            throw error;
        }
    });
}

export async function updateStatus(result: any, status: string) {
    try {

        let db = pgp(postgresDBConfig);
        //db.connect();
        let updateData = [];
        _.forEach(result, function (row) {
            let updateInfo = {};
            updateInfo["sessionid"] = row.sessionid;
            updateInfo["status"] = status;
            updateData.push(updateInfo);
        });
        console.log("updateData::" + updateData.length);
        const tableName = new pgp.helpers.TableName('table_name', 'schema_name');
        let columnset = new pgp.helpers.ColumnSet(['?sessionid', 'status'], { table: tableName });
        let update = pgp.helpers.update(updateData, columnset);
        db.none(update).then(() => {
            console.log("Updated successfully");
        })
            .catch(error => {
                console.log("Error updating the status" + error);
            });
    }

    catch (error) {
        log.error("Error in function updateStatus::" + error);
        throw error;
    }
}

Answer 1

The code exhibits problems all over the place

• You should initialize the database object only once
• You should not use db.connect() at all, which you also use incorrectly for the async code
• You again incorrectly use async block, skipping await, so it doesn't execute correctly.
• You do not append any UPDATE logic clause, so it is updating everything all over again, unconditionally, which may be resulting in a delayed mess that you're in.

Here's an improved example, though it may need some more work from your side...

const pgp = require('pg-promise')();

const postgresDBConfig = {
    host: Config.postgresDBHost,
    port: Config.postgresDBPort,
    database: Constants.postgresDBName,
    user: Config.postgresDBUser,
    password: 'pswd'
};

const db = pgp(postgresDBConfig);

const tableName = new pgp.helpers.TableName('table_name', 'schema_name');
const columnSet = new pgp.helpers.ColumnSet(['?sessionid', 'status'], {table: tableName});

export async function getTransactionDetails(): Promise<any> {
    try {
        const res = await db.any('SELECT * FROM table_name');
        console.log('Executed successfully::');
        return res;
    } catch (error) {
        console.log('ERROR:', error);
        throw error;
    }
}

export async function updateStatus(result: any, status: string) {
    try {
        let updateData = [];
        _.forEach(result, row => {
            let updateInfo = {};
            updateInfo["sessionid"] = row.sessionid;
            updateInfo["status"] = status;
            updateData.push(updateInfo);
        });
        console.log('updateData::', updateData.length);
        const update = pgp.helpers.update(updateData, columnSet) +
            ' WHERE v.sessionid = t.sessionid';
        await db.none(update);
        console.log('Updated successfully');
    }
    catch (error) {
        console.log('Error in function updateStatus:', error);
        throw error;
    }
}