Let Postgres Do the Work (2013)

http://sorentwo.com/2013/12/30/let-postgres-do-the-work.html

by Jarred • 10 years ago

420 201 10 years ago

Walkman6• 10 years 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• 10 years 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?

Walkman6• 10 years ago

Ok, now I know why! There are a lot of ignorant people here, wow!

sctb• 10 years ago

This account has been posting many unsubstantive comments recently. Please stop and re-read the guidelines: https://news.ycombinator.com/newsguidelines.html. We have to ban accounts that continue on like this.

randall• 10 years 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.

Walkman6• 10 years 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.

jklein113• 10 years 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.

Walkman6• 10 years ago

For me it questions that the author is knowledgable enough to teach me any detail about Ruby.

mikewhy• 10 years ago

Really it's Ruby with the activesupport gem. Not a huge leap in simply calling it Ruby.

Walkman6• 10 years ago

You are the only one cared to elaborate, thank you very much!

jsjohnst3• 10 years 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• 10 years 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.

Sean17082• 10 years ago

Surely if it's Rails then it must also, by definition, be Ruby?

justusw• 10 years ago

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

_uhtu• 10 years 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• 10 years ago

Unless it's a table with high write load. You're basically shifting the expensive part from read time to write time.

malisper• 10 years 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• 10 years 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• 10 years 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.

jklein113• 10 years ago

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

oftenwrong• 10 years 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".

alexnewman2• 10 years ago

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

liquidise• 10 years 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/

vinceguidry• 10 years 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• 10 years ago

Data integrity isn't an optimization.

gfodor3• 10 years 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/

howdopasswordsw• 10 years 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!

gfodor3• 10 years ago

That certainly sounds right -- though I guess I still am very skeptical that the complexity tradeoff there is the right one (at least until you hit the scale that dictates it.)

ako• 10 years ago

And because you can't really report across microservices, everyone is now setting up datalakes, where they aggregate all the data of their services.

blahi• 10 years ago

You can run R code in SQL Server, Oracle, Teradata, Hana and Vertica.

All of these databases kick the crap out of HN darlings like Redshift and BigQuery. Special mention goes to Vertica which is free up to three nodes and/or 1TB data + 1 TB in flex tables. It is, like the rest, analytical AND transactional. Has proper admin features, proper edge case coverage and a truck load of features. It also has a very sensible licensing.

pg314• 10 years ago

You can write your stored procedures in a number of scripting languages. Out of the box PostgreSQL supports Tcl [1], Perl [2] and Python [3]. There's also a general mechanism to register other languages [4].

On github I found [5], which is embeds Ruby in PostgreSQL. I haven't used it and don't know how mature it is.

Using the same scripting language in your stored procedures and application code might lessen the impedance mismatch. It doesn't solve testing, deployment/migrations and tooling.

[1] https://www.postgresql.org/docs/current/static/pltcl.html

[2] https://www.postgresql.org/docs/current/static/plperl.html

[3] https://www.postgresql.org/docs/current/static/plpython.html

[4] https://www.postgresql.org/docs/9.6/static/plhandler.html

[5] https://github.com/knu/postgresql-plruby

inopinatus5• 10 years ago

That pl/ruby embedding is unmaintained for years and doesn't currently build.

To me the most interesting current extension language is javascript, via PL/v8: http://pgxn.org/dist/plv8/doc/plv8.html - now you can have isomorphic code at all three tiers of your web stack.

pjmlp• 10 years ago

Actually most of our enterprise customers prefer to let the database to the work.

Then again we tend to work with the likes of SQL Server, Oracle and similar.

sbov• 10 years ago

I would be interested in seeing what your actual proposed solution to the example problem is here. You seem to be implying that we should pull back the full list of posts and order & limit in memory. This might make sense for some scales and workloads but it definitely wouldn't be my first or second solution for the given example.

gfodor3• 10 years ago

today, lambda (speed layer + batch layer) or kappa architecture (kafka streams) -- ymmv. not sure if I would build that infra out just for this single problem, but generally once that infra is in place it can be applied to many problems including this one.

edit: (assuming i can't get it working with a jury rigged cronjob and the usual dumb solutions that will still use the ruby code. i'd try a bunch of those types of solutions first obv.)

ianamartin4• 10 years ago

I may be in the minority here, and I'll happily accept that if I am. But my rule of thumb is that anything that can be done inside the database should be done inside the database.

But I'm a python/C#/.Net person.

The stored procs/functions go into a repo just like everything else does. It's not that big a deal. Alembic for migrations is pretty solid. And if you're dealing with a large code base, you log changes inside the DB itself. Use the database for version control.

It's really manageable. It requires that your dev team really knows SQL. But I don't think that's a bad thing.

It's fast, gets the job done, and if you're running a language that doesn't make it easy to deal with concurrency, it's nice to just let the database handle transactions.

For everyone who isn't facebook or google, this is the way to go, in my experience.

Let the db do the hard work, let your preferred language be the middleman, and have a reasonable front end framework do the presentation.

I can scale a web app to millions of users on a couple hundred bucks a month of hardware with this approach. You can too.

josteink3• 10 years ago

It's really manageable. It requires that your dev team really knows SQL

I'd question the credentials of any developer who doesn't know SQL. It's an essential skill across the entire IT-indutry. Not knowing it means you're obviously not serious about your job.

And here you're talking like programmers not knowing it is a normal thing... Are we working in the same industry?

kodablah• 10 years ago

my rule of thumb is that anything that can be done inside the database should be done inside the database

For non-distributable (or at least non-multi-master) DBs, you are often using your most expensive and least distributable resource to do your cheapest and most distributable work. My rule of thumb is that unless you need atomicity or the performance (as profiled) makes a real difference, do it outside the DB. Also has the side effect of avoiding system lock-in (at the expense of language lock-in which I think is ok).

Granted, it's subjective, but I've had the most trouble scaling DBs than other systems, and it's usually CPU/mem of business logic pushing the boundaries.

lm2s• 10 years ago

I believe that many people don't use stored procedures/functions because of lack of knowledge/know-how, difficulty or needing something done yesterday (which in itself is related to the previously), unfortunately.

I say this because I've seen it happen multiple times.

memracom3• 10 years ago

We are using Liquibase to manage the SQL code in the database as a separate GIT repository from the several applications that use the database. We have a dev database, and are beginning to use pgTAP for SQL unit tests. Code review for the SQL comes from BitBucket. GitHub works similarly for code review. The SQL code can be versioned and deployed as changesets either ins synchronization with an app change if that is appropriate, or separately. If you also embrace the concept of "database refactoring" then there is less need for migrations in lockstep with an app deploy.

So, putting some of the business logic into the database can be done using modern development best practices.

Yes there are switching costs and it means that developers need to really learn SQL and PostgreSQL's advanced features but it pays off. And if you break the lockstep of db migrations and application deployments then you can easily have people specialize by language.

In fact, I would say that the era of the DBA is fading and what we really need are devops developers with a strong understanding of PostgreSQL who can do db architecture, db engineering, db design, and SQL coding/refactoring.

Probably my perspective is different from a lot of people because I worked many years in large ISPs where Network Engineering folks designed everything, built it in test labs, verified everything, documented how to deploy and then handed it over to an Operations team that actually set up the routers and installed the configuration code. I don't see coding as the top of the pyramid. Instead it is Engineering Design and Architecture of applications that we should all aspire to.

smac84• 10 years 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 :)

oomkiller2• 10 years ago

Doesn't include everything that Horizon does, but it provides a good foundation for building it http://postgrest.com/

smac84• 10 years 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

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

sheraz• 10 years ago

You might want to take a look at dreamfactory[1]. They have a nice app that wraps all manner of services to become a self-hosted MBaaS. I've used it to put a REST interface on my postgres database, views, and stores procs.

[1] - Dreamfactory.com

quink3• 10 years ago

where the model is subject to change

You cite this as the primary reason, but how much easier would this have been without stored procedures and especially views? I would think harder if anything.

smac84• 10 years ago

It can be the primary reason, but it is generally one of a few very good reasons (performance is often another big one, as this article points out).

I will say its importance is somewhat contextual though. The 3 principal contextual factors that make this reason significant are: (1) A large, complex database/model, (2) A rapidly evolving data model (such as during development or prototyping and fast business requirement changes, and (3) a clear division between back-end and front-end guys on your team. Those are the most important factors, but they are not the only ones. When these factors are significant, you really want to encapsulate DB access from client work. The client should simply declaratively say what they want, and this enforced contract is maintained by the back-end team while the back-end team is free to implement the details, presumably better than the front-end team could. For example, if a query initially involves just joining a fact to a dimension, a front-end user might think they could simply write this in their ORM. But, if the model changes and this later requires 4 or 5 other joins to the get the needed data (because the modeler or business or whatever decided it to be so), then the client code can remain as is while the back-end team can rewrite the implementation details, maintaining performance and correctness of implementation as needed.

tsxxst• 10 years 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?

Sean17082• 10 years ago

No.

mooneater• 10 years ago

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

Jarred• 10 years 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.

jsjohnst3• 10 years 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.

jsjohnst3• 10 years ago

Care to expand on that?

czep• 10 years 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.

majewsky7• 10 years 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

phereford16• 10 years 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• 10 years 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?

phereford16• 10 years 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.

quink3• 10 years ago

report

Always pure SQL, at least as you're using a DBMS. Never anything else.

SQL can stretch to full Turing completeness, so it'll always be good enough for that. And between JSON support and PLPGSQL most everything can be done in Postgres.

andrewprock• 10 years ago

Unfortunately, "pure sql" usually means passing in strings, which leaves you without good tooling to manage the code. This can greatly slow down the speed of delivery. It's great if you are in an ossified section of the code/database, but it can be very brittle (or expensive to validate) otherwise.

dllthomas• 10 years ago

This allows for version control to track the changes of the sql function.

You... realize you could just put the SQL itself in git, right? Not saying there aren't potential benefits, but "it can go in version control" isn't one of them.

phereford16• 10 years ago

Absolutely! We use rails. I just mentioned doing it in that method as opposed to throwing it in a migration. That migration shouldn't change in version control. We wanted a history of the changes and an easy way to access it in migrations.

Sorry for not explaining that fully.

pmontra• 10 years ago

You are right but a Rails migration would provide a way to deploy to the database. Far from ideal though, because you could have many migrations with that SQL code, one for each version of the code. Not sure what's the best solution in this case.

azernik4• 10 years ago

Oh absolutely. I've tried doing complicated logic with (MySQL) stored procedures. It's painful. Definitely send the queries from a sane programming language.

One way to think about this is that you just want to take control back from the ORM of the exact database queries sent down the pipe. Which, BTW, is why every ORM worth its salt has functions for dropping down to plain SQL - teach that as best practice for doing computation in SQL queries, not stored procedures.

phereford16• 10 years ago

Most ORMs will not do the level of aggregations you want or need without writing raw SQL within your ORM. If you are already writing raw SQL in your ORM for aggregation level computations, you might as well create a stored procedure, make it IMMUTABLE (if you can) and gain those performance benefits.

At the end of the day, I would just use the tool that has the most comfortability with the team on the project. You can obviously start with an ORM, but there may come a point in time where performance needs a little boost.

odammit• 10 years ago

I've had similar experiences. And it's not always Ruby processing being the slow part but returning multiple result sets to your application layer.

I've seen some huge result sets (thousands of rows) returned to do a simple aggregation for a dashboard widget.

phereford16• 10 years ago

Absolutely agreed. In our particular situation, the ruby calculations were the bottlenecks based on our benchmarks.

wsmith• 10 years ago

I once reduced the running time of a report from 45 minutes to 3 seconds (900x improvement) by moving the code inside the database.

If a programming language wants to stay fast it must eventually become a database. I realize this is an unpopular opinion but popularity is the wrong metric to judge by.

dom06• 10 years ago

Hence Kx, ZODB.

Normal_gaussian• 10 years ago

576 hours -> 12 minutes (2880)

Admittedly the code I was moving away from was hilariously bad. Threaded custom build of PHP bad. Then again I haven't tried to optimise the code I wrote at all

You are absolutely right about fast code becoming a database, this is simply down to the query planner - it can try and do the least possible work for the data you actually have.

inopinatus5• 10 years ago

I recently used temporary per-transaction tables (CREATE TEMPORARY TABLE .. ON COMMIT DROP, basically CTEs that persist across statements, and that can be indexed) with json_to_recordset and turned a three-minute ruby ETL background process into a sub-1-second inline call.

vincentdm• 10 years ago

Do you mean something like Datomic? It has been intriguing me for a while...

harperlee• 10 years ago

One shower idea that I had is the concept of a clojure version that instead of compiling to JVM bytecode, javascript or CLR, compiled to postgreSQL. I think that would be awesome: you could just run the same functions, seamlessly, from the database, through the web server, to the browser. And, whilst of course you need to know about the limitations of the database, it could be great for pushing code to the database seamlessly.

Unfortunately I don't think I have the skills for that, so posting here in the hopes that someone that can likes the idea :)

semi-extrinsic• 10 years ago

I'm not a database guy, but I have to ask: you're talking about 4000 records, which is essentially nothing.

Why not just load all the data from SQL into a DataFrame in Python or R and do the processing there? I've written some pretty big R DataFrame crunching scripts, including very wide-window moving averages operating on tens of billions of records, that run in less than five minutes on my laptop.

Hell, I have a web app built with Flask+Pandas, running on a single t2.small instance, that does complex queries into a 6000 records big chemistry data set based on user input of selectors. It can serve thousands of simultaneous users without noticeable slowdown.

What am I missing here?

phereford16• 10 years ago

4000 records with a one-to-many join (the many part capped at 240 records). The aggregations are done mostly on that join table.

In our original ruby process, it would return just that, 240 records per join and aggregate on that. Our SQL process aggregates that in a stored procedure and returns the flat 4000 records.

We really didn't want to introduce another piece of technology to this stack, hence we didn't look at R or Python or Elixir. We did consider storing all of this inside elasticsearch for a hot second but keeping things in sync at the correct times seemed sub optimal and would add a layer of complexity we were all uncomfortable with.

mst5• 10 years ago

My immediate guess would be that they already knew SQL and had the data in there - and both R and pandas have a tricky initial learning curve if you're not familiar with dataframe style operations (hell, I was a pure math major some years ago and I have to contort my brain to do dataframes whereas I long since got comfortable with SQL).

memracom3• 10 years ago

You can install R or Python into a PostgreSQL server as a language extension and then use it to define SQL functions. This is really useful to define functions which can reduce the size of the recordset transmitted across the network.

You wouldn't likely put the whole app to run on the database server, but typically PostgreSQL makes good use of the available RAM and disk, but leaves lots of CPU cores idle. So running some R and Python on the PostgreSQL server is an overall improvement in performance.

P.S. Yes, I know that PostgreSQL 9.6.1 will use multiple cores for aggregate and join queries on large tables, but few people have upgraded to that yet.

tkyjonathan2• 10 years ago

It could be that each of the 4000 records needs another 10k records each to get the report you are looking for.

bbcbasic2• 10 years ago

27s is still an awfully time. Why does it take so long?

phereford16• 10 years ago

We didn't make it immutable and we didn't add an index to it. Those are our next action items to help improve the performance of it.

Honestly, instead of looking into more performance gains, I wanted the team to get a huge win and for the team to look good in front of senior management :D.

davyjones• 10 years ago

6 hours to 15 minutes here. This is with the right indices and stock install. I am sure an expert can do much better.

dom06• 10 years ago

Correct usage of ORMs if it's not in the database is another important and easy thing to get right. This often gives a 10-1000 times performance improvement just due to the avoided "ping pong" game by using eg. query annotations that tell the ORM which related objects you'll access.

tkyjonathan2• 10 years ago

I took a 20-page multi-threaded ruby code that took 2-4 days to complete and changed it to a 1-page MySQL stored procedure with some @variables. Brought it down to 4 minutes to run. And to top it off, reduce the overall server load from 96% to 20%.

I kept that CPU usage chart and added it to my CV as it was quite impressive to look at.

goflyapig• 10 years 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.

majewsky7• 10 years 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).

kofejnik10• 10 years 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.

phereford16• 10 years 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.

kofejnik10• 10 years 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.

josteink3• 10 years ago

SQL is rather readable these days. It's obviously not as easy to read as ruby

As someone who can't read Ruby but can handle "any level" of SQL fluently, that's obviously not a very objective statement.

dom06• 10 years ago

This sounds like the approach was taken but the necessariy development tools were not created. Hence the painful experience.

kofejnik10• 10 years ago

I don't quite agree it's the tools only, my bigger gripe was total lack of scalability. Basically, even for moderately complex calculations, you can cache a lot of slow-moving stuff (such as products, categories, etc) to redis and do most of stuff on web_worker_{nnn}, using potgres only where you need ACID guarantees (eg orders, payments, etc). When you do everything on your master instance, any heavy calculation can become a problem pretty quickly.

z3t4• 10 years 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• 10 years 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) 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/). Well worth a look. I haven't done much myself with this one, but will likely do so soon.

alexnewman2• 10 years 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• 10 years 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.

memracom3• 10 years 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• 10 years 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• 10 years 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.

rtpg2• 10 years ago

As someone who's thought about embracing over using the ORM, here's a couple issue that come up:

- Version control. The SQL functions end up just being data in your DB. This feels like an anti-feature in the world of continuous integration and code review.

- Lack of composability. An ORM gives you the ability to easily compose filters. This lets me write functions like "take this query, and make sure it's properly filtered down to one user's data". Because of how SQL works, each query needs to be hand-crafted, and it's hard to make parts of it generic.

- Rewriting business logic. I have business logic in Python already, but if I want to query off of that in the DB in SQL, now I need two implementations. You can sort of get around this in an ORM by annotations.

- I'm not sure what the developer tooling environment is like. PyCharm is pretttty nice.

To be honest, you citing that it's easy to get around the validation process when using Postgres is a major red flag. Why would I want to circumvent code review for things that are most undoubtably code?

grzm4• 10 years ago

Version control. The SQL functions end up just being data in your DB. This feels like an anti-feature in the world of continuous integration and code review.

Functions can be treated the same way as migrations. With PostgreSQL you have transactional DDL, so you can atomically deploy and rollback functions. There plenty of different script packages to help manage this. At the end of the day it’s deploy and rollback SQL scripts in text files, which are easily managed with version control.

Lack of composability. An ORM gives you the ability to easily compose filters. This lets me write functions like "take this query, and make sure it's properly filtered down to one user's data". Because of how SQL works, each query needs to be hand-crafted, and it's hard to make parts of it generic.

The trade off here is that you’re trusting the ORM to produce an optimal query (or queries) for whatever you’re trying to do compared with writing the SQL yourself, with knowledge of your schema. Like any trade off, its one you need to decide for yourself.

You may have filtering on the data that you’re returning from the database that you chose not to do in the query itself. At this point, you should just be dealing with data structures. I don’t think this would be different whether you’re using an ORM or not.

Rewriting business logic. I have business logic in Python already, but if I want to query off of that in the DB in SQL, now I need two implementations. You can sort of get around this in an ORM by annotations.

I’m not sure what you’re getting at here, so I’m not sure if this addresses your concerns: If you’ve got clean separation of your code, you should only need to update a single location for any given piece of logic. Single source of truth, and all that.

I'm not sure what the developer tooling environment is like. PyCharm is pretttty nice.

SQL scripts are generally text files, so anything that supports some kind of SQL syntax highlighting is likely all you need. I suspect PyCharm will work. The Jetbrains folks are pretty sharp.

Why would I want to circumvent code review for things that are most undoubtably code?

Since you can have your scripts under version control, you can likey use the same code review tools that you currently do. You can also use libraries such as pgtap[0] to test your schema and functions indepedent of the rest of your application, if you so choose.

These are all issues I've dealt with in a large production PostgreSQL environment. They're good things to think about, and you're right to consider them. At the end, you need to make a decision you and your team is comfortable with.

[0] http://pgtap.org

gfody• 10 years ago

The right way to have composability in a RDBMS is with things like parameterized views or table-valued functions. This would fall under embracing the RDBMS, leveraging its modern capabilities.

ankurdhama• 10 years 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• 10 years 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• 10 years 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• 10 years ago

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

eddieroger• 10 years 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• 10 years 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?

Let Postgres Do the Work (2013)

Related Stories