db hint

[vitaly-t]

var db = pgp(cn); // database instance;

you should bring this up to the module loading time, as it is meant to be done once (per database configuration).

[vitaly-t]

Also, you can change the code to:

var q = "SELECT prod.sku, prod.ppsid, p.store_name, p.price, p.last_update, p.source, p.url " +
    "FROM products AS prod " +
    "JOIN stores AS client_store on client_store.id = prod.store_id " +
    "LEFT OUTER JOIN pricing AS p ON p.ppsid = prod.ppsid AND p.approved = 1 AND p.source = 8" +
    "AND GETDATE()-p.last_update <= INTERVAL '7 days' AND p.store_name <> client_store.store_name AND p.url LIKE $2" +
    "WHERE prod.store_id = $1 AND prod.ppsid IN ($3:csv) ORDER BY prod.sku;"

db.query(q, [store_id_int, compDomain, ppsids])

i.e. use :csv to convert the array element into comma-separate values, with proper escaping.

And it will be more efficient to store such SQL in external files, see:

• QueryFile API
• pg-promise-demo, as an example