For a long time I've wondered if we could just invent an extension for Postgres that allow physically ordered, append-only tables.

The main two things that makes Postgres less suitable for Kafka-type logs is that tables aren't very efficient for sequentially ordered data, and that deletion incurs bloat until vacuumed. You could solve both by providing a new table engine (table access method), although I'm not sure you can control heap storage placement to the degree desired for a physically ordered table. But you could also do a lot of tricks to make it delete faster (append only means no updates; just prune from the head without MVCC when provably safe against concurrent reads?) and make filtering faster.

Kafka is of course more than that, but I bet you can get quite far with this.