Situation: I have a live trading script which computes all sorts of stuff every x minutes in my main thread (Python). the order sending is performed through such thread. the reception and execution of such orders though is a different matter as I cannot allow x minutes to pass but I need them as soon as they come in. I initialized another thread to check for such data (execution) which is in a database table (POSTGRES SQL).

Problem(s): I cannot continuosly perform query every xx ms, get data from DB, compare table length, and then get the difference for a variety of reasons (not only guy to use such DB, perforamnce issues, etc). so I looked up some solutions and came up with this thread (https://dba.stackexchange.com/questions/58214/getting-last-modification-date-of-a-postgresql-database-table) where basically the gist of it was that "There is no reliable, authorative record of the last modified time of a table".

Question: what can I do about it, that is: getting near instantenuous responses from a postgres sql table without overloading the whole thing using Python?

You can use notifications in postgresql:

import psycopg2
from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT
import selectdef dblisten(dsn):connection = psycopg2.connect(dsn)connection.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)cur = connection.cursor()cur.execute("LISTEN new_id;")while True:select.select([connection],[],[])connection.poll()events = []while connection.notifies:notify = connection.notifies.pop().payloaddo_something(notify)

and install a trigger for each update:

CREATE OR REPLACE FUNCTION notify_id_trigger() RETURNS trigger AS $$
BEGINPERFORM pg_notify('new_id', NEW.ID);RETURN new;
END;
$$ LANGUAGE plpgsql;CREATE TRIGGER data_modified AFTER insert or update on data_table for each row execute procedure notify_id_trigger();")