14 ms·

Let Postgres Do the Work (2013)

420 points by Jarred 10y ago

Walkman 10y ago
It's unbelievable that an article can get to HN front page in which the author doesn't know the difference between Rails and Ruby. So annoying! This is NOT Ruby for God's sake: SYSTEM_EPOCH = 1.day.ago.to_i
- wolf550e 10y ago
  I thought it was a truism that because of all the rampant monkeypatching, many ruby developers don't know how to use ruby without rails?
- Walkman 10y ago
  Ok, now I know why! There are a lot of ignorant people here, wow!
  - sctb 10y ago
    This account has been posting many unsubstantive comments recently. Please stop and re-read the guidelines: https://news.ycombinator.com/newsguidelines.html https://news.ycombinator.com/newsguidelines.html. We have to ban accounts that continue on like this.
- randall 10y ago
  People are downvoting I think because this is a rather pedantic point to be annoyed by. The tone is one of superiority, rather than pointing out the simple mistake. Calling a rails function a ruby function is unlikely to invalidate the entirety of the article.
  - Walkman 10y ago
    I know the point is somewhat pedantic, but I just don't really like to learn from people who doesn't even know what is what. Somewhat invalidates the article IMO, but I read it anyway, just with very carefully, trying not to learn anything just understanding the basic idea he/she want to tell. I think they downvote because they are so ignorant I can't even believe it.
    - randomdata 10y ago
      Perhaps they are downvoting because of your own ignorance? The aforementioned code does depend on ActiveSupport (or similar library), but has absolutely nothing to do with Rails.
      - Walkman 10y ago
        Thanks for explaining!
    - dvdhnt 10y ago
      class Walkman < Troll def comment self.ignorance += 1 end end Pretty sure that's standard ruby.
      - grzm 10y ago
        Please be civil regardless of the behavior of other users.
        dvdhnt 10y ago
        That was more civil than outright calling multiple users ignorant. Instead, I acknowledged demonstrated ignorance by cheekily posting a valid ruby class definition.
    - grzm 10y ago
      HN users will also down vote for tone and complaining about receiving down votes. Please strive to contribute constructively and in keeping with the guidelines. https://news.ycombinator.com/newsguidelines.html https://news.ycombinator.com/newsguidelines.html
- jklein11 10y ago
  It is Ruby in that it requires a Ruby interpreter to execute that line of code. It certainly isn't Javascript or Python. Is the article missing the context that these lines of code are being executed along side the Rails framework? Sure. Is it distracting from the main meaning of the article? I would say not.
  - Walkman 10y ago
    For me it questions that the author is knowledgable enough to teach me any detail about Ruby.
    - jklein11 10y ago
      That's fair, and definitely warrants skepticism, but was there anything in the article that you found counterfactual? I'm no RoR expert, but I do enjoy looking at how other people solved problems. Is there anything in the article that's counterfactual or that you've experienced causes headaches down the road?
- mikewhy 10y ago
  Really it's Ruby with the activesupport gem. Not a huge leap in simply calling it Ruby.
  - Walkman 10y ago
    You are the only one cared to elaborate, thank you very much!
- jsjohnst 10y ago
  It most certainly is Ruby. It's Ruby with a library (ActiveSupport) added that augments with extra helper methods. When you write Ruby code that adds methods does it suddenly become not Ruby anymore?
- pselbert 10y ago
  I am the original author, and I assure you I know the difference between Ruby and Rails. This was written over 3 years ago, and while I don't remember all of the details, I don't believe conflating Ruby level code with Rails specific code corrupts the article.
- Sean1708 10y ago
  Surely if it's Rails then it must also, by definition, be Ruby?
justusw 10y ago
What is the performance impact on INSERT when using tables with complex indices like the one mentioned in the article?
- coltonv 10y ago
  I'm not the author but I'd guess almost nothing. Basically you just need to calculate the result of the function and index it by the result, should be almost completely unnoticeable.
  - zerd 10y ago
    Unless it's a table with high write load. You're basically shifting the expensive part from read time to write time.
- malisper 10y ago
  I'm maintaining a database cluster with 10s of millions of partial indexes, many of which contain a LIKE pattern. Each table in the cluster has several of these partial indexes. Because of all of these partial indexes, we became CPU bound. For the longest time, we thought all of the CPU was going to evaluating the LIKE patterns. Eventually, I decided to generate a flame graph of our database and discovered that we were only spending 0.3% of the time evaluating the partial index predicates. In turns out most of the CPU usage was evenly split between looking up the metadata of the index and in parsing the index predicate. So the index expression itself doesn't really matter, but having lots of these indexes can become an issue.
blowski 10y ago
I tried doing this, but I found it hard to maintain as it needed more slaves to cope with the load of the complex functions. Also, it was harder to tweak the algorithm for things like "increment the score by x in case y, otherwise increment by z". I guess it's possible, but I just found it too hard to work with. For me, it was easier to use Redis to keep a sorted set of article IDs. I updated the scores in my application code using Redis native functions, and then got a slice of IDs for a `SELECT ... WHERE IN (ids, from, redis)`. It's probably because I'm a rubbish database engineer, so stick with my strengths.
- toast0 10y ago
  > It's probably because I'm a rubbish database engineer, so stick with my strengths. This doesn't make you a rubbish database engineer. Scaling databases is complex, so it makes sense to have your source of truth database do only the things it must do (consistent updates, indexing the ranking value if needed for queries) and things it's good at, and move everything else (calculating the ranking value) to another part of your system. If you can keep database load low enough to avoid sharding, that's a big win; you don't have to not grow your DB, just grow slower than Intel grows CPU performance / accessible RAM.
jklein11 10y ago
I thought the point of using an ORM like Active Record was to decouple the business logic and the database?
- howdopasswordsw 10y ago
  HAAHHAHAHAHAHAHAHHAHAHAHAHAHAHAHAHAHAHAHAHAHA!!!!! There is nothing more database-coupling than Active Record. Other ORMs may have decoupling as a goal, however.
  - jklein11 10y ago
    Ah that makes sense then. I guess I should have prefaced my comment with the fact that I have little Rails experience.
- oftenwrong 10y ago
  It can decouple you from the specific database implementation. The ORM can generate SQL that targets the database you are using by altering the generated queries to account for dialect and feature differences. For example, it can, potentially, make it possible to switch your application from using MySQL to PostgreSQL rather seamlessly. However, in my experience, this is rarely straightforward, and rarely done. It's not unusual for the application layer to become dependent on some quirk or unique feature of the underlying database, and sometimes that dependency is not obvious. The sibling reply is not far off the mark when it comes to using ORMs "in practice".
alexnewman 10y ago
The future is this but backed with super cool FDW for s3 and bigtable
liquidise 10y ago
I've written about this before [1] but i think the higher level message here is about Ruby on Rails developers expanding their database understanding and/or reliance. Given how cleanly ActiveRecord abstracts database porcelain, many devs who started with Rails don't lean on the database enough. This, along with validators, table indexing and foreign keys are some of the more common shortcomings i see in devs who learned on Rails. 1: https://blog.benroux.me/be-the-better-rails-developer/ https://blog.benroux.me/be-the-better-rails-developer/
- vinceguidry 10y ago
  When you're starting out, you don't need these optimizations. Depending on what you're trying to do, you may never need them, and instead find that a caching solution is best.
  - hderms 10y ago
    Data integrity isn't an optimization.
gfodor 10y ago
Cool demonstration of IMMUTABLE functions (TIL), but there's a reason people have moved away from stored procedures/functions in RDBMSs in favor of moving this stuff into much slower application code -- you now have business logic living inside of the database. From there, you have to now think about how this affects: - Testing - Deployment/Migrations - Day-to-day tooling to work on it - Switching costs (mental overhead, using multiple languages) - Documentation - Probably more stuff The ruby code is just like all of your other ruby code and can be grokked and worked on by anyone who is up to speed on your ruby stack. The DB function meanwhile requires jumping through lots of hoops and will always feel like a second class citizen relative to the vast majority of your application code, unless you do a lot of work and maintenance (unlikely to happen without feeling distracting if this code is a relatively small slice of your code base.) In some cases, this can be worth it for massive optimization wins. But the article doesn't really touch on why this might be a bad idea. I do think it highlights the potential of systems like MADLIB [1] where you can push the data crunching into the database, while having that be abstracted away in your primary programming language (in this case, R.) One could imagine a world where you wrote the ruby code and it was somehow mapped in to be run on the database. But that's not the world we live in today unfortunately. It's really a shame, because people using a database as capable of PostgreSQL but with a fairly dumb ORM on top are under-utilizing its potential, for sure, but there's not really a great path afaik to leverage it. (I remember years ago MS introduced running C# inside of SQL Server, I am not sure if that ever ended up panning out into being something smarter than the dumb "upload code to database manually" model we are used it.) [1] http://madlib.incubator.apache.org/ http://madlib.incubator.apache.org/
- howdopasswordsw 10y ago
  While you're right of course, I think you also just described why people have been switching to SOA (cough, I mean microservices). If you encapsulate the database and related logic behind its own service interface, then as you get big enough to care about query performance, then only the team working on that microservice needs to care about how complex/tricky/whatever the optimizations become!
  - gfodor 10y ago
smac8 10y ago
At my last job we finally got our front-end devs to move all client side db work to calls to stored procedures or views. After doing so, nearly all of them fell completely in favor of this method. This is escpecially true with large, complex DBs or data warehouses, where the model is subject to change and reliance on back-end devs better knowledge of the database is more important. That said, despite some of the tradeoffs - for example with testing, or migrating to a new db (which is extremely rare anyway) - it is ultimately far better for client code not to have to know back-end implementation. I think things like graphql are finally making this abundantly clear. What I have been dying for is a real time DB BAAS that is ACID compliant, preferably relational, has a simple rest api, and allows me to write stored procedures that I can call from my client code. Horizon is probably the closest thing out there. Right now using firebase, and sick of the absurd amount of client side code I have to commmit to to pull data from various parts of the database. Requires huge amounts of overfetching, unecessary coupling of client-server code, horrible consistency support, overly loose type structuring, etc. If somebody writes a postgres version of horizon I will pay big money to use it :)
- oomkiller 10y ago
  Doesn't include everything that Horizon does, but it provides a good foundation for building it http://postgrest.com/ http://postgrest.com/
  - smac8 10y ago
    Cool, hadn't seen that. There is also postgraphql, which I have become a big fan of and is a great foundation: https://github.com/calebmer/postgraphql https://github.com/calebmer/postgraphql That said, the real-time aspect is what I'm really dying for, plus a company supporting a BAAS. The ease of firebase is so nice, and you can make really cool real time apps with it very fast. I'm not sure if switching to a relational model would make impose some technical limitation that json stores don't (besides obvious complexity). Postgraphql + socket.io is kind of what i'm thinking is the start
    - ianbicking 10y ago
tsxxst 10y ago
Is it really needed to declare separate functions with all that boilerplate just to multiply and add a few numbers in an SQL statement?
- Sean1708 10y ago
  No.
mooneater 10y ago
"We can replace the timestamp with the primary key for the “recentness” calculation". Very different properties, not a great replacement.
- Jarred 10y ago
  Yeah. If you wanted to delay making posts visible but create them in advance, they wouldn't have the same ranking as though they were just posted. You'd have to create them at the exact time you'd want them to start being displayed in order for the desired ranking to be correct.
  - jsjohnst 10y ago
    The example was a social media site like Instagram, not a blog, which doesn't allow future posting. If you have different design constraints than the given example, obviously the implementation will need to adjusted to the new requirements.
- jsjohnst 10y ago
  Care to expand on that?
- czep 10y ago
  I definitely agree that there may be some unexpected consequences of using the primary key in this way. It certainly won't behave like a timestamp and could distort the function outputs. Would it be possible to substitute the integer count of seconds since epoch (or some other arbitrary base timestamp) and use that instead? Then you'd still have an immutable function to satisfy the index, but also you'd preserve valuable information about the relative time intervals between posts.
  - deleted 10y ago
    [deleted]
- majewsky 10y ago
  No problem, just add a bot that creates a post every second, so that the pace of the primary key approximates that of the timestamp. /s
phereford 10y ago
At work, we have an internally generated report that was done purely in ruby. It worked, barely, munging numbers for about 1000 records. When we hit 4000 records, the report took about 6 hours to run in ruby. We looked for some obvious optimizations like eager loading and some map reduce magic and implemented them. That brought us to a range of 45 minutes to 2 hours. We decided that it was still not good enough. We moved ALL of the report into SQL functions (Postgres) and we have the whole function running in 27 seconds. I agree with some of the top comments that maintaining this is a little trickier than ruby code, but some ideas we came up with in maintenance: 1) Write the SQL in a ruby file that has a class method of sorts that returns the generated function. This allows for version control to track the changes of the sql function. 2) Focus on tests at the interface level. We had great test coverage on the end result prior to moving to SQL. We were able to re utilize those tests to verify accuracy with calculations that are now in SQL. This really isn't for everyone, but for those that want to try something new, it is worth it.
- herge 10y ago
  Have you considered a hybrid approach, like doing the heavy lifting/querying/calculating in Postgres, but the final formatting of the report (something that might change often, or be very finicky to implement in pl/sql or in the db) is left to ruby?
  - phereford 10y ago
    That's exactly what we did. We return an anonymous table of records in Postgres that are then passed to ruby to render an excel file. The SQL query takes 27s on average. The ruby view generation is fast as well but I haven't profiled that part of the report.
    - koolba 10y ago
      > That's exactly what we did. We return an anonymous table of records in Postgres that are then passed to ruby to render an excel file. The SQL query takes 27s on average. The ruby view generation is fast as well but I haven't profiled that part of the report. What's an "anonymous table of records"? Do you mean it's just a result set (i.e. query)?
goflyapig 10y ago
The article makes a good point that if you want ordering by a computed 'ranking' to be performant, your database will need to store and index that value. But then it takes another leap and says that this means your database should actually compute that value. I don't see why that's necessary, and it would bring with it all sorts of issues that other people have mentioned (maintainability, scaling, testing, coupling). It seems you could equally solve the problem by adding an additional indexed 'ranking' column, and computing and storing the value at the same time you insert the row [1]. It seems that's essentially what Postgres would do anyway. Also, I'd note that the algorithm here is very simplistic, and even so, the author had to make a functional change in order to get it to be performant in Postgres. You can't just substitute an ID for a timestamp, just because both are monotonically increasing. The initial Ruby version of this algorithm treats a given 'popularity' as a fixed jump in some amount of time (e.g. 3 hours). The Postgres version treats it as a fixed jump in ranking (e.g. 3 ranks). Those are not equivalent. [1] This does assume that you can control all the database manipulation from a centralized place.
- majewsky 10y ago
  > It seems that's essentially what Postgres would do anyway. Yes, but if Postgres does it, it's literally impossible for the developer to screw up, whereas manually putting the ranking in the table can easily cause inconsistent records if you don't know what you're doing. And even if you know what you're doing, next month's code change might not remember that it's important to update the ranking with the rest of the record (unless you're calculating it in a before_save hook or something).
kofejnik 10y ago
I had to work on/with a rather large backend which used this approach. Most of the logic was in stored procedures, with python/sqlalchemy used as a glue and to process requests. It was absolutely horrible to develop and a nightmare to test. For one, there's nothing approaching ActiveRecord migrations in stability and ease of use, so schema changes on dev/test/deploy were a huge pain. Then, you can't really work in normal "auto-run tests on save" mode as you would with saner frameworks, syncing source from git to postgres is needed (the principal dev altogether preferred to edit directly in pgadmin and then sync). Then, SQL tends to be a lot harder to read than python/ruby, especially so if not well documented and with many join tables. And finally, you can't scale out the heavy stuff, you are limited by your master instance basically. 2/5, would not recommend.
- phereford 10y ago
  With the correct abstractions, you can definitely run tests on save. SQL is rather readable these days. It's obviously not as easy to read as ruby or python and is slightly more verbose, but it is certainly readable. Mileage will vary of course. There are cost/benefit trade offs with every architectural decision made.
  - kofejnik 10y ago
    >> With the correct abstractions, you can definitely run tests on save. You definitely can, but you'll have to roll your own watcher and sync, there's no Guard equivalent that I'm aware of. >> SQL is rather readable these days. Used to think so as well. But then I had to read some pretty nasty sp's which calculated discounts depending on what user already has, and geolocation, and time of day, and day of month, and tons of other stuff, and the return value had to be a complex hierarchy of categories/products. So what would be 2 pages of python turns into 500 lines of SQL with some statements 80 lines long, plus it was still a pain to json-encode in python. The only positive thing about it was that I wasn't the one who had to write it.
    - oelmekki 10y ago
      > You definitely can, but you'll have to roll your own watcher and sync, there's no Guard equivalent that I'm aware of. [shameless plug] here's mine :) https://github.com/oelmekki/pgrebase https://github.com/oelmekki/pgrebase EDIT: sqitch is also often mentioned in that field http://sqitch.org/ http://sqitch.org/
z3t4 10y ago
its often much easier to scale via app "farms" then to scale the db backend. Sometimes db procedures can lower the total db load, but be careful letting the db do too much work.
sbuttgereit 10y ago
PostgreSQL Stored Procedures can absolutely be a pain to manage and change management can be tricky. Having said that these are not problems without solutions and there are some good wins if you can be organized enough to pull it off. First off, plan and organize things. Yes, it would be nice if there were another level of abstraction, like Oracle's packages, but you can use schemas a bit more freely for namespacing. But a little time thinking instead of just coding can avoid much of the spaghetti. Many good devs will think through the rationality of their APIs, this is no different. Second. Use a tool that lets you manage the code-bits like code. Migration tools like FlywayDB, etc. are not good for this because they sacrifice too much to the stateful nature of the database: you end up organizing things on a timeline rather than based on your structure. A better approach is like the one in Sqitch (http://sqitch.org http://sqitch.org) where you organize files according the organization of the database rather than the history of the database... very powerful approach. There are also verify steps which can incorporate testing: and testing the store procedures is easy... it's maging the data in tables across tests that can be hard and that is true regardless where your business logic lives (insofar as those tests can change data). Third there are good testing tools available: The guy that made Sqitch also makes pgTAP (http://pgtap.org/ http://pgtap.org/). Well worth a look. I haven't done much myself with this one, but will likely do so soon.
alexnewman 10y ago
Speaking of this i'm looking at hiring someone to do some OSS rust/postgresql work. Namely writing FDW for s3 and bigtable.
combatentropy 10y ago
For version control, I put .sql files right alongside my other source code, and check them in. I might try snippets out interactively, but after a statement grows so long that it wraps a couple of times, I usually move it to a text file and edit from there. I run it in psql with the \i command: psql> \i test.sql Usually I have one terminal window for the text file opened in vim, and another window for psql where all I do is keep running "\i test.sql". When the code is polished and ready to go live, I turn it into a view or function and move it into a permanent file (views.sql, functions.sql, ddl.sql, make.sql, or whatever you want to call it). This file can be run as many times as I want, idempotently. This is because it starts off dropping all the views or functions that it later defines. You can try instead saying "CREATE OR REPLACE . . ." but PostgreSQL only lets you replace a view or function if its outer appearance stays the same (for views, the output columns can't change; for functions, the input and output can't change). So I find it simpler to just drop all of them ("DROP TABLE foo IF EXISTS...") and then create them (You usually have to drop them in the opposite order you create them, if any depend on one another). I wrap the whole file with BEGIN and COMMIT, so if there is any error the whole thing aborts. Actually I wrap it in BEGIN and ROLLBACK, and run it to see if there are any errors. Only when it's solid, polished, and error-free do I temporarily change ROLLBACK to COMMIT, run it, and immediately change it back to ROLLBACK. The file that gets checked into version control ends in ROLLBACK, for safety. Later if I want to change something, I always do it first by editing the .sql file and then running it from psql. Then I check the changed file into version control. --- For views and functions this is easy, because they don't keep their own data. You can drop and recreate them at any time. For tables, it's a little more complicated. You can't just drop and recreate them, or you'll lose all your data. So for tables I keep their definitions in a separate file. I run this file at an app's inception but seldom afterward. If I need to make a change to a table, I do it interactively, in the command-line tool ("ALTER TABLE foo ADD COLUMN blah ..."). Then I will probably edit the tables.sql file to reflect the change, just so it's in version control. But it's up to you. You can always pull the table definition from Postgres with the pg_dump command.
memracom 10y ago
This is an example of what I call "embracing PostgreSQL". Roughly speaking it means ditching the ORM layers and using a simple API to run any SQL query that you can think up. Then your application can leverage the many features that exist in PostgreSQL and not just treat it as a dumb SQL server. Using functions as an intermediary to raw queries, also decouples the SQL details from the application. What I mean is that if the application calls an SQL function to read, update, insert data then you can revise the SQL functions independently of application code. In other words, if you refactor an SQL function, you can deploy the change to the database without rebuilding and deploying the application. This is especially helpful in companies that have an onerous verification and validation process for application changes, because often "performance improvements" are classified as administrative changes which can be done with less bureaucracy. So, embrace PostgreSQL as a permanent part of your application and reap the benefits of its many powerful features.
- Klathmon 10y ago
  Our application uses JSON throughout, and i've found the JSON functions in postgres to be invaluable! I recently refactored several functions which kind of organically grew from several different functions each with it's own SQL query into one big query. Not only did performance increase by a significant amount, the whole function is basically just "run this query and return the result" as the JSON functions in postgres allow me to leave the actual processing in the database and to not have to do the always annoying "loop over the 30,000 rows that were returned to build a JSON file" thing which is never fast.
  - mozumder 10y ago
    JSON in Postgres is so fast... it's definitely not used widely enough. I'm not even sure why people would use something like Django Rest Framework, when Postgres outputs JSON directly from a query.
    - keshab 10y ago
      Because it is easier to maintain Python code rather than SQL.
ankurdhama 10y ago
The idea is: Compute where your data is and yes data do have a location when computing has to be done with physics(computer).
yandrypozo 10y ago
I think ~0.442ms is very slow for a social media web or app, considering that it's only 1 query, maybe that example isn't the best for that kind of queries
JBiserkov 10y ago
>The integer value of a timestamp is just an increasing counter, ticking off each microsecond. The database itself has another form of increasing counter, the primary key. This observation is either profound or trivial, depending on the mindset. The hundred-fold improvements it brings (along with the immutability) are not trivial, by any measure.
godmodus 10y ago
he mentions 'logarithmic clamping' towards the end, can someone provide a link to what that is? google-fu failing.
eddieroger 10y ago
In one of my gigs, it was normal operating procedure to put logic in sprocs and call out to that from elsewhere. It makes so much sense - RDBMS engines are good at doing math, so we should let them do as much math as possible. ORM layers hide that away, and thinking about these problems in Ruby or Python work, but those languages just aren't as good at plain math as a database can be. I've done this type of thing in Rails and Express apps, and it makes complete sense there. You can even keep the sproc in source control by creating and changing them as migrations. It's tops.
mrwnmonm 10y ago
what happens if i got 10 posts then before getting the second 10 one of the posts that i should get in the second query got 50 likes, it will got higher ranking and i will miss it, right?

420 points by Jarred 10y ago

It's unbelievable that an article can get to HN front page in which the author doesn't know the difference between Rails and Ruby. So annoying! This is NOT Ruby for God's sake: SYSTEM_EPOCH = 1.day.ago.to_i

wolf550e 10y ago
I thought it was a truism that because of all the rampant monkeypatching, many ruby developers don't know how to use ruby without rails?
Walkman 10y ago
Ok, now I know why! There are a lot of ignorant people here, wow!
- sctb 10y ago
  This account has been posting many unsubstantive comments recently. Please stop and re-read the guidelines: https://news.ycombinator.com/newsguidelines.html https://news.ycombinator.com/newsguidelines.html. We have to ban accounts that continue on like this.
randall 10y ago
People are downvoting I think because this is a rather pedantic point to be annoyed by. The tone is one of superiority, rather than pointing out the simple mistake. Calling a rails function a ruby function is unlikely to invalidate the entirety of the article.
- Walkman 10y ago
  I know the point is somewhat pedantic, but I just don't really like to learn from people who doesn't even know what is what. Somewhat invalidates the article IMO, but I read it anyway, just with very carefully, trying not to learn anything just understanding the basic idea he/she want to tell. I think they downvote because they are so ignorant I can't even believe it.
  - randomdata 10y ago
    Perhaps they are downvoting because of your own ignorance? The aforementioned code does depend on ActiveSupport (or similar library), but has absolutely nothing to do with Rails.
    - Walkman 10y ago
      Thanks for explaining!
  - dvdhnt 10y ago
    class Walkman < Troll def comment self.ignorance += 1 end end Pretty sure that's standard ruby.
    - grzm 10y ago
      Please be civil regardless of the behavior of other users.
      - dvdhnt 10y ago
        That was more civil than outright calling multiple users ignorant. Instead, I acknowledged demonstrated ignorance by cheekily posting a valid ruby class definition.
  - grzm 10y ago
    HN users will also down vote for tone and complaining about receiving down votes. Please strive to contribute constructively and in keeping with the guidelines. https://news.ycombinator.com/newsguidelines.html https://news.ycombinator.com/newsguidelines.html
jklein11 10y ago
It is Ruby in that it requires a Ruby interpreter to execute that line of code. It certainly isn't Javascript or Python. Is the article missing the context that these lines of code are being executed along side the Rails framework? Sure. Is it distracting from the main meaning of the article? I would say not.
- Walkman 10y ago
  For me it questions that the author is knowledgable enough to teach me any detail about Ruby.
  - jklein11 10y ago
    That's fair, and definitely warrants skepticism, but was there anything in the article that you found counterfactual? I'm no RoR expert, but I do enjoy looking at how other people solved problems. Is there anything in the article that's counterfactual or that you've experienced causes headaches down the road?
mikewhy 10y ago
Really it's Ruby with the activesupport gem. Not a huge leap in simply calling it Ruby.
- Walkman 10y ago
  You are the only one cared to elaborate, thank you very much!
jsjohnst 10y ago
It most certainly is Ruby. It's Ruby with a library (ActiveSupport) added that augments with extra helper methods. When you write Ruby code that adds methods does it suddenly become not Ruby anymore?
pselbert 10y ago
I am the original author, and I assure you I know the difference between Ruby and Rails. This was written over 3 years ago, and while I don't remember all of the details, I don't believe conflating Ruby level code with Rails specific code corrupts the article.
Sean1708 10y ago
Surely if it's Rails then it must also, by definition, be Ruby?

justusw 10y ago

What is the performance impact on INSERT when using tables with complex indices like the one mentioned in the article?

coltonv 10y ago
I'm not the author but I'd guess almost nothing. Basically you just need to calculate the result of the function and index it by the result, should be almost completely unnoticeable.
- zerd 10y ago
  Unless it's a table with high write load. You're basically shifting the expensive part from read time to write time.
malisper 10y ago
I'm maintaining a database cluster with 10s of millions of partial indexes, many of which contain a LIKE pattern. Each table in the cluster has several of these partial indexes. Because of all of these partial indexes, we became CPU bound. For the longest time, we thought all of the CPU was going to evaluating the LIKE patterns. Eventually, I decided to generate a flame graph of our database and discovered that we were only spending 0.3% of the time evaluating the partial index predicates. In turns out most of the CPU usage was evenly split between looking up the metadata of the index and in parsing the index predicate. So the index expression itself doesn't really matter, but having lots of these indexes can become an issue.

blowski 10y ago

I tried doing this, but I found it hard to maintain as it needed more slaves to cope with the load of the complex functions. Also, it was harder to tweak the algorithm for things like "increment the score by x in case y, otherwise increment by z". I guess it's possible, but I just found it too hard to work with. For me, it was easier to use Redis to keep a sorted set of article IDs. I updated the scores in my application code using Redis native functions, and then got a slice of IDs for a `SELECT ... WHERE IN (ids, from, redis)`. It's probably because I'm a rubbish database engineer, so stick with my strengths.

toast0 10y ago
> It's probably because I'm a rubbish database engineer, so stick with my strengths. This doesn't make you a rubbish database engineer. Scaling databases is complex, so it makes sense to have your source of truth database do only the things it must do (consistent updates, indexing the ranking value if needed for queries) and things it's good at, and move everything else (calculating the ranking value) to another part of your system. If you can keep database load low enough to avoid sharding, that's a big win; you don't have to not grow your DB, just grow slower than Intel grows CPU performance / accessible RAM.

jklein11 10y ago

I thought the point of using an ORM like Active Record was to decouple the business logic and the database?

howdopasswordsw 10y ago
HAAHHAHAHAHAHAHAHHAHAHAHAHAHAHAHAHAHAHAHAHAHA!!!!! There is nothing more database-coupling than Active Record. Other ORMs may have decoupling as a goal, however.
- jklein11 10y ago
  Ah that makes sense then. I guess I should have prefaced my comment with the fact that I have little Rails experience.
oftenwrong 10y ago
It can decouple you from the specific database implementation. The ORM can generate SQL that targets the database you are using by altering the generated queries to account for dialect and feature differences. For example, it can, potentially, make it possible to switch your application from using MySQL to PostgreSQL rather seamlessly. However, in my experience, this is rarely straightforward, and rarely done. It's not unusual for the application layer to become dependent on some quirk or unique feature of the underlying database, and sometimes that dependency is not obvious. The sibling reply is not far off the mark when it comes to using ORMs "in practice".

alexnewman 10y ago

The future is this but backed with super cool FDW for s3 and bigtable

liquidise 10y ago

I've written about this before [1] but i think the higher level message here is about Ruby on Rails developers expanding their database understanding and/or reliance. Given how cleanly ActiveRecord abstracts database porcelain, many devs who started with Rails don't lean on the database enough. This, along with validators, table indexing and foreign keys are some of the more common shortcomings i see in devs who learned on Rails. 1: https://blog.benroux.me/be-the-better-rails-developer/ https://blog.benroux.me/be-the-better-rails-developer/

vinceguidry 10y ago
When you're starting out, you don't need these optimizations. Depending on what you're trying to do, you may never need them, and instead find that a caching solution is best.
- hderms 10y ago
  Data integrity isn't an optimization.

gfodor 10y ago

Cool demonstration of IMMUTABLE functions (TIL), but there's a reason people have moved away from stored procedures/functions in RDBMSs in favor of moving this stuff into much slower application code -- you now have business logic living inside of the database. From there, you have to now think about how this affects: - Testing - Deployment/Migrations - Day-to-day tooling to work on it - Switching costs (mental overhead, using multiple languages) - Documentation - Probably more stuff The ruby code is just like all of your other ruby code and can be grokked and worked on by anyone who is up to speed on your ruby stack. The DB function meanwhile requires jumping through lots of hoops and will always feel like a second class citizen relative to the vast majority of your application code, unless you do a lot of work and maintenance (unlikely to happen without feeling distracting if this code is a relatively small slice of your code base.) In some cases, this can be worth it for massive optimization wins. But the article doesn't really touch on why this might be a bad idea. I do think it highlights the potential of systems like MADLIB [1] where you can push the data crunching into the database, while having that be abstracted away in your primary programming language (in this case, R.) One could imagine a world where you wrote the ruby code and it was somehow mapped in to be run on the database. But that's not the world we live in today unfortunately. It's really a shame, because people using a database as capable of PostgreSQL but with a fairly dumb ORM on top are under-utilizing its potential, for sure, but there's not really a great path afaik to leverage it. (I remember years ago MS introduced running C# inside of SQL Server, I am not sure if that ever ended up panning out into being something smarter than the dumb "upload code to database manually" model we are used it.) [1] http://madlib.incubator.apache.org/ http://madlib.incubator.apache.org/

howdopasswordsw 10y ago
While you're right of course, I think you also just described why people have been switching to SOA (cough, I mean microservices). If you encapsulate the database and related logic behind its own service interface, then as you get big enough to care about query performance, then only the team working on that microservice needs to care about how complex/tricky/whatever the optimizations become!
- gfodor 10y ago

smac8 10y ago

At my last job we finally got our front-end devs to move all client side db work to calls to stored procedures or views. After doing so, nearly all of them fell completely in favor of this method. This is escpecially true with large, complex DBs or data warehouses, where the model is subject to change and reliance on back-end devs better knowledge of the database is more important. That said, despite some of the tradeoffs - for example with testing, or migrating to a new db (which is extremely rare anyway) - it is ultimately far better for client code not to have to know back-end implementation. I think things like graphql are finally making this abundantly clear. What I have been dying for is a real time DB BAAS that is ACID compliant, preferably relational, has a simple rest api, and allows me to write stored procedures that I can call from my client code. Horizon is probably the closest thing out there. Right now using firebase, and sick of the absurd amount of client side code I have to commmit to to pull data from various parts of the database. Requires huge amounts of overfetching, unecessary coupling of client-server code, horrible consistency support, overly loose type structuring, etc. If somebody writes a postgres version of horizon I will pay big money to use it :)

oomkiller 10y ago
Doesn't include everything that Horizon does, but it provides a good foundation for building it http://postgrest.com/ http://postgrest.com/
- smac8 10y ago
  Cool, hadn't seen that. There is also postgraphql, which I have become a big fan of and is a great foundation: https://github.com/calebmer/postgraphql https://github.com/calebmer/postgraphql That said, the real-time aspect is what I'm really dying for, plus a company supporting a BAAS. The ease of firebase is so nice, and you can make really cool real time apps with it very fast. I'm not sure if switching to a relational model would make impose some technical limitation that json stores don't (besides obvious complexity). Postgraphql + socket.io is kind of what i'm thinking is the start
  - ianbicking 10y ago

tsxxst 10y ago

Is it really needed to declare separate functions with all that boilerplate just to multiply and add a few numbers in an SQL statement?

Sean1708 10y ago
No.

mooneater 10y ago

"We can replace the timestamp with the primary key for the “recentness” calculation". Very different properties, not a great replacement.

Jarred 10y ago
Yeah. If you wanted to delay making posts visible but create them in advance, they wouldn't have the same ranking as though they were just posted. You'd have to create them at the exact time you'd want them to start being displayed in order for the desired ranking to be correct.
- jsjohnst 10y ago
  The example was a social media site like Instagram, not a blog, which doesn't allow future posting. If you have different design constraints than the given example, obviously the implementation will need to adjusted to the new requirements.
jsjohnst 10y ago
Care to expand on that?
czep 10y ago
I definitely agree that there may be some unexpected consequences of using the primary key in this way. It certainly won't behave like a timestamp and could distort the function outputs. Would it be possible to substitute the integer count of seconds since epoch (or some other arbitrary base timestamp) and use that instead? Then you'd still have an immutable function to satisfy the index, but also you'd preserve valuable information about the relative time intervals between posts.
- deleted 10y ago
  [deleted]
majewsky 10y ago
No problem, just add a bot that creates a post every second, so that the pace of the primary key approximates that of the timestamp. /s

phereford 10y ago

At work, we have an internally generated report that was done purely in ruby. It worked, barely, munging numbers for about 1000 records. When we hit 4000 records, the report took about 6 hours to run in ruby. We looked for some obvious optimizations like eager loading and some map reduce magic and implemented them. That brought us to a range of 45 minutes to 2 hours. We decided that it was still not good enough. We moved ALL of the report into SQL functions (Postgres) and we have the whole function running in 27 seconds. I agree with some of the top comments that maintaining this is a little trickier than ruby code, but some ideas we came up with in maintenance: 1) Write the SQL in a ruby file that has a class method of sorts that returns the generated function. This allows for version control to track the changes of the sql function. 2) Focus on tests at the interface level. We had great test coverage on the end result prior to moving to SQL. We were able to re utilize those tests to verify accuracy with calculations that are now in SQL. This really isn't for everyone, but for those that want to try something new, it is worth it.

herge 10y ago
Have you considered a hybrid approach, like doing the heavy lifting/querying/calculating in Postgres, but the final formatting of the report (something that might change often, or be very finicky to implement in pl/sql or in the db) is left to ruby?
- phereford 10y ago
  That's exactly what we did. We return an anonymous table of records in Postgres that are then passed to ruby to render an excel file. The SQL query takes 27s on average. The ruby view generation is fast as well but I haven't profiled that part of the report.
  - koolba 10y ago
    > That's exactly what we did. We return an anonymous table of records in Postgres that are then passed to ruby to render an excel file. The SQL query takes 27s on average. The ruby view generation is fast as well but I haven't profiled that part of the report. What's an "anonymous table of records"? Do you mean it's just a result set (i.e. query)?

goflyapig 10y ago

The article makes a good point that if you want ordering by a computed 'ranking' to be performant, your database will need to store and index that value. But then it takes another leap and says that this means your database should actually compute that value. I don't see why that's necessary, and it would bring with it all sorts of issues that other people have mentioned (maintainability, scaling, testing, coupling). It seems you could equally solve the problem by adding an additional indexed 'ranking' column, and computing and storing the value at the same time you insert the row [1]. It seems that's essentially what Postgres would do anyway. Also, I'd note that the algorithm here is very simplistic, and even so, the author had to make a functional change in order to get it to be performant in Postgres. You can't just substitute an ID for a timestamp, just because both are monotonically increasing. The initial Ruby version of this algorithm treats a given 'popularity' as a fixed jump in some amount of time (e.g. 3 hours). The Postgres version treats it as a fixed jump in ranking (e.g. 3 ranks). Those are not equivalent. [1] This does assume that you can control all the database manipulation from a centralized place.

majewsky 10y ago
> It seems that's essentially what Postgres would do anyway. Yes, but if Postgres does it, it's literally impossible for the developer to screw up, whereas manually putting the ranking in the table can easily cause inconsistent records if you don't know what you're doing. And even if you know what you're doing, next month's code change might not remember that it's important to update the ranking with the rest of the record (unless you're calculating it in a before_save hook or something).

kofejnik 10y ago

I had to work on/with a rather large backend which used this approach. Most of the logic was in stored procedures, with python/sqlalchemy used as a glue and to process requests. It was absolutely horrible to develop and a nightmare to test. For one, there's nothing approaching ActiveRecord migrations in stability and ease of use, so schema changes on dev/test/deploy were a huge pain. Then, you can't really work in normal "auto-run tests on save" mode as you would with saner frameworks, syncing source from git to postgres is needed (the principal dev altogether preferred to edit directly in pgadmin and then sync). Then, SQL tends to be a lot harder to read than python/ruby, especially so if not well documented and with many join tables. And finally, you can't scale out the heavy stuff, you are limited by your master instance basically. 2/5, would not recommend.

phereford 10y ago
With the correct abstractions, you can definitely run tests on save. SQL is rather readable these days. It's obviously not as easy to read as ruby or python and is slightly more verbose, but it is certainly readable. Mileage will vary of course. There are cost/benefit trade offs with every architectural decision made.
- kofejnik 10y ago
  >> With the correct abstractions, you can definitely run tests on save. You definitely can, but you'll have to roll your own watcher and sync, there's no Guard equivalent that I'm aware of. >> SQL is rather readable these days. Used to think so as well. But then I had to read some pretty nasty sp's which calculated discounts depending on what user already has, and geolocation, and time of day, and day of month, and tons of other stuff, and the return value had to be a complex hierarchy of categories/products. So what would be 2 pages of python turns into 500 lines of SQL with some statements 80 lines long, plus it was still a pain to json-encode in python. The only positive thing about it was that I wasn't the one who had to write it.
  - oelmekki 10y ago
    > You definitely can, but you'll have to roll your own watcher and sync, there's no Guard equivalent that I'm aware of. [shameless plug] here's mine :) https://github.com/oelmekki/pgrebase https://github.com/oelmekki/pgrebase EDIT: sqitch is also often mentioned in that field http://sqitch.org/ http://sqitch.org/

z3t4 10y ago

its often much easier to scale via app "farms" then to scale the db backend. Sometimes db procedures can lower the total db load, but be careful letting the db do too much work.

sbuttgereit 10y ago

PostgreSQL Stored Procedures can absolutely be a pain to manage and change management can be tricky. Having said that these are not problems without solutions and there are some good wins if you can be organized enough to pull it off. First off, plan and organize things. Yes, it would be nice if there were another level of abstraction, like Oracle's packages, but you can use schemas a bit more freely for namespacing. But a little time thinking instead of just coding can avoid much of the spaghetti. Many good devs will think through the rationality of their APIs, this is no different. Second. Use a tool that lets you manage the code-bits like code. Migration tools like FlywayDB, etc. are not good for this because they sacrifice too much to the stateful nature of the database: you end up organizing things on a timeline rather than based on your structure. A better approach is like the one in Sqitch (http://sqitch.org http://sqitch.org) where you organize files according the organization of the database rather than the history of the database... very powerful approach. There are also verify steps which can incorporate testing: and testing the store procedures is easy... it's maging the data in tables across tests that can be hard and that is true regardless where your business logic lives (insofar as those tests can change data). Third there are good testing tools available: The guy that made Sqitch also makes pgTAP (http://pgtap.org/ http://pgtap.org/). Well worth a look. I haven't done much myself with this one, but will likely do so soon.

alexnewman 10y ago

Speaking of this i'm looking at hiring someone to do some OSS rust/postgresql work. Namely writing FDW for s3 and bigtable.

combatentropy 10y ago

For version control, I put .sql files right alongside my other source code, and check them in. I might try snippets out interactively, but after a statement grows so long that it wraps a couple of times, I usually move it to a text file and edit from there. I run it in psql with the \i command: psql> \i test.sql Usually I have one terminal window for the text file opened in vim, and another window for psql where all I do is keep running "\i test.sql". When the code is polished and ready to go live, I turn it into a view or function and move it into a permanent file (views.sql, functions.sql, ddl.sql, make.sql, or whatever you want to call it). This file can be run as many times as I want, idempotently. This is because it starts off dropping all the views or functions that it later defines. You can try instead saying "CREATE OR REPLACE . . ." but PostgreSQL only lets you replace a view or function if its outer appearance stays the same (for views, the output columns can't change; for functions, the input and output can't change). So I find it simpler to just drop all of them ("DROP TABLE foo IF EXISTS...") and then create them (You usually have to drop them in the opposite order you create them, if any depend on one another). I wrap the whole file with BEGIN and COMMIT, so if there is any error the whole thing aborts. Actually I wrap it in BEGIN and ROLLBACK, and run it to see if there are any errors. Only when it's solid, polished, and error-free do I temporarily change ROLLBACK to COMMIT, run it, and immediately change it back to ROLLBACK. The file that gets checked into version control ends in ROLLBACK, for safety. Later if I want to change something, I always do it first by editing the .sql file and then running it from psql. Then I check the changed file into version control. --- For views and functions this is easy, because they don't keep their own data. You can drop and recreate them at any time. For tables, it's a little more complicated. You can't just drop and recreate them, or you'll lose all your data. So for tables I keep their definitions in a separate file. I run this file at an app's inception but seldom afterward. If I need to make a change to a table, I do it interactively, in the command-line tool ("ALTER TABLE foo ADD COLUMN blah ..."). Then I will probably edit the tables.sql file to reflect the change, just so it's in version control. But it's up to you. You can always pull the table definition from Postgres with the pg_dump command.

memracom 10y ago

This is an example of what I call "embracing PostgreSQL". Roughly speaking it means ditching the ORM layers and using a simple API to run any SQL query that you can think up. Then your application can leverage the many features that exist in PostgreSQL and not just treat it as a dumb SQL server. Using functions as an intermediary to raw queries, also decouples the SQL details from the application. What I mean is that if the application calls an SQL function to read, update, insert data then you can revise the SQL functions independently of application code. In other words, if you refactor an SQL function, you can deploy the change to the database without rebuilding and deploying the application. This is especially helpful in companies that have an onerous verification and validation process for application changes, because often "performance improvements" are classified as administrative changes which can be done with less bureaucracy. So, embrace PostgreSQL as a permanent part of your application and reap the benefits of its many powerful features.

Klathmon 10y ago
Our application uses JSON throughout, and i've found the JSON functions in postgres to be invaluable! I recently refactored several functions which kind of organically grew from several different functions each with it's own SQL query into one big query. Not only did performance increase by a significant amount, the whole function is basically just "run this query and return the result" as the JSON functions in postgres allow me to leave the actual processing in the database and to not have to do the always annoying "loop over the 30,000 rows that were returned to build a JSON file" thing which is never fast.
- mozumder 10y ago
  JSON in Postgres is so fast... it's definitely not used widely enough. I'm not even sure why people would use something like Django Rest Framework, when Postgres outputs JSON directly from a query.
  - keshab 10y ago
    Because it is easier to maintain Python code rather than SQL.

ankurdhama 10y ago

The idea is: Compute where your data is and yes data do have a location when computing has to be done with physics(computer).

yandrypozo 10y ago

I think ~0.442ms is very slow for a social media web or app, considering that it's only 1 query, maybe that example isn't the best for that kind of queries

JBiserkov 10y ago

>The integer value of a timestamp is just an increasing counter, ticking off each microsecond. The database itself has another form of increasing counter, the primary key. This observation is either profound or trivial, depending on the mindset. The hundred-fold improvements it brings (along with the immutability) are not trivial, by any measure.

godmodus 10y ago

he mentions 'logarithmic clamping' towards the end, can someone provide a link to what that is? google-fu failing.

eddieroger 10y ago

In one of my gigs, it was normal operating procedure to put logic in sprocs and call out to that from elsewhere. It makes so much sense - RDBMS engines are good at doing math, so we should let them do as much math as possible. ORM layers hide that away, and thinking about these problems in Ruby or Python work, but those languages just aren't as good at plain math as a database can be. I've done this type of thing in Rails and Express apps, and it makes complete sense there. You can even keep the sproc in source control by creating and changing them as migrations. It's tops.

mrwnmonm 10y ago

what happens if i got 10 posts then before getting the second 10 one of the posts that i should get in the second query got 50 likes, it will got higher ranking and i will miss it, right?