PostgreSQL: the relational default for transactional and JSON data

OutsourcingVN is operated by Netbase JSC, and this page decides the operational database engine behind an application, separate from the shared data and AI platform a workload may later sit inside.

Implementation patterns

A PostgreSQL schema starts from the entities and relationships the application owns, normalised into tables with foreign keys that enforce referential integrity at the database layer rather than only in application code. Versioned migrations move the schema forward in small, reversible steps, each one reviewed alongside the application change it supports, so a rollback never depends on remembering what a column used to mean. Alongside strictly relational columns, PostgreSQL's jsonb type stores JSON as a decomposed binary value rather than raw text, which trades a small write-time conversion cost for far faster queries, containment operators and index support than a plain json column offers; it earns its place on a handful of flexible fields, such as a per-tenant settings blob, not as the shape of the whole schema. Full-text search runs inside the same database through tsvector and tsquery: documents are parsed and normalised into lexemes, then matched and ranked without a separate search service, which suits a catalogue or content set that does not yet need a dedicated index.

Backups combine continuous write-ahead log archiving with periodic base backups, so a restore can reach any point in time rather than only the last snapshot; a read replica, kept current from the same write-ahead log, absorbs reporting queries so they do not compete with transactional traffic on the primary. On the multi-tenant cloud ERP record, PostgreSQL is one of four database engines in the stack, alongside MySQL, MariaDB and MongoDB, for a US client that is not named; phase one, from 2020 to 2023, covered CRM, real-time messaging, HR, a knowledge base, custom fields and workflows, work and project management and API integrations, deployed on AWS, one of the providers Netbase chooses between per workload alongside Google Cloud, DigitalOcean and Cloudflare.

What trade-offs should you weigh?

1. Consistency against write throughput. PostgreSQL enforces transactional consistency at the database layer, which protects data integrity under concurrent writes but means a schema change is a planned migration, not a silent shape change the application absorbs on its own.
2. A few JSON fields against a document-shaped schema. jsonb columns carry flexibility well for a handful of fields; a schema that is mostly JSON fights the relational model it sits inside and usually signals a document database is the better starting point.
3. Vertical scale against sharding. A single PostgreSQL primary scales a long way with enough CPU, memory and fast storage, and read replicas absorb reporting load; a workload that outgrows one writer needs an explicit sharding or partitioning design, which PostgreSQL does not provide automatically.
4. Operational ownership against convenience. Running PostgreSQL yourself means owning backup verification, replica failover and the annual upgrade cycle; a managed service shifts that work to a provider at the cost of configuration limits that provider sets.

Where does AI fit on a PostgreSQL database?

With AI, every new capability — a retrieval index, an evaluation pipeline, a model-scoring step — is new to every supplier, and Netbase takes each one on and combines it into delivery rather than waiting for a long track record to form first. On a PostgreSQL-backed application, that usually means an AI feature reads from the same governed schema through an API rather than through its own private copy of the data, and the database stays the system of record while the model or retrieval layer runs as a service the application calls. Hosting for that service is chosen per workload among AWS, Google Cloud, DigitalOcean and Cloudflare, not fixed to one provider in advance.

What to check before committing

PostgreSQL releases a new major version about once a year and supports each one for five years from release, with a final minor release marking the end of that window; minor releases ship at least every three months with bug fixes and security fixes only, and the project recommends always running the current minor release of whichever major version is in use. Confirm before scoping a build:

• Which major version, and how close it is to end of support, since a major version stops receiving fixes five years after its release.
• Whether a field is a handful of flexible attributes or the shape of the whole record, since that decides between a jsonb column and a document database such as MongoDB.
• Whether search or reporting load should run on a replica, so it never competes with transactional writes on the primary.
• Who owns the backup restore test and the annual upgrade, since nothing forces either the way a managed service's schedule would.

What Netbase has built with PostgreSQL

PostgreSQL is part of the stack of the multi-tenant cloud ERP, for a client and product that are not named; phase one, from 2020 to 2023, covered CRM, messaging, HR, a knowledge base, custom fields and workflows, work and project management and API integrations. The underlying stack adds MySQL, MariaDB and MongoDB alongside PostgreSQL, deployed on AWS. These are delivered as Custom Product Engineering milestones: most Netbase projects are agreed as fixed-scope contracts after discovery, following discovery and strategic alignment, team assembly and architecture planning, agile execution with outcome-based milestones, modular components, training and rollout, and ongoing support.

Where Netbase applies this stack

Sources

• PostgreSQL, Versioning Policy
• PostgreSQL, JSON Types
• PostgreSQL, Full Text Search
• PostgreSQL, Extending SQL