Performance in PgSQL update

[zzz]

I used to use python driver to pull data from RS5 and do batch insertion into PgSQL for data analysis. Recently I tested a little more with the ModArcPostgreSql. With this and ExtDepPostgreSql, if the SQL performance can improve further, I can deprecate my python driver and play data in PgSQL directly once my production setup is upgraded to RS6. 😀

So far, I have only tested Current data archive, the performance doesn’t seem very good (I have triggers setup on this table for testing).
I checked the source, it seem that the update is done in a line by line approach. I would suggest to do this with batch insertion into a temp table and batch update from that table in postgres instead of direct update line by line especially with ON CONFLICT clause. I am sure this would enable the module to work much more performantly even under heavy load.

This approach worked quite well for my python scripts when doing batch updates.

sql_mk_tbl = "CREATE TEMP TABLE temp1 ON COMMIT DROP AS SELECT o, e, t FROM (VALUES "
    
    for cnlnum, vlist in data.items():
        if cnlnum in self.fwd_cnls.keys():
            ret += f"({int(self.fwd_cnls[cnlnum])}, {int(vlist[0])}, '{ts:%Y-%m-%d %H:%M:%S}'::timestamp),"
    
ret = ret.rstrip(",") + " ) as x(o,e,t);"

sql = f"""-- BEGIN
-- send data in batch
{sql_mk_tbl}
-- update in batch
UPDATE utsys.obj_state SET p_ev = temp1.e, t = temp1.t from temp1 WHERE temp1.o = obj_state.p_obj;
COMMIT;
-- END"""

• This topic was modified 2 years, 2 months ago by zzz.

[Mikhail]

Hello,

Could you provide results of performance test in number of records and milliseconds?
For executing custom scripts, may be Database Export Module is better suited.

[Author]

Posts