Tables, views, and data

Tables are the core objects that store business data in PostgreSQL. When designing tables, consider data types, primary keys, constraints, indexes, permission policies, and future migration costs.

Table design

For each business table, define at least the following items:

Item	Recommendation
Primary key	Use bigint generated by default as identity or uuid. Avoid business fields as primary keys
Required fields	Set not null for fields that must exist
Defaults	Set defaults for created time, status, owner, and similar fields
Unique constraints	Add unique constraints for emails, external order IDs, and other unique business keys
Ownership field	For multi-user tables, keep a user_id field for RLS policies

create table public.todos (
  id bigint generated by default as identity primary key,
  user_id varchar(64) not null default auth.uid(),
  title text not null,
  done boolean not null default false,
  priority int not null default 0,
  created_at timestamptz not null default now()
);

Common field types

Type	Use case
text	Titles, descriptions, nicknames
boolean	Flags such as done or enabled
int / bigint	Counts, sort values, identity primary keys
numeric	Money and exact decimals
timestamptz	Time with time zone, recommended for created and updated times
jsonb	Flexible attributes, configs, third-party callback payloads
uuid	Distributed IDs and public IDs
enum	Small and stable fixed values, such as order status or workflow stage

For array fields, see Arrays. For fixed value sets, see Enum types. For flexible data, see JSON and unstructured data.

Constraints

Constraints keep data valid at the database layer:

• primary key: uniquely identifies a row.
• not null: prevents empty values.
• unique: prevents duplicate values.
• check: requires values to satisfy an expression.
• foreign key: enforces relationships between tables.

alter table public.todos
  add constraint todos_priority_check check (priority between 0 and 5);

For foreign keys, see Foreign key configuration.

Views

Views are useful for wrapping common queries or exposing a reduced set of fields.

create view public.active_todos as
select id, title, priority, created_at
from public.todos
where done = false;

If the underlying table has RLS enabled, check the view security mode. PostgreSQL 15 and later can use security_invoker = true so the view runs with the caller's permissions.

create view public.active_todos
with (security_invoker = true) as
select id, title, priority, created_at
from public.todos
where done = false;

For details, see Basic permissions.

Data management

During development, you can maintain small amounts of data through DMC or the console. For bulk imports, complex updates, and production fixes, use SQL scripts and keep execution records.

insert into public.todos (title, priority)
values
  ('Prepare launch checklist', 3),
  ('Add database indexes', 2);

Business code can read and write data through SDKs, HTTP APIs, or server-side direct connections. Configure RLS before allowing frontend access to user data.

Schema changes

Production schema changes should be backward compatible where possible. A common flow is to add fields first, release code gradually, and remove old fields later.

Avoid long-running lock operations during peak traffic. For large tables, test index creation, bulk updates, and type conversions in a staging environment first.