PostgreSQL autoincrement up to certain number then reset for sharding

postgresqlsharding

I've was recently reading an article around how Instgram used to shard their IDs and they use a unique function to generate ids on their servers https://instagram-engineering.com/sharding-ids-at-instagram-1cf5a71e5a5c

But it got me curious around whether if you could just assign a shard to a particular user rather than every item and how performant it would be. Especially in a large scale website.

So would a function similar to the following be a valid approach.

Assuming a users table on a dedicated database purely for users.

User
– UID
– name
– shardId

Where shardId is calculated via a function that starts at 1, and for every new insert it would increment – upto a max of n (say 2048) for this example. Then reset to 1. Almost acting as a round robin to distribute users data among a shards.

Would there be serious performance implications to such a function, especially during high load? As serial columns simply select a nextval from a sequence do they not? It's just an extra step of reseting a sequence once it reaches n. Or am I barking up the wrong tree?

Best Answer

As serial columns simply select a nextval from a sequence do they not? It's just an extra step of reseting a sequence once it reaches n

Not even an extra step because cycling is a built-in functionality of sequences, See CREATE SEQUENCE.

Example:

=> create sequence s maxvalue 5 cycle;

=> select nextval('s') from generate_series(1,7);
 nextval 
---------
       1
       2
       3
       4
       5
       1
       2

Related Solutions

Mongodb – Sharding Mongodb while limiting the number of documents to be migrated

For your 7 collections on the primary shard:

Enter as administrator on the primary replica (if you shard your replica) and create your collection there by inserting 1 document or creating an index there. When you create a collection via the mongos - shard client - then the collection is started on a random shard; if you create it on the shard itself first, then you know it's on the one you want. shard1$ mongo --port 27018 localhost/mydb --eval 'db.mycol.insert({firstdocument:'hello'})'
For the 25 collections:

Standard sharding indeed as you mention. Be patient with the distribution. The first shards will start distributing after a certain amount of data, can be 100k documents if the documents are small.
For the big collection:

Have a look at shard-tags. So, you have to tag your collection on shard1 with let's say : {status:'in_use'}; shard2 is tagged {status:'freeBeer'}. Then depending on the status in the document, the shard is chosen.
For the disk issue, I think you gonna have to write a script. Get a warning when 60% of a disk is used, and another warning at 80% to take action. Then you'll need to upgrade your disk or redistribute the chunks. Certainly the 2TB collection could give you space via the tag, to send more to shard2. Or, add a shard3 ... what is actually the purpose of sharding, to grow horizontally.
Choose shardKeys well, practice configuration and setup on a small server, practice moving chunks, adding tags, ....
Have a look at the AllChunkInfo script of Adam Comerford for you scripts and testing : https://github.com/comerford/mongodb-scripts

Postgresql – Row number with reset in PostgreSQL

Window functions and a single subquery:

SELECT user_id, date, is_cleared
     , count(*) OVER (PARTITION BY user_id, grp ORDER BY date) AS custom_rank
FROM  (
   SELECT user_id, date, is_cleared
        , count(is_cleared = 1 OR NULL) OVER (PARTITION BY user_id ORDER BY date DESC) AS grp
   FROM   tbl
   ) t
ORDER  BY user_id, date;

The special difficulty here: each partition ends with 1. In most similar cases, each partition would start with 1. The simple solution is to count occurrences of 1 in descending order. This way each partition includes the next 1 if it exists. Voilá.

Assuming all involved columns to be NOT NULL and (user_id, date) unique.

Basics:

Select longest continuous sequence

Asides:

The first row of B gets custom_rank = 1. Not 2.
The column is_cleared should really be boolean.
A timestamp column shouldn't be called "date".

Best Answer

Related Solutions

Mongodb – Sharding Mongodb while limiting the number of documents to be migrated

Postgresql – Row number with reset in PostgreSQL

Related Question