One Postgres Database for Several Products

Why one instance serves multiple products

Running one postgres database for multiple apps is a practical choice when the total data volume and compute load fit comfortably on a single machine. The decision rests on resource efficiency rather than architectural purity. On a single VPS with 12GB of RAM, spinning up separate PostgreSQL instances for each product would waste memory on idle listeners and duplicate system overhead. Instead, a single PostgreSQL 16 instance handles all relational data for the active services. This approach keeps the hardware footprint small and the operational complexity low, which is essential for an independent studio maintaining several live products simultaneously.

Isolation through roles and schemas

Shared infrastructure requires strict logical boundaries to prevent accidental cross-contamination. The setup uses separate databases within the single instance, each protected by a dedicated role. The Next.js process, which serves four distinct sites via host-based routing, connects to its specific database. Instalaz, running as a separate Node process on port 3050, has its own distinct database and role. These roles are granted minimal permissions, ensuring that a query from one product cannot inadvertently read or modify data belonging to another. This separation mimics the security of isolated instances without the cost of managing multiple service daemons. It allows the system to treat each product as a distinct tenant while sharing the underlying engine.

Connection limits and resource contention

Processes on one box compete for memory, and database connections are a finite resource. The default maximum connections in PostgreSQL is often higher than the machine can handle if every application opens many concurrent connections. To prevent the database from exhausting the available memory, the connection limit is tuned to match the actual usage patterns of the Next.js app and Instalaz. Since GamingCap and Invoiceful are static builds served directly from disk by Caddy, they do not consume database connections. This leaves the connection pool primarily for the two active server processes. Monitoring these limits is critical; if the combined load approaches the ceiling, queries begin to queue or fail, degrading performance across all products rather than just one.

Upgrades and restoration challenges

The primary trade-off for sharing a single instance is the coupling of maintenance tasks. Upgrading PostgreSQL involves stopping the service, which takes down data access for every product simultaneously. There is no way to upgrade the database for one application without affecting the others. Similarly, restoring a backup is a coarse-grained operation. If a corruption occurs in one product's data, the standard recovery process often requires restoring the entire instance or carefully extracting specific files from a dump. This makes disaster recovery more complex than it would be with isolated instances. For a solo developer, this risk is acceptable because the total data size is small and backups are frequent. The operational simplicity outweighs the inconvenience of coordinated downtime during major version upgrades.

For those interested in the broader context of running independent digital products, you can explore more technical stories in the PracandyFlix archive.