# Schemas and search_path

> Organize database objects into schemas and control name resolution with search_path.

---

LLMS index: [llms.txt](/en/llms.txt)

---

A *schema* in PostgreSQL is a namespace inside a database — a way to group tables, views, functions, sequences, and types under a name. Every database has at least one (`public`), and most production deployments use several. Schemas are how you organize a large project, host multiple tenants in one database, and isolate extensions from application code. Once you understand `search_path`, you understand how PostgreSQL resolves unqualified names.

## What you'll learn {#learn}

- Creating and dropping schemas
- The `schema.table` qualified-name syntax
- The `search_path` and how name resolution works
- Per-role and per-database `search_path` defaults
- Common patterns: an `app` schema, multi-tenant schemas, separating extensions

## Example {#example}

```sql {num="23.1" caption="Schemas and search_path"}
-- Create a schema and a table inside it
CREATE SCHEMA IF NOT EXISTS app;

DROP TABLE IF EXISTS app.events_example;
CREATE TABLE app.events_example (
  id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  kind text NOT NULL,
  at   timestamptz NOT NULL DEFAULT current_timestamp
);

INSERT INTO app.events_example (kind) VALUES ('login'), ('logout');

-- Fully qualified vs. unqualified access
SELECT * FROM app.events_example ORDER BY id;

-- Make the schema findable without prefixing every name
SET search_path TO app, public;
SELECT * FROM events_example ORDER BY id;   -- resolved as app.events_example

-- Inspect the current search path
SHOW search_path;

-- List schemas and the tables in each
SELECT schemaname, tablename
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY schemaname, tablename
LIMIT 10;

-- Reset to default
SET search_path TO public;
```

When you write `SELECT * FROM events`, PostgreSQL looks up the name in each schema listed in `search_path`, in order, and uses the first match. The default `search_path` is `"$user", public` — meaning "first look in a schema named after the connecting user, then `public`." If a schema with the user's name exists, it wins.

You can change the `search_path` per session (`SET search_path TO ...`), per role (`ALTER ROLE alice SET search_path TO ...`), or per database (`ALTER DATABASE app_db SET search_path TO ...`). The last two persist across reconnections.

Schema separation is the standard way to keep extension objects (`postgis`, `pg_trgm`) out of `public`, to give each tenant its own namespace, or to logically group `audit.*`, `app.*`, `staging.*` tables.

## Run the example {#run}

To run:

```text
$ psql -f source/schemas.sql postgres
CREATE SCHEMA
DROP TABLE
CREATE TABLE
INSERT 0 2
 id |  kind  |              at
----+--------+-------------------------------
  1 | login  | 2024-xx-xx xx:xx:xx.xxxxxx+00
  2 | logout | 2024-xx-xx xx:xx:xx.xxxxxx+00
(2 rows)
 ...
 search_path
-------------
 app, public
(1 row)
 ...
```

## Common pitfalls {#pitfalls}

- Forgetting `search_path` in a function: a `SECURITY DEFINER` function uses its owner's privileges but, without a function-level setting, the caller's `search_path`. Set a path containing only trusted schemas, with `pg_temp` last (for example, `SET search_path = pg_catalog, app, pg_temp` when `app` is trusted), and qualify sensitive object names.
- Putting extensions in `public`: extensions install their objects in whatever schema is current. Many teams `CREATE SCHEMA extensions; ALTER ROLE ... SET search_path = "$user", public, extensions;` and create extensions there.
- Dropping a schema with dependencies fails. Use `DROP SCHEMA name CASCADE` to drop the schema and its dependent objects. It can be rolled back inside a transaction; after commit, recovery requires a backup or recreation.
- Forgetting that `\dt` only shows tables in the current `search_path` schemas. Use `\dt *.*` to see everything.

> [!TIP] Tip
> For multi-tenant systems, schema-per-tenant works at small scale (tens to hundreds of tenants) but becomes painful as the catalog grows. Row-level isolation (a `tenant_id` column with `ROW SECURITY` policies) is the more common large-scale pattern.

> [!NOTE] Try it
> Run `SHOW search_path;` then `SET search_path TO app, public;` and `\dt` in interactive psql. Create a table in two schemas with the same name and see which one wins. Then drop the schema: `DROP SCHEMA app CASCADE;` (use only a disposable demo schema).

## Downloads {#downloads}

SQL file: [schemas.sql](/examples/source/schemas.sql) · [All lesson files](/examples/examples.zip) · [Setup guide](/en/#prerequisites)