Overview
2 Standard RDBMS capabilities
This chapter gives a practical, high-level tour of core relational capabilities through a multitenant eCommerce example implemented in Postgres. It explains how to shape a database structure that balances isolation and flexibility: separate databases to isolate tenants, schemas to mirror application modules (products, customers, sales), and tables as the primary storage units. It shows how to pick appropriate data types and identifiers (sequences or UUIDs), organize objects with schemas and search paths, and use the essential data manipulation commands—SELECT, INSERT, UPDATE, DELETE—to build and manage a product catalog.
A major focus is data integrity and transactional correctness. The chapter demonstrates how constraints (not-null, unique, primary keys, foreign keys, check, exclusion) prevent bad data at the source, how to add or tighten them over time, and how foreign keys both enforce valid references and protect against accidental deletions (with options like soft deletes or cascades). It then illustrates ACID transactions in action: single-statement implicit transactions, multi-statement explicit transactions for order placement, and how MVCC with read-committed isolation avoids dirty reads and writes while permitting safe concurrency. The reporting side introduces joins to combine data across schemas and tables for insights such as top customers, customers with no orders, and product popularity.
Beyond storage and queries, the chapter shows how to implement application logic in the database with functions and triggers. It builds a reusable shopping-cart flow with SQL and PL/pgSQL functions that add items, compute totals at checkout, and run atomically inside the database; a trigger keeps order totals synchronized as items change. For analytics, views encapsulate complex queries, while materialized views trade freshness for speed and can be refreshed on demand. Finally, it covers access control with roles and privileges, advocating least-privilege per-tenant roles, explicit GRANT/REVOKE of database, schema, table, and sequence permissions, and avoiding superuser accounts in application paths to keep each tenant’s data secure and properly scoped.
Summary
- Postgres supports all the standard capabilities you would expect from a relational database.
- Databases, schemas, and tables are essential building blocks that let you easily map the database structure to your application architecture.
- SQL is a highly flexible language that allows you to query and manipulate individual tables or join data stored across multiple tables.
- Postgres's transactional and data integrity capabilities ensure that your data remains accurate and consistent at all times.
- Database functions and triggers enable you to create and execute application logic directly within the database.
- With roles, you can define and manage database-level access at a granular level.
FAQ
What’s the difference between a Postgres database and a schema, and how does that affect queries?
A database is the top-level container that holds schemas and all other objects; a schema is a named namespace inside a database that groups tables, views, functions, etc. You can query across schemas within the same database by qualifying object names (for example, products.catalog), but you cannot run a single SQL query across different databases without external tools. Postgres uses a search_path to decide the “current” schema when one isn’t specified; by default it includes public.How can I structure a multitenant SaaS in Postgres?
One practical approach is database-level multitenancy: create one database per tenant to isolate data and access. Inside each tenant’s database, create schemas aligned with application modules (for example, products, customers, sales). This provides clear isolation, avoids name conflicts, supports cross-schema queries within a tenant, and lets you distribute tenant databases across multiple Postgres instances as you scale.How do I create tables with auto-generated IDs, and when should I use SERIAL/BIGSERIAL vs UUID?
SERIAL and BIGSERIAL create integer columns backed by sequences that auto-generate IDs (32-bit and 64-bit ranges, respectively). They’re simple and fast. UUID primary keys avoid sequence coordination and scale well across distributed writers; you can default a UUID with gen_random_uuid() in column definitions (for example, id UUID PRIMARY KEY DEFAULT gen_random_uuid()). Choose based on ID volume, distribution needs, and interoperability requirements.Which constraints does Postgres support, and why should I use them?
Postgres provides NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and EXCLUSION constraints. Use them to enforce correctness at the database layer—for example: NOT NULL on required columns; CHECKs like category IN ('coffee','mug','t-shirt') and stock_quantity >= 0; range checks like rank BETWEEN 1 AND 5; FOREIGN KEYs linking reviews to products and customers; EXCLUSION constraints to ensure one pending order per customer. Constraints prevent bad writes even if application checks regress.Can I add constraints to existing tables later? What if current data violates the new rule?
Yes—use ALTER TABLE ... ADD CONSTRAINT. Postgres validates all existing rows; if any violate the constraint, the statement fails and no constraint is added. Fix or remove offending data first, then reapply the constraint. This preserves integrity without silently changing existing rows.What’s the difference between implicit and explicit transactions, and how does MVCC affect concurrency?
Implicit transactions wrap each individual statement automatically (for example, a single UPDATE). Explicit transactions group multiple statements between BEGIN and COMMIT to succeed or roll back atomically. Postgres’s MVCC with the default read committed isolation avoids dirty reads; concurrent updates to the same row will block to prevent dirty writes. Readers don’t block readers, and typical SELECTs can proceed while unrelated writes occur.How and why should I use JOINs across schemas?
JOINs combine related data from multiple tables—even across schemas in the same database—using join conditions. Common patterns include INNER JOINs (for example, customers.accounts to sales.orders to list top buyers) and LEFT JOINs (for example, customers with no orders, or product popularity by joining products.catalog to sales.order_items). GROUP BY and aggregates turn these into useful analytics.When should I move business logic into database functions or triggers?
Use functions when logic is shared by many services/clients or when multiple round trips and large data transfers would be inefficient. Functions run transactionally and can return tabular results (for example, a shopping-cart function that creates/updates a pending order and returns its items). Use triggers to react to data changes automatically (for example, keep orders.total_amount in sync after INSERT/UPDATE/DELETE on order_items). In Postgres, procedures (CREATE PROCEDURE) don’t return values, while functions do.What’s the difference between a view and a materialized view, and when should I use each?
A view is a named query that runs each time it’s referenced—ideal for centralizing complex SELECT logic. A materialized view stores the result set and serves it until refreshed (REFRESH MATERIALIZED VIEW ...). Use materialized views for expensive aggregations and reporting where slightly stale data is acceptable, refreshing on demand or on a schedule.How do I grant least-privilege access for a tenant in Postgres?
Create a role with LOGIN and a password; GRANT CONNECT on only the tenant’s database; REVOKE CONNECT on other databases from PUBLIC. Within the tenant database, GRANT USAGE on needed schemas; GRANT SELECT/INSERT/UPDATE/DELETE on tables; and GRANT USAGE/SELECT on sequences so IDs can be generated. Don’t grant object ownership or DDL privileges—then the role can work with data but can’t alter/drop schemas, tables, or views.
Just Use Postgres! ebook for free
