[HN Gopher] Postgres LISTEN/NOTIFY does not scale
___________________________________________________________________
Postgres LISTEN/NOTIFY does not scale
Author : davidgu
Score : 547 points
Date : 2025-07-07 14:05 UTC (4 days ago)
(HTM) web link (www.recall.ai)
(TXT) w3m dump (www.recall.ai)
| hombre_fatal wrote:
| Interesting. What if you just execute `NOTIFY` in its own
| connection outside of / after the transaction?
| soursoup wrote:
| Isn't it standard practice to have a separate TCP stream for
| NOTIFY or am I mistaken
| remram wrote:
| You mean for LISTEN?
| nick_ wrote:
| My thought as well. You could add notify commands to a temp
| table during the transaction, then run NOTIFY on each row in
| that temp table after the transaction commits successfully?
| foota wrote:
| Wouldn't you need to then commit to remove the entries from
| the temp table?
| zbentley wrote:
| No, so long as the rows in there are transactionally
| guaranteed to be present or not, a sweeper script can
| handle removing failed "publishes" (notifys that didn't
| delete their row) later.
|
| This does sacrifice ordering and increases the risk of
| duplicates in the message stream, though.
| zbentley wrote:
| This is roughly the "transactional outbox" pattern--and an
| elegant use of it, since the only service invoked during the
| "publish" RPC is also the database, reducing distributed
| reliability concerns.
|
| ...of course, you need dedup/support for duplicate messages
| on the notify stream if you do this, but that's table stakes
| in a lot of messaging scenarios anyway.
| parthdesai wrote:
| You lose transactional guarantees if you notify outside of the
| transaction though
| hombre_fatal wrote:
| Yeah, but pub/sub systems already need to be robust to missed
| messages. And, sending the notify after the transaction
| succeeds usually accomplishes everything you really care
| about (no false positives).
| parthdesai wrote:
| What happens when transaction succeeds but the execution of
| NOTIFY fails if it's outside of transaction, in it's own
| separate connection?
| saltcured wrote:
| For reliability, you can make the recipient poll the
| table(s) of record for relevant state and use the out-of-
| band notification channel as a latency-reducer. So, the
| poller is eventually consistent at some configured
| polling interval, but opportunistically can respond much
| sooner when told to check again ahead of the next
| scheduled poll time.
|
| In my experience, this means you make sure the polling
| solution is complete and correct, and the notifier gets
| reduced to a wake-up signal. This signal doesn't even
| need to carry the actionable change content, if the
| poller can already pose efficient queries for whatever
| "new stuff" it needs.
|
| This approach also allows the poller to keep its own
| persistent cursor state if there is some stateful
| sequence to how it consumes the DB content. It
| automatically resynchronizes and the notification channel
| does not need to be kept in lock-step with the
| consumption.
| parthdesai wrote:
| fwiw - that's what Oban did for the most part. It sent a
| signal to a worker that there was a new job to pick up
| and work on. At scale, even that was an issue.
| valenterry wrote:
| > you can make the recipient poll the table(s) of record
| for relevant state
|
| That is tricky due to transactions and visibility. How do
| you write the poller to not miss events that were written
| by a long/blocked transaction? You'd have to set the
| poller scan to a long time (e.g. "process events that
| were written since now minus 5minutes") and then make
| sure transactions are cancelled hard before those
| 5minutes.
| Groxx wrote:
| The same thing that happens if the notified process dies
| suddenly.
|
| If you're not handling that, then whatever you're doing
| is unreliable either way.
| cheesekunator wrote:
| 98% of developers can't see it
| gwbas1c wrote:
| ... And working outside of the guarantee is harder,
| especially if you're in a "move fast and break things because
| we can fix it later" mode.
|
| Anyway, the article indicates that the fix was very simple
| and primarily in the application layer. Makes me wonder if
| someone was getting "creative" when they used LISTEN/NOTIFY.
| zerd wrote:
| That would make the locked time shorter, but it would still
| contend on the global lock, right?
| polote wrote:
| Rls and triggers dont scale either
| shivasaxena wrote:
| Yeah, I'm going to remove triggers in next deploy of a POS
| system since they are adding 10-50ms to each insert.
|
| Becomes a problem if you are inserting 40 items to order_items
| table.
| GuinansEyebrows wrote:
| that, and keeping your business logic in the database makes
| everything more opaque!
| lelanthran wrote:
| > that, and keeping your business logic in the database
| makes everything more opaque!
|
| Opaque to who? If there's a piece of business logic that
| says "After this table's record is updated, you _MUST_
| update this other table ", what advantages are there to
| putting that logic in the application?
|
| When (not if) some other application updates that record
| you are going to have a broken database.
|
| Some things are business constraints, and as such they
| should be moved into the database if at all possible. The
| application should _never_ enforce constraints such as
| "either this column or that column is NULL, but at least
| one must be NULL and both must never be NULL at the same
| time".
|
| Your database enforces constraints; what advantages are
| there to code the enforcement into every application that
| touches the database over simply coding the constraints
| into the database?
| thisoneisreal wrote:
| I think the dream is that business requirements are
| contained to one artifact and everything else responds to
| that driver. In an ideal world, it would be great to have
| databases care only about persistence and be able to swap
| them out based on persistence needs only. But you're
| right, in the real world the database is much better at
| enforcing constraints than applications.
| GuinansEyebrows wrote:
| you make good points; i'm overcorrecting from past
| trigger abuses :)
| brikym wrote:
| Have you tried deferring them?
| candiddevmike wrote:
| How do you handle trigger logic that compares old/new without
| having a round trip back to the application?
| SoftTalker wrote:
| Do it in a stored procedure not a trigger. Triggers have
| their place but a stored procedure is almost always better.
| Triggers can surprise you.
| candiddevmike wrote:
| I don't follow how you would do that in a stored
| procedure outside of a trigger.
| const_cast wrote:
| I think instead of performing an INSERT you call a stored
| proc that does the insert and some extra stuff.
| shivasaxena wrote:
| Yes, we already have all of our business logic in
| postgres functions(create_order, create_partial_payment
| etc).
|
| Doing the extra work in stored procedures is noticeably
| faster than relying on triggers.
| lelanthran wrote:
| > Yeah, I'm going to remove triggers in next deploy of a POS
| system since they are adding 10-50ms to each insert.
|
| Do you expect it to be faster to do the trigger logic in the
| application? Wouldn't be slower to execute two statements
| from the application (even if they _are_ in a transaction)
| than to rely on triggers?
| nine_k wrote:
| Hmm, imho, triggers do scale, they are just slow. But as you
| add more connections, partitionss, and CPUs, the slowness per
| operation remains constant.
| ants_a wrote:
| Triggers are not even particularly slow. They just hide the
| extra work that is being done and thus sometimes come back
| to bite programmers by adding a ton of work to statements
| that look like they should be quick.
| Spivak wrote:
| Neither do foreign keys the moment you need to shard. Turns out
| that there's no free lunch when you ask your database to do
| "secret extra work" that's supposed to be transparent-ish to
| the user.
| mulmen wrote:
| Does that only apply when you need to shard within tenants?
|
| If each tenant gets an instance I would call that a "shard"
| but in that pattern there's no need for cross-shard
| references.
|
| Maybe in the analytics stack but that can be async and
| eventually consistent.
| cpursley wrote:
| Right, plus there's character limitations (column size). This is
| why I prefer listening to the Postgres WAL for database changes:
|
| https://github.com/cpursley/walex?tab=readme-ov-file#walex
| (there's a few useful links in here)
| williamdclt wrote:
| I found recently that you can write directly to the WAL with
| transactional guarantees, without writing to an actual table.
| This sounds like it would be amazing for queue/outbox purposes,
| as the normal approaches of actually inserting data in a table
| cause a lot of resource usage (autovacuum is a major concern
| for these use cases).
|
| Can't find the function that does that, and I've not seen it
| used in the wild yet, idk if there's gotchas
|
| Edit: found it, it's pg_logical_emit_message
| cyberax wrote:
| One annoying thing is that there is no counterpart for an
| operation to wait and read data from WAL. You can poll it
| using pg_logical_slot_get_binary_changes, but it returns
| immediately.
|
| It'd be nice to have a method that would block for N seconds
| waiting for a new entry.
|
| You can also use a streaming replication connection, but it
| often is not enabled by default.
| williamdclt wrote:
| I think replication is the way to go, it's kinda what it's
| for.
|
| Might be a bit tricky to get debezium to decode the logical
| event, not sure
| cyberax wrote:
| Sure, but the replication protocol requires a separate
| connection. And the annoying part is that it requires a
| separate `pg_hba.conf` entry to be allowed. So it's not
| enabled for IAM-based connections on AWS, for example.
|
| pg_logical_slot_get_binary_changes returns the same
| entries as the replication connection. It just has no
| support for long-polling.
| gunnarmorling wrote:
| Debezium handles logical decoding messages OOTB. There's
| also an SMT (single message transform) for decoding the
| binary payload: https://debezium.io/documentation/referen
| ce/stable/transform....
| gunnarmorling wrote:
| pg_logical_emit_message() is how I recommend users on
| Postgres to implement the outbox pattern [1]. No table
| overhead as you say, no need for housekeeping, etc. It has
| some other cool applications, e.g. providing application-
| specific metadata for CDC streams or transactional logging,
| wrote about it at [2] a while ago. Another one is making sure
| replication slots can advance also if there's no traffic in
| the database they monitor [3].
|
| [1] https://speakerdeck.com/gunnarmorling/ins-and-outs-of-
| the-ou...
|
| [2] https://www.infoq.com/articles/wonders-of-postgres-
| logical-d...
|
| [3] https://www.morling.dev/blog/mastering-postgres-
| replication-...
| williamdclt wrote:
| Ha, your [2] is how I learnt about it! Thanks :)
| brightball wrote:
| You know, this would be a great talk at the 2026 Carolina
| Code Conference...
| cryptonector wrote:
| `pg_logical_emit_message()` is great and better than `NOTIFY`
| in terms of how it works, but...
|
| `pg_logical_emit_message()` perpetuates/continues the lack of
| authz around `NOTIFY`.
| williamdclt wrote:
| What do you mean by this? What authz would you expect/like?
| cryptonector wrote:
| I'd like to say that only some roles can NOTIFY to some
| channels. Similarly for alternatives to LISTEN/NOTIFY.
| denysonique wrote:
| For node.js users there is postgres.js that can listen to the
| Postgres WAL and emit node events that can be handled by
| application code.
| meesles wrote:
| Yeah until vendors butcher Postgres replication behaviors and
| prevent common paths of integrating these capabilities into
| other tools. Looking at you AWS
| CaliforniaKarl wrote:
| I appreciate this post for two reasons:
|
| * It gives an indication of how much you need to grow before this
| Postgres functionality starts being a blocker.
|
| * Folks encountering this issue--and its confusing log line--in
| the future will be able to find this post and quickly understand
| the issue.
| Gigachad wrote:
| Sounds like ChatGPT appreciated the post
| acdha wrote:
| If you think they're a bot, flag and move on. No need for a
| derail about writing style.
| jjgreen wrote:
| Just for the em-dashes? Some humans also use them.
| Gigachad wrote:
| It's also the fact it's just a summary of the post content
| without anything extra or any opinions.
| jjgreen wrote:
| Fair point
| TrackerFF wrote:
| A decent way to classify human vs bot when it comes to
| dashes, is that _all_ bots use 'em-dashes(--), while almost
| none use regular dashes (-) in writing. While plenty of
| humans will use regular dashes, because they won't bother
| to look for 'em-dashes on the keyboard, or phone.
|
| Of course, you have the people that correctly use em-
| dashes, too.
| cryptonector wrote:
| On iPhones the input methods turn -- into --. If you see
| me using em-dashes it's cause I wrote on an iPhone. But I
| prefer -- to --.
| tracker1 wrote:
| I've had it happen with various editors on the desktop as
| well. It's kind of annoying at times.
| yrds96 wrote:
| I'm ESL, so I often check my grammar on ChatGPT, and 99% of
| the time it includes em dashes in the corrected sentences,
| which I remove or just replace with commas or hyphens to
| sound more natural. So maybe this was not entirely written
| but just revised by ChatGPT.
| CaliforniaKarl wrote:
| I did not use ChatGPT--nor any AI--in writing the post. I'm
| curious, would you mind emailing--or replying--with what made
| you think that it was written by AI? Or why you do not
| believe my statement?
| h1fra wrote:
| You had one problem with listen notify which was a fair one, but
| now you have a problem with http latency, network issues, DNS,
| retries, self-DDoS, etc.
| GuinansEyebrows wrote:
| it sounds like the impact of LISTEN/NOTIFY scaling issues was
| much greater on the overall DB performance than the actual
| load/scope of the task being performed (based on the end of the
| article), and they're aware that if they needed something more
| performant for that offloaded task, they have options (pub/sub
| via redis or w/e).
| NightMKoder wrote:
| Facebook's wormhole seems like a better approach here - just
| tailing the MySQL bin log gets you commit safety for messages
| without running into this kind of locking behavior.
| mulmen wrote:
| Sounds like one centralized Postgres instance, am I understanding
| that correctly? Wouldn't meeting bots be very easy to parallelize
| across single-tenant instances?
| supportengineer wrote:
| LISTEN/NOTIFY isn't just a lock-free trigger. It can jeopardize
| concurrency under load.
|
| Features that seem harmless at small scale can break everything
| at large scale.
| edoceo wrote:
| It's true and folk should also choose the right tool at their
| scale and monitor it. There are plenty of cases where
| LISTEN/NOTIFY is the right choice.
|
| However, in 2025 I'd pick Redis or MQTT for this kind of role.
| I'm typically in multi-lamg environments. Is there something
| better?
| andrewstuart wrote:
| There's lots of ways to invoke NOTIFY without doing it from with
| the transaction doing the work.
|
| The post author is too focused on using NOTIFY in only one way.
|
| This post fails to explain WHY they are sending a NOTIFY. Not
| much use telling us what doesn't work without telling us the
| actual business goal.
|
| It's crazy to send a notify for every transaction, they should be
| debounced/grouped.
|
| The point of a NOTIFY is to let some other system know something
| has changed. Don't do it every transaction.
| 0xCMP wrote:
| Agreed, I am struggling to understand why "it does not scale"
| is not "we used it wrong and hit the point where it's a
| problem" here.
|
| Like if it needs to be very consistent I would use an unlogged
| table (since we're worried about "scale" here) and then `FOR
| UPDATE SKIP LOCKED` like others have mentioned. Otherwise what
| exactly is notify doing that can't be done after the first
| transaction?
|
| Edit: in-fact, how can they send an HTTP call for something and
| not be able to do a `NOTIFY` after as well?
|
| One possible way I could understand what they wrote is that
| somewhere in their code, within the same transaction, there are
| notifies which conditionally trigger and it would be difficult
| to know which ones to notify again in another transaction after
| the fact. But they must know enough to make the HTTP call, so
| why not NOTIFY?
| andrewstuart wrote:
| Agreed.
|
| They're using it wrong and blaming Postgres.
|
| Instead they should use Postgres properly and architect their
| system to match how Postgres works.
|
| There's correct ways to notify external systems of events via
| NOTIFY, they should use them.
| tomrod wrote:
| Assuming you skip select transaction, or require logging on it
| because your regulated industry had bad auditors, then every
| transaction changes something.
| thom wrote:
| Yeah, the way I've always used LISTEN/NOTIFY is just to tell
| some pool of workers that they should wake up and check some
| transactional outbox for new work. False positives are
| basically harmless and therefore don't need to be
| transactional. If you're sending sophisticated messages with
| NOTIFY (which is a reasonable thing to think you can do) you're
| probably headed for pain at some point.
| sorentwo wrote:
| Postgres LISTEN/NOTIFY was a consistent pain point for Oban
| (background job processing framework for Elixir) for a while. The
| payload size limitations and connection pooler issues alone would
| cause subtle breakage.
|
| It was particularly ironic because Elixir has a fantastic
| distribution and pubsub story thanks to distributed Erlang.
| That's much more commonly used in apps now compared to 5 or so
| years ago when 40-50% of apps didn't weren't clustered. Thanks to
| the rise of platforms like Fly that made it easier, and the
| decline of Heroku that made it nearly impossible.
| cpursley wrote:
| How did you resolve this? Did you consider listening to the
| WAL?
| sorentwo wrote:
| We have Postgres based pubsub, but encourage people to use a
| distributed Erlang based notifier instead whenever possible.
| Another important change was removing insert triggers,
| partially for the exact reasons mentioned in this post.
| MuffinFlavored wrote:
| > Another important change was removing insert triggers,
| partially for the exact reasons mentioned in this post.
|
| What did you replace them with instead?
| sorentwo wrote:
| In app notifications, which can be disabled. Our triggers
| were only used to get subsecond job dispatching though.
| parthdesai wrote:
| Distributed Erlang if application is clustered, redis if it
| is not.
|
| Source: Dev at one of the companies that hit this issue with
| Oban
| alberth wrote:
| I didn't realize Oban didn't use Mnesia (Erlang built-in).
| sorentwo wrote:
| Very very few applications use mnsesia. There's absolutely no
| way I would recommend it over Postgres.
| arcanemachiner wrote:
| I have heard the mnesia is very unreliable, which is a damn
| shame.
|
| I wonder if that is fixable, or just inherent to its
| design.
| sb8244 wrote:
| My understanding is that mnesia is sort of a relic.
| Really hard to work with and lots of edge / failure
| cases.
|
| I'm not sure if it should be salvaged?
| asg0451 wrote:
| can you explain why?
| spooneybarger wrote:
| Mnesia along with clustering was a recipe for split brain
| disasters a few years ago I assume that hasn't been
| addressed.
| ahoka wrote:
| I have only worked with a product that used it, so no
| direct experience, but one problem that was often
| mentioned is split-brains happening very frequently.
| tecleandor wrote:
| I think RabbitMQ still uses by default for its metadata
| storage. Is it problematic?
| schaum wrote:
| They are in the process of migrating away from it
| https://www.rabbitmq.com/docs/metadata-store
| nightpool wrote:
| What about Heroku made Erlang clustering difficult? It's had
| the same DNS clustering feature that Fly has, and they've had
| it since 2017: https://devcenter.heroku.com/articles/dyno-dns-
| service-disco....
| sorentwo wrote:
| The problem was with restrictive connections, not DNS based
| discovery for clustering. It wasn't possible (as far as I'm
| aware) to connect directly from one dyno to another through
| tcp/udp.
| nightpool wrote:
| That is not an issue when using Private Spaces, which have
| been available since 2015
| cshimmin wrote:
| If I understood correctly, the global lock is so that notify
| events are emitted in order. Would it make sense to have a
| variant that doesn't make this ordering guarantee if you don't
| care about it, so that you can "notify" within transactions
| without locking the whole thing?
| GuinansEyebrows wrote:
| possibly, but i think at that point it would make more sense to
| move the business logic outside of the database (you can wait
| for a successful commit before triggering an external process
| via the originating app, or monitor the WAL with an external
| pub/sub system, or something else more clever than i can think
| of).
| leontrolski wrote:
| I'd be interested as to how dumb-ol' polling would compare here
| (the FOR UPDATE SKIP LOCKED method
| https://leontrolski.github.io/postgres-as-queue.html). One day I
| will set up some benchmarks as this is the kind of thing people
| argue about a lot without much evidence either way.
|
| Wasn't aware of this AccessExclusiveLock behaviour - a reminder
| (and shameless plug 2) of how Postgres locks interact:
| https://leontrolski.github.io/pglockpy.html
| aurumque wrote:
| I'll take the shameless plug. Thank you for putting this
| together! Very helpful overview of pg locks.
| notarobot123 wrote:
| It's funny how "shameless plug" actually means "excuse the
| self-promotion" and implies at least a little bit of shame
| even when the reference is appropriate and on-topic.
| cpursley wrote:
| Have you played with pgmq? It's pretty neat:
| https://github.com/pgmq/pgmq
| edoceo wrote:
| Another thing for @leontrolski to add to the benchmarks -
| which I cannot wait to read.
| cpursley wrote:
| There's a pretty cool solution built on pgmq called pgflow:
|
| https://www.pgflow.dev/concepts/how-pgflow-works
| RedShift1 wrote:
| I use polling with back off up to one minute. So when a
| workload is done, it immediately polls for more work. If
| nothing found, wait for 5 seconds, still nothing 10 seconds,
| ... until one minute and from then on it polls every minute
| until it finds work again and the back off timer resets to 0
| again.
| singron wrote:
| Polling is the way to go, but it's also very tricky to get
| right. In particular, it's non-trivial to make a reliable queue
| that's also fast when transactions are held open and vacuum
| isn't able to clean tuples. E.g. "get the first available
| tuple" might have to skip over 1000s of dead tuples.
|
| Holding transactions open is an anti-pattern for sure, but it's
| occasionally useful. E.g. pg_repack keeps a transaction open
| while it runs, and I believe vacuum also holds an open
| transaction part of the time too. It's also nice if your
| database doesn't melt whenever this happens on accident.
| time0ut wrote:
| An approach that has worked for me is to hash partition the
| table and have each worker look for work in one partition at
| a time. There are a number of strategies depending on how you
| manage workers. This allows you to only consider 1/Nth of the
| dead tuples, where N is the number of partitions, when
| looking for work. It does come at the cost of strict
| ordering, but there are many use cases where strict ordering
| is not required. The largest scale implementation of this
| strategy that I have done had 128 partitions with a worker
| per partition pumping through ~100 million tasks per day.
|
| I also found LISTEN/NOTIFY to not work well at this scale and
| used a polling based approach with a back off when no work
| was found.
|
| Quite an interesting problem and a bit challenging to get
| right at scale.
| dfsegoat wrote:
| If there were a toy or other public implementation of this,
| I would love to see it.
| j16sdiz wrote:
| Can't change the number of partition dynamically.
|
| Additional challenge if jobs comes in funny sizes
| AlisdairO wrote:
| Depending on exactly what you need, you can often fake
| this with a functional index on mod(queue_value_id,
| 5000). You then query for mod(queue_value_id,5000)
| between m and n. You can then dynamically adjust the gap
| between m and n based on how many partitions you want
| CBLT wrote:
| This is how Kafka does it. Kafka has spent years working on
| the rough edges (e.g. partition resizing), haven't used it
| recently though.
| leontrolski wrote:
| > also fast when transactions are held open
|
| In my linked example, on getting the item from the queue, you
| immediately set the status to something that you're not
| polling for - does Postgres still have to skip past these
| tuples (even in an index) until they're vacuumed up?
| atombender wrote:
| Dead tuples is a real and significant problem, not just
| because it has to skip the tuples, but because the statistics
| that drive the planner don't account for them.
|
| I found this out the hard way when I had a simple query that
| suddenly got very, very slow on a table where the application
| would constantly do a `SELECT ... FOR UPDATE SKIP LOCKED` and
| then immediately delete the rows after a tiny bit of
| processing.
|
| It turned out that with a nearly empty table of about 10-20k
| dead tuples, the planner switched to using a different index
| scan, and would overfetch tons of pages just to discard them,
| as they only contained dead tuples. What I didn't realize is
| that the planner statistics doesn't care about dead tuples,
| and ANALYZE doesn't take them into account. So the planner
| started to think the table was much bigger than it actually
| was.
|
| It's really important for these uses cases to tweak the
| autovacuum settings (which can be set on a per-table basis)
| to be much more aggressive, so that under high load, the
| vacuum runs pretty much continuously.
|
| Another option is to avoid deleting rows, but instead use a
| column to mark rows as complete, which together with a
| partial index can avoid dead tuples. There are both pros and
| cons; it requires doing the cleanup (and VACUUM) as a
| separate job.
| singron wrote:
| Unfortunately, updating the row also creates dead tuples.
| It's very tricky!
| atombender wrote:
| It does, but because of how indexes work, I believe it
| won't be skewed by the presence of dead tuples (though
| the bloat can cause the live dat to be spread across a
| lot more blocks and therefore generate more I/O) as long
| as you run autoanalyze semi-regularly.
| singron wrote:
| It depends on if you are getting Heap Only Tuples (HOT)
| updates or not.
| https://www.postgresql.org/docs/current/storage-hot.html
|
| In this case, you might have enough dead tuples across
| your heap that you might get a lot of HOT updates. If you
| are processing in insertion order, you will also probably
| process in heap order, and you can actually get 0 HOT
| updates since the other tuples in the page aren't fully
| dead yet. You could try using a lower fillfactor to avoid
| this, but that's also bad for performance so it might not
| help.
| atombender wrote:
| If you have a "done" column that you filter on using a
| partial index, then it would never use HOT updates
| anyway, since HOT requires that none of the modified
| columns have an index.
| menthe wrote:
| False.
|
| As of PG16, HOT updates are tolerated against summarizing
| indexes, such as BRIN.
|
| https://www.postgresql.org/docs/16/storage-hot.html
|
| Besides, you probably don't want "done" jobs in the same
| table as pending or retriable jobs - as you scale up, you
| likely want to archive them as it provides various
| operational advantages, at no cost.
| atombender wrote:
| Not false. Nobody would ever use BRIN for this. I'm
| talking about regular indexes, which do prevent HOT.
|
| If you read my earlier comment properly, you'll notice a
| "done" column is to avoid deleting columns on the hot
| path and avoid dead tuples messing up the planner. I
| agree that a table should not contain done jobs, but then
| you risk running into the dead tuple problem. Both
| approaches are a compromise.
| TkTech wrote:
| With that experience behind you, would you have feedback for
| Chancy[1]? It aims to be a batteries-included offering for
| postgres+python, aiming for hundreds of millions of jobs a day,
| not massive horizontal worker scaling.
|
| It both polls (configurable per queue) and supports
| listen/notify simply to inform workers that it can wake up
| early to trigger polling, and this can be turned off globally
| with a notifications=false flag.
|
| [1]: https://github.com/tktech/chancy
| qianli_cs wrote:
| My colleague did some internal benchmarking and found that
| LISTEN/NOTIFY performs well under low to moderate load, but
| doesn't scale well with a large number of listeners. Our
| findings were pretty consistent with this blog post.
|
| (Shameless plug [1]) I'm working on DBOS, where we implemented
| durable workflows and queues on top of Postgres. For queues, we
| use FOR UPDATE SKIP LOCKED for task dispatch, combined with
| exponential backoff and jitter to reduce contention under high
| load when many workers are polling the same table.
|
| Would love to hear feedback from you and others building
| similar systems.
|
| [1] https://github.com/dbos-inc/dbos-transact-py
| mind-blight wrote:
| Nice! I'm using DBOS and am a little active on the discord. I
| was just wondering how y'all handled this under the hood.
| Glad to hear I don't have to worry much about this issue
| eatonphil wrote:
| Why not read the WAL?
| qianli_cs wrote:
| We considered using WAL for change tracking in DBOS, but it
| requires careful setup and maintenance of replication
| slots, which may lead to unbounded disk growth if
| misconfigured. Since DBOS is designed to bolt onto users'
| existing Postgres instances (we don't manage their data),
| we chose a simpler, less intrusive approach that doesn't
| require a replication setup.
|
| Plus, for queues, it's so much easier to leverage database
| constraints and transactions to implement global
| concurrency limit, rate limit, and deduplication.
| sorentwo wrote:
| Ping requires something persistent to check. That requires
| creating tuples, and most likely deleting them after they've
| been consumed. That puts pressure on the database and requires
| vacuuming in ways that pubsub doesn't because it's entirely
| ephemeral.
|
| Not to mention that pubsub allows multiple consumers for a
| single message, whereas FOR UPDATE is single consumer by
| design.
| broken_broken_ wrote:
| I have implemented polling against a cluster of mixed
| mariadb/mysql databases which do not offer listen/notify. It
| was a pain in the neck to get right.
|
| - The batch size needs to be adaptative for performance,
| latency, and recovering smoothly after downtime.
|
| - The polling timeouts, frequency etc the same.
|
| - You need to avoid hysteresis.
|
| - You want to be super careful about not disturbing the main
| application by placing heavy load on the database or
| accidentally locking tables/rows
|
| - You likely want multiple distributed workers in case of a
| network partition to keep handling events
|
| It's hard to get right especially when the databases at the
| time did not support SKIP LOCKED.
|
| In retrospect I wish I had listened to the WAL. Much easier.
| cryptonector wrote:
| Instead of LISTEN/NOTIFY you could listen to the wal / logical
| replication stream.
|
| Or you could have a worker whose only job is to listen to the
| wal / logical replication stream and then NOTIFY. Being the
| only one to do so would not burden other transactions.
|
| Or you could have a worker whose only job is to listen to the
| wal / logical replication stream and then publish on some non-
| PG pubsub system.
| shivasaxena wrote:
| Out of curiosity: Would appreciate if others can share what other
| things like AccessExclusiveLock should postgres users beware of?
|
| What I already know
|
| - Unique indexes slow inserts since db has to acquire a full
| table lock
|
| - Case statements in Where break query planner/optimizer and
| require full table scans
|
| - Read only postgres functions should be marked as `STABLE
| PARALLEL SAFE`
| franckpachot wrote:
| Can you provide more details? Inserting with unique indexes do
| not lock the table. Case statements are ok in where clause, use
| expression indexes to index it
| hans_castorp wrote:
| > Unique indexes slow inserts since db has to acquire a full
| table lock
|
| An INSERT never results in a full table lock (as in "the lock
| would prevent other inserts or selects on the table)
|
| Any expression used in the WHERE clause that isn't indexed will
| probably result in a Seq Scan. CASE expressions are no
| different than e.g. a function call regarding this.
|
| A stable function marked as "STABLE" (or even immutable) can be
| optimized differently (e.g. can be "inlined"), so yes that's a
| good recommendation.
| 1a527dd5 wrote:
| https://pglocks.org/?pglock=AccessExclusiveLock is my go to
| reference.
|
| My other reference for a slightly different problem is
| https://www.thatguyfromdelhi.com/2020/12/what-postgres-sql-c...
| cellis wrote:
| It does scale. Just not to recall levels of traffic. Come on guys
| let's not rewrite everything in cassandra and rust now.
| dumbfounder wrote:
| Transactional databases are not really the best tool for writing
| tons of (presumably) immutable records. Why are you using it for
| this? Why not Elastic?
| incoming1211 wrote:
| Because transactional databases are perfectly fine for this
| type of thing when you have 0 to 100k users.
| 0xbadcafebee wrote:
| The total number of users in your system is not a performance
| characteristic. And transactions are generally wrong for
| write-heavy anything. Further, if you can just append then
| the transaction is meaningless.
| Kwpolska wrote:
| [citaiton needed]
| anonu wrote:
| was hoping the solution was: we forked postgres.
|
| cool writeup!
| threecheese wrote:
| I had a similar thought, as I was clicking through to TFA;
| "NOTIFY does not scale, but our new Widget can! Just five
| bucks"
| randall wrote:
| wow thanks for the heads up! no idea this was a thing.
| wordofx wrote:
| It's not a thing.
| randall wrote:
| i don't understand. is the serialized write global lock a
| thing or no?
| 0xbadcafebee wrote:
| RBDMS are not designed for write-heavy applications, they are
| designed for read-heavy analysis. Also, an RDBMS is not a message
| queue or an RPC transport.
|
| I feel like somebody needs to write a book on system architecture
| for Gen Z that's just filled with memes. A funny cat pic telling
| people not to use the wrong tool will probably make more of an
| impact than an old fogey in a comment section wagging his finger.
| hombre_fatal wrote:
| But those rules of thumb aren't true. People use Postgres for
| job queues and write-heavy applications.
|
| You'd have to at least accompany your memes with empirics. What
| is write-heavy? A number you might hit if your startup succeeds
| with thousands of concurrent users on your v1 naive
| implementation?
|
| Else you just get another repeat of everyone cargo-culting
| Mongo because they heard that Postgres wasn't web scale for
| their app with 0 users.
| 0xbadcafebee wrote:
| There are lots of ways to empirically tell what solutions are
| right for what applications. The simplest is using basic
| computer science like applying big-O notation, or using
| something designed as a message queue to do message queueing,
| etc. Slightly more complicated are simple benchmarks with
| immutable infrastructure.
| kccqzy wrote:
| There are OLTP and OLAP RDBMSes. Only OLAP ones are designed
| for read-heavy analyses.
| const_cast wrote:
| People have been using RDBMS' for write-heavy workflows for
| forever. Some people even use stored procs or triggers for
| getting complicated write operations to work properly.
|
| Databases can do _a lot_ of stuff, and if you 're not hurting
| for DB performance it can be a good idea to just... do it in
| the database. The advantage is that, if the DB does it, you're
| much less likely to break things. Putting data constraints in
| application code can be done, but then you're just waiting for
| the day those constraints are broken.
| 0xbadcafebee wrote:
| That is the same logic that led every failed design I've seen
| in my career to take months (if not years) and tons of money
| to fix. "YOLO engineering" is simple at first and a huge pain
| in the ass later. Whereas actually correct engineering is
| slightly painful at first and saves your ass later.
|
| The people who design it walk away after a few years, so they
| don't give a crap what happens. The rest of us have to
| struggle to support or try to replace whatever the lumbering
| monstrosity is.
| doc_manhat wrote:
| Got up to the TL;DR paragraph. This was a major red flag given
| the initial presentation of the discovery of a bottleneck:
|
| ''' When a NOTIFY query is issued during a transaction, it
| acquires a global lock on the entire database (ref) during the
| commit phase of the transaction, effectively serializing all
| commits. '''
|
| Am I missing something - this seems like something the original
| authors of the system should have done due diligence on before
| implementing a write heavy work load.
| kccqzy wrote:
| I think it's just difficult to predict how heavy is heavy
| enough to make this a problem. FWIW I had worked at a startup
| with a much more primitive data storage system where serialized
| commits were actually totally fine. The startup never outgrew
| that bottleneck.
| whatevaa wrote:
| You don't know how heavy it will be in new systems. As another
| commenter mentioned, you might never reach that point. Simplier
| is always better.
| Someone wrote:
| If "doing due diligence" involves reading the source code of a
| database server to verify a design, I doubt many people writing
| such systems do due diligence.
|
| The documentation doesn't mention any caveats in this
| direction, and they had 3 periods of downtime in 4 days, so I
| don't think it's a given that testing would have hit this
| problem.
| callamdelaney wrote:
| My kneejerk reaction to the headline is 'why would it?'.
|
| It's unsurprising to me that an AI company appears to have chosen
| exactly the wrong tool for the job.
| bravesoul2 wrote:
| Yeah I have no idea whether it would. But I'd load test it if
| it needed to scale.
|
| SQS may have been a good "boring" choice for this?
| kristianc wrote:
| Sounds like a deliberate attempt to avoid spinning up Redis,
| Kafka, or an outbox system early on.. and then underestimated
| how quickly their scale would make it blow up. Story as old as
| time.
| j16sdiz wrote:
| Kafka head of line blocking sucks.
| chrnola wrote:
| Guaranteeing order has its tradeoffs.
|
| There is work happening currently to make Kafka behave more
| like a queue: https://cwiki.apache.org/confluence/display/K
| AFKA/KIP-932%3A...
| LgWoodenBadger wrote:
| Isn't this one of the things partitioning is meant to
| ameliorate? Either through partitions themselves, or
| through an appropriate partitioning strategy?
| const_cast wrote:
| I find the opposite story more true: additional complexity in
| the form of caching early, for a scale that never comes. I've
| worked on one too many sprawling, distributed systems with
| too little users to justify it.
| physix wrote:
| "Sprawling distributed systems".
|
| I like that. Sounds like a synonym for "Platform
| Engineering". :-)
|
| I remember being amazed that lambda architecture was
| considered a kind of reference, when it looked to me more
| like a workaround.
|
| We like to build IT cathedrals, until we have to run them.
| const_cast wrote:
| If there's one thing I took away from school, it's that
| distributed systems are _hard_. More failure points and
| much more communication hops. Serialization into
| deserialization into serialization again over network
| hops.
| oulipo wrote:
| Not sure I get it... how would you replicate this
| functionality with Kafka? You'd still need to have the
| database LISTEN to changes and push it to Kafka no?
| v5v3 wrote:
| Better to be successful with simple tech and have a minor
| 'blow up', then over engineer and go bust.
| TheTaytay wrote:
| Because documentation doesn't warn about this well-loved
| feature effectively ruins the ability to perform parallel
| writes, and because everything else in Postgres scales well.
|
| I think it's a reasonable assumption. Based on the second half
| of your comment, you clearly don't think highly of "AI
| companies," but I think that's a separate issue.
| to11mtm wrote:
| Seriously people just layer shit with NATS for pubsub after
| persist and make sure there's a proper way to place a 'on restart
| recoonect' thing.
| caleblloyd wrote:
| Amen! NATS is how we do AI streaming! JetStream subject per
| thread with an ordered consumer on the client.
| freeasinbeer2 wrote:
| Am I supposed to be able to tell from these graphs that one was
| faster than the other? Because I sure can't.
|
| What were the TPS numbers? What was the workload like? How big is
| the difference in %?
| deadbabe wrote:
| Honestly this article is ridiculous. Most people do not have tens
| of thousands of concurrent writers. And most applications out
| there are read heavy, not write. Which means you probably have
| read replicas distributing loads.
|
| _Use_ LISTEN /NOTIFY. You will get a lot of utility out of it
| before you're anywhere close to these problems.
| acdha wrote:
| I would phrase this as "know where your approach hits scaling
| walls". You're right that many people never need more than
| LISTEN/NOTIFY but the reason that advice became so popular was
| the wave of people who had jumped straight into running some
| complicated system like Kafka when they hadn't done any
| analysis to justify it; it would be nice if the lesson we
| taught was that you should do some analysis rather than just
| picking one popular option.
| maxdo wrote:
| What a discovery , even Postgres itself doesn't scale easy. There
| are so many solutions that are dedicated and cost you less.
| sleepy_keita wrote:
| LISTEN/NOTIFY was always a bit of a puzzler for me. Using it
| means you can't use things like pgbouncer/pgpool and there are so
| many other ways to do this, polling included. I guess it could be
| handy for an application where you know it won't scale and you
| just want a simple, one-dependency database.
| nightfly wrote:
| > I guess it could be handy for an application where you know
| it won't scale and you just want a simple, one-dependency
| database
|
| That's where we use it at my work. We have host/networking
| deployment pipelines that used to have up to one minute latency
| on each step because each was ran on a one-minute cron. A short
| python script/service that handled the LISTENing + adding
| NOTIFYs when the next step was ready removed the latency and
| we'll never do enough for the load on the db to matter
| valenterry wrote:
| How about using a service that runs continuously and brings
| it's own pool? So basically all Java/JVM based solutions that
| use something like HiKariCP.
| nhumrich wrote:
| You can setup notify to run as a trigger on an events table.
| The job that listens shouldn't need a pool, it's a long lived
| connection anyway. Now you can keep using pgbouncer everywhere
| else.
| spoaceman7777 wrote:
| This is part of the basis for Supabase offering their realtime
| service, and broadcast, rather than supporting native
| LISTEN/NOTIFY. The scaling issues are well known.
| osigurdson wrote:
| I like this article. Lots of comments are stating that they are
| "using it wrong" and I'm sure they are. However, it does help to
| contrast the much more common, "use Postgres for everything" type
| sentiment. It is pretty hard to use Postgres wrong for relational
| things in the sense that everyone knows about indexes and so on.
| But using something like L/N comes with a separate learning curve
| anyway - evidenced in this case by someone having to read
| comments in the Postgres source code itself. Then if it turns out
| that it cannot work for your situation it may be very hard to
| back away from as you may have tightly integrated it with your
| normal Postgres stuff.
|
| I've landed on Postgres/ClickHouse/NATS since together they
| handle nearly any conceivable workload managing relational,
| columnar, messaging/streaming very well. It is also not painful
| at all to use as it is lightweight and fast/easy to spin up in a
| simple docker compose. Postgres is of course the core and you
| don't always need all three but compliment each other very well
| imo. This has been my "go to" for a while.
| goodkiwi wrote:
| I've been meaning to check out NATS - I've tended to default to
| Redis for pubsub. What are the main advantages? I use
| clickhouse and Postgres extensively
| sbstp wrote:
| I've been disappointed by Nats. Core Nats is good and works
| well, but if you need stronger delivery guarantees you need
| to use Jetstream which has a lot of quirks, for instance it
| does not integrate well with the permission system in Core
| Nats. Their client SDKs are very buggy and unreliable. I've
| used the Python, Rust and Go ones, only the Go one worked as
| expected. I would recommend using rabbitmq, Kafka or redpanda
| instead of Nats.
| chatmasta wrote:
| Are those recommendations based on using them all in the
| same context? Curious why you chose Kafka (or Redpanda
| which is effectively the same) over NATS.
| PaoloBarbolini wrote:
| I've had the same experience and I fixed part of the
| problem by writing my own Rust client, Watermelon. It's
| still missing a lot of features but at least I'm not
| blocked by weird decisions taken by upstream.
| FZambia wrote:
| Client SDKs are often a major challenge in systems like
| these. In my experience, building SDKs on top of
| asynchronous protocols is particularly tricky. It's
| generally much easier to make the server-side part
| reliable. The complexity arises because SDKs must account
| for a wide range of usage patterns - and you are not
| controlling the usage.
|
| Asynchronous protocols frequently result in callback-based
| or generator-style APIs on the client side, which are hard
| to implement safely and intuitively. For example, consider
| building a real-time SDK for something like NATS. Once a
| message arrives, you need to invoke a user-defined callback
| to handle it. At that point, you're faced with a design
| decision: either call the callback synchronously (which
| risks blocking the socket reading loop), or do it
| asynchronously (which raises issues like backpressure
| handling).
|
| Also, SDKs are often developed by different people, each
| with their own design philosophy and coding style, leading
| to inconsistency and subtle bugs.
|
| So this isn't only about NATS. Just last week, we ran into
| two critical bugs in two separate Kafka SDKs at work.
| osigurdson wrote:
| NATS gives you regular pub/sub but also streams as well
| (similar to Kafka along with strong durability guarantees,
| etc).
| fathomdeez wrote:
| This kind of issue always comes up when people put business
| logic inside the database. Databases are for data. The data
| goes in and the data goes out, but the data does not get to
| decide what happens next based on itself. That's what
| application code is for.
| bevr1337 wrote:
| > the data does not get to decide what happens next based on
| itself.
|
| Then why bother with a relational database? Relations and
| schemas are business logic, and I'll take all the data
| integrity I can get.
| Jailbird wrote:
| I've seen both of these philosophies. I liken them to
| religions, the believers are devout. Code is King vs the DB
| is King.
|
| I'm personally Code is King, and I have my reasons (like
| everyone else)
| IgorPartola wrote:
| I am mostly on the side of business logic should live in
| applications and relationships between data types are not
| business logic so much as just the layout of the data.
| But I typically access data via an ORM and they typically
| don't have support for triggers and stored procedures. If
| they did, I would certainly use it because projects I
| work on might have multiple people writing application
| code but everyone uses a single set of database models.
| This would mean that critical constraints on the shape of
| the data could be defined and respected at all times vs
| some developer on my team forgetting to include some
| critical check in their data update routine.
| sgarland wrote:
| Every ORM I'm aware of allows you to drop down to raw
| SQL. Write your stored procedure, store it in VCS, add it
| as a migration, and then call it. If you want to make it
| friendlier, wrap the call in a function in your language
| so you can add helpers, better error handling, etc.
| IgorPartola wrote:
| What I would prefer is integration at the model
| definition level. For example let's say that I have a
| Customer model and an Order model. I don't always want to
| pull in the customer fields when listing orders. Most
| ORMs would allow me to create a join and specify the
| field from Customer I want when fetching Orders but those
| joins add up quickly. I could denormalize the data and
| put things like the customer name and email onto each
| order but if the customer changes either value now the
| application code has to remember to update it. And yes I
| could put that in the model's save() method but that is
| fragile too because what if someone else does run code
| that updates stuff at the raw SQL level and doesn't
| include these updates.
|
| Now if I could specify that I want Order.customer_name to
| come from a specific other model and be updated
| automatically the ORM could automatically create a
| trigger to update that field when the customer table is
| updated.
|
| Obviously this is a very simplistic example but there are
| many more, including versioning and soft deletes that
| could be incredibly useful. But the key is that the ORM
| has to generate the code for the triggers and stored
| procedures. Doing that manually is possible now but (a)
| uses a different language even than regular SQL which not
| everyone is familiar with, and (b) there is no type
| checking for what you are doing. The ORM model
| definitions are the main source of truth about the shape
| of your database, so I want to use them as such.
| ako wrote:
| It's really not about code is better or database it
| better, it's mostly about locality: if you want to update
| thousands of records, you can't pull those records into a
| separate process, update them there and then write back.
| So you put your code next to the data in the database.
| Stored procedures are just code deployed to a database
| container...
| 0xFEE1DEAD wrote:
| Sure you can, I've done it plenty of times. I'm genuinely
| curious why you think it's not possible.
|
| The only reasons I can think of:
|
| - you're rewriting a legacy system and migrate parts
| incrementally
|
| - data compliance
|
| - you're running a dangerous database setup
|
| I try my best to avoid putting any business logic inside
| databases and see stored procedures only as a temporary
| solution.
| bevr1337 wrote:
| Although I'm partial to a SPROC, I do not deploy them
| because I understand my colleagues might throw me from a
| window. But without going full tilt DB-as-the-
| application,
|
| The DB can make much stronger guarantees about
| transactions and updates the closer that logic happens to
| itself. In the world of cloud computing, this can be a
| cost savings for ingress/egress too.
| whstl wrote:
| And both of those philosophies will lead to bad
| engineering.
|
| There are things that work better, are safer and simpler
| to do on the database, and things that work better, are
| safer and simpler in code. And those things might change
| depending on context, technology, requirements, size of
| project, experience of contributors, etc.
|
| Forcing round pegs into square holes will always lead to
| brittle code and brittle products, often for more cost
| (mental and financial!) than actually using each tool
| correctly.
| dotancohen wrote:
| I believe that both code and data are kings, under
| different realms. Code is king of the "what we're doing
| today" realm. Data is king of the "what's possible
| tomorrow" realm.
|
| Both have their place in business.
| sgarland wrote:
| Every company I've been at that relied on application
| code to handle referential integrity had orphaned rows,
| and incidents related to data errors or the absurd
| pipelines they had built to recreate what FK constraints
| and triggers already do.
|
| RDBMS are extremely well-tested pieces of software that
| do their job incredibly well. To think that you could do
| better, or even equally as well, is hubris. If you want
| to trade those guarantees for "velocity" go right ahead,
| but you also need to take into account the inevitable
| incidents and recoveries that will occur.
| jl6 wrote:
| I think an argument can be made that relations, schemas and
| constraints encode a kind of business logic that is
| _intrinsic_ to the definition and integrity of the data,
| while other types of business logic represent _processes_
| that may hinge on data but aren't as tightly coupled to it.
| Similar to the difference between a primitive type and a
| function.
|
| I guess some will argue that their business logic is
| special and really is so tightly coupled to the data
| definition that it belongs in the database, and I'm not
| going to claim those use cases don't exist, but I've seen
| over-coupling far more often than under-coupling.
|
| This is why I say: Applications come and go, but data is
| forever.
| panzi wrote:
| So what are your thoughts on constraints then? Foreign keys?
| Should that only be handled by the application, like Rails
| does (or did, haven't used in a long time).
| fathomdeez wrote:
| I don't think of those as business logic, per se. They're
| just validity checks on what the data should look like
| before it's written to disk - they're not actionable in the
| way L/N is. That being said, constraints usually end up
| being duplicated outside the db anyway, but having them
| where the data rests (so you don't have to assume every
| client is using the correct constraint code) makes sense.
| panzi wrote:
| I see. Further I have used triggers to automatically
| populate log tables or aggregate statistics on write. Why
| do I need fast statistics? For API limits. Customers have
| N free operations per months and such, so I have to query
| that on every operation. Do you consider these things as
| business logic that don't belong in the database?
| Footkerchief wrote:
| You still use constraints even if you put all your business
| logic in stored procedures.
| Lio wrote:
| Rails fully supports constraints and encourages you to use
| them.
|
| You can either execute SQL in your migration or use
| add_check_constraint.
| panzi wrote:
| Back when I used Rails the sentiment was: You don't need
| foreign keys, this is all handled by ActiveRecord.
| parthdesai wrote:
| What happens to FKs when you've to partition/shard the db?
| At a certain scale, they actually hinder the inserts.
| sgarland wrote:
| FK Constraints on partitioned tables has been a solved
| problem for years for Postgres. MySQL still doesn't
| support them, unfortunately.
|
| For sharding, Vitess kind of supports them; Citus fully
| supports them.
|
| You're correct that they do impact performance to an
| extent, but as a counter argument, if your data is
| incorrect, it doesn't matter how quickly you wrote it.
| cryptonector wrote:
| FKs are nothing special. It's just more
| INSERTs/UPDATEs/DELETEs. If you can't have a few more
| DMLs in your transactions in your sharded DB then you've
| already got scaling problems.
|
| Really, FKs are typically implemented internally by
| RDBMSes as TRIGGERs that do what you expect FKs to do,
| which means they really are nothing more than syntactic
| sugar.
| platzhirsch wrote:
| If you want your database to just store bytes, use a key-
| value store. But SQL gives you schemas and constraints for a
| reason; they're guardrails for your business logic. Just
| don't ask your tables to run the business for you.
| IgorPartola wrote:
| If only different ORMs had more support for triggers and
| stored procedures. Things would be so much easier if I
| could do things like denormalize certain frequently
| accessed fields across tables but with proper ability to
| update them automatically without having to do them in
| application code.
| cryptonector wrote:
| ORMs are crutches. You don't need them if you're able-
| bodied. Just ditch them. Just say no to ORMs.
| tracker1 wrote:
| I largely agree.. though data mapper libraries (such as
| Dapper for .Net) can be pretty helpful, even if there's a
| minor disconnect from the SQL used and the POCO/Record
| definitions used... It's far simpler than most ORMs and
| keeps you a little closer to the DB.
| IgorPartola wrote:
| I used to think like this, but over the past decade and a
| half they have gotten a lot more performant and usable
| and the speed with which you can develop using them is
| just unmatched by writing raw SQL. Again, I say this as
| someone who used to be very much team just write SQL and
| even created a quasi-ORM that allowed me to write all the
| queries by hand but returned model instances that could
| have methods as a sort of in-between solution. I still
| routinely use raw SQL but only when it is actually
| necessary.
| physix wrote:
| That may hold to a certain extent for relational databases
| where your business model doesn't align well with physical
| model (tables). Although you might wonder why stored
| procedures and triggers were invented.
|
| In databases where your domain is also your physical data
| model, coupling business logic to the database can work quite
| well, if the DBMS supports that.
|
| https://medium.com/@paul_42036/entity-workflows-for-event-
| dr...
| chatmasta wrote:
| The first thing I did when I saw this article was to check
| the Postgres docs, because I thought "heh, surely they just
| didn't read the fine print," but the LISTEN/NOTIFY page has
| zero mentions of "lock" in the entire content.
| perlgeek wrote:
| I really hope somebody reading this article (or HN thread)
| writes a doc patch to mention that.
|
| I'm unlikely to get it myself today, and by tomorrow I've
| probably already forgotten it :-(
| Cthulhu_ wrote:
| > and by tomorrow I've probably already forgotten it :-(
|
| You're self-aware and are writing about it, why not
| maintain and add it to your todo list if this is a
| recurring issue?
| dathinab wrote:
| I think, It's because the locking is part of the
| transaction commit locking, but yes it should be mentioned.
|
| But it's oversimplified a case of "high queue load f* up
| the availability/timings for other DB operations" (and
| themself).
|
| And thats a generic problem you have, even if just due to
| "generic CPU/WAL/disk load" if you put your queue into your
| DB even iff that specific lock would be somehow solved with
| some atomic concurrent algorithms or similar (not sure if
| that even is possible).
|
| So in general make your storage db, and queue a different
| service (and you cache too), even if it uses the same kind
| of storage. (Through technically there are clever in-
| between solutions which run their own queue service but
| still use you DB for final storage but have a ton of
| caching, in memory locking etc. to remove a huge part of
| the load from the DB. )
| cryptonector wrote:
| One can replace LISTEN/NOTIFY with logical replication /
| CDC. And it's funny because _somehow_ , somewhere, PG must
| be serializing the writing of the WAL to some degree. So
| it's not clear to me why LISTEN/NOTIFY needs additional
| serialization. Perhaps PG should turn NOTIFY into INSERTs
| on a special table that a worker process watches and turns
| those inserts into notifies (and deletes the inserts).
| tsimionescu wrote:
| The way you model data and store it in your database is
| fundamentally a part of your business logic. The same data
| can be modeled in many different ways, with different trade-
| offs for different use cases. Especially if you have a large
| amount of data, you can't just work with it as is, you need
| to know how you will use it and model it in a way that makes
| the common operations fast enough. As your application
| evolves, this may change, and even require data migrations.
|
| None of this means you have to or even should use stored
| procedures, triggers, or listen/notify. I'm just making the
| point that there is no clean separation between "data" and
| "business logic".
| ehansdais wrote:
| Can't upvote this enough. The point is not that procedures
| outside of the DB is wrong, nor is it that procedures
| should always go into the DB. It's that you should look at
| the context and decide what the best way to solve the
| problem is.
| brightball wrote:
| Agreed. I used triggers frequently for things like
| incrementing/decrementing count fields for dashboards
| because it's the only way to guarantee those numbers are
| correct while ensuring something in the application
| hasn't bypassed a callback or handler to modify the data.
|
| You only need to cover three scenarios and it's very
| simple to implement. Recorded added +1, Record removed
| -1, Record moved +1 & -1.
|
| If you have counts that are more complicated, it doesn't
| work but this solution easily beats semi-frequent COUNT
| queries.
| Cthulhu_ wrote:
| It really depends, but it's also a factor of time, that is,
| "back in the day", databases were designed to serve many
| different clients, nowadays a common practice is to have a
| 1:1 relationship between a database and a client application.
|
| Of course, this is sometimes abused and taken to extremes in
| a microservices architecture where each service has their own
| database and you end up with nastiness like data duplication
| and distributed locking.
| sgarland wrote:
| > Of course, this is sometimes abused and taken to extremes
| in a microservices architecture where each service has
| their own database and you end up with nastiness like data
| duplication and distributed locking.
|
| Not to mention the difficulty in maintaining referential
| integrity with all of that duplicated data. There are
| various workarounds, but at that point it feels very much
| like we're recreating a shared DB, but shittily, and
| netting zero benefits.
| djfivyvusn wrote:
| That's purely because nobody knows how to write SQL let alone
| stored procedures. If stored procedures had better devex
| they'd be used for most of your app.
| sgarland wrote:
| Postgres lets you write stored procedures out of the box in
| pgSQL, C, Tcl, Perl, and Python. There are also 3rd party
| extensions for most languages you might want, including
| Rust and JS.
|
| More broadly, not knowing how to write SQL is a very
| solvable problem, and frankly anyone accessing an RDBMS as
| a regular part of their job should know it. Even if you're
| always using an ORM, you should understand what it's doing
| so you can understand the EXPLAIN output you'll probably be
| looking at eventually.
| v5v3 wrote:
| >... and frankly anyone accessing an RDBMS as a regular
| part of their job should know it.
|
| With entity framework code first, Microsoft made it
| possible for generations of developers to barely touch a
| database.
|
| A lot of Devs have poor database skills nowadays.
|
| Which suits the cloud sellers who want to push managed
| platforms
| sgarland wrote:
| Agreed. What's worse is when they confidently proclaim
| that they had to scale up N times "to handle the load,"
| but then a brief reading of of their schema and queries
| reveals that an RPi could probably handle it if they'd
| designed a better schema, and had a basic understanding
| of B+trees.
| v5v3 wrote:
| A lot of SQL consultants had/have a great job going into
| companies having issues and producing a report of the
| obvious!!
| KronisLV wrote:
| > That's what application code is for.
|
| I've seen people who disagree with that statement and say
| that having a separate back end component often leads to
| overfetching and in-database processing is better. I've
| worked on some systems where the back end is essentially just
| passing data to and from stored procedures.
|
| It was blazing fast, but working with it absolutely sucked -
| though for whatever reason the people who believe that seem
| to hold those views quite strongly.
| whstl wrote:
| This is one of those absolute statements that cause the kind
| of problem stated by grandparent. There are lots of those:
| "Use Postgres for everything", "No business data on the DB",
| "No methods bigger than 10 lines", "Abstractions only after 3
| usages".
|
| Back to the topic: Lots of potential bugs and data corruption
| issues are solved by moving _part_ of the business logic to
| the database. Other people already covered two things: data
| validation and queue atomicity.
|
| On the other hand, lots of potential issues can also arise by
| putting _other_ parts of business logic to the database, for
| example, calling HTTPS endpoints from inside the DB itself is
| highly problematic.
|
| The reality is that the world is not black and white, and
| being an engineer is about navigating this grey area.
| cryptonector wrote:
| Thank you for bringing some sanity into this discussion.
| sgarland wrote:
| Disagree; these issues come up when people use more advanced
| features of DBs without having the requisite DB expertise on
| staff. I'll give OP that Postgres' docs do not mention this
| gotcha (and props to them for drilling down to source code!),
| but by and large, these issues are from people operating via
| tech blogs.
|
| The DB is - or should be - the source of truth for your
| application. Also, since practically everyone is using cloud
| RDBMS with (usually) networked storage, the latency is
| atrocious. Given those, it seems silly to rely on an
| application to react to and direct changes to related data.
|
| For example, if you want to soft-delete customer data while
| maintaining the ability to hard-delete, then instead of
| having an is_deleted and/or deleted_at column, have a
| duplicate table or tables, and an AFTER DELETE trigger on the
| originals that move the tuples to the other tables.
|
| Or if you want to have get_or_create without multiple round
| trips (and you don't have Postgres' MERGE ... RETURNING), you
| can easily accomplish this with a stored procedure.
|
| Using database features shouldn't be seen as verboten or
| outdated. What _should_ be discouraged is not treating things
| like stored procedures and triggers as code. They absolutely
| should be in VCS, should go the same review process as
| anything else, and should be well-documented.
| cryptonector wrote:
| You're reaching the wrong conclusion, probably because of
| confirmation bias. Certainly this LISTEN/NOTIFY problem does
| not lead to your conclusion, nor does it support it. After
| all if you were relying on LISTEN/NOTIFY you could instead
| rely on logical replication decoding / CDC instead. And heck,
| you could even have a client connected to the same database
| that uses logical decoding to pick up events worth NOTIFYing
| about and then does just that, but without burdening any
| other transactions.
| ownagefool wrote:
| Largely agree. Functionality wise if you don't have many jobs,
| using the database as the queue is fine.
|
| However, I've been in several situations where scaling the
| queue brings down the database, and therefore the app, and am
| thus of the opinion you probably shouldn't couple these systems
| too tightly.
|
| There are pros and cons, of course.
| mike_hearn wrote:
| Using the database for queues is more than fine, it's often
| essential to correctness. In many use cases for queues you
| need to atomically update the database with respect to
| popping from the queue, and if they're separate systems you
| end up needing either XA or brittle and unreliable custom
| idempotency logic. I've seen this go wrong before and it's
| not nice, the common outcome is business-visible data
| corruption that can have financial impact.
|
| This seems like another case where Postgres gets free
| marketing due to companies hitting its technical limits. I
| get why they choose to make lemonade in these cases with an
| eng blog post, but this is a way too common pattern on HN.
| Some startup builds on Postgres then spends half their eng
| budget at the most critical growth time firefighting around
| its limits instead of scaling their business. OpenAI had a
| similar blog post a couple of months ago where they revealed
| they were probably spending more than quarter of a million a
| month on an Azure managed Postgres, and it had stopped
| scaling so they were having to slowly abandon it, where I
| made the same comment [1].
|
| Postgres is a great DB for what you pay, but IMHO well
| capitalized blitzscaling startups shouldn't be using it. If
| you buy a database - and realistically most Postgres users do
| anyway as they're paying for a cloud managed db - then you
| might as well just buy a commercial DB with an integrated
| queue engine. I have a financial COI because I have a part
| time job there in the research division (on non-DB stuff), so
| keep that in mind, but they should just migrate to an Oracle
| Database. It has a queue engine called TxEQ which is
| implemented on top of database tables with some C code for
| efficient blocking polls. It scales horizontally by just
| adding database nodes whilst retaining ACID transactions, and
| you can get hosted versions of them in all the major clouds.
| I'm using it in a project at the moment and it's been working
| well. In particular the ability to dequeue a message into the
| same transaction that does other database writes is very
| useful, as is the exposed lock manager.
|
| Beyond scaling horizontally the nice thing about TxEQ/AQ is
| that it's a full message queue broker with all the normal
| features you'd expect. Delayed messages, exception queues,
| queue browsing, multi-consumer etc. LISTEN/NOTIFY is barely a
| queue at all, really.
|
| For startups like this, the amount of time, money and morale
| they are losing with all these constant stories of firefights
| just doesn't make sense to me. It doesn't have to be Oracle,
| there are other DBs that can do this too. But "We discovered
| X about Postgres" is a eng blog cliche by this point. You're
| paying $$$ to a cloud and GPU vendor anyway, just buy a
| database and get back to work!
|
| [1] https://news.ycombinator.com/item?id=44074506
| jumski wrote:
| Using queues in atomic, transactional way was a core
| principle for building https://pgflow.dev - having whole
| workflow state transactionally updated alongside the work
| on the in db queue really simplifies a lot of things:
| debugging is easier, audit log is easy, reporting, stats
| etc are one SQL query away.
| mike_hearn wrote:
| Nice! I'm also using queues as part of a workflow engine.
| jumski wrote:
| Oh really? Would love to check it out and borrow some
| ideas! :)
| pbronez wrote:
| Looks interesting- but why the Supabase dependency?
| That's a much tighter requirement than a vanilla
| PostgreSQL extension or something like PostgREST
| jumski wrote:
| Valid point!
|
| So pgflow is really agnostic and Postgres is it's
| fundamental dependency. All components are modular and
| ready to be adapted to other runtimes.
|
| It's just that Supabase is what I use and I figured out
| this will be my first platform, but the abstraction to
| port to others is there!
| ownagefool wrote:
| It actually depends on the workload.
|
| Sending webhooks, as an example, often has zero need to go
| back and update the database, but I've seen that exact
| example take down several different managed databases (
| i.e., not just postgres ).
| mike_hearn wrote:
| Yes that's true but in good implementations you will want
| to surface to the recipient via some dashboard if
| delivery consistently fails. So at some point a message
| on the exception queue will want to update the db.
| sgarland wrote:
| > "We discovered X about Postgres" is a eng blog cliche by
| this point.
|
| It really is, and it's often surprising to me how basic
| some of the issues are being discovered. Like Figma, when
| they waited a shocking amount of time add [0] PgBouncer and
| read replicas. This is such a well-trod path that it's
| baffling to me why you wouldn't add it once it's clear you
| have a winning product. At the very least, PgBouncer (or
| PgCat, or any other connection pooler / proxying service) -
| it adds negligible cost per month (in comparison to DB read
| replicas) to run a couple of containers with a load
| balancer.
|
| Re: Oracle, as much as I despise the company for its
| litigious practices, I'll hand it to you that the features
| your DB has are astonishing. RAC is absolutely incredible
| (on paper - I've never used it).
|
| [0]: https://www.figma.com/blog/how-figma-scaled-to-
| multiple-data...
| dathinab wrote:
| if you need transaction across a queue into a normal SQL DB
| or similar I believe you are doing something very wrong.
|
| Sure you need transaction about processing things in a
| queue (mark as "taken out", but not yet remove then remove
| or "place back in (or into a failed messages inbox)" on
| timeout or similar can be _very_ important for queue
| systems.
|
| But the moment the "fail save if something dies while
| processing a message" becomes a directly coupled with DB
| transactions you have created something very brittle and
| cumbersome.
|
| To be fair that might still be the best solution for some
| situations.
|
| But the better solution is to make sure you treat a queue
| as message passing system and handle messages as messages
| with the appropriate delivery semantics. And if you can't
| because idk. idempotency logic is supper unreliable then
| there indeed is a problem, but its not in the missing cross
| transactions but how you write that logic (missing
| ?_tooling_, strict code guidelines people actually comply
| with, interface regression checks, tests (including
| prop/fuzz tests, regression tests, integration/e2e tests
| etc., not just "dump" unit test)).
|
| > just migrate to an Oracle Database.
|
| In my experience while Oracle DB is very powerful but also
| very cumbersome in a lot of ways and if you need thing only
| they can provide you most likely already fucked up big time
| somewhere else in your design/architecture. Sure if you are
| at that point Oracle can lightly be the cheaper solution.
| But still preferable you never endup there.
|
| As a side note, there are also a lot of decent plugins
| which can provide similar capabilities to PG, but they tend
| to have the issue that they aren't part of managed PG
| solutions and self managing PG (or most other reasonable
| powerful DB) can be a huge pain, and then yes Oracle can be
| a solution.
|
| Still the amount of startups which had a overall good
| experience are in my experience overall non existing in my
| experience. (But there are some pretty big
| companies/projects I know of which have a overall good
| experience with Oracle.)
|
| > constant stories of firefights
|
| If you mean stories on HN, than that isn't a meaningful
| metric, you will only hear about the "interesting" stories
| which mostly are about fire fighting or "using pg for
| everything is grate" but rarely the majority of in-between
| stories and boring silent successes. If it's about stories
| from you carriers and asking dev friends you have what
| their experience is then it is more meaningful. But in a
| bubble (like this answer of mine is, without question, in a
| bubble).
|
| Generally I think people really overestimate how
| representative HN is, idk. about the US but outside of it
| _huge_ parts of the IT industry are not represented by HN
| in any meaningful way. I would say in my country HN is _at
| most_ representative for 1/4 of the industry, but that 1/4
| also contains many of the very very motivated software
| developers. But also very few of the "that my work but not
| my calling", "bread and butter" work software devs, which
| are often 1/3 to over 1/2 of devs in most countries as far
| as I can tell.
| imtringued wrote:
| >But the moment the "fail save if something dies while
| processing a message" becomes a directly coupled with DB
| transactions you have created something very brittle and
| cumbersome.
|
| The standard workflow for processing something from a
| queue is to keep track of all the messages you have
| already processed in the transactional database and
| simply request the remaining unprocessed messages. Often
| this is as simple as storing the last successfully
| processed message ID in the database and updating it in
| the same transaction that has processed the message. If
| an error occurs you roll the transaction back, which also
| rolls back the last message ID. The consumer will
| automatically re-request the failed message on the next
| attempt, giving you out of the box idempotency for at
| least once messaging.
| tracker1 wrote:
| My approach is to have fields for started/completed where
| started includes the system/process/timestamp of when an
| item was started... this gets marked as part of the
| process to tag and take the next item by the worker(s).
| It also allows for sweep and retry.
|
| That said, I tend to reach for redis/rabbit or kafka
| relatively early depending on my specific needs and
| what's in use. Main use of a dbms queue historically is
| sending/tracking emails where the email service I had
| been using was having hiccups.
| osigurdson wrote:
| >> To be fair that might still be the best solution for
| some situations.
|
| It is arguable. Let's say your team knows Postgres well
| from a relational standpoint. Now they need to do
| something messages and require some type of notification
| / messaging. There is a learning curve here anyway. I'd
| argue they should spend it on more standard approaches
| which are not harder to start with. Of course, if you
| know that your site / service will only be used by
| yourself and your grandmother do whatever you want (just
| use a text file or better yet just call her instead).
| osigurdson wrote:
| >> but they should just migrate to an Oracle Database
|
| No big tech companies or unicorn type startups are using
| Oracle. Is your claim that they are all wrong?
|
| >> Some startup builds on Postgres then spends half their
| eng budget at the most critical growth time firefighting
| around its limits instead of scaling their business
|
| This is why I suggest starting with some kind of normal
| queue / stream mechanism and columnar DB if needed. It
| isn't even harder than using one DB, particularly if you
| are using niche features.
| v5v3 wrote:
| Isn't Kafka the Postgresql of pub/sub
|
| I.e. use Kafka unless you have a explicit reason not to?
|
| So why Nats?
| evnix wrote:
| After working with NATS, I wouldn't want to touch Kafka even
| with a long stick. Its just too complex and a memory hog for
| no good reason. It doesn't have all the features that NATS
| supports as well.
| v5v3 wrote:
| What about the Kafka V2, Pulsar?
| the_duke wrote:
| Kafka is far from trivial to operate, for one thing, even
| post zookeeper.
| ahoka wrote:
| And it's kinda wrong to use as a queue (in most cases),
| being a log stream you can seek in.
| riedel wrote:
| Actually LISTEN/NOTIFY does also not scale the other way.
| Immich also moved to that pg for everything mentality (trying
| to remove redis dependencies). The problem: postgres needs a
| WAL flush for all notifications. I ran immich on my HDD-NAS.
| The result was constant noise because the pg backed socket.io
| backend issues constant keep alive messages.
| KronisLV wrote:
| > Postgres/ClickHouse/NATS
|
| Maybe throw in a dedicated key-value store like Redis or
| Valkey.
|
| Oh and maybe something S3 compatible like MinIO, Garage or
| SeaweedFS for storing bunches of binary data.
|
| With all of that, honestly it should cover most of the common
| workloads out there! Of course, depends on how specialized vs
| generic you like your software to be.
| whaleofatw2022 wrote:
| NATS does KV pretty well now (didn't have expiration till
| earlier this year)
| indeyets wrote:
| Nats is getting there, but not yet.
|
| Redis is still much more powerful: lists, sorted sets and
| bazillion of other data structures
| j45 wrote:
| There's no reason this article and start with Postgres for
| everything can't be true.
|
| In the beginning having fewer parts to connect and maintain
| lets the needs and bottlenecks of the actual application
| emerge.
|
| If it was listen/notify in such a scenario at some volume where
| optimizing it isn't in the cards... so be it. It would be some
| time down the road before sharding a function into a specific
| subsystem like what you described.
|
| Appreciate learning about the Postgres/Clickhouse/nats combo.
| If there might be an article if the three together that you
| liked would be happy to read and learn.
| jelder wrote:
| "use Postgres for everything" is certainly wrong, eventually.
| It's still the second-best choice for every new project, and
| most products will never see the traffic levels that justify
| using something more specialized. Obviously, recall.ai hit the
| level of traffic where Postgres was no longer ideal. I bet they
| don't regret it for the other parts of their product.
| closeparen wrote:
| They aren't even questioning its use as a database, just as
| an event bus.
| dathinab wrote:
| Honestly whatever kind of DB you are speaking about always be
| wary of "niche/side features" which don't fit it's core design
| goals, they tend to have unexpected limitations.
|
| listen/notify isn't necessary a replacement for redis or other
| pub/sub systems, redis pub/sub and similar isn't necessary a
| replacement for idk. Kafka or similar queue/messaging system
|
| but a lot of companies have (for modern standards) surprisingly
| small amounts of data, very even a increase by 2,3,4x still
| isn't that big. In that case listen/notify and similar might
| just work fine :shrug:
|
| also same is true the other way around, depending on you
| application you can go redis only, as long as you data volume
| stays small enough and needs for transactional/sync are
| reasonable simple enough (with watch+exec, NX,XX options etc.
| and maybe some redis side lua scripts you can do quite a lot
| for data synchronization). Issue with that is that
| stylistically redis data sync/transaction code is often much
| more similar to writing atomic data-structures then to SQL
| transactions, and even for SQL transactions there is a trend of
| devs severely overestimating what they provide, so often you
| are better of not touching on it when you can avoid it, also
| BTW. redis has something very similar to sqlite or Notify where
| "basically" (oversimplified by a lot) there is only one set of
| writes done at a time ;) (and then afterwards distributed to
| replicas), just that outside of some micro lua scripts you
| don't really run much logic outside of some NX, XX checks etc.
| so it's not blocking much and it's "more or less" all just in
| memory not touching a WAL (again oversimplified).
| cryptonector wrote:
| I think PG could relax the ordering thing with NOTIFYs since...
| it seems a bit silly, but NOTIFYs already are unsafe to use
| because there is no authorization around channel access, so one
| might as well use change data capture (logical replication,
| basically) instead.
| riku_iki wrote:
| > However, it does help to contrast the much more common, "use
| Postgres for everything" type sentiment.
|
| I think sentiment is to use "for everything in 99% business
| cases", which involves few 100GB of data with some thousands
| QPS, and could be handled by PG very well.
| baristaGeek wrote:
| Postgres is a great DB, but it's the wrong tool for a write-
| heavy, high-concurrency, real-time system with pub-sub needs.
|
| You should split your system into specialized components: - Kafka
| for event transport (you're likely already doing this). - An LSM-
| tree DB for write-heavy structured data (eg: Cassandra) - Keep
| Postgres for queries that benefit from relational features in
| certain parts of your architecture
| baristaGeek wrote:
| Very good article! Succinct, and very informative.
| ryanjshaw wrote:
| IMO They don't have a high concurrency DB writing system, they
| just think they do.
|
| Recordings can and should be streamed to an object store.
| Parallel processes can do transcription on those objects;
| bonus: when they inevitably have a bug in transcription,
| retranscribing meetings is easy.
|
| The output of transcription can be a single file also stored in
| the object store with a single completion message notification,
| or if they really insist on "near real-time", a message on a
| queue for every N seconds. Much easier to scale your queue than
| your DB, eg Kafka partitions.
|
| A handful of consumers can read those messages and insert into
| the DB. Benefit is you have a fixed and controllable write load
| into the database, and your client workload never overloads the
| DB because you're buffering that with the much more distributed
| object store (which is way simpler than running another
| database engine).
| aaa12365 wrote:
| hi
| merb wrote:
| Wouldn't it be better nowadays to listen to the Wal. With a
| temporary replication slot and a publication just for this table
| and the id column?
| bjornsing wrote:
| If I'm not mistaken LISTEN/NOTIFY doesn't work with connection
| poolers, and you can't have tens of thousands of connections to a
| Postgres database. Not sure you need a more elaborate analysis
| than that to reach the same conclusion.
| calderwoodra wrote:
| Why doesn't LISTEN/NOTIFY work with connection poolers?
| cryptonector wrote:
| Because if you have N connections in your pool you're going
| to have to execute LISTEN on all N, or else the connection
| pool needs to be LISTEN-aware so it can process async
| notifies by calling some registered callback.
|
| I.e., the connection pool API has to be designed with this in
| mind.
|
| For that matter connection pools also need to be designed
| with the ability to run code upon connecting to create TEMP
| schema elements because PG lacks GLOBAL TEMP.
| ilitirit wrote:
| > The structured data gets written to our Postgres database by
| tens of thousands of simultaneous writers. Each of these writers
| is a "meeting bot", which joins a video call and captures the
| data in real-time.
|
| Maybe I missed it in some folded up embedded content, or some
| graph (or maybe I'm probably just blind...), but is it mentioned
| at which point they started running into issues? The quoted bit
| about "10s of thousands of simultaneous writers" is all I can
| find.
|
| What is the qualitative and quantitative nature of relevant
| workloads? Depending on the answers, some people may not care.
|
| I asked ChatGPT to research it and this is the executive summary:
| For PostgreSQL's LISTEN/NOTIFY, a realistic safe throughput is:
| Up to ~100-500 notifications/sec: Handles well on most systems
| with minimal tuning. Low risk of contention.
| ~500-2,000 notifications/sec: Reasonable with good tuning (short
| transactions, fast listeners, few concurrent writers). May start
| to see lock contention. ~2,000-5,000
| notifications/sec: Pushing the upper bounds. Requires careful
| batching, dedicated listeners, possibly separate Postgres
| instances for pub/sub. >5,000 notifications/sec: Not
| recommended for sustained load. You'll likely hit serialization
| bottlenecks due to the global commit lock held during NOTIFY.
| DumBthInker007 wrote:
| My understanding: i think as postgres takes an exclusive lock to
| enqueue the notifications into a shared queue in
| PreCommit_Notify(), as the actual commit happens after
| notification was enqueued into the queue,as other transactions
| also try to notify but wait becacause of the lock ,so does the
| commit waits.
| winterrx wrote:
| Funny, I got to their homepage and get 504'd
| winterrx wrote:
| They're the same company that ran into this, at least they're
| learning! > How WebSockets cost us $1M on our AWS bill
| seunosewa wrote:
| They have a history of not prioritising performance.
| vb-8448 wrote:
| I didn't see it in the article, can some tell me what is the
| scale of " many writers."?
| redskyluan wrote:
| Postgres users often hit scaling issues -- whether it's with
| LISTEN/NOTIFY, PGVector, or even basic relational queries.
|
| For startups, Postgres is a fantastic first choice. But plan
| ahead: as your workload grows, you'll likely need to migrate or
| augment your stack.
| grumple wrote:
| I'm mostly a MySQL user. Two things stand out:
|
| 1) the Postgres documentation does not mention that Notify causes
| a global lock or lock of any sort (I checked). That's crazy to
| me; if something causes a lock, the documentation should tell you
| it does and what kind. Performance notes also belong in
| documentation for dbs.
|
| 2) why the hell does notify require a lock in the first place?
| Reading the comment this design seems insane; there's no good
| reason to queue up notifications for transactions that aren't
| committed. Just add the notifications in commit order with no
| lock, you're building a db with concurrency, get used to it.
| JoelJacobson wrote:
| Hey folks, I ran into similar scalability issues and ended up
| building a benchmark tool to analyze exactly how LISTEN/NOTIFY
| behaves as you scale up the number of listeners.
|
| Turns out that all Postgres versions from 9.6 through current
| master scale linearly with the number of idle listeners -- about
| 13 ms extra latency per connection. That adds up fast: with 1,000
| idle listeners, a NOTIFY round-trip goes from ~0.4 ms to ~14 ms.
|
| To better understand the bottlenecks, I wrote both a benchmark
| tool and a proof-of-concept patch that replaces the O(N) backend
| scan with a shared hash table for the single-listener case -- and
| it brings latency down to near-O(1), even with thousands of
| listeners.
|
| Full benchmark, source, and analysis here:
| https://github.com/joelonsql/pg-bench-listen-notify
|
| No proposals yet on what to do upstream, just trying to gather
| interest and surface the performance cliff. Feedback welcome.
| cryptonector wrote:
| That's pretty cool.
|
| IMO LISTEN/NOTIFY is badly designed as an interface to begin
| with because there is no way to enforce access controls (who
| can notify; who can listen) nor is there any way to enforce
| payload content type (e.g., JSON). It's very unlike SQL to not
| have a `CREATE CHANNEL` and `GRANT` commands for dealing with
| authorization to listen/notify.
|
| If you have authz then the lack of payload content type
| constraints becomes more tolerable, but if you add a `CREATE
| CHANNEL` you might as well add something there regarding
| payload types, or you might as well just make it so it has to
| always be JSON.
|
| With a `CREATE CHANNEL` PG could provide: -
| authz for listen - authz for notify - payload
| content type constraints (maybe always JSON if you
| CREATE the channel) - select different
| serialization semantics (to avoid this horrible,
| no good, very bad locking behavior) - backwards-
| compatibility for listen/ notify on non-created
| channels
| maxbond wrote:
| > there is no way to enforce access controls
|
| (I thought this was a fun puzzle, so don't take this as
| advice or as disagreement with your point.)
|
| There is the option to use functions with SECURITY DEFINER to
| hack around this, but the cleanest way to do it (in the
| current API) would be to encrypt your messages on the
| application side using an authenticated system (eg AES-GCM).
| You can then apply access control to the keys. (Compromised
| services could still snoop on when adjacent channels were in
| use, however.)
| daitangio wrote:
| I wrapped together a simple yet powerful queue system:
|
| https://github.com/daitangio/pque
|
| I evaluated Listen/notify but it seems to loose messages if no
| one is listening, so its use case seems pretty limited to me (my
| 2 cents).
|
| Anyway, If you need to scale, I suggest an ad hoc queue server
| like rabbitmq.
| FZambia wrote:
| For real-time notifications, I believe Nats (https://nats.io) or
| Centrifugo (https://centrifugal.dev) are worth checking out these
| days. Messages may be delivered to those systems from PostgreSQL
| over replication protocol through Kafka as an intermediary
| buffer. Reliable real-time messaging comes with lots of
| complexities though, like late message delivery, duplicate
| message delivery. If the system can be built around at most once
| guarantees - can help to simplify the design dramatically.
| Depends on the use case of course, often both at least once and
| at most once should co-exist in one app.
| cryptonector wrote:
| And Debezium.
| westurner wrote:
| Re: Postgres LISTEN/NOTIFY and PgQueuer, which is built on
| LISTEN/NOTIFY:
| https://news.ycombinator.com/item?id=41284703#41285614
| FZambia wrote:
| Many here recommend using Kafka or RabbitMQ for real-time
| notifications. While these tools work well with a relatively
| stable, limited set of topics, they become costly and inefficient
| when dealing with a large number of dynamic subscribers, such as
| in a messaging app where users frequently come and go. In
| RabbitMQ, queue bindings are resource-intensive, and in Kafka,
| creating new subscriptions often triggers expensive rebalancing
| operations. I've seen a use case for a messenger app with 100k
| concurrent subscribers where developers used RabbitMQ and
| individual queues for each user. It worked at 60 CPU on Rabbit
| side during normal situation and during mass reconnections of
| users (due to some proxy reload in infra) - it took up to several
| minutes for users to reconnect. I suggested switching to
| https://github.com/centrifugal/centrifugo with Redis engine
| (combines PUB/SUB + Redis streams for individual queues) - and it
| went to 0.3 CPU on Redis side. Now the system serves about 2
| million concurrent connections.
| aryav07 wrote:
| Nice to know about this, good article.
| mattxxx wrote:
| The article is good, but maybe a bit negative on the postgres
| feature. I think the article reads much better with the slant:
| "LISTEN/NOTIFY got us to this level of concurrency; here's how we
| diagnosed the performance cliff, and here's what we're doing
| now."
|
| Which is like... cool, you were able to scale pretty far and
| create a lot of value before you needed to find a new solution.
| Matthias247 wrote:
| Clarification question:
|
| > When a NOTIFY query is issued during a transaction, it acquires
| a global lock on the entire database (ref) during the commit
| phase of the transaction, effectively serializing all commits.
|
| It only serializes commits where NOTIFY was issued as part of the
| transaction, right? Transactions which did not call NOTIFY should
| not be affected?
| gwbas1c wrote:
| > our Postgres database
|
| > tens of thousands of simultaneous writers
|
| I'm surprised they aren't sharding at this scale. I wonder why?
| bhollis wrote:
| The pattern I've always used for this, which I suspect is what
| they landed on, is to have an optimistic notification method in a
| separate message queue that says "something changed that's
| relevant to you". Then you can dedupe that, etc. Then structure
| the data to easily sync what's new, and let the client respond to
| that notification by calling the sync API. That even lets you use
| multiple notification methods for notification. None of that
| involves having to have the database coordinate notifications in
| the middle of a transaction.
___________________________________________________________________
(page generated 2025-07-11 23:01 UTC)