12 ms·

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 wil

• 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]