Does increasing the number of database users/roles impact performance?

General
1

We’re currently in the planning phase for our user/role strategy in YugabyteDB and would appreciate some guidance.

Our situation:

  • Initially, we planned to have a single user with all privileges
  • However, we’re now considering separating privileges into two distinct users per application/service:
    • One user with DDL privileges (schema changes, table creation, etc.)
    • One user with DML privileges (data operations like SELECT, INSERT, UPDATE, DELETE)
  • This approach would effectively double our user count

Our question:
Does increasing the number of database users/roles have any impact on database performance? Specifically:

  1. Does a higher number of users/roles affect query performance or connection handling?
  2. Is there any overhead in privilege checking during query execution as user count grows?
  3. Are there any best practices for separating DDL and DML privileges in YugabyteDB?
2

Hi @kotha.purushotham

Having more roles / users should not have any impact.

Though when workload is running , DDL operations involving roles results in catalog version increment for each Database thus causing invalidation message flow to each database. Precisely following DDL’s will show that pattern

  • roles are created / deleted / altered
  • permission granted or revoked

If roles are created upfront with relevant permissions and no further change when workload is running then it should be fine.

Having more roles makes the catalog bit bigger (pg_authid/pg_authid_member) and will cause more memory consumption if catalog preloading is enabled. Though i still expect these tables to be relatively small enough to have any real impact.

There shouldn’t be any.

Not for this specific scenario.