In PostgreSQL: Creating a third table by joining columns of two table and new table rows should be updated in realtime

Another take: you can keep feeding all signals into one, common table and construct your "incremental materialized view" on the fly, in the same singular trigger which broadcasts notify to your python code.

create table table_all_signals (
    source_id int not null,
    signal_id text not null,
    signal_timestamp timestamp not null,
    value numeric not null );

create table signals_per_source_over_time(
  signal_timestamp timestamp unique );

Whenever you get a signal from any source, the trigger checks if it’s already collecting signals from that source and if not, adds the new source dynamically. The same trigger relays the incoming information to your python code via pg_notify().

create function f_signal_collector()returns trigger as $f$
declare target_value_column text:=concat_ws('_','source',new.source_id,'value');
        new_signals_per_source_over_time record;
begin 
  if not exists (select from information_schema.columns
             where table_schema='public'
             and table_name='signals_per_source_over_time'
             and column_name=target_value_column)
  then
     execute format ($dynamic_sql$
                     alter table public.signals_per_source_over_time 
                     add column %I numeric;
                     $dynamic_sql$, target_value_column);
  end if;
  execute format ($dynamic_sql$
                     insert into public.signals_per_source_over_time AS n
                     (signal_timestamp,%1$I)
                     values ($1,$2)
                     on conflict (signal_timestamp) do update
                     set %1$I=$2
                     returning n.*;
                  $dynamic_sql$, target_value_column)
           using new.signal_timestamp, new.value
           into new_signals_per_source_over_time;
  perform pg_notify('incoming_signals_feed', 
                     to_jsonb(new_signals_per_source_over_time)::text);
  return new;
end $f$ language plpgsql;

create trigger t_signal_collector after insert or update on table_all_signals
for each row execute function f_signal_collector();

Generating some sample signals:

select setseed(0.2);
insert into table_all_signals 
select random()*5,
       gen_random_uuid(),
       '2023-11-21 12:46:49.734534'::timestamp-'100 microseconds'::interval*random(),
       round((random()*1e3)::numeric,6)
from generate_series(1,1e3,1);

select * from table_all_signals order by signal_timestamp,source_id limit 6;

You can see they automatically populate your other table on the fly, adjusting its structure accordingly.

select * from signals_per_source_over_time order by signal_timestamp limit 5;

fiddle

1. The trigger keeps writing and updating your structure.
2. Each change is published via notify for your python code to listen to.
3. Note that the heavier a table grows, the longer alter table gets, so if you don’t ever need the full table, you can just keep a view for the purpose of looking up signals that match the new one’s timestamp, and feed that into notify.
4. You’ll get multiple notifications for the same timestamp, whenever a new signal matching an older timestamp arrives. Because of that, it would make more sense to relay just the timestamp, source and value in notify and assemble/update your output structure entirely in python.