PostgreSQL Best Practices on Nhost for Modern SaaS: Schemas, Migrations, and Performance Tuning

The Nhost-aware Postgres mindset

Your SaaS back end moves fastest when Postgres, real-time GraphQL, Auth, Storage, and Functions behave like a single, coordinated system. Nhost gives you that system: a production-grade Postgres database, Hasura’s real-time GraphQL engine, built-in authentication and file storage, and scalable serverless functions. To get compounding speed and predictability, apply Postgres database best practices in ways that respect how Nhost executes queries, propagates session variables, ships database migrations through Git, and scales subscriptions.

This guide is a practical playbook for SaaS teams that want boringly fast performance and a stable schema as they ship features. We will map classic techniques—tenant modeling, indexes, partitions, online-safe changes—to the realities of real-time GraphQL, Auth-driven permissions, and Git-driven deployments on Nhost. If you are still choosing a platform, you can also benchmark trade-offs in Nhost vs Supabase vs Appwrite (2026): Open-Source Backend Comparison for Postgres, GraphQL, Auth, and Pricing.

Schema design for SaaS on Nhost

The right schema makes your real-time GraphQL API expressive without being fragile, and it keeps your authorization rules simple and testable. Model tenants and roles so Nhost Auth variables map naturally to Hasura permissions, and use relational constraints so the generated GraphQL relationships are correct and efficient.

Tenant modeling that maps to Auth and permissions

Adopt a consistent tenant key—typically tenant_id or org_id—on every multi-tenant table. Nhost propagates user context to Hasura as session variables, such as x-hasura-user-id and x-hasura-allowed-roles. With those available, you can enforce least privilege in Hasura by writing row-level rules that compare the row’s tenant_id to a session variable. This unifies your Postgres database best practices with real-time GraphQL access control: your authorization logic lives next to your data model, is evaluated per-query, and stays consistent across queries, mutations, and subscriptions.

Practical tips:

• Normalize membership: store a user-to-tenant membership table with role information to power x-hasura-allowed-roles.
• Make tenant_id part of composite primary or unique keys where needed to prevent cross-tenant collisions.
• Keep ownership columns (owner_id) on user-scoped tables to support per-user privacy and auditing.

Namespacing with Postgres schemas

Use Postgres schemas to separate concerns and keep what you expose via GraphQL crystal clear:

• app: transactional domain tables for your product (tracked in Hasura).
• infra: operational tables like job metadata, audit logs, or event bookkeeping (often not tracked).
• read: views and materialized views designed for fast reads (selectively tracked to power dashboards and feeds).

This keeps your real-time GraphQL surface area intentional and reduces the chance of leaking internal structures.

Relationships, foreign keys, and GraphQL types

Declare foreign keys and supporting indexes early. Hasura detects these and generates robust object and array relationships. That pays off two ways: your GraphQL schema remains stable as you add columns, and the query planner can avoid N+1 patterns by joining through indexed relationships. For high-read paths, prefer narrow, well-indexed foreign key joins over denormalized duplications that drift out of sync.

Computed fields, views, and generated columns

For derived data, use a tiered approach that keeps performance predictable:

• Generated columns for cheap, deterministic expressions you frequently filter or order by.
• Expression indexes (for example, on lower(email)) to make case-insensitive queries fast.
• STABLE SQL functions and views tracked in Hasura to expose readable computed attributes without duplicating data.

This pattern aligns with performance tuning for real-time GraphQL by making the planner’s job straightforward and reducing CPU on every request.

Migrations that match Git and GraphQL

Nhost treats schema and metadata as first-class citizens in Git. Keeping database migrations small, ordered, and paired with Hasura metadata changes makes every deploy safe, reviewable, and reversible.

Order of operations: SQL vs metadata

Adopt a consistent order to avoid broken GraphQL schemas during deploys:

1. Create tables, columns, indexes, and constraints in SQL migrations.
2. Track new objects and expose them in Hasura metadata (permissions, relationships, computed fields).
3. Update client code once the API is visible and permitted.
4. When removing, reverse the order: unexpose in metadata, then drop in SQL.

This prevents failures where your GraphQL layer references columns that do not yet exist or exposes data before permissions are in place.

Online-safe changes on large tables

For high-traffic tables, favor patterns that avoid table rewrites and long locks:

• CREATE INDEX CONCURRENTLY to keep writes flowing.
• Two-step NOT NULL: add a nullable column, backfill in chunks, then enforce NOT NULL with a validation step.
• Avoid adding a DEFAULT that rewrites the table; backfill instead and then set the default.
• Batch deletes or updates to stay below autovacuum thresholds and keep bloat manageable.

Because Nhost deploys via Git, your SQL and metadata changes are applied deterministically. For local rehearsal of these database migrations, see Local Development with the Nhost CLI: Run Postgres, GraphQL, and Auth in Containers (BYOC Step-by-Step).

Seed and reference data

Keep lookup tables (countries, plan tiers, feature flags) in version-controlled migrations so every environment stays consistent. Expose read-only views for reference lists in Hasura to simplify client code without risking accidental edits.

Backfills via Functions and Events

Run heavy backfills with Serverless Functions or Event Triggers, not ad hoc one-shot scripts. Process in small, ordered batches (for example, by primary key ranges), commit often, and run an ANALYZE pass after large phases. For scheduled work, Cron Triggers keep the timeline explicit in Git and observable in logs.

Performance tuning for real-time GraphQL

Performance tuning on a Postgres database that powers real-time GraphQL is about matching indexes to your actual filters, designing pagination for subscriptions, and keeping transactions short and predictable under pooling.

Index what GraphQL actually asks

Design composite indexes that mirror your common where/order_by patterns. A classic shape in multi-tenant SaaS is (tenant_id, status, created_at DESC) or (tenant_id, updated_at DESC). This ensures fast, stable results for feeds, dashboards, and live queries. Validate index effectiveness by sampling the top 5–10 most expensive operations in Hasura and confirming index usage in Postgres.

Keyset pagination over OFFSET

For infinite scroll and live updates, keyset pagination outperforms OFFSET/LIMIT. Use a monotonic key like created_at (plus id as a tiebreaker) and include the sort column in your composite index. Subscriptions remain responsive because the planner avoids scanning increasingly large offsets under load.

Expression indexes and search

When filtering on computed expressions, add matching expression indexes. Examples include case-insensitive email searches (lower(email)) and generated columns that extract JSONB fields you query frequently. For fuzzy matching, consider trigram indexes. These small improvements compound into big latency wins under concurrent subscriptions.

Connection pooling and transaction scope

Hasura uses pooled connections, so design queries to be session-state-free and friendly to prepared statements. Keep transactions short, avoid long-held locks, and prefer deterministic queries that can be cached or planned effectively. This is classic performance tuning that matters even more for real-time GraphQL, where a slow query can lag multiple clients at once.

Partitioning for hot data and retention

Partition write-heavy or time-series tables by created_at or tenant_id. Track only the parent table in Hasura. Use partition detach/drop for data retention policies, and keep hot partitions in memory by indexing them precisely. Partitioning reduces bloat and vacuum pressure, which keeps latency predictable as your dataset grows.

Real-time at scale: Subscriptions, Events, and Cron

Separate real-time UI from heavy background work. Subscriptions make the UX live and responsive. Event Triggers and Cron Triggers do the long-running tasks, retries, and integrations.

Subscriptions do UI; Events do work

Write narrow subscriptions: tight filters, strong indexes, and conservative limits. Ship additional fan-out or notifications through Event Triggers or Functions where you can batch, parallelize, and retry without blocking user reads.

Idempotency and at-least-once delivery

Event Triggers are at-least-once. Make them idempotent by storing processed event IDs or enforcing unique constraints on side-effect tables. This keeps your system correct during retries and deploys, even when traffic spikes.

Table-as-queue patterns

For lightweight job queues, use a status column and SKIP LOCKED to process rows concurrently. When throughput, latency, or specialized semantics require a dedicated broker, reach for Bring Your Own Containers (BYOC) to run Kafka, NATS, or a language-specific worker while keeping the Postgres database on the hot path only for state.

Observability and guardrails

Great teams make performance a routine habit. Tie GraphQL operations to SQL, test hypotheses with EXPLAIN (ANALYZE), and keep statistics fresh so the planner chooses the right paths.

Tie GraphQL logs to pg_stat_statements

Name your GraphQL operations and track their latencies. Use the query template (excluding variables) to find matching entries in pg_stat_statements. This helps you prioritize which indexes or query refactors will eliminate the most pain for your users.

EXPLAIN (ANALYZE) workflow

Reproduce slow operations with sample variables close to production sizes. Compare estimated vs actual rows and look for missed index usage, hash joins that spill, or sorts on large sets. Fix with targeted indexes or predicates, then re-measure. This disciplined loop turns anecdotal slowness into measurable, repeatable wins.

Analyze and vacuum after big writes

After bulk inserts or backfills, run ANALYZE so the optimizer has accurate statistics. Watch bloat on hot tables and schedule maintenance during low-traffic windows. Healthy statistics and low bloat keep your real-time GraphQL queries steady under load.

Security-by-default with Auth and Storage

Nhost Auth and Hasura permissions work hand-in-hand to enforce least privilege at query time. Align roles, rules, and storage policies with the same tenant and user keys you use in your schema to get reliable, testable security with minimal friction.

Roles and session variables

Set x-hasura-default-role and x-hasura-allowed-roles from Nhost Auth and use x-hasura-user-id along with tenant_id/owner_id columns to define per-table permissions. Write each rule once and inherit it across queries, mutations, and subscriptions. For advanced token flows (including agents or external consumers), see How to Add OAuth2 and JWT Authentication to Your AI Agents (Step-by-Step Guide).

Audit trails without pain

Capture changes with Event Triggers that append to an audit table in the infra schema. Expose a read-only view in read for compliance and support without cluttering your primary app schema. Because events are at-least-once, keep inserts idempotent by natural keys or unique constraints.

Storage linked to DB records

Tie files in Storage to database rows by storing file IDs and ownership on the related table. Issue signed URLs via Functions, and on row deletion, fire an Event Trigger to clean up associated files. This keeps your data lifecycle consistent and prevents orphaned blobs.

When to move compute to Functions or BYOC

Let the Postgres database own state, constraints, and transactional integrity. Move CPU-heavy, long-running, or integration-heavy work off the hot path.

Serverless Functions for bursty tasks

Use Functions for webhooks, PDF generation, image transforms, notifications, and chunked backfills. They scale horizontally, respect your database connections, and keep latency off user-facing queries.

BYOC for specialized services

When you need language-specific runtimes, ML inference, or dedicated brokers, Bring Your Own Containers. Deploy these services beside your Nhost stack, integrate via GraphQL or Events, and keep the core application path lean while still benefiting from Git-driven deployments and platform observability.

A 30/60/90-day rollout checklist

Use this phased approach to pair Postgres database best practices with Nhost’s platform guarantees.

• Day 0–30
  • Finalize tenant model and standardize tenant_id/owner_id across tables.
  • Set up roles and session variables; write Hasura permissions per table.
  • Add baseline composite indexes that mirror top GraphQL filters and orders.
  • Establish Git workflows for database migrations and metadata reviews.
• Day 31–60
  • Productionize backfills with Functions or Events and add Cron schedules.
  • Adopt online-safe change patterns (CONCURRENT indexes, two-step NOT NULL).
  • Instrument GraphQL operation names and map to pg_stat_statements.
  • Introduce app/infra/read schema separation and track only what you expose.
• Day 61–90
  • Harden subscriptions: narrow predicates, limits, and matching indexes.
  • Evaluate partitioning for hot tables and implement retention via detach/drop.
  • Set SLOs for p95/p99 query latency; create a quarterly EXPLAIN review cadence.
  • Run a full restore test and audit trail verification.

Want to validate local parity before every merge? Spin up your entire stack on your laptop and rehearse end-to-end changes with Local Development with the Nhost CLI: Run Postgres, GraphQL, and Auth in Containers (BYOC Step-by-Step).

Conclusion

Great SaaS back ends are built on a disciplined Postgres database, sharp authorization rules, and predictable deploys. On Nhost, you can connect these pieces: strong tenant modeling and permissions through Auth session variables, safe and reviewable database migrations through Git, and measurable performance tuning that keeps your real-time GraphQL API fast under load. Start with stable schemas, index what you actually query, and move heavy work to Functions or BYOC when it competes with user reads. You will ship faster today and avoid painful rewrites later.

Ready to build with a complete, production-ready backend that scales with you? Get started for free.

Frequently Asked Questions

How do Hasura permissions differ from Postgres RLS on Nhost, and which should I use for multi-tenancy?

Hasura permissions are evaluated at the GraphQL layer using session variables from Nhost Auth, and they apply uniformly to queries, mutations, and subscriptions. Postgres Row Level Security (RLS) is enforced by the database itself. On Nhost, most teams use Hasura permissions for multi-tenancy because they are explicit, versioned in metadata, and easy to reason about with real-time GraphQL. RLS can complement Hasura for defense-in-depth or for non-GraphQL access paths, but start with Hasura permissions tied to tenant_id and x-hasura-user-id.

What’s the safest way to add a NOT NULL column with a default in a large Postgres database?

Avoid table rewrites. Add the column as NULL without a default, backfill in small batches until all rows are populated, add the NOT NULL constraint, then set the DEFAULT for future inserts. This keeps locks short and lets live traffic proceed while you change the schema through your Git-based database migrations pipeline.

How do I index for real-time GraphQL subscriptions and infinite scroll without timeouts?

Mirror your where/order_by with composite indexes, usually starting with (tenant_id, created_at DESC) and using an id tiebreaker for keyset pagination. Keep subscriptions narrow with selective filters and limits. Verify index usage by correlating slow GraphQL operations with pg_stat_statements and ensuring the planner hits the composite index rather than scanning large ranges.

Should I expose derived data as views or computed fields in Hasura for best performance?

Prefer generated columns and expression indexes when you need to filter or order by the derived value. For richer derived attributes that are read often, a view tracked in Hasura is a good balance of speed and clarity. Use STABLE SQL functions as computed fields for attributes that do not need heavy filtering. The principle is to keep the planner’s path simple and indexable.

How can I run large data backfills on Nhost without locking tables or slowing live traffic?

Run backfills in Serverless Functions or via Event Triggers with small, ordered batches and frequent commits. Use CREATE INDEX CONCURRENTLY for new indexes, avoid defaults that rewrite entire tables, and run ANALYZE after major phases. Schedule the work with Cron Triggers so it is observable and repeatable under Git-driven deployments.