sql - Is it possible to use a PG sequence on a per record label? -

does postgresql 9.2+ provide functionality make possible generate sequence namespaced particular value? example:

 .. | user_id | seq_id | body | ...  ----------------------------------   - |    4    |   1    |  "abc...."   - |    4    |   2    |  "def...."   - |    5    |   1    |  "ghi...."   - |    5    |   2    |  "xyz...."   - |    5    |   3    |  "123...." 

this useful generate custom urls user:

domain.me/username_4/posts/1     domain.me/username_4/posts/2  domain.me/username_5/posts/1 domain.me/username_5/posts/2 domain.me/username_5/posts/3 

i did not find in pg docs (regarding sequence , sequence functions) this. sub-queries in insert statement or custom pg functions other options?

maybe answer little off-piste, consider partitioning data , giving each user own partitioned table posts.

there's bit of overhead setup need triggers managing ddl statements partitions, result in each user having own table of posts, along own sequence benefit of being able treat posts 1 big table also.

general gist of concept...

psql# create table posts (user_id integer, seq_id integer); create table  psql# create table posts_001 (seq_id serial) inherits (posts); create table  psql# create table posts_002 (seq_id serial) inherits (posts); create table  psql# insert posts_001 values (1); insert 0 1  psql# insert posts_001 values (1); insert 0 1  psql# insert posts_002 values (2); insert 0 1  psql# insert posts_002 values (2); insert 0 1  psql# select * posts;  user_id | seq_id  ---------+--------        1 |      1        1 |      2        2 |      1        2 |      2 (4 rows) 

i left out rather important check constraints in above setup, make sure read docs how these kinds of setups used