The Architectural Choice
Business software that manages sensitive data — client records, financial information, investigation files, strategic plans — must store that data somewhere. The fundamental architectural choice is between a database engine running on your own hardware and a database service running on someone else's. Here is how PostgreSQL-based local deployment compares to cloud database services across the dimensions that matter.
Data Control
Local PostgreSQL: The database files are on your filesystem. You can back them up with pg_dump, replicate them to a standby server, encrypt the filesystem, and physically destroy the drive when it is decommissioned. You have direct access to every byte of your data in a standard, well-documented format.
Cloud database: Your data lives in the provider's infrastructure. You interact with it through a network endpoint. You cannot access the underlying files. Export is possible but may be rate-limited, cost money (egress fees), or produce formats that are not directly usable without the provider's tooling.
Performance
Local PostgreSQL: Queries execute over localhost (sub-millisecond network latency) or LAN (1-5ms). Complex queries joining multiple tables, full-text searches across large document collections, and graph-traversal operations all run at wire speed on your hardware.
Cloud database: Every query crosses the public internet (10-100ms+ latency per round trip). Applications that make many sequential queries — common in relationship mapping and entity exploration — become noticeably slower. Connection reliability depends on your internet connection and the provider's uptime.
Cost Predictability
Local PostgreSQL: PostgreSQL is open source and free. Your costs are the hardware it runs on and the time to administer it. There is no per-query, per-GB, or per-user charge. Scaling from 10,000 to 10 million records changes nothing about your cost.
Cloud database: Costs scale with usage — storage volume, compute instances, I/O operations, data transfer. A growing dataset means a growing monthly bill. A spike in usage (a large data import, a complex reporting period) means an unexpected charge.
Data Portability
Local PostgreSQL: Your data is in PostgreSQL — the most widely deployed open-source relational database in the world. You can migrate to any other PostgreSQL instance, any tool that speaks the PostgreSQL wire protocol, or export to any format PostgreSQL supports (CSV, JSON, SQL dump). No proprietary format, no export tax, no lock-in.
Cloud database: While many cloud databases are PostgreSQL-compatible, they often include proprietary extensions, modified query planners, and custom replication mechanisms. Moving away requires careful testing to ensure compatibility, and the provider's export tools may not preserve all data types or structures.
Offline Capability
Local PostgreSQL: The database runs on your machine. No internet connection is needed — ever. The application and the database are on the same machine or the same LAN. Power outages, ISP failures, and cloud provider outages do not affect your ability to work with your data.
Cloud database: No internet = no database access. The application may have offline caching, but the authoritative data is unreachable. For field work, travel, or locations with unreliable connectivity, this is a real limitation.
When Cloud Databases Make Sense
Cloud databases are the right choice for many applications: public-facing web apps, globally distributed services, applications where the data is not sensitive and the convenience of managed infrastructure outweighs the loss of control. But for business software managing sensitive, proprietary, or confidential data — where control, portability, and offline capability matter — a local PostgreSQL deployment is the superior architectural choice.
ONS Data Terminal and PostgreSQL
ONS Data Terminal uses PostgreSQL as its local database engine. All entity data, relationship links, document metadata, and configuration live in a standard PostgreSQL database on your machine. You have full access to the database — run custom queries, integrate with reporting tools, back up on your schedule, and migrate when you choose. It is your data, in your database, on your hardware.