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
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?
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.