Anything can look reliable until something is deleted. We choose engines on integrity guarantees, the skill to operate them locally, and whether data can be recovered inside the window the business actually tolerates.
Recovery objectives come first: how much data loss the business accepts and how long it can wait for service, agreed in writing and rehearsed. Then come constraints, referential integrity and whether the people who inherit the system can restore it without ringing us. Feature comparisons decide very little in comparison.
The reasoning we would give in a design review, including the part we are uneasy about.
Default for greenfield systems: strong indexing, JSON columns where genuinely useful, logical replication for reporting copies. It rewards a little tuning, and how it reclaims space on busy tables is usually learned later than it should be.
Chosen where licences already exist, vendor software expects it, or client reporting depends on it. Tooling is excellent and operations predictable, though index and schema changes against large tables require planning rather than optimism.
Used mainly where an existing application requires it. Capable and cheaply hosted, but rarely proposed new, because constraints and windowing behaviour that other engines treat as basic arrived later here.
Reserved for genuinely shapeless payloads such as captured webhook bodies and evolving vendor objects. It suits transactional data poorly, because removing the schema relocates integrity problems into application code nobody re-audits.
Applied to high-volume append-heavy data with simple access patterns, such as event logs and device telemetry. Access patterns must be known in advance, since introducing a new one is a migration rather than a new index.
Deployed as a cache and short-lived coordination store, never as the source of truth. Everything cached must be reconstructable, so replacing or restarting an instance stays an operational non-event.
Where documents, images and exports live, deliberately separate from the database. Durable and inexpensive, but permissions are declared in code, and versioning protects against the overwrite nobody notices for several weeks.
Agreed retention moves ageing objects to cheaper classes automatically. Without written rules, storage grows indefinitely and nothing can ever be deleted, because nobody can prove it is not required for an audit.
Reporting and extracts run against a replica so analytical loads never contend with transactions. The caveat is lag: replicated figures are true as of a particular moment, and that moment should be visible to whoever reads them.
Every schema change is a reviewed forward-only script kept beside the application. Widening a column in one release and narrowing it in the next keeps deployments restartable, which is what makes releases uneventful.
Automated point-in-time snapshots complemented by periodic logical exports held separately. Snapshots answer failure, exports answer the account-level mistake nobody planned for, and both are restored during rehearsals.
Used to feed reporting and search without repeatedly polling production tables. It brings real operational overhead and monitoring duties, so it appears when there is a specific need rather than by default.
Four decisions that shape every later conversation about cost, reporting and recovery.
Choose PostgreSQL unless something concrete points elsewhere. Choose SQL Server when existing licences, internal skills, vendor software or established reporting make it the path of least resistance, accepting the higher running cost.
Choose relational when records relate to each other and someone will reconcile totals. Choose document storage only for payloads whose shape genuinely varies, never to defer decisions about a data model.
Choose a cache for expensive computed values and repeated lookups with clear expiry. Choose a replica when the load is analytical or reporting-shaped, since caching genuine reporting queries causes staleness arguments forever.
Choose managed unless someone externally owns patching, failover and backup verification. Self-hosting to save licence cost usually relocates the expense into incidents, unplanned overtime and restore risk nobody budgeted for.
Patterns worth designing for before the data is large enough to make them expensive.
Every relationship that gets traversed or filtered needs an index on the referencing side. The omissions are invisible at ten thousand rows and become the whole performance problem at ten million.
Append-only history grows without limit and its indexes fragment. These tables are partitioned by period with maintenance scheduled, because a report scanning three years of logs should not compete with transactions.
Adding a column with a default, rebuilding an index or altering a type can block writes for longer than the deployment window. Changes are performed online where supported and rehearsed against realistic volumes first.
Objectives are agreed before the system exists, then measured in a rehearsal. Teams are frequently surprised that restoring a large instance and verifying the application against it takes hours rather than minutes.
Currency uses exact numeric types and explicit rounding rules applied at defined points. Binary floating types produce discrepancies nobody can explain to a finance team, usually discovered during reconciliations months later.
Positions we hold because the alternative has cost clients real money.
If an engine cannot meet the agreed recovery objective with evidence from a rehearsal, we do not put it in production. An elegant store nobody can rebuild inside the tolerated window is an outage waiting for a bad afternoon.
We decline to build on major versions no longer receiving patches, including ones that still run fine. That conversation is easier before launch than after an advisory appears with no upgrade path available.
Applications connect with least-privilege accounts and people are named individually. A shared administrative login makes audit trails meaningless and turns an offboarding process into a password change nobody can safely perform.
We do not hand out production connection strings so that local reports can be assembled by hand. Read replicas and governed exports give people what they need without putting live writes one mistaken formula away.
We assess backup and recovery arrangements before proposing engine or schema changes.
