If the objective is to minimize number of database operations per reordering operation:

Assuming that

• All shopping items can be enumerated with 32-bit integers.
• There is a maximum size limit for a user's wish list. (I saw some popular website use 20 - 40 items as limit)

Store the user's sorted wish list as a packed sequence of integers (integer arrays) in one column. Every time the wish list is reordered, the entire array (single row; single column) is updated - which is to be performed with a single SQL update.

http://www.postgresql.org/docs/8.2/static/arrays.html

If the objective is different, stick with the "position column" approach.

Regarding the "speed", make sure to benchmark the stored procedure approach. While issuing 20+ separate updates for one wish list shuffle may be slow, there might be a fast way using stored procedure.

If the objective is to minimize number of database operations per reordering operation:

Assuming that

• All shopping items can be enumerated with 32-bit integers.
• There is a maximum size limit for a user's wish list. (I saw some popular website use 20 - 40 items as limit)

Store the user's sorted wish list as a packed sequence of integers (integer arrays) in one column. Every time the wish list is reordered, the entire array (single row; single column) is updated - which is to be performed with a single SQL update.

https://www.postgresql.org/docs/current/static/arrays.html

If the objective is different, stick with the "position column" approach.

Regarding the "speed", make sure to benchmark the stored procedure approach. While issuing 20+ separate updates for one wish list shuffle may be slow, there might be a fast way using stored procedure.

If the objective is to minimize number of database operations per reordering operation:

Assuming that

• All shopping items can be enumerated with 32-bit integers.
• There is a maximum size limit for a user's wish list. (I saw some popular website use 20 - 40 items as limit)

Store the user's sorted wish list as a packed sequence of integers (integer arrays) in one column. Every time the wish list is reordered, the entire array (single row; single column) is updated - which is to be performed with a single SQL update.

http://www.postgresql.org/docs/8.2/static/arrays.html

If the objective is different, stick with the "position column" approach.

If the objective is to minimize number of database operations per reordering operation:

Assuming that

• All shopping items can be enumerated with 32-bit integers.
• There is a maximum size limit for a user's wish list. (I saw some popular website use 20 - 40 items as limit)

Store the user's sorted wish list as a packed sequence of integers (integer arrays) in one column. Every time the wish list is reordered, the entire array (single row; single column) is updated - which is to be performed with a single SQL update.

http://www.postgresql.org/docs/8.2/static/arrays.html

If the objective is different, stick with the "position column" approach.

Regarding the "speed", make sure to benchmark the stored procedure approach. While issuing 20+ separate updates for one wish list shuffle may be slow, there might be a fast way using stored procedure.