1

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?

1
  • Tenants are companies and the application has something to do with accounting. Dec 16, 2015 at 21:08

1 Answer 1

2

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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.