Time Complexity: Public schema vs custom schemas
O(n)
0
0
Public schema vs custom schemas in PostgreSQL - Performance Comparison
Understanding Time Complexity
When working with PostgreSQL schemas, it's important to understand how accessing tables in different schemas affects query performance.
We want to see how the time to find and use tables changes when using the public schema versus custom schemas.
Scenario Under Consideration
Analyze the time complexity of querying a table in different schemas.
-- Query from public schema
SELECT * FROM public.users WHERE id = 10;
-- Query from custom schema
SELECT * FROM custom_schema.users WHERE id = 10;
-- Setting search path to custom schema
SET search_path TO custom_schema;
SELECT * FROM users WHERE id = 10;
This code shows queries accessing the same table name in different schemas and using search_path to simplify access.
Identify Repeating Operations
Look at what happens when PostgreSQL finds the table to run the query.
- Primary operation: Schema lookup to find the table definition.
- How many times: Once per query execution, but can repeat if multiple tables or schema changes occur.
How Execution Grows With Input
Finding a table in a schema is like looking up a name in a small list.
| Number of Schemas | Approx. Lookup Steps |
|---|---|
| 1 (public only) | 1 step |
| 5 schemas | Up to 5 steps |
| 100 schemas | Up to 100 steps |
Pattern observation: The more schemas in the search path, the longer it may take to find the table, growing roughly linearly with the number of schemas.
Final Time Complexity
Time Complexity: O(n)
This means the time to find a table grows linearly with the number of schemas checked in the search path.
Common Mistake
[X] Wrong: "Using the public schema is always faster than custom schemas because it's the default."
[OK] Correct: The speed depends on how many schemas PostgreSQL searches before finding the table, not just which schema it is.
Interview Connect
Understanding how schema search paths affect query time helps you design databases that are easy to maintain and perform well.
Self-Check
"What if we add an index on the schema name lookup? How would that change the time complexity of finding tables?"