I'm designing a CRM for sensitive client information, including legal documents and case-related records. The system will eventually support millions of documents, multiple concurrent users, strict access controls, and substantial growth. Security and reliability matter more than raw performance or horizontal scalability.
I'm comparing a conventional PostgreSQL architecture with a distributed SQL option such as CockroachDB. I've worked with a CRM built on PostgreSQL before, so I'm wondering whether distributed SQL would solve a real requirement or simply add unnecessary complexity.
Would PostgreSQL with high availability, replication, backups, and object storage be the sensible default? Should the documents themselves live in object storage, with PostgreSQL holding metadata and references? What security architecture would you consider essential for sensitive legal data, including tenant isolation, RBAC or ABAC, audit logging, encryption, key management, backups, and disaster recovery?
At what point does PostgreSQL become a bottleneck for this kind of application? What CockroachDB failure modes or behavioral differences should someone with a PostgreSQL background understand? I'm less interested in which database is faster and more interested in the architecture you would trust in production for legal, healthcare, financial, or similarly sensitive documents.
3 Answers
For this workload, PostgreSQL would be my default. Millions of document records and their metadata are not especially large for PostgreSQL; the files should be handled separately. Use managed or well-operated HA, streaming replication or an equivalent failover design, point-in-time recovery, tested restore procedures, monitoring, and a documented disaster-recovery plan. CockroachDB becomes more compelling when you genuinely need multi-region writes, survival of a regional failure without manual failover, or strict data-placement requirements. Otherwise, its operational and application-level complexity may not provide much benefit.
Keep the document bytes in private object storage and store the object key, checksum, size, content type, version, and related metadata in PostgreSQL. Have the application authorize access and issue short-lived signed URLs rather than exposing the bucket directly. Object storage is a better fit for large files, but it still needs versioning, lifecycle controls, access logging, replication where appropriate, and tested backups or recovery procedures. Signed URLs do not replace encryption, key management, authorization checks, or audit trails.
Be careful with the differences between the two databases. CockroachDB uses serializable isolation by default, so transactions can legitimately abort with retry errors under contention. Application code needs retry handling, especially around hot rows and counters. Some PostgreSQL features and assumptions also do not carry over directly, such as LISTEN/NOTIFY and certain extension or locking behaviors.
With PostgreSQL, RLS can be useful for tenant isolation and sometimes for document-level authorization, but it should not be the only security layer. Ensure the application role does not own the tables, use FORCE ROW LEVEL SECURITY where appropriate, and remember that owners, superusers, and roles with BYPASSRLS can circumvent policies. For sensitive document permissions, combine database-enforced tenant boundaries with explicit authorization checks, immutable audit events, least-privilege roles, encryption in transit and at rest, managed keys with rotation, and regularly tested backup and recovery procedures.
Would you use PostgreSQL plus RLS, object storage, and HA as the default, then introduce CockroachDB only if multi-region availability or data residency becomes an actual requirement? Also, would you apply RLS only at the tenant boundary or use it for document-level permissions too?