PostgreSQL hosting for SaaS is the operating model and infrastructure used to run a PostgreSQL database for a software-as-a-service application. The database may be provider-managed or self-hosted, but it must be sized and operated around connections, working data, storage behavior, recovery targets, and growth.
Choose managed PostgreSQL when the team wants backups, point-in-time recovery, monitoring, patching, pooling, and optional high availability without operating the database host. Choose self-hosting when the application requires operating-system access, unsupported extensions, custom replication, precise version control, or a topology the managed service cannot provide.
Raff Technologies supports both paths through Raff Managed PostgreSQL and self-hosted PostgreSQL on Raff VMs. At Raff, we treat connection count, recovery time, and storage behavior as first-class capacity requirements alongside CPU and RAM. A database is not production-ready merely because it starts and accepts queries.
This guide is the connector for PostgreSQL production operations. Use Managed vs Self-Hosted Databases for the broader ownership decision, VPS for Databases for VM-based database hosting, and Install PostgreSQL on Ubuntu 24.04 for a tested installation workflow.
PostgreSQL hosting starts with the operating model
Managed and self-hosted PostgreSQL can expose the same SQL interface while assigning operational work differently.
| Responsibility | Managed PostgreSQL | Self-hosted PostgreSQL |
|---|---|---|
| Infrastructure and database process | Provider | Your team |
| Operating-system patching | Provider | Your team |
| Minor database maintenance | Usually provider-controlled | Your team |
| Major-version timing | Guided or service-defined | Your team |
| Backup platform | Provider-operated within plan scope | Your team designs and operates it |
| Point-in-time recovery | Available when included by the service | Your team configures base backups and WAL archiving |
| Connection pooling | May be built in | Your team deploys and operates it |
| High availability | Optional service capability | Your team designs and tests it |
| Monitoring platform | Usually included to a defined scope | Your team selects and integrates it |
| Schema, queries, and indexes | Your team | Your team |
| Migration safety | Shared responsibility | Your team |
| Application reconnection | Your team | Your team |
A managed service reduces platform operations. It does not fix unsafe migrations, missing indexes, inefficient queries, excessive pools, weak credentials, or application code that cannot reconnect after failover.
Self-hosting gives deeper control, but every control becomes a recurring responsibility. The team must own package updates, PostgreSQL configuration, monitoring, backups, restore tests, storage growth, replication, certificates, and incidents.
Use a hard requirement to justify self-hosting. Examples include a required extension missing from the managed catalog, direct filesystem access, custom background processes, unusual authentication, or a replication topology the provider cannot support. "We prefer control" is not enough unless someone is assigned to operate that control.
PostgreSQL sizing begins with workload evidence
PostgreSQL sizing is not a fixed mapping from user count to vCPU and RAM. Two applications with the same number of users can have very different query complexity, working sets, write rates, connection behavior, and storage requirements.
Collect these inputs before selecting a plan:
- peak requests that reach the database;
- concurrent active transactions;
- number of application instances and workers;
- query latency at the 50th, 95th, and 99th percentiles;
- database size and 30-day growth rate;
- index size and growth;
- read-to-write ratio;
- write-ahead log generation rate;
- temporary-file activity;
- storage latency during normal and peak periods;
- backup size and measured restore duration;
- maintenance work such as vacuuming, indexing, and migrations;
- expected growth over the next two planning cycles.
CPU reflects query work and concurrency
CPU pressure can come from complex joins, sorting, aggregation, index creation, compression, extension work, or too many concurrent queries. A short CPU spike is different from sustained saturation combined with growing query latency and queueing.
Scale CPU only after checking whether the expensive work is necessary. A missing index, repeated query, unbounded report, or application retry storm can consume any larger plan.
Memory reflects the working set and concurrent work
Memory supports shared buffers, operating-system cache, connection processes, sorting, hashing, maintenance, and extensions. More RAM can improve cache behavior when the active working set fits, but memory settings multiply across concurrent operations.
Watch for swapping, out-of-memory events, falling cache effectiveness, large temporary files, and latency that appears only under concurrency. Those signals are more useful than choosing RAM from database size alone.
Storage requires capacity and latency planning
Database storage must handle tables, indexes, write-ahead logs, temporary files, maintenance, and growth. Capacity planning should include more than the current database size.
Required storage envelope = current database and indexes + expected growth + WAL and temporary-file headroom + maintenance and migration headroom + operational safety margin
Monitor remaining capacity and growth rate together. A database with 40 GB free may be safe for months or unsafe for one weekend depending on write volume, retention, imports, and index creation.
Storage performance also matters. Look at latency during checkpoints, backups, migrations, index builds, and write-heavy peaks. Increasing disk capacity does not automatically solve an I/O bottleneck.
Restore time is a sizing input
A larger database may still run comfortably on its current compute plan while taking too long to restore. Backup throughput, index rebuilds, WAL replay, and application validation can determine the recovery time objective.
Measure restore behavior before production data growth makes the first test urgent.
Connection budgeting prevents avoidable saturation
PostgreSQL typically starts with max_connections set to 100. Increasing the value allocates more server resources and should not replace connection discipline.
A SaaS connection budget should include every process that can reach the database:
Maximum application instances × pool size per instance + background workers + scheduled jobs + migration connections + monitoring connections + administrative reserve = planned connection demand
Consider an application that can scale to six instances. Each instance has a pool of 10 connections, workers use 10 more, and administration plus monitoring reserves another 10.
6 × 10 + 10 + 10 = 80 planned connections
That design leaves limited room inside a typical 100-connection configuration. Increasing the pool to 20 would create 140 planned connections before unexpected deploy overlap, retries, or maintenance.
The correct response is often to reduce pool sizes, control maximum application instances, or use a connection pooler rather than raising the database limit repeatedly.
Pool size should follow database capacity
A pool is a concurrency control, not a performance score. More open connections can increase contention, memory use, lock pressure, and context switching without increasing completed work.
Set the total database-side pool from measured concurrent query capacity, then divide it among application instances and workers. Recalculate the budget whenever autoscaling limits or worker counts change.
PgBouncer changes how client demand reaches PostgreSQL
PgBouncer reuses PostgreSQL server connections across client connections. Session pooling preserves a server connection for the client session. Transaction pooling returns the server connection after each transaction and can support more client concurrency, but it changes session behavior.
Transaction pooling needs application compatibility review. Session-level settings, advisory locks, temporary-table behavior, prepared statements, and other session assumptions may require changes or session pooling.
Raff treats PostgreSQL connection count as a capacity requirement, not an application detail.
Monitor:
- active and idle database connections;
- pool wait time;
- client connections versus server connections;
- transaction duration;
- idle transactions;
- rejected connections;
- deploy overlap;
- connection churn;
- application retry behavior.
A database with low CPU can still be unavailable because every connection slot is occupied.
Recovery and availability solve different failures
Backups, point-in-time recovery, replication, high availability, and snapshots serve different purposes.
| Control | Primary purpose | What it does not solve alone |
|---|---|---|
| Logical backup | Portable or selective recovery | Fast recovery for every large database |
| Base backup plus WAL archive | Full recovery and point-in-time recovery | Immediate failover |
| Standby replication | Availability and optional read scaling | Recovery from a replicated delete or bad migration |
| Infrastructure snapshot | Fast server-level rollback | Independent database history |
| Restore test | Proves recovery procedure and duration | Prevents the original incident |
| Failover test | Proves standby promotion and reconnection | Proves historical recovery |
Replication keeps another system near the current state. It usually reproduces accidental changes as well as correct changes. Backups preserve an independent recovery history.
Use PostgreSQL Replication vs Backups vs Snapshots for the full protection-layer decision and Database Backup Strategy for SaaS Apps for retention and restore planning.
RPO determines data-capture requirements
Recovery Point Objective defines the maximum acceptable data-loss window. A daily logical backup cannot meet a 15-minute RPO. Workloads with tight RPOs usually need continuous WAL archiving or a managed point-in-time recovery service.
RTO determines recovery architecture
Recovery Time Objective defines how quickly service must return. A standby can shorten recovery after primary failure, while a tested restore path protects against logical damage or loss of the whole database environment.
Measure the complete recovery path:
- identify a safe recovery point;
- provision or select the recovery destination;
- restore the base data;
- replay required WAL;
- validate extensions, users, and permissions;
- reconnect the application;
- run critical user checks;
- record actual data loss and recovery time.
Application reconnect behavior is part of high availability
Automatic database failover is incomplete when the application holds broken connections indefinitely, retries without backoff, or continues writing to an obsolete endpoint.
Test connection timeouts, retry limits, pool recycling, DNS or endpoint behavior, in-flight transactions, and idempotency during a controlled failover.
Production operations require monitoring and maintenance
PostgreSQL exposes database activity, locks, table and index statistics, replication state, progress views, and disk-usage information. Infrastructure metrics should be combined with database and application signals.
Monitor at least:
- connection count, active sessions, and pool wait time;
- query latency and frequently expensive statements;
- lock waits, deadlocks, and long transactions;
- CPU, memory, storage capacity, and storage latency;
- temporary-file activity;
- table and index growth;
- sequential scans where indexes are expected;
- replication lag and standby health;
- backup completion, backup age, and WAL archive failures;
- restore-test age;
- autovacuum activity and dead-row growth;
- checkpoint behavior;
- error logs and authentication failures;
- certificate and version status.
Autovacuum is an operating requirement
PostgreSQL uses vacuuming to reuse space from updated or deleted rows, refresh planner statistics, maintain visibility information, and prevent transaction ID wraparound. Autovacuum is enabled by default and should not be disabled casually.
High-update tables may need per-table tuning. Monitor whether autovacuum completes, whether long transactions prevent cleanup, and whether table or index growth is outpacing expected data growth.
Slow queries should be investigated before resizing
Use query statistics and EXPLAIN to identify expensive work. A larger server can hide an inefficient query temporarily, but it does not correct missing indexes, poor join strategy, unnecessary data retrieval, or an unsafe reporting pattern.
Treat query tuning and capacity scaling as separate actions:
- tune when the workload performs unnecessary work;
- scale when necessary work has outgrown the current resources;
- change architecture when reads, writes, availability, or recovery need independent components.
Major upgrades need an application plan
Major PostgreSQL upgrades can affect extensions, drivers, authentication, replication, query plans, and rollback. Test with representative data and application behavior.
A production upgrade plan should include:
- supported source and target versions;
- extension compatibility;
- migration method;
- expected downtime or replication cutover;
- backup and rollback evidence;
- application and migration tests;
- monitoring during and after the change;
- a deadline before the old version becomes unsupported.
Database access should be private or narrowly restricted
Keep PostgreSQL off the public internet where the architecture supports private networking. Give each application a scoped role, store credentials outside source code, rotate exposed secrets, require encrypted connections, and reserve administrative privileges for maintenance.
Public connectivity may be necessary for some managed services or external applications. In that case, combine TLS, IP allowlists, scoped credentials, and monitoring rather than exposing port 5432 broadly.
A decision framework connects workload signals to action
Use the following framework during initial selection and each capacity review.
| Evidence | Keep current plan | Scale vertically | Add pooling or application controls | Add replica or HA | Change hosting model |
|---|---|---|---|---|---|
| CPU and query latency | Peaks recover and latency stays within target | Necessary queries remain CPU-bound under sustained load | Excess concurrency creates queueing | Read load can be safely separated | Team cannot operate recurring performance incidents |
| Memory | Working set is stable without swap or OOM events | Cache pressure or necessary operations need more memory | Too many connections multiply memory use | Replica can move eligible read work | Service limits or control needs no longer fit |
| Connections | Demand stays below budget with reserve | Rarely solved by compute alone | Pools, workers, or autoscaling exceed the budget | Replica may help reads, not connection discipline | Managed pooling or deeper control is required |
| Storage capacity | Growth and maintenance headroom are safe | More storage is needed | Retention, logs, or temporary work should be bounded | Replica does not replace capacity planning | Storage model or access requirement has changed |
| Storage latency | Latency remains stable at peak | Faster or larger storage tier may help | Query and write patterns need correction | Replica can move eligible reads | Required storage behavior is unavailable |
| Recovery | Tested restore meets RPO and RTO | Larger resources may shorten restore | Procedures and retention need correction | HA reduces selected outage time | Managed recovery or custom self-hosted control is required |
| Operations | Named owners handle maintenance consistently | Capacity is the main constraint | Automation and runbooks are missing | Availability requirement justifies added systems | Team should transfer or regain operational responsibility |
Choose managed PostgreSQL when the application fits the supported versions and extensions, and the team wants the provider to operate backups, monitoring, pooling, maintenance, and optional HA.
Choose self-hosted PostgreSQL when a documented technical requirement needs root access, custom extensions, filesystem control, exact patch timing, or a topology outside the managed service.
Choose a larger plan only after measurements show that necessary work is resource-bound.
Choose pooling when client concurrency exceeds the safe database-side connection budget.
Choose a read replica when measured read traffic can be separated and the application can tolerate replication behavior.
Choose high availability when the cost of primary-node downtime justifies a standby, tested failover, and application reconnect work.
:::cluster
How PostgreSQL hosting applies on Raff
Raff offers two PostgreSQL hosting paths.
Raff Managed PostgreSQL currently supports PostgreSQL 14 through 16 with managed backups, continuous WAL archiving for point-in-time recovery, built-in PgBouncer, metrics and query insights, TLS, IP allowlists, private networking, storage expansion, read-replica options, and optional synchronous high availability. Verify the current versions, extension catalog, retention window, limits, and plan availability on the live product page before deployment.
A managed architecture can remain simple:
Application ↓ private or restricted connection Raff Managed PostgreSQL ↓ Managed backups, PITR, metrics, pooling, and optional HA
Use this path when the team wants to own schemas, queries, indexes, migrations, and application recovery while Raff operates the PostgreSQL platform.
A self-hosted database can run on a Raff VM:
Application VM ↓ private network PostgreSQL VM ↓ Customer-operated pooling, backups, monitoring, patching, and recovery
Use self-hosting when the workload needs operating-system access or configuration outside the managed boundary. Pair it with Private Cloud Networks, a database-native backup design, and separate recovery storage. The team remains responsible for PostgreSQL maintenance and restore evidence.
Raff Managed PostgreSQL supports versions 14 through 16; self-hosting provides control over versions your team can safely operate.
Do not choose only from the first-month infrastructure price. Compare the complete operating model: engineering time, after-hours response, backups, pooling, monitoring, standby capacity, upgrades, and recovery testing.