--- title: "Wiring PostgreSQL LISTEN/NOTIFY to a KMP Mobile Client: Real-Time Push Without WebSockets" published: true description: "Map pg_notify channels to KMP SharedFlows for real-time mobile push. Zero broker overhead, sub-100ms latency, with a reconnect strategy that survives flaky networks." tags: kotlin, android, mobile, postgresql canonical_url: https://mvpfactory.co/blog/postgres-listen-notify-kmp-sharedflow --- ## What We Will Build By the end of this tutorial, you will have a working real-time push pipeline from a PostgreSQL trigger through a Kotlin Multiplatform `SharedFlow` to your Android and iOS clients — with no WebSockets, no polling, and no message broker added to your stack. Let me show you a pattern I use in every project that needs real-time updates before the infrastructure bill gets out of hand. ## Prerequisites  - PostgreSQL 14+ (though this works back to 6.4) - A KMP project targeting Android and iOS - JDBC datasource on the JVM backend (HikariCP or similar) - Familiarity with Kotlin coroutines and `Flow`  ---  ## Step 1: Set Up the Database Trigger Here is the minimal setup to get this working on the Postgres side. This trigger fires on every insert into an `events` table and broadcasts the new row as JSON:


sql
CREATE OR REPLACE FUNCTION notify_event_insert()
RETURNS trigger AS $$
BEGIN
PERFORM pg_notify(
'event_channel',
row_to_json(NEW)::text
);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER on_event_insert
AFTER INSERT ON events
FOR EACH ROW EXECUTE FUNCTION notify_event_insert();

 Any client subscribed to `event_channel` receives the payload the moment the transaction commits. No polling interval, no separate write path. --- ## Step 2: Map the Channel to a KMP SharedFlow A `SharedFlow` fits naturally here — it is hot, it multicasts, and it survives subscriber churn without dropping the upstream connection. In `commonMain`:


kotlin
class PgNotifyChannel(private val channelName: String) {
private val _events = MutableSharedFlow(
replay = 0,
extraBufferCapacity = 64,
onBufferOverflow = BufferOverflow.DROP_OLDEST
)
val events: SharedFlow = _events.asSharedFlow()

suspend fun emit(payload: String) = _events.emit(payload)

}

 `DROP_OLDEST` is a deliberate choice here. A slow subscriber should never back-pressure the notification pipeline. Size `extraBufferCapacity` to your expected burst volume, not the average. --- ## Step 3: Hold the JDBC LISTEN Connection On the JVM backend, a dedicated coroutine owns the connection and forwards payloads into the flow:


kotlin
fun listenOnChannel(scope: CoroutineScope, channel: PgNotifyChannel) {
scope.launch(Dispatchers.IO + SupervisorJob()) {
val conn = dataSource.connection
conn.createStatement().execute("LISTEN ${channel.channelName}")
val pgConn = conn.unwrap(PGConnection::class.java)

 while (isActive) { val notifications = pgConn.getNotifications(1000) ?: continue notifications.forEach { channel.emit(it.parameter) } } }

}

 `SupervisorJob()` ensures a failure here does not cascade to the parent scope. For a long-lived connection managed alongside other application work, this matters. --- ## Step 4: Reconnect Without Thundering-Herd The docs do not mention this, but a persistent JDBC connection in `LISTEN` mode will die silently under network interruptions, Postgres restarts, or load balancer idle timeouts. Here is the gotcha that will save you hours: a blind retry loop causes a thundering-herd problem on outage recovery. Use exponential backoff with full jitter instead:


kotlin
suspend fun withReconnect(block: suspend () -> Unit) {
var attempt = 0
while (true) {
try {
block()
attempt = 0
} catch (e: Exception) {
val delay = minOf(30_000L, (500L shl attempt)) + Random.nextLong(500)
delay(delay)
attempt++
}
}
}

 On Android, gate reconnection attempts on `NetworkCallback`. On iOS, use `NWPathMonitor`. Expose both through `expect`/`actual` declarations and skip the backoff loop entirely while the device is offline — there is no point cycling through attempts when the path is not satisfied. --- ## Gotchas **Buffer overflow is silent by default.** `DROP_OLDEST` prevents back-pressure but means a slow consumer loses events. Instrument your `SharedFlow` buffer fill level under load before going to production. **Connection count is the real ceiling.** LISTEN/NOTIFY is latency-competitive with a WebSocket layer (~50–150ms) and beats polling (0–5s) while adding zero infrastructure. Redis Pub/Sub wins on raw latency (~5–20ms), but that tradeoff only matters once listener counts stress Postgres connection limits — typically in the tens of thousands. Benchmark your actual listener count before deploying a broker. **`unwrap` is driver-specific.** `conn.unwrap(PGConnection::class.java)` works with the `pgjdbc` driver. If you are using R2DBC or another driver, the notification polling API differs. --- ## Conclusion Most teams reach for a message broker before they have tested whether Postgres already does the job. `LISTEN/NOTIFY` has been in Postgres since version 6.4, costs nothing to run, and delivers latency that mobile users will not notice. Taking breaks to actually think this through helps too — I keep HealthyDesk running during long architecture sessions for exactly that reason. The three things worth getting right: benchmark `pg_notify` before deploying a broker, supervise the `LISTEN` connection with `SupervisorJob()` and gate reconnection on device connectivity, and use `SharedFlow` with `DROP_OLDEST` sized to burst volume — not average load. **Resources:** - [PostgreSQL NOTIFY docs](https://www.postgresql.org/docs/current/sql-notify.html) - [KMP SharedFlow docs](https://kotlinlang.org/api/kotlinx.coroutines/kotlinx-coroutines-core/kotlinx.coroutines.flow/-shared-flow/) - [pgjdbc PGConnection API](https://jdbc.postgresql.org/documentation/publicapi/org/postgresql/PGConnection.html)