9 ms·

Listen to your to PostgreSQL database in realtime via WebSockets

• kitd 5y ago
  Nice! Can you go in the other direction, ie push via WS into a table? Obviously you'd need the correct auth. I had an idea of doing something similar with MQTT. Data at rest and data in motion in 1 (or maybe 1.5) components.
  • edoceo 5y ago
    yea, thats pretty easy. a small Go/Rust/whatever program to do WS and write (well formed, authenticated) message data via INSERT
  • jordan801 5y ago
    Sure can! I contributed on this project a few months ago. Hoping to use it soon on a project. I implemented a portion of crud with a vuejs app. It's a pretty neat setup. Just authorize with OAuth2 and then if you have permissions youre good to go!
• karambahh 5y ago
  For some reason I understood the title as an audio rendering of changes on your pg db The actual purpose is actually way cooler, looks like a great tool
  • domoritz 5y ago
    Same here and I am slightly disappointed it's not what I thought it was.
    • SamBam 5y ago
      Same. Figured pop-pop-poppop-pop could be the background noise when we go back to the office...
  • m_mueller 5y ago
    Same here. App idea geared at DevOps: ambient sound scape generator based on monitoring events, e.g. from Datadog.
    • jensneuse 5y ago
      Techno livestream. BPM increases when your website gets high traffic.
    • jrockway 5y ago
      We have this in New York City to monitor the flow of traffic on quiet residential streets. If the flow rate is OK, it's nice and quiet. If the flow rate gets too low (or a traffic light turns red) then a chorus of very angry car horns erupts, and you are jolted out of your train of thought, wishing you had the guts / tolerance for prison time to run outside and smash every single one of the cars with a crowbar. I think a recording of this is actually built into Pagerduty to use as a sound when you're getting paged. I went with the "golf ball hit into a flock of geese" one, though. Every time that goes off my first thought is "OH GOD I'M DYING HELP" but then I look and it's just GCP down again. Ironically therapeutic.
    • tetha 5y ago
      There is an implementation and a paper called peep, the network auralizer. I've been thinking of building something like this in rust or go as a learning project, but audio streaming and mixing is harder than I thought. If someone has good libraries or tutorials there, I'd be grateful. http://peep.sourceforge.net/intro.html http://peep.sourceforge.net/intro.html https://www.usenix.org/legacy/publications/library/proceedings/lisa2000/full_papers/gilfix/gilfix_html/index.html https://www.usenix.org/legacy/publications/library/proceedin...
  • jmull 5y ago
    That's what I thought too. I guess someone needs to do it now, hm... (But: I think live db-activity rendered to music as a kind of monitoring mechanism is potentially way cooler than just getting db updates over a web socket.)
  • mark-wagner 5y ago
    Similar: Solaris's snoop (tcpdump equivalent) with the -a option: Listen to packets on /dev/audio (warning: can be noisy). https://docs.oracle.com/cd/E23823_01/html/816-5166/snoop-1m.html https://docs.oracle.com/cd/E23823_01/html/816-5166/snoop-1m....
    • krrrh 5y ago
      You’ve got to throw in some uncompressed pcm audio streams just to keep it interesting. When we were learning Linux a friend and I used to pipe /dev/hda into /dev/dsp for fun and when we hit some fragments of uncompressed piano recordings we joked that we must have hit the kernel source code. Good times.
  • yokto 5y ago
    This reminds me of a project I did a few years ago: "Stockify is a live music service that follows the mood of the stock markets. It plays happy music only when the stock is going up and sad music when it’s going down." https://vimeo.com/310372406 https://vimeo.com/310372406 (it's of course a parody, but I made a functional prototype)
    • dkersten 5y ago
      I know someone who uses tones as an easy way of knowing if there's a market move on some cryptocurrencies. Basically, there's different tones for a trade that traded higher than previous and for a trade that traded lower than the previous. Possibly pitch or volume or something was used to indicate the volume of the trade, I don't quite remember. Either way, he could quickly hear if there was a major market move or something just by the amount and tone of the beeps. He used this as a way to know then he should look at the chart or whatever.
      • krrrh 5y ago
        We spend a lot of time training digital computers to deal with analog information that has been converted into digital forms, and I wonder how much we could also gain by finding better ways to convert digital information into analog forms that our brains (as analog computers) can better parse.
      • recuter 5y ago
        Oh you're also friends with Gilfoyle? https://www.youtube.com/watch?v=uS1KcjkWdoU https://www.youtube.com/watch?v=uS1KcjkWdoU
        • dkersten 5y ago
          Hahaha I forgot about this
    • shrimpx 5y ago
      I had a related idea, to make a running program produce an audible hum like a car engine would. And the hum would vary depending on what paths of the program are executing, so you get an idea what/how your code is "doing" by listening in.
  • rzzzt 5y ago
    You can listen to GitHub commits: https://github.audio/ https://github.audio/
    • Ombudsman 5y ago
      This is usually how I fall asleep.
      • arthurcolle 5y ago
        This is how you guarantee your child will become an ace coder. The fractal audio will transform key centers in the developing brain, and give them subliminal knowledge of all major repos of note
• endisneigh 5y ago
  I'm really curious to see how this ends up doing vs. Hasura. Postgrest (which is basically what Supabase is, obviously with other value-adds) or Hasura which basically exposes a GraphQL server that interfaces with Postgres. Personally I prefer GraphQL as there's more tooling around that compared to Postgrest but it's interesting to see. In this case if supabase was GraphQL you could just use a subscription. I'd be curious to know why supabase didn't go with GraphQL.
  • michael_j_ward 5y ago
    I believe the graphql equivalent of postgrest is postgraphile [0]. [0] https://www.graphile.org/postgraphile/ https://www.graphile.org/postgraphile/
  • pier25 5y ago
    > I'd be curious to know why supabase didn't go with GraphQL. I'm guessing because GraphQL is very limited compared to SQL. From reading their docs [1] it seems they have a JS API for SQL with support for nested data a la GraphQL. Not sure if its their own or comes from some other library. [1] https://supabase.io/docs/reference/javascript/select https://supabase.io/docs/reference/javascript/select
    • rattray 5y ago
      With postgraphile you get the full power of postgresql exposed through graphql. Using a few commonly-used add-ons, you can write a whole lot of sql in gql and it Just Works, translating your deeply-nested and richly-filtered gql query into a single, performant sql query. You can see roughly how this translation ends up here: https://gist.github.com/rattrayalex/ae39c2cf0356f1257ece4f3c4f6488cd https://gist.github.com/rattrayalex/ae39c2cf0356f1257ece4f3c... (in production it's more condensed etc). You can also extend with sql functions or add/wrap resolvers at the js level. (And yes, you can easily hide columns, rename, etc)
      • efxhoy 5y ago
        I recently spent a week doodling around with postgraphile and postgrest. I really liked both, writing everything in SQL is super great. It does feel a bit weird at first but the amount of boilerplate it removes is fantastic and it really forced me to think about the correct schema for my business logic. My only gripe with postgraphile is not supporting column grants in RLS policy.[0] Instead they recommend splitting tables in two and having one-to-one foreign keys and using grants on the full tables instead. It's a shame because PostgREST dealt with column grants just fine and I don't want to use postgraphile's specific "smart comments" in my database, they just seem like a really un-elegant solution to what is otherwise a super nice pattern: SQL everything. I also considered Hasura but they have their own auth system and don't use RLS, which is a shame. Having access control in the database is super great when you have multiple APIs and different people with psql and different roles, all with different levels of access to the same DB. 0 : > Don't use column-based SELECT grants: column-based grants work well for INSERT and UPDATE (especially when combined with --no-ignore-rbac!), but they don't make sense for DELETE and they cause issues when used with SELECT. https://www.graphile.org/postgraphile/requirements/ https://www.graphile.org/postgraphile/requirements/
• bcoates 5y ago
  Neat! Is the idea to subscribe to the websocket directly from frontend code or from an application server? It sounds like it's the latter, which seems like an unusual use case for websockets. (or at least I can't think of anything else that does ws for a server-to-server API)
  • cookguyruffles 5y ago
    As far as getting cheap framing and bidirectional communication over an HTTP port goes, there's nothing to stop you using websocket server->server. For example users, many crypto exchanges publish their feed this way
    • codeflo 5y ago
      But why, except to claim “web-scale”? Especially for publishing events, isn’t something like MQTT a lot saner?
      • cookguyruffles 5y ago
        I already listed two, because you need - cheap framing - bidirectional comms - are already exposing an HTTP port - can make no assumptions about the client's networking than port 443 available, even if only via a HTTPS proxy - can make no assumptions about the client's software except they can speak an extremely common protocol - want security to work the same way it does for the rest of your services "web-scale" doesn't come into it. Websockets are inherently difficult to scale because they're stateful, but in return you get the lowest latency in both directions the underlying network can provide with extremely reasonable (2-4 bytes) overhead compared to raw TCP or SSL
      • megous 5y ago
        No broker.
  • jordan801 5y ago
    Yeah! Front end apps can skip the server and get data from the db directly. There's OAuth2 setup and you can authorize users to have crud access to tables, or even a granular as rows.
• hmsimha 5y ago
  This is something I have an immediate need for (and was about to build myself) to use with a PostgreSQL/TimescaleDB instance. Is it possible to have new subscriptions get up to an hour of historical data before streaming starts, or even better, to supply a parameter to receive historical data from a timestamp?
  • sk5t 5y ago
    Stream history is kind of thorny; oughtn't one consider Kafka, Kinesis, etc., for that instead?
    • hmsimha 5y ago
      To be clear, it's not really stream history that I'm after. Just the result of a database query upon connection ( `select all items from table X with time > (now() - 1hr)` )
      • gregw2 5y ago
        not open source, but I believe snowflake allows this
• brap 5y ago
  I really want Supabase to take off. For me, the #1 reason not to go with Firebase was lack of relational DB support, and #2 vendor lock-in.
  • pier25 5y ago
    For #1 you could check out Fauna. Much more powerful than Firebase regarding querying and data modeling. With Fauna you still get vendor lock-in though.
    • jadbox 5y ago
      If Fauna went open-source, I'd make the case to pay for their hosting. There's business value in just having the option to host it internally or make modifications.
      • pier25 5y ago
        I agree. I love Fauna, and I'd definitely use their hosting even if they offered a self hosting option, but the closed source and vendor lock-in are really strong points.
    • yawnxyz 5y ago
      i've moved from Fauna to Supabase... their FQL language was kind of confusing and poorly documented compared to Supabase
  • kabes 5y ago
    For 2 you could look at meteor, which does a similar thing with mongo. It's not relational, but with supabasr you also don't really get the advantages of a relational db, since the update events are about single tables.
• cglace 5y ago
  So this is similar to how you could subscribe to queries in meteorjs.
• raarts 5y ago
  So basically Apollo GraphQL server could use the same mechanism for enabling GraphQL subscriptions right? Any idea what they currently use?
  • endisneigh 5y ago
    GraphQL severs handle subscriptions websockets as well usually.
  • speedgoose 5y ago
    They use websockets and a set of plug-ins to support redis, rabbitmq, or many other things. However, Apollo is planning to remove subscriptions for the next major release.
    • ex3ndr 5y ago
      Where did you get plans to remove subscriptions?
      • speedgoose 5y ago
        In their roadmap here : https://github.com/apollographql/apollo-server/issues/2360#issuecomment-803149134 https://github.com/apollographql/apollo-server/issues/2360#i...
        • ex3ndr 5y ago
          That's completely different answer: They remove old graphql-subscirption-ws package that is not maintained anymore and split everything to separate packages to make them work faster. No one is going to remove subscriptions.
    • raarts 5y ago
      Apollo removing subscriptions? I don't understand, aren't those a core feature of GraphQL?
      • 5y ago
• tyre 5y ago
  Do you know if there is something similar for SQLite? I've been looking for a while and wonder if maybe listening to the WAL is the best option. There are hooks to tie into but the database I'm looking at isn't mine (it's local on my machine for another application) so idk if I should be futzing around with it.
  • WJW 5y ago
    https://litestream.io/ https://litestream.io/ ?
    • benbjohnson 5y ago
      Litestream author here. Litestream monitors and replicates based off the WAL and there are some tricks it has to do to make that work. However, it doesn't currently decode those WAL pages and determine the changed data. That is something that's on the roadmap though: https://github.com/benbjohnson/litestream/issues/129 https://github.com/benbjohnson/litestream/issues/129
      • tyre 5y ago
        This is awesome! I guess more likely given the title of this post, but still pretty cool that the author of a library is here in the comments.
• JakaJancar 5y ago
  This works until you have an A->B->C hierarchy of entities and want to monitor the subtree rooted at a specific A. Then the WAL for table C, even with REPLICA IDENTITY FULL, will not tell you whether a change is relevant. At that point you need to do a JOIN anyways, so might as well just use a trigger and NOTIFY instead. Two of the other reasons for this over triggers are also misleading: - Setting up triggers can be automated easily. - True, you only use 1 connection to the database, but you now operate this app. You could also run pgbouncer.
  • skyde 5y ago
    if C is a "child entity" of C then it's primary key should have the primary key of B as a prefix. This is how I design all my table schema. and it make database partitioning easier too.
    • JakaJancar 5y ago
      “Should” is pretty strong. The choice of PK has its own tradeoffs. But you are correct, if you go the prefix route, it makes the WAL approach trivial, at least while everything really is a tree and there are no many-to-many relationships.
    • derefr 5y ago
      I wish I could do this, but I already have more than four billion As, so my A IDs need 64 bits. I use composite keys for my Bs (A ID + sequence number per A), which allows me to search on the pkey index using just the A column; but by the time I get to C, that gets unwieldy. So my Cs have UUIDs, my Bs also have UUIDs (secondary unique ID, not the pkey) and my Cs have a foreign key pointing to their B. While also having an indexed A ID field. I'd love to just just have "A-B-C" as my Cs' IDs... but it'd only work for my use-case (i.e. be performant) if it was running on a computer with 256-bit registers.
      • skyde 5y ago
        Curious why your primary key are GUID? Also if the primary key of table C is made of 3 column (A GUID, B GUID, C GUID) the index in PostgreSQL will not be that big with compression enabled because all the common prefix will not be stored redundantly. Also having a single compound index on table C covering column (A ForeingKey, B ForeingKey, C GUID) is much better than having multiple index on table C.
• skyde 5y ago
  I love it! Only issue is if you can't consume the history of changes. But this is good for caching materialized view in the client 1- subscribe to event and buffer event 2- run SQL query for your materialized view 3- apply all buffered event and new event to incrementally update the materialized view this way the (slow/expensive) query of the materialized view don't need to be run periodically and your cache always is always fresh without need to set TTL. If you websocket connection get disconnected, drop the materialized view and repeat step #1.
• pihentagy 5y ago
  How does this relates to Debezium?
  • simonw 5y ago
    Looks to me like it uses the same trick as Debezium (and similar projects) - listening to the replication stream and turning it into usable information - but it's a totally separate implementation built on top of Elixir: https://github.com/supabase/realtime/blob/master/server/lib/adapters/postgres/decoder/decoder.ex https://github.com/supabase/realtime/blob/master/server/lib/...
• sscarduzio 5y ago
  Will this work on AWS RDS?
• yawnxyz 5y ago
  How is this similar/different from using CouchDB as a live database? I've been toying around with both (hosting CouchDB on DO) Which one would you recommend for small-ish (~100 user, <10k records) projects?
  • eloff 5y ago
    For a project that small you can use NoSQL without issue because you can always scan the whole thing when needed. For larger data sets you want the relational model unless you're very sure you don't need it (and most people who think they're in this category are just wrong and will find out later the hard way.)
    • yawnxyz 5y ago
      Thank you, that's really helpful! I'm always wary of getting stuck in the "I wish I didn't use NoSQL" camp, but thankfully I haven't been in that situation yet
• gregw2 5y ago
  Anyone know of any open source or cheap web/browser apps that let you edit data in postgres tables? Using this tech or something more primitive? I want to get some end user feedback on predictive model outputs...
  • anaganisk 5y ago
    https://github.com/dbeaver/cloudbeaver https://github.com/dbeaver/cloudbeaver
• tarun_anand 5y ago
  How does this work if I also need to replicate the database for disaster recovery using replication methods other than logical?