Hacker News Reader

Hacker News

Comment by iffycan | Hacker News Reader

Parent Full thread

iffycan

I've had success just using letters between a and z.

- The first item added has rank "m"

- If I want to add an item before that, it's m-a/2 (somewhere around "f")

- If I want to put something between "b" and "c" that's "bm"

- The database can sort it just fine.

View on HN

This is the "what about leaving room?" variant described in the submission with either (a) a smaller range of available values if you're limiting yourself to single characters and/or (b) less efficient storage.

It's less efficient storage-wise, sure, but there's no "occasionally take the hit of shifting" problem.

Your strings could get REALLY long.

Suppose you have 3 items:

    id  pos  name
    1   'a'  apple
    2   'm'  banana
    3   'z'  cherry

You move cherry to the middle of the list. Now, you have:

    id  pos  name
    1   'a'  apple
    3   'g'  cherry
    2   'm'  banana

Now you move banana back to the middle of the list, which gives you this:

    id  pos  name
    1   'a'  apple
    2   'd'  banana
    3   'g'  cherry

Keep doing this, and the gap keeps shrinking. You need to make pos longer to be more precise to fit in the gap. Eventually you run out of space in your string field.

I read their response as not limiting themselves to a single character (which you have too, if I read you correctly). If they use a text column, it's practically arbitrarily large. I believe the limit is around 1GB in Postgres. They've also dismissed concerns of storage, so it's a price they're willing to pay.

Yes, they could get long in theory. In practice, in the place I'm currently using this method, the strings are 3 characters max.

Reply on news.ycombinator.com