Hacker News new | comments | show | ask | jobs | submit login

One of my favorite idioms lately for doing bulk updates without incurring lock contention is to chain CTEs, like so:

  with candidate_rows as (
     select id
       from table
      where conditions
      limit 1000
        for update nowait
  ), update_rows as (
     update table
        set column = value
       from candidate_rows
      where candidate_rows.id = table.id
  returning table.id
  )
  select count(1) from update_rows;
...and loop on issuing that query until the "count(1)" returns zero some for number of iterations (three works pretty well).

Want to add a column to your "orders" table and populate it without blocking concurrent writes for as long as it will take to rewrite a multi-million row table? Want to re-hash user passwords using something stronger than MD5, but not prevent users from ... you know, logging in for the duration?

CTEs are all that and the bag of chips.




Do you have any resources for learning cool advanced SQL like this? I've used CTEs in the past, just from reading about them in the documentation, but I feel like I'm only scratching the surface of what's possible out there.


http://www.postgresql.org/docs/

Just read it. Seriously. Literally cover to cover, or nearly so. (Maybe simply scan the C-related sections if you're not into that, but do take note they exist. Similarly for some pl/ sections.) It might take you a few evenings, but you won't regret it. The Postgres docs is one of the best out there...


Other than The Fine Manual, linked by a sibling comment, not really. Everything I've learned, I've just picked up along the way.

EDIT: When I wanted to learn about CTEs, for example, I conveniently had a large, ugly materialized view that needed refactoring, and they happened to fit the need perfectly. The previous version was, in places, five or six layers of subqueries deep, many of them repeated several times as they were reused. It hurt to read. Rewriting it to use CTEs made it about eight times faster, and the SQL script was a third the size of the original.


The author of the slides, Markus Winand, wrote the book "SQL Performance Explained".


The book appears to me to be the contents of use-the-index-luke.com. My friends impulsed bought it when I told him I was planning on buying it after confirming that it had more content than the website but after skimming the table of contents it doesn't appear to have anything more than the website.

Still, its good to support Markus' effort and if you prefer having a PDF rather than going to the website, just buy it. It's like $15.


Quoting from http://Use-The-Index-Luke.com/ (the main page):

Use The Index, Luke is the free web-edition of SQL Performance Explained. If you like this site, consider getting the book. Also have a look at the shop for other cool stuff that supports this site.

http://use-the-index-luke.com/shop


I was in his Modern SQL talk at FOSDEM on Saturday, he clearly knows his stuff. His book is definitely on my list.


X-comment so you get it too:

Quoting from http://Use-The-Index-Luke.com/ (the main page):

Use The Index, Luke is the free web-edition of SQL Performance Explained. If you like this site, consider getting the book. Also have a look at the shop for other cool stuff that supports this site.

http://use-the-index-luke.com/shop


Second that request. What can I read to go beyond my SELECT * from t order by c-level knowledge?


Apologies for the self-plug, but you might find some stuff in the later-stage Aggregates and Recursive exercises on http://pgexercises.com . There's a bunch of stuff on CTEs and window functions in there.


I like Date's SQL and Relational Theory (http://shop.oreilly.com/product/0636920022879.do). It won't tell you about the latest changes to the standard, but it will help you get a handle on the relational model, which is the key to using SQL intelligently.


"Want to add a column to your "orders" table and populate it without blocking concurrent writes for as long as it will take to rewrite a multi-million row table?"

PostgreSQL ALTER TABLE ... ADD COLUMN is an O(1) operation, and requires no data rewrite (as long as you are OK with NULLs).

You can even add an attribute to a composite type, and existing tables using that type will see the extra attribute. Again, O(1), no data rewrite.


Yeah, it's not about adding the column; I know that's instantaneous. I'm talking about the UPDATE you'll have to issue to populate the column once it's been added — unless you're okay with it staying NULL (or doing some sort of "lazy loading" is viable in your specific scenario). If you try to do that for the entire table in one go, you're contending with concurrent writes, incurring a lot of IO at once, and as much as doubling the disk size of your table.

This way, you're hitting a limited number of rows per iteration, significantly softening the IO impact (granted, at the expense of wall-clock time); the NOWAIT fails fast on rows that have write locks out against them when you try to grab them; and (if the column you're populating is indexed, obviating HOT updates) leaving a radically smaller number of dead tuples — particularly if you up-tune autovacuum on the table in question while doing this (though that can mitigate some of the reduction in disk IO).


Could you elaborate on why there'd be a smaller number of dead tuples? Are you just taking into account the autovacuuming that can take place as you continue through the iterations?


Primarily, yes. Once autovacuum is marking dead tuple space as reusable (dead tuple space that was largely created by earlier iterations of the loop issuing your query), subsequent iterations can use that space for their new tuples, and the table should reach steady-state size, or at the very least, grow much more slowly. This is even more likely when you make autovacuum more aggressive on the table in question while you're doing this.

Additionally, however, if you're bulk rewriting the table in one query, autovacuum has no (or very limited) opportunity to mark dead tuples as truly dead — especially if there's any lock contention going on, so other, older xids are waiting for your bulk update to complete — so in the worst case your table is half dead tuples, and twice the size it needs to be.


Really? I had the impression that if you added a column to a composite-type, the rows that have that composite-type as a column will be rewriten (tested in 9.3 couple of months ago)?


> re-hash user passwords using something stronger than MD5

Isn't this only possible if you do it on user login - unless you are also cracking user passwords... Actually has anybody cracked their own MD5 user passwords to upgrade them?


You can sort of upgrade hashes in place at the cost of extra book keeping.

Take the old hash, and use it as the input to a new stronger hash. Mark some column to indicate you've done this. Then next time the user logs in, you calculate the old hash and then the new hash from that. Once you validate the password use it to calculate a new new hash, put that in the field and clear the update column.


You don't even really need the book keeping - you can update the hash in place as you say, then just try both eg bcrypt . password and bcrypt . md5 . password. It would be even simpler to style all future passwords as bcrypt . md5 . password but I'm not sure of the security implications of that as a permanent measure and so wouldn't do it personally.


Security wise bcrypt(md5(password)) is at least as strong as bcrypt(password). However broken md5 is it is certainly no easier to find a preimage than where you already have the preimage. (Edit: now that I think about it, it could be worse if there is a side channel in your md5 implementation.)

If you do it that way you'll pay a performance cost on every password check forever. I suppose that trade-off might worth it in some cases.


So much of password hashing is a deliberate performance cost anyways, it almost hardly matters if your password passed through md5 and sha{1,2}. bcrypt will eclipse them all in relative cost, as it should be.


This is exactly what facebook do. They've done it several times over, such that a plaintext password goes through about 8 steps before making it into a database.


You might be right. It was a while ago that we did that, and I may have conflated it with another bulk update of our "users" table.

Either way, I've used this idiom at least half a dozen times in production, all without any downtime or user-visible effect, and we have millions of orders, SKUs and users.


Is this really different from using a transaction with separate read (using an update lock) and update statements, and looping over that in the same way from the application until the count is 0?


Yes. That incurs many of the consequences of doing them all in one query (particularly lock contention and dead tuple bloat), and takes longer — making those consequences worse than doing it in a single query.




Guidelines | FAQ | Support | API | Security | Lists | Bookmarklet | DMCA | Apply to YC | Contact

Search: