Generate incrementing IDs without sequences

Question

Suppose there's a multi-tenant application where users can create some kind of documents with basic structure like

CREATE TABLE users (id SERIAL PRIMARY KEY, email TEXT);
CREATE TABLE documents (
 id SERIAL PRIMARY KEY
, document_id INT NOT NULL
, user_id INT NOT NULL
, text TEXT);

For each user document_id starts with 1 and increases preferably with gaps being a rare occurrence. The obvious solution is to create a sequence for each user get the document_id from there. But according to this databases don't behave well when there are lots of relations there. Another solution is to store next_document_id in users table and update it as necessary, but that means the lock on this row will be highly contested slowing simultaneous transactions from the same user. Any other ideas?

• DirtyOldMan: Tenants are companies and the application has something to do with accounting.

Answer 1

Stick to one serial column per document and create a gap-less sequence per user_id dynamically in a VIEW - if you really need it.

CREATE TABLE users (
 user_id serial PRIMARY KEY
, email text
);
CREATE TABLE document (
 document_id serial PRIMARY KEY
, user_id int NOT NULL
, document text);
CREATE VIEW document_with_rn_per_user AS
SELECT *, row_number() OVER (PARTITION BY user_id
 ORDER BY document_id) AS doc_per_usr_id
FROM document;

Never use basic type names like text as identifier. It's allowed, but it makes queries and error messages confusing. Generally, use descriptive names.

Related:

• Compacting a sequence in PostgreSQL

• Chris F Carroll: When using a view, do we not also need the insert to be done inside a repeatable read transaction? Otherwise two threads could calculate the same doc_per_usr_id and then both try to insert it.
• Chris F Carroll: Or - sorry - in Postgres, repeatable read is sufficient, but in general you have to guarantee that no new rows have been created between querying the view and using the new id. i.e. you neede isolation level serializable, to rule out phantom reads?