Youre right, real-time is generally achieved by streaming CDC data with logical replication via a connector server to Kafka (Debezium does this) or a different queue to be picked up by consumers downstream.

Supabase does a great job of abstracting that part away with their real-time feature set