26 ms·

RethinkDB versus PostgreSQL: my personal experience

• deleted 10y ago
  [deleted]
• dhd415 10y ago
  I think there are several useful points here: 1) It's rare to have enough insight into the internals of a particular datastore to accurately predict how it will perform on a particular workload. Whenever possible, early testing on production-scale workloads is essential for planning and proofs of concept. 2) Database capabilities are a moving target. E.g., the performance improvements to pgsql's LISTEN/NOTIFY are essential to its ability to handle this particular workload. In previous jobs, I've had coworkers cite failed experiences with 15-20 year-old databases as reasons for not considering them for new projects. Database tech has come a long way in that time. 3) Carefully-tuned RDBMSs are more capable than many tend to admit.
  • fapjacks 10y ago
    I think you're right about number 3. I have a friend who likes to do as much as possible with pgsql and I'm often surprised at how well it performs for the things he uses it for.
    • ruslan_talpa 10y ago
      And yet i hear day after day, don't put business logic in the db, it does not scale ... it's like people are stuck in 2005
      • grzm 10y ago
        I'm not sure if it's fair to read your parent as putting business logic in the db: it may just use Postgres whenever a datastore is required and Postgres is not a bad fit. Deciding where business/application logic should reside is an important decision, and treating PostgreSQL as an application server in addition to a database does require a different way of approaching the tool. Of course, this is no different than any of the other application architecture choices that need to be made.
        • ruslan_talpa 10y ago
          I am not sure i understand your comment also :) My point was that people (read web devs) treat databases (postgres) as only data stores, put in data, get data out, and they are so much more. Sure not anything goes into the db, but if it's a bit of logic that relates only to the data (who can access it, who can change it and in what way), most likely it belongs in the database. PS: but let's not derail this thread with debates on where business logic should go :)
          • grzm 10y ago
            I think I agree with you that there are some things that belong in the database. For example, anything I can do in the database to ensure data integrity (without unduly affecting performance), I'll put there (data types, constraints, triggers, functions which encapsulate transactions). Similarly with access privileges. My initial comment was in response to these statements: I have a friend who likes to do as much as possible with pgsql and I'm often surprised at how well it performs for the things he uses it for. I understood you to read this as putting business logic in the database (indicated by the And yet) in your comment: And yet i hear day after day, don't put business logic in the db I read the initial comment by 'fapjacks as meaning using Postgres whenever appropriate, as opposed to doing everything one could with Postgres in Postgres. Not a big deal at all. I saw a potential misunderstanding and attempted to clarify. PS: Of course, business logic should go where I put it, right? ;)
            • ruslan_talpa 10y ago
              You are right in both cases, yes i read that comment like you described and yes that is probably not what fapjacks meant. As for the beginning of your comment, i am 100% with you. The problem is that the cases you describe (integrity,access,reports on data) are most of the time what the entire application does (we are all just doing CRUD and all that ...) and instead seeing that for what it is (let's call it DataLogic) people are all to often calling it Business logic, and the moment they put that label on it their brain automatically (after years of conditioning) does not even begin to consider the database as the appropriate place for that type of logic.
      • realusername 10y ago
        My problem for putting business logic in the db is not so much about performance but readability, custom sql scripts are very hard to maintain...
        • ruslan_talpa 10y ago
          If you do not like pgsql syntax i get that, you can always use python or something but why would you say custom sql scripts are hard to maintain?
          • dwaltrip 10y ago
            Isn't it harder to create well designed abstractions for business logic in SQL compared to a full-fledged backend application server? I know SQL has stored procedures and the like, but is it correct that SQL is limited in how it can express domain logic, compared to languages that backend application servers are generally written in?
            • ruslan_talpa 10y ago
              The point i was making was more in the direction of logic in the database, not specifically in pure SQL, it can be PgSql, Python ... does not matter. For the tasks that database side code should be used for (data consistency/access rights/reports on data) i would say they can express the domain logic for those tasks quite nicely, even better then in other languages. For example i can say "grant select on invoices to accountant" or i can add a constraint on a table like "CHECK (price > discounted_price)" or "CHECK (price > 0)". I do that in one place and i no longer have to worry about checking this (simple example of) domain logic anywhere else. Most of the time this is what people call domain logic (consistency/access/reports) although i would think a better name for it would be "data logic", heard that somewhere and in a lot of the cases this is almost everything the application does
            • masklinn 10y ago
              > I know SQL has stored procedures and the like, but is it correct that SQL is limited in how it can express domain logic, compared to languages that backend application servers are generally written in? Procedural extensions aren't, really. I mean you can literally just use Python (pg/python) or Javascript (plv8) to write postgres functions.
      • lacampbell 10y ago
        At one dev position I had, the DBA wrote stored procedures for everything I could possibly want to do with the database, and that was the only way I interacted with it period. I wrote a small wrapper around these stored procedures and the records they returned, and used it exclusively. It was a much more pleasant experience than having no stored procedures and a full blown ORM, which IMO inevitably leads to sillyness like doing inner joins at the UI level.
        • daemoncoder 10y ago
          This sounds a lot like what Stephen Feuerstein and Tom Kyte advocate for Oracle environments.
        • webmaven 10y ago
          > and a full blown ORM, which IMO inevitably leads to sillyness like doing inner joins at the UI level. What ORM was this that made you do inner joins in the UI layer?
    • philliphaydon 10y ago
      We store settings based on a hierarchy in the database using JSONB. Basically: global -> country -> account -> user PostgreSQL doesn't support deep merging of json docs, so I just wrote an aggregate function using plv8 to merge them. (the result from the query always results in ~10 records which is then grouped and merged so perf is always fast) https://gist.github.com/phillip-haydon/02e1cda346b4900a3e0090cefe85b176 https://gist.github.com/phillip-haydon/02e1cda346b4900a3e009... This doesn't include the javascript used for merging cos I just wanted to show the stub. This means we can just pass the args to postgresql and return the settings for any given user. We used to attempt to run 6 queries and merge in C# code when we did this in SQL Server.
      • williamstein 10y ago
        I also had to write JSONB deep merging as part of this RethinkDB --> PostgreSQL rewrite. Here's the code I wrote https://github.com/sagemathinc/smc/blob/ce594ff0574ce781bf784ebd3e0cff2a7a695a73/src/smc-hub/postgres-base.coffee#L286 https://github.com/sagemathinc/smc/blob/ce594ff0574ce781bf78... That code constructs a single complicated query; I wonder whether I should write it as a stored function in the database instead. (I'm the author of the blog post.)
      • inopinatus 10y ago
        I've deliberately avoided deep arrays for settings, so my jsonb settings merge is simply: SELECT COALESCE(companies.options, '{}'::jsonb) || COALESCE(groups.options, '{}'::jsonb) || COALESCE(users.options, '{}'::jsonb) FROM users JOIN companies ON users.company_id = companies.id LEFT JOIN groups ON users.group_id = groups.id WHERE users.id = ? Where I need a hierarchy for key names I'm just prefixing. I sit right on the knife edge of tossing all the jsonb options and replacing with an old-school normalized EAV model.
  • koolba 10y ago
    > Carefully-tuned RDBMSs are more capable than many tend to admit. I'd generalize that further: RDBMSs are more capable than many tend to admit. When it comes to a persistent data store, you've got to go out of your way to justify using something that isn't statically typed with firm ACID guarantees. I'm not saying those use cases don't exist, I'm saying most people don't have them. "Saving" 15 minutes of dev time because you don't know what your schema is going to look like is going to cost you orders of magnitude more time down the road asking yourself that same question.
    • aidos 10y ago
      After a long time with RDBMSs I used nosql at one point for my product in anger and I found the work to keep the data consistent just too stressful. I'm happy to be back on Postgres again (and have been for a couple of years). It protects me from my own human inadequacies.
      • wallstprog 10y ago
        "Those who refuse to study history are condemned to repeat it" People like Jim Gray, Mike Stonebraker, et al. were/are not dummies.
        • Entangled 10y ago
          "Those who ignore Codd are condemned to learn Codd, forcefully".- Codd's Law Adapted to DBs.
      • threeseed 10y ago
        Posts like this are annoying and completely stupid. NoSQL databases encapsulates a wide variety of data stores including Key Value (Riak), BigTable (Cassandra), Document (MongoDB), Time Series (InfluxDB), In Memory Grid (Ignite) and dozens and dozens of others that blur the already grey lines. Many of which are strongly consistent (negating your consistency point). So your experience with one does not at all translate to other data stores. The equivalent of what you're saying is "PHP sucks therefore all programming languages suck".
  • BinaryIdiot 10y ago
    > 3) Carefully-tuned RDBMSs are more capable than many tend to admit. So important. I have, in the past, worked with many people who simply thought NoSQL was new and RDMS was old therefore ALWAYS use new. Trying to get someone to pick an RDMS solution in said environment was impossible. The times I've had to take a document store and make it store relational data are more than I'd like to admit.
    • deleted 10y ago
      [deleted]
    • kaeluka 10y ago
      I've never worked with postgres before my current project and I'm so impressed with how easy it is to get stuff done. I used Cassandra for a prototype (bad choice for my workload, not Cassandra's fault) and documentation, ease of tuning, tooling maintenance all seem to be so much easier. Of course, Cassandra has a host of problems to solve that postgres doesn't -- being meant to run on clusters and all. But you know what, I don't care. I also was heavily leaning new stuff before. But what can I say, if an RDBMS is what I need -- I probably should use one.
    • adw 10y ago
      I have a pet theory that any given system has a maximum cleverness budget. Make sure to spend it on the things which actually matter in your problem domain. Interesting data products often have to spend a lot of that cleverness budget in the algorithmic layer – machine learning and statistics. So when I work on that kind of thing, I've learned to have a real appreciation for boring data storage. I'll even happily use MySQL; the ways in which MySQL can suck are very well-understood and that's a hugely underappreciated virtue.
      • fuck_google 10y ago
        On the other hand having the right kind of data storage can greatly benefit some algorithms. For example a graph database might work better with some graph based algorithms.
      • shredwheat 10y ago
  • randomTought 10y ago
    What are some resource one could use to learn 3.)? I know the very basics of postgres since I am a web dev, but I like to learn more about fine tunning rdbms for better performance.
    • pg314 10y ago
      The PostgreSQL manual [1] is an incredible resource. The first 100 or so pages give you a lay of the land. If you read those you will be lightyears ahead of the average web dev. After that you can dig into other chapters/topics that interest you. [1] https://www.postgresql.org/docs/manuals/ https://www.postgresql.org/docs/manuals/
      • Cyph0n 10y ago
        Even though I've never sat down and read it, I can say with confidence that the Postgres manual is one of the most impressive manuals I've ever seen. They could have published the manual as 2 books, and I would have paid money to read them.
        • petergeoghegan 10y ago
          They published it as 3 books. Or, a third party did.
          • Cyph0n 10y ago
            The more you know! It looks like they published the v9.0 reference manual in late 2010. In case anyone is curious, you can order them here: * Volume 1A - http://www.network-theory.co.uk/postgresql9/vol1a/ http://www.network-theory.co.uk/postgresql9/vol1a/ * Volume 1B - http://www.network-theory.co.uk/postgresql9/vol1b/ http://www.network-theory.co.uk/postgresql9/vol1b/ * Volume 2 - http://www.network-theory.co.uk/postgresql9/vol2/ http://www.network-theory.co.uk/postgresql9/vol2/ * Volume 3 - http://www.network-theory.co.uk/postgresql9/vol3/ http://www.network-theory.co.uk/postgresql9/vol3/
        • unixhero 10y ago
  • SmellTheGlove 10y ago
    And yet when I see data engineering positions, particularly around here, it's all about Hadoop/HDFS, Spark, Cassandra, Mongo. I'm not saying there aren't use cases for those, just that it seems like they've become the default basket of options rather than starting with an RDBMS and figuring out why you shouldn't use that. I feel like a dinosaur working with Teradata all these years, but it hasn't failed me. Sure we spend some extra time modeling, and then some extra when we need changes, but other than the cost, it's really been solid.
    • deleted 10y ago
      [deleted]
    • threeseed 10y ago
      > but other than the cost Why do you think people are moving to Hadoop ? That cost that you glossed over is massive. Teradata is very, very expensive. It's great. It works. But it's expensive. Especially if you want to store large amounts of data e.g. networks or web traffic.
      • SmellTheGlove 10y ago
        Fair enough. I've been in large companies for too long, it seems. We'll pinch pennies over things like coffee and continuing education, but we're still paying those Teradata and SAS bills.
  • deleted 10y ago
    [deleted]
  • cygned 10y ago
    RDBMS are more capable in multiple aspects; may it be speed, may it be functionality. I often see developers doing all kinds of things in the application layer; enforcing constraints, manipulating data, even sorting and filtering. It's like some folks are feared of writing SQL or don't trust their database.
    • fuck_google 10y ago
      Ideally RDBMS would be an integrated part of the development environment so that you could refactor your code and your data/schema simultaneously. Unfortunately our IDEs aren't mature enough. Perhaps RDBMSs will gradually evolve into IDEs that combine application logic and data management into a seamless whole.
    • h1d 10y ago
      Maybe they got spoiled by ORM mappers that anything beyond standard CRUD may feel cumbersome to write.
      • webmaven 10y ago
        > > I often see developers doing all kinds of things in the application layer; enforcing constraints, manipulating data, even sorting and filtering. It's like some folks are feared of writing SQL or don't trust their database. > Maybe they got spoiled by ORM mappers that anything beyond standard CRUD may feel cumbersome to write. A decent ORM[0] will use the constraints expressed in the application to reflect those as constraints in the DB schema as well. Why the duplication, you ask? For the same reason that many of the same constraints are imposed in the front-end code: Responsiveness. Validation that happens in the front-end avoids a request/response to the server just to give the user feedback that their password isn't long enough. Relationships and constraints expressed in the application can avoid querying the database just to get the same kind of feedback. This duplication may seem useless, pointless, repetitive, and redundant, but not all accesses to the web server will be via a JS-enabled client, for example anyone accessing the application via an API. [0] eg. SQLAlchemy: http://docs.sqlalchemy.org/en/latest/core/constraints.html http://docs.sqlalchemy.org/en/latest/core/constraints.html
  • deleted 10y ago
    [deleted]
• fapjacks 10y ago
  I think any rewrite is going to be orders of magnitude more efficient anyway. You've got the benefit of hindsight.
  • bjt 10y ago
    But it's not like he wrote RethinkDB and then wrote Postgres with the benefit of hindsight. When he says that the big change was the database and not his own code, I believe him.
    • fapjacks 10y ago
      I believe him, too. I'm not trying to participate in any database war. I've only said that any rewrite is going to have the benefit of hindsight, and because of that, his results are going to reflect that to a degree.
  • vacri 10y ago
    The article talks about more than mere code efficency.
    • fapjacks 10y ago
      I don't mean any more or less than what I said. Rewrites are going to have the benefit of hindsight.
  • bluGill 10y ago
    He outlines several places where he had to do something because postgreSQL doesn't do it, but he suspected before hand that RethinkDB didn't do that well.,,
• bandrami 10y ago
  Isn't that kind of like saying "jackhammers vs. crescent wrenches: my personal experience"?
• brilliantcode 10y ago
  This gives me hope that maybe we could see something similar with Datomic. Perhaps it is possible to implement the same append-only (this is the best way I understand it), immutable audit trail on top of PostgreSQL and still walk away with SQL (datalog is neat-o but has a learning curve). but I dream the dream...
  • tensor 10y ago
    Technically PostgreSQL already has this in terms of the WAL log. The problem is that "rolling back" to a given point of time requires recreating the database from the WAL log so it's not useful in the same way. There are other "remember everything" schemes you can play with clever triggers, but it always comes back to how you end up using the stored data and how easy it is to bring it back to a queryable state.
    • twic 10y ago
      PostgreSQL also has it in the form of its MVCC table structure. If you never delete 'old' rows, you could query old states of the databases by ignoring rows with newer transaction IDs. However, nothing about pgSQL is designed for this approach, so i imagine the performance would be terrible.
      • grzm 10y ago
        nothing about pgSQL is designed for this approach Interestingly enough, that was a part of the original design from the Berkeley days. http://db.cs.berkeley.edu/papers/ERL-M85-95.pdf http://db.cs.berkeley.edu/papers/ERL-M85-95.pdf Our proposed approach is to treat the log as normal data managed by the DBMS which will simplify the recovery code and simultaneously provide support for access to the historical data. ... 3.3. Time Varying Data POSTQUEL allows users to save and query historical data and versions [KATZ85, WOOD83]. By default, data in a relation is never deleted or updated. Conventional retrievals always access the current tuples in the relation. Historical data can be accessed by indicating the desired time when defining a tuple variable. ... Finally, POSTGRES provides support for versions. A version can be created from a relation or a snapshot. Updates to a version do not modify the underlying relation and updates to the underlying relation will be visible through the version unless the value has been modified in the version. One of the purposes of the much-maligned VACUUM command was to push the historical data to archival (optical) media. The archival store holds historical records, and the vacuum demon can ensure that ALL archival records are valid.
• cpr 10y ago
  Thanks for taking the time to write this up, William. This is a great read, even if only as a helpful "this is how I did something hard, and how it turned out" kind of hacker story. And I could see this being quite relevant to some ideas I have for a multi-user semi-real-time cooperative-editing web app.
• mi100hael 10y ago
  > I didn’t seriously consider MySQL since it doesn’t have LISTEN/NOTIFY, and is also GPL licensed, whereas PostgreSQL has a very liberal license. GPL/AGPL is perfectly permissive and doesn't require any sort of special disclosure if you're just running a vanilla distribution of a server without modifying its code...
  • newsat13 10y ago
    Yeah, that line stood out for me as well. Can someone explain why MySQL's GPL license is a problem in practice for his use case?
    • infradig 10y ago
      In the actual article he mentioned a big potential customer who had license limitations... no AGPL! I can undertand someone not wanting to limit the sales audience.
      • daenney 10y ago
        That particular quote was in relation to RethinkDB though, not MySQL: > Then I was in a very long and intense meeting with a potentially major customer for an on-premises install, and one of their basic requirements was “no AGPL in the stack”. With the RethinkDB company gone, there was no way to satisfy that requirement, and my requests went nowhere at the time.
        • sirclueless 10y ago
          The point being that the AGPL licensing of RethinkDB caused problems selling SMC to customers. So why invite those same problems again by replacing it with other GPL'ed software?
          • daenney 10y ago
            I don't know. But if you read further down the article: > All the code I wrote related to this blog post is – ironically – AGPL. Basically it is everything that starts with postgres- here. All the code they just wrote for the Pgsql rewrite is AGPL. Obviously they can relicense since they own the copyright, but it's weird to mention MySQL's GPL license in passing as a reason they didn't went that way yet put your own code up as AGPL.
• StreamBright 10y ago
  Should be titled: Finding out how awesome Postgres is (the hard way)
• grizzles 10y ago
  A really nice thing about code written from the 90s and earlier is that it was designed from the get go to be perfomant, because it had to be. No one in conventional sw writes code like that anymore, not really. eg. When was the last time you used a profiler? I recently updated from Fedora 24 to 25. I noticed a big performance drop until I shoved more ram into my desktop, and now it's fine again. I can't be certain but I'd wager that this might be because F25 is the first Fedora to use Wayland (over X) by default. X might be old and fugly but it was certainly written in an era where it had to achieve a certain baseline level of performance.
  • LeanderK 10y ago
    to be fair, wayland improves upon X in many other areas other than performance and while this might be true for the machine/workloads i did not notice any slowdown running it in a vm (i also don't use that much ram, while an editor/ide might be hungry they only eat a few gigabytes. I rarely run things that really eat ram for lunch).
  • trishume 10y ago
    It's true that Wayland uses more RAM and if you don't have enough swapping will kill you. But in this case (and others) RAM use is inversely correlated with performance. For example Wayland uses more RAM as full backing buffers for every window, and this actually makes moving windows around faster because it doesn't have to ask the app to re-draw exposed areas. Unless you spill into swap, then you are indeed screwed.
    • floatboth 10y ago
      Xorg with a compositor does the exact same thing with full backing buffers for every window. The only way X is using less RAM is if you run without compositing. If you're not using a compositor, you must be either living in the 90s or using a device without a supported GPU. Compositing is not new, people were excited about Compiz desktop cubes and wobbly windows back in the mid-2000s! A Wayland compositor running directly on EGL should always be faster than Xorg with a compositor. Xorg acts as an awkward middleman between your app and the compositor, wasting time on buffer copies.
• ruslan_talpa 10y ago
  Good read and yes, postgres is king, no argument there but i do think the comparison is a bit unfair to RethinkDB. Work on Postgres started in 1986, you can't compare performance of tools when one of them had 30 years to work on performance and the other is like 5y old which had the benefit of only a handful of brains working on it. I would say it's remarkabl what RethinkDB did in this timeframe. In relation to Postgres and real time messages, another approach is to use a real messaging server instead of using only the simple listen/notify interface pg provides. It's possible to connect them using this https://github.com/gmr/pgsql-listen-exchange https://github.com/gmr/pgsql-listen-exchange I am just wrapping up the integration here (http://graphqlapi.com http://graphqlapi.com) and so far it looks good. Postgres provides the power and features we all know and rabbitmq gives you all the realtime capabilities you need, and you can route messages in complex ways and have them delivered to a whole bunch of clients.
  • paulsutter 10y ago
    > you can't compare performance of tools when one of them had 30 years to work on performance and the other is like 5y old Actually, you can and should. In fact that's the whole point. You certainly can't make a judgement on the skills of the developers, but William isn't doing that.
    • ruslan_talpa 10y ago
      You are right, that was not a good choice of words. What i was getting at is that the article reads as "i was constantly fighting Rdb and Pg solved all my problems" and even if ppl don't make judgements on the developers they do so on the tool and that kind of remains in your brain, you attach a stigma to the tool and never consider it even though it will get a lot better if you just give it time. For example, i tried pg back in 2000 something, did just a stupid benchmark on how fast it does a count, compared it to mysql and never looked back, so for 10 years i ignored pg because of that stupid test and because in my brain there was this idea that it's slow. Thank god i somehow got over that ...
      • aidos 10y ago
• jwr 10y ago
  Do I understand correctly that the author went from a distributed database to a single-master scenario? That's a valid tradeoff, but I'd clearly describe it as such. My experiences with RethinkDB have been rather positive, but my load is nowhere near that of what the article describes. I agree that ReQL could be improved, I found that there are too many limitations in chaining once you start using it for more complex things. But the two most important advantages remain for me: * changefeeds (they work for me), * a distributed database that I can run on multiple nodes. I do agree that PostgreSQL is fantastic and that SQL is a fine tool. In my case the above points were the only reasons why I did not use PostgreSQL. EDIT: after thinking about this for a while, I wonder if the RethinkDB changefeed scenario is doable with the tools in PostgreSQL: get initial user data, then get all subsequent changes to that data, with no race conditions. Many workloads seem to concentrate on twitter-like functionality, where the is no clear concept of a change stream beginning and races do not matter.
  • ruslan_talpa 10y ago
    You say that you did not have such loads, so why did you need multiple nodes then? Why the complexity when databases like Postgres can do 1.5M queries per second on a single box? StackOverflow is running just fine on basically one big mssql box (the other is just a standby replica, if i remember correctly)
    • jwr 10y ago
      Two reasons: * I expect a larger load in the future, * I want multiple nodes not just for speed, but mostly for data replication.
      • ruslan_talpa 10y ago
        * you are solving problems you don't have * you can have replicas with postgres also even if you do end up getting the load you are hoping for (though i doubt you will have 1.5 million qps), reads are easily scalable with replicas, and writes are also possible with sharding or tools from CitusData
• rattray 10y ago
  > I wrote code that automates creation of all triggers to do listen/notify Care to elaborate and/or open-source? Sounds potentially enticing.
  • rattray 10y ago
    Author actually linked in the following paragraph, I believe the referenced bit is mostly here: https://github.com/sagemathinc/smc/blob/master/src/smc-hub/postgres-synctable.coffee#L882 https://github.com/sagemathinc/smc/blob/master/src/smc-hub/p... Looks pretty nice!
• bryogenic 10y ago
  How far has postgres come w.r.t. setting up a cluster with automatic fail-over and recovery? I didn't see the author address this aspect of Rethink that has a lot going for it.
  • gaius 10y ago
    Have a look at http://repmgr.org http://repmgr.org
    • williamstein 10y ago
      Thanks. Indeed, I'm just using one PostgreSQL master, with no HA, but plan to set something up later, and have put repmgr on my todo list. (I'm the post author.)
      • nimrody 10y ago
        A single Postgres machine holds all data? So you scale the database by switching to a stronger machine? Or do you plan to shard the data somehow? Do you have any queries iterating over the entire table?
        • williamstein 10y ago
          > A single Postgres machine holds all data? Yes. It's currently using 80GB of disk, so regarding disk space I can scale for years. I have tiering system where older data gets stored in Google cloud storage, and grabbed on demand. So regarding space, there isn't an issue scaling up. It's also trivial to increase disk space on live VMs on GCE. > So you scale the database by switching to a stronger machine? That's my plan. We're at about 50% overall load as I write this, and here's the usage on the database server: "load average: 0.09, 0.16, 0.17". We're collecting historical Prometheus and other monitoring about load and queries, etc. By the time we need 32 cpus (the GCE limit), either the GCE limit will be raised, the company will be dead (no way in hell), or the company will be wildly successful. So in the only case in which I need to scale out, it will be an exciting new problem that I can hire a team to help with, and we'll benefit from understanding exactly what the problem is we need to solve. I have wasted an enormous amount of time until now due to premature worry about scalability, fueled by my naive assumptions about how quickly SageMathCloud would grow.
• eikenberry 10y ago
  IMO the main advantage for RethinkDB is its HA story. Last time I had to manage a PostgreSQL cluster (2012-2013) its HA story was pretty bad. It was limited to a master-slave(s) setup with manual failover and manual cluster rebuilding all dependent on incomplete 3rd party tools. Has PostgreSQL improved on this? A quick googling leads me to believe it hasn't and I'd only even consider it again if it were managed by a 3rd party (eg. aws rds).
  • api 10y ago
    If you want a managed solution RedShift supports PGSQL syntax and connectors now I think. That's a fully managed turn-key DB, but of course it locks you into AWS.
    • mej10 10y ago
      Redshift isn't highly available (if a node goes down you have to wait for it to restore a new one, which takes hours) and only has a small subset of PostgreSQL features. It is really only intended for OLAP (analytics) processing.
    • drewbug01 10y ago
      Postgres and Redshift should not be considered equivalent. It's true that Redshift originated as a fork of Postgres 8, but Postgres has come a _long_ way since then and is quite different. On top of that, Redshift is columnar database, which is an entirely different animal than vanilla Postgres (or any other DBMS). Ref: http://docs.aws.amazon.com/redshift/latest/dg/c_unsupported-postgresql-features.html http://docs.aws.amazon.com/redshift/latest/dg/c_unsupported-... https://en.wikipedia.org/wiki/Amazon_Redshift https://en.wikipedia.org/wiki/Amazon_Redshift
    • dguaraglia 10y ago
      Not meaning to be an ass, but this is really bad advice. Redshift is not supposed to be used like Postgres. Redshift is a data warehousing solution, with completely different tradeoffs, and accommodating completely different work loads than your average Postgres database. For example, you can't create indices on Redshift tables or relationships between them, the consistency story is completely different from Postgres and Redshift is optimized for bulk loads, not millions of discrete inserts/updates a second. Amazon RDS does provide hosted Postgres, and that is what you want to use if you want managed Postgres. Or you can use Herokus's hosted Postgres (it does get expensive with size, though.)
• api 10y ago
  What I didn't see mentioned here is clustering. One of the things that sold us on RethinkDB was how easy it was to cluster compared to PostgreSQL. The latter has poor documentation and it's very hard to know you've done things right... and if you don't the results can be catastrophic failures or mysterious replication problems with cryptic error messages. Edit: also I was led to believe by PG documentation that LISTEN/NOTIFY is impossible across a cluster, which means that code depending on LISTEN/NOTIFY is impossible to cluster. If that's the case you're stuck with master/slave and manual or (scary) automatic failover now. We wanted a system that is masterless (or all-master) in the sense that any node can fail at any time and the system doesn't care. RethinkDB delivers that, at least within the bounds of sane failure scenarios, and it delivers it without requiring a full time DBA to set up and maintain. That's worth a certain amount of CPU, disk, and RAM in exchange for stability and personnel costs, especially when a bare metal 32GB RAM SSD Xeon on OVH is <$200/month fully loaded with monitoring and SLA. So far we've been unable to throw a real world work load at those things that makes them do anything but yawn, and OVH has three data centers in France with private fiber between them allowing for a multi-DC Raft failover cluster. It's pretty sweet. The only thing that would make me reconsider is if the use patterns of our data were really aggressively relational. In that case PGSQL would be a clear winner in terms of the performance of advanced relational operations and the expressivity of SQL for those operations. ReQL gives you some relational features on top of a document DB but it has limitations and is really designed for simpler relational use cases like basic joins.
  • zzzcpan 10y ago
    "We wanted a system that is masterless (or all-master) in the sense that any node can fail at any time and the system doesn't care." Neither PostgreSQL, nor MySQL can do much here and this is actually why we have the nosql movement. These problems are fundamentally unsolvable with the trade offs those RDBMSs made.
    • erichocean 10y ago
      ahem CockroachDB would like a word…
• crad 10y ago
  (Also posted to the comments section of the blog) Great writeup! One of the issues I've run into with LISTEN/NOTIFY is the fact that it's not transaction safe. ie if you call NOTIFY and then encounter an error causing a rollback, you can't undo the NOTIFY. I ended up building a system on top of PgQ (https://wiki.postgresql.org/wiki/SkyTools#PgQ https://wiki.postgresql.org/wiki/SkyTools#PgQ) called Mikkoo (https://github.com/gmr/mikkoo#mikkoo https://github.com/gmr/mikkoo#mikkoo) that uses RabbitMQ to talk with the distributed apps that needed to know about the transaction log. Might be helpful if you end up running into transactional issues with your use of LISTEN/NOTIFY.
  • neilc 10y ago
    NOTIFY is transaction-safe: if the NOTIFY-ing transaction rolls back, the NOTIFY will not be delivered. See the discussion here ("NOTIFY interacts with SQL transactions in some important ways..."): https://www.postgresql.org/docs/9.6/static/sql-notify.html https://www.postgresql.org/docs/9.6/static/sql-notify.html
    • deleted 10y ago
      [deleted]
    • crad 10y ago
      Yup, my bad, thanks. I confused the two problems I worked through in working through my use case. The transaction safety one was not the NOTIFY version of the project. That had different issues, which I need to go back and look at my notes for. My memory is a bit hazy, but IIRC we were seeing notifications dropped under high velocities and needed delivery guarantees.
      • netghost 10y ago
        I seem to remember that when it was first implemented there was a fairly small buffer for queued messages. That might have contributed to your problem. The docs say it's 8GB by default now, which is probably sufficient.
• Kiro 10y ago
  My use case: I run a couple of stateful multiplayer games where I have a Node.js server writing to MongoDB on each player update (e.g. "update player.x to 123"). I only read from the DB when a player logs in and is added to the game or when the server restarts (items etc). As long as a player is online the state is just kept in in memory (a big array of all the players) but is written to the DB every time it's updated. This means the game could theoretically work without a DB at all, the time it takes to write to the DB etc doesn't matter as long as it happens in the correct order. Read speed is also not really relevant since it happens so seldom. The MongoDB document is the same as the player object in Node.js. I've been thinking of migrating to RethinkDB but I've also been looking at PostgreSQL. Would the JSON support cover this sufficiently and would it make sense? I don't need any schemas or anything like that. I just want to be able to add and update JSON objects.
  • raverbashing 10y ago
    You could move to redis
    • Kiro 10y ago
      Thanks. Can I update a specific property without overwriting the whole JSON blob? Or how do I do it effectively?
      • gamesbrainiac 10y ago
        Yes, you can namespace properly, and have that effect.
  • siddboots 10y ago
    The biggest benefit of using an RDBMS is that it forces you to organise your data in a generalised way, and make it more available for queries that haven't been written yet. In your particular use case that doesn't sound very important, as you are basically just using it to store the game state. Postgres's JSON types are really powerful, but using a document store is probably all you need, so I wouldn't recommend switching unless you've identified the tangible benefit. One scenario that I can imagine is if you want to generate statistics across your game data. If you find yourself pulling lots of JSON from the database, and then doing loops, aggregations, or manual joins, within your application code, then switching out to a RDBMS could be a good idea. In that case, you could use postgres to store JSON much as you are doing with Mongo, but then have some in-database ETL to transform that data into a relational schema, which you could query in more powerful ways.
• mglukhovsky 10y ago
  I appreciate the detailed analysis. A few comments: > This post is probably going to make some people involved with RethinkDB very angry at me. Actually, our community has always felt the opposite. Performance and scalability issues are considered bugs worth solving. That may have been the reaction of one or two community members, but that doesn't represent our values at all. > A RethinkDB employee told me he thought I was their biggest user in terms of how hard I was pushing RethinkDB. This may have been true (at the time) in terms of how SMC was using changefeeds, but RethinkDB is used in far more aggressive contexts. Here's a talk from Fidelity about how they used RethinkDB (for 25M customers across 25 nodes): https://www.youtube.com/watch?v=rm2zerSz6aE https://www.youtube.com/watch?v=rm2zerSz6aE SMC did seem to uncover a number of surprising bugs along the way: I would describe it as one of the more forward-thinking use cases that pushed the envelope of some of RethinkDB's newest features. This definitely came with lots of performance issues to solve along the way. I appreciate William’s tenacity and patience in helping us track down and fix these along the way. > In particular, he pointed out this 2015 blog post, in which RethinkDB is consistently 5x-10x slower than MongoDB. It’s worth pointing out that this particular blog post raised serious questions in its methodology, and recent versions of RethinkDB included very significant performance improvements: https://github.com/rethinkdb/rethinkdb/issues/4282 https://github.com/rethinkdb/rethinkdb/issues/4282 > Even then, the proxy nodes would often run at relatively high cpu usage. I never understood why. I'd have to double-check with those who are far more familiar with RethinkDB's proxy mode, but it's because the nodes are parsing and processing queries as well, which can be CPU-intensive. They don't store any data, but if you use ReQL queries in a complex fashion (especially paired with changefeeds) it's going to require more CPU usage. We generally recommend that you run nodes with a lot of cores to take advantage of the parallelized architecture that RethinkDB has. This can get expensive if you aren't running dedicated hardware. > The total disk space usage was an order of magnitude less (800GB versus 80GB). RethinkDB doesn't yet have compression (https://github.com/rethinkdb/rethinkdb/issues/1396 https://github.com/rethinkdb/rethinkdb/issues/1396). Between this fact and running 1/3 the number of replicas, the reduced disk usage is not surprising. > I imagine databases are similar. Using 10x more disk space means 10x more reading and writing to disk, and disk is (way more than) 10x slower than RAM… This isn't necessarily true, especially with SSDs. RethinkDB's storage engine neatly divides its storage into extents that can be logically accessed in an efficient fashion. This is particularly valuable when running on SSDs, which are fundamentally parallelized devices. RethinkDB also caches data in memory as much as possible to avoid going to disk, but using more disk space doesn't immediately translate to lower performance. One other interesting detail: since RethinkDB doesn’t have schemas, it stores the field names of each document individually. This is one of the trade-offs of not having a schema: even with compression, RethinkDB would use more space than Postgres for this reason. (This also impacts performance, since schemaless data is more complicated to parse and process.) > Not listening to users is perhaps not the best approach to building quality software. [referring to microbenchmarks] I think William may have misinterpreted the quote he describes from Slava’s post-mortem. Slava was referring to benchmarks that don’t affect the core performance of the database or production quality of the system, but may look better when you run micro-benchmarks: https://rethinkdb.com/blog/the-benchmark-youre-reading-is-probably-wrong/ https://rethinkdb.com/blog/the-benchmark-youre-reading-is-pr... We have always had an open development process on GitHub to collaboratively decide what features to build, and what their implementation should look like. I’m not certain what design choices William is suggesting we rejected. One has to only look at the proposal for dates and times in RethinkDB to see how this process and open conversation unfolds with our users: https://github.com/rethinkdb/rethinkdb/issues/977 https://github.com/rethinkdb/rethinkdb/issues/977 > Really, what I love is the problems that RethinkDB solved, and where I believed RethinkDB could be 2-3 years from now if brilliant engineers like Daniel Mewes continued to work fulltime on the project. RethinkDB development is proceeding after joining The Linux Foundation, despite the company shutdown. We believe that with a few years of work, RethinkDB will continue to mature as a database to reach Postgres’ level of stability and performance. We’re exploring options for funding dedicated developers long-term as an open-source project. My thoughts: whatever technology you end up picking is going to have tradeoffs depending on your use case (and the maturity of the technology) and it's going to come with baggage. That's true of Postgres, MongoDB, RethinkDB, any programming language you choose, any tools you pick. If you're willing to carry that baggage it can be worth it: especially if it gives you developer velocity or if the problem you're solving is particularly well-suited to the tool. Pick the technology that will have the least baggage for your problem. I often recommend Postgres to people, despite being one of the RethinkDB founders. Pragmatism wins over idealism, every time.
• gamesbrainiac 10y ago
  Quick question, how does one impact PG's query planner other than creating indexes (partial or otherwise)?
  • wiredfool 10y ago
    You can intentionally do calculations to 'break’ indexes and take them out of consideration. (E.g., pg will tend to prefer scanning a primary key index if you're ordering by that in the query. Sometimes this is a very poor choice if there's a more selective index) There are also statistics that can be tweaked on vacuum analyze to improve the accuracy of the planner.
  • netghost 10y ago
    There are different ways you can write queries that result in equivalent results. For instance you might try using inner joins, sub selects, lateral joins, etc.
  • radiowave 10y ago
    Potentially, there was an index which the author knew would yield good performance for a particular query, but the query planner over-estimated the number of rows, and so decided that a sequential scan would be a better choice. By executing the command, "SET enable_seqscan = OFF;" inside a session, you can force postgres to use an index if it possibly can. These sort of problems can occur (for example) when very complicated joins are performed, or when unstructured data is being used as part of a join, as these tend to lead to poor estimates of the number of rows that the different parts of the query will yeild. Relevant docs here: https://www.postgresql.org/docs/current/static/runtime-config-query.html https://www.postgresql.org/docs/current/static/runtime-confi...
    • Groxx 10y ago
      For additional anecdotes, I've had to hint MySQL's planner twice, both times to exclude an index. It'd switch because the cardinality of one index was much better, but there were enough pathological cases (and they were the most-often used) that the planner's choices were proving fatal. It's not too abnormal. Sometimes you know the structure of your data better than the database's internal summary does, so you just give it a nudge in the right direction.
• devmunchies 10y ago
  No mention of RethinkDB joining the Linux foundation?
  • wmf 10y ago
    It happened after he finished migrating to Postgres so it's not that relevant. The timing of the relicensing was pretty bad luck in this case.
• jbhatab 10y ago
  I would LOVE a layer built on top of postgres for reactive db events. +1 If it also configured into graphql like you were mentioning at one point, even sexier.
  • lobster_johnson 10y ago
    We're preparing for an open source release of exactly that -- a document-oriented data store on top of Postgres and Elasticsearch, with transactions, joins, optional schemas, a query language, fine-grained document patches and change feeds. Email me if you want a ping when it's available.
    • floatboth 10y ago
      Why Elasticsearch when Postgres has full-text search built in? It's one of my favorite Postgres features, I basically never have to bother with Elasticsearch/Solr/etc.
      • lobster_johnson 10y ago
        Good question. I hope to create a backend that actually stores data in Postgres, too. We don't really use Elasticsearch because of the fulltext support, although we do use that, too. Our document store is split into two parts: (1) A highly transactional data store (which uses Postgres) which stores master data and where everything is strict; and (2) an eventually-consistent search index (which uses Elasticsearch) where everything is expendable and queries are less strict. This has the benefit of allowing extreme horizontal scalability on the read path, without impacting the performance of the write path, but at the cost of less consistency. By dividing the two, we can control the flow of data into the write path; for example, some clients do batch imports that are indexed more slowly than real-time updates. The challenge with layering the search index on top of Postgres is how to represent the data. The document store manages the schemas for you, so we know to some extent what the data is, but also supports either partially or completely open schemas (where the allowed fields are either partially validated or completely schemaless). We could build tables dynamically from schemas, or we could denormalize the data into a less efficient [id, field, type, value] table, or we could index JSONB documents (GIN, not as efficient as B-trees on normal columns afaik, and limited in some ways). There are a bunch of options.
• iamleppert 10y ago
  It doesn't make sense to me to architect these kinds of applications at the database level. What's wrong with using something like redis pubsub? I don't get the obsession of evented databases, or implementing this kind of thing at the database level. I suppose its attractive to "listen" to a table for changes but the pattern can be implemented elsewhere and with better tools. Databases should be used for persistence, organization and schema of data, have flexible querying, and not much else.
  • gamesbrainiac 10y ago
    I think that is is also a good idea to have a changefeed caching layer on top of PGNOTIFY, and you can definitely use redis for that.
  • danbruc 10y ago
    I am explicitly not taking any side here, but that is certainly a matter of debate. Especially relational database management systems have all this functionality like views and triggers and stored procedures so that you can move a substantial part of your business logic into the database management system and only have very thin clients in order to minimize duplication of business logic across different clients. Treating a database management system as a dumb data store is only one possible philosophy, admittedly the one that seems to be the dominant one currently, at least as far as I can tell.
  • ankrgyl 10y ago
    You can flip this argument and make a similar point: "What's wrong with using something like Postgres LISTEN? I don't get the obsession of redis pubsub, or implementing this kind of thing outside the database. I suppose its attractive to "subscribe" to a collection for changes but the pattern can be implemented inside a database which has better tools." In more constructive terms, databases are familiar to a lot of folks and have reliable guarantees (persistence, ACID) that are generally useful properties. If you're able to achieve the performance you need within the database, then you get a lot of operational benefits from keeping your workload running inside of it. If your workload for some reason is slow inside of a database, then it certainly makes sense to consider specialized alternatives (like Redis). In my experience, however, you can tune a database to perform better than these tools in pretty much every real-world case (i.e. moderate concurrency with realistic load).
• rattray 10y ago
  The author mentioned they're running in Google Cloud Engine. I'm curious; why not use RDS, which has Postgres support? (Especially considering that they are moving from a 3x-redundant setup to one with no redundancy)
  • gamesbrainiac 10y ago
    Ideally because he deploys using containers so all his services are containerized.
  • brianwawok 10y ago
    GCE does not have RDS, RDS is AWS. GCE only has CloudSQL which is MySQL.
• acidity 10y ago
  >>> Weird. OK, I tried it with some other parameters, and it suddenly took 15 seconds at 100% CPU, with PostgreSQL doing some linear scan through data. Using EXPLAIN I found that with full production data the query planner was doing something idiotic in some cases. I learned how to impact the query planner, and then this query went back to taking only a few milliseconds for any input. With this one change to influence the query planner (to actually always use an index I had properly made), things became dramatically faster. Basically the load on the database server went from 100% to well under >>> 5%. I am actually interested in this part. Figuring out issues with EXPLAIN is one of my favorite things.
  • williamstein 10y ago
    I added code to my clients to do "SET enable_nestloop TO off" anytime they connect to the database. This sets a global flag for that session, which disables nestloop query planning. It could indeed impact other unrelated queries, which is deeply disturbing, but I don't know any way to disable nestloop query planning only for a specific query (aside from constantly setting and unsetting that flag?). Incidentally, here is an example of the query that causes all the trouble: "SELECT * FROM file_use WHERE project_id = any(select project_id from projects where users ? '25e2cae4-05c7-4c28-ae22-1e6d3d2e8bb3') ORDER BY last_edited DESC limit 100;" Search for nestloop in https://github.com/sagemathinc/smc/blob/master/src/smc-hub/postgres-base.coffee https://github.com/sagemathinc/smc/blob/master/src/smc-hub/p... to see my relevant client code.
    • malisper 10y ago
      > I don't know any way to disable nestloop query planning only for a specific query You can do that by using SET LOCAL. Here's what your query would become: BEGIN; SET LOCAL enable_nestloop = off; <QUERY> COMMIT; SET LOCAL applies the setting, but only within the current transaction. If you post a link to the output from EXPLAIN, I could probably advise you the right way to handle this query.
      • williamstein 10y ago
• wildchild 10y ago
  OMG what next? Redis vs PostgreSQL?
• thewhitetulip 10y ago
  > I care about solutions, not glorifying a particular piece of code for its own sake I wish everyone were like this!
• jorblumesea 10y ago
  I'm confused, the situation clearly called for a relational database. Almost any RDBMS would have been better than any "schemaless" db in this case. Was it just to get the reactive architecture features? I'm confused why you would architect an application from the database up.
  • StreamBright 10y ago
    Actually I could do it with SQL, key-value store, and even with document stores, not to mention Erlang's Mnesia (whichever category it falls into). I think without understanding the complete picture, write patterns, read patterns, number of open connections etc. it is hard to say it is definitely SQL.
  • williamstein 10y ago
    Nothing was clear to me when I started writing the application. However, after years of running it in production, things did become much clearer, and indeed, a relational database is ideal.
• perlgeek 10y ago
  I don't understand the approach, really. They manage a 25+ node cluster of RethinkDB, but are reluctant to introduce a message broker? In my experience, a message broker isn't such a big operational burden, even more so if it doesn't have to persist messages. And a message broker with pub/sub doesn't make the architecture necessarily more complicated. Somehow that seems like optimizing along the wrong axis.
• 9gunpi 10y ago
  The year people discover RDBMS are quick and reliable. Again.
• h1d 10y ago
  I've always wondered how ReQL didn't look intuitive from this comparison and stayed away but I guess that wasn't a wrong assumption. "Definitely, the act of writing queries in SQL was much faster for me than writing ReQL, despite me having used ReQL seriusly for over a year. There’s something really natural and powerful about SQL." https://www.rethinkdb.com/docs/sql-to-reql/python/ https://www.rethinkdb.com/docs/sql-to-reql/python/
• living-fossil 10y ago
  >Everything is an order of magnitude more efficient using PostgreSQL than it was with RethinkDB. A large part of the sales pitch of "NoSQL" was that traditional RDBMSs couldn't handle "webscale" loads, whatever that meant. Yet somehow, we continue to see PostgreSQL beating Mongo, Rethink, and other trendy "NoSQL" upstarts at performance, one of the primary advantages they're supposed to have over it. Let's be frank. The only reason "NoSQL" exists at all is 20-something hipster programmers being too lazy to learn SQL (let alone relational theory), and ageism--not just against older programmers, but against older technology itself, no matter how fast, powerful, stable, and well-tested it may be. After all, PostgreSQL is "old," having its roots in the Berkeley Ingress project three decades ago. Clearly, something hacked together by a cadre of OSX-using, JSON-slinging hipster programmers MUST be better, right? Nevermind that "NoSQL" itself is technically even older, with "NoSQL" systems like IBM's IMS dating back the 1960s: https://en.wikipedia.org/wiki/IBM_Information_Management_System https://en.wikipedia.org/wiki/IBM_Information_Management_Sys...
  • littlehood 10y ago
    My biggest problem with SQL for systems with web interfaces is that it forces you onto OLTP, when your workflow is just "select data, show to the user" -> "user saves changes, save them in the db". Googling for "asp.net deadlock" gives 160k results.
    • MarHoff 10y ago
      You should have a look at PostgREST, still in development but look like a promising missing link for you use case.
    • jeltz 10y ago
      I probably have not worked enough with ASP.NET but I do not get that reference at all. In my experience deadlocks in databases are rare and do not happen on simple databases.
      • littlehood 10y ago
        Most asp.net workshops rely on SQL Server, which up to version from 2012 did not support MVCC and relied on locking on writes and reads. The problem is, it had problems with web-kind load. If something was writing into a table X, and there would be a parallel query for table X join... (especially including index seek and not just lookups), you'd get into territory of heavy locking. Add ORM to that, which wouldn't let you influence the order of the joined tables and you've just got yourself a deadlock. This happened on a few occasions in a few different projects I've worked on. The real problem is: there is lot of technicalities you need to know and understand, to use it. Some NoSQL solutions (including MongoDb) try to abstract away those technicalities, and that was the point of my comment.
• skc 10y ago
  So are there any actual success stories that the NoSQL movement can point at because it's bizarre to me how NoSQL can still be all the rage when time after time all I read is post mortems detailing painful experience after painful experience. I'm at a stage where I haven't built enough of my current project to make moving back to an RDBMS painful yet, so all this stuff scares me.
  • keithnz 10y ago
    I've read a number of things on HN where people are building massive DBs with Cassandra with good success.
  • phamilton 10y ago
    I've built out several large scale features on DynamoDB and I can say that with a proper design it is really nice to only worry about budget when scaling. Just crank the provisioned read/write capacity up as needed.
  • l1ambda 10y ago
    I built https://github.com/llambda/agilegps https://github.com/llambda/agilegps on RethinkDB. The atomic changefeeds made it easy to have real-time updates of tracked vehicle's locations. Though it might be possible to do that with listen/notify in PostgreSQL too.
• ssfak 10y ago
  I am a bit puzzled about the scalability of listen/notify in Postgres and its use in the article. Each "listener" in the code requires a connection in the database so it's not a good design to have one listen "query" for each user. You will probably need a dedicated connection in a thread (or a limited number of connections) for the database listening functionality. You can possibly use some async PG driver but still on the database end I am not sure how efficient and scalable this solution will be. I can assume that this is a good solution if you don't have (need) a high rate of "notify" statements and a high number of subscribers waiting on "listen". Any comments on these limits of PostgreSQL?
• bartread 10y ago
  Good grief. Well this is a genuinely fascinating post containing a couple of absolutely terrifying insights: "A RethinkDB employee told me he thought I was their biggest user in terms of how hard I was pushing RethinkDB." Erm. If I were working for/a founder of a relatively small company using a product or service, especially one that's so critical to my own business, that is not the sort of thing I'd want to hear from the provider. "Everything was a battle; even trying to do backups was really painful, and eventually we gave up on making proper full consistent backups (instead, backing up only the really important tables via complete JSON dumps)." Holy crap. Well, the story has a happy ending, and I think the point about the fundamental expressiveness of SQL is something that a lot of people miss in the mad dash to adopt "simpler" NoSQL solutions. I personally find SQL verbose and a bit ugly, but I still sort of love it because it's hugely powerful and expressive. I was perhaps 6 or 7 years into my career before I became comfortable with it, but I wish I'd thrown myself into learning it properly sooner because it is so incredibly useful.
• postila 10y ago
  Was curious when were LISTEN/NOTIFY implemented in Postgres. Seems like 6.4 version already had it: https://www.postgresql.org/docs/6.4/static/sql-notify.html https://www.postgresql.org/docs/6.4/static/sql-notify.html 6.4 was released in 1998... https://github.com/postgres/postgres/blob/REL6_4/doc/src/sgml/ref/listen.sgml https://github.com/postgres/postgres/blob/REL6_4/doc/src/sgm...
  • williamstein 10y ago
    Thanks! NOTIFY is much nicer to use today, since you can also include a payload, and there is a clean way to format data to include in the payload using all the new json functions. I understand that NOTIFY has also been sped up over the years...