PostgreSQL Engineering
Most data problems are design problems that surfaced late. PostgreSQL gives us the tools to make invalid states impossible rather than merely unlikely — which is worth more than any amount of application-level validation.
ACID
Real transactional guarantees
Numeric
Exact decimals, no float drift
RLS
Row-level security available
JSONB
Structured plus flexible data
The database is the one part of a system that outlives everything else. Frameworks get replaced, front-ends get rewritten, teams change — the data model stays, and every defect baked into it is inherited by whatever comes next. That is why we spend a disproportionate amount of design effort here, and why we use PostgreSQL for essentially all new work.
What we actually want from it is not features but guarantees. Exact decimal arithmetic so money never drifts. Real foreign keys, check constraints and unique indexes so an invalid state cannot be written even by code that has a bug. Transactions that genuinely roll back. Those properties are what allow a system to reconcile rather than merely report — the difference described on our manufacturing and fintech pages.
We also work extensively with Microsoft SQL Server, which means our advice on which to use — and on whether to migrate — is not shaped by only knowing one of them.
The Database Problems Behind Most Application Bugs
Fix these and a surprising number of “application bugs” disappear.
Money stored as float or double
The most common serious defect we find. Floating point cannot represent decimal currency exactly, so balances drift by fractions that accumulate into reconciliation failures nobody can explain or reproduce.
Constraints enforced only in application code
No foreign keys, no check constraints, no unique indexes — because the application validates. Then a script, a bug or a second service writes data that could not have existed, and the invariant is quietly broken forever.
Indexes added by guesswork
An index on every column that appears in a WHERE clause somewhere. Writes slow down, the planner has more options than it needs, and the query that is actually slow still is.
Reporting queries on the primary
Analytical scans running against the same instance serving transactions, producing lock contention and timeouts that get reported as application faults.
PostgreSQL Capabilities
Design, tuning and operational work — on new systems and on databases that have outgrown their original design.
Schema & Data Model Design
Normalised models for genuinely complex domains, with constraints, foreign keys and check rules that make invalid data impossible rather than merely unlikely.
Query & Index Tuning
Execution plan analysis and index design targeting the queries that actually consume resources, with measured before-and-after timings rather than general advice.
Transactional Integrity
Correct isolation levels, explicit transaction boundaries, and locking strategy — including atomic stock reservation patterns that prevent overselling under concurrency.
Audit & History Patterns
Append-only ledgers, temporal tables and change-tracking triggers so financial and operational history is preserved and any figure can be traced to its source.
Row-Level Security
Multi-tenant and role-based data isolation enforced by the database, so a bug in application code cannot expose another tenant's rows.
Partitioning & Archival
Table partitioning for large transactional histories and an archival strategy, so a table growing indefinitely does not gradually become the system's bottleneck.
Reporting & Replicas
Read replicas and materialised views so analytical load never competes with transactional writes for locks and IO.
JSONB & Full-Text Search
Structured columns where the shape is known and JSONB where it genuinely varies, plus built-in full-text search — often removing the need for a separate search service.
Migrations & Zero-Downtime Change
Schema changes written to be backward-compatible so old and new application versions can run simultaneously during a rolling deployment.
Making Invalid States Impossible
Constraints belong in the database
A common argument is that validation belongs in the application because that is where the business rules live. The problem is that the application is never the only writer. There is a management command, a data-fix script someone ran once, a second service, an integration, and eventually a person with database access solving an urgent problem at speed. Any of them can write data the application would have rejected.
So structural invariants go into the database as foreign keys, check constraints, unique indexes and not-null columns. The application still validates — it gives better error messages and faster feedback — but the database is the guarantee. The practical test is simple: if a rule being violated would be a genuine data-integrity problem rather than a user error, it should be impossible to violate, not merely validated against.
- Foreign keys, checks and unique indexes as real guarantees
- Application validation for feedback; database for correctness
- Invalid states impossible to write, not just unlikely
- Uniqueness enforced by index, never by a read-then-write check
Exact arithmetic, and why float is never acceptable for money
Floating-point types cannot represent most decimal fractions exactly. Store a monetary value as a float and you have already introduced a small error; do arithmetic on it repeatedly and the errors accumulate. This surfaces as a balance that is a few paise out, a reconciliation that never quite matches, and a bug that cannot be reproduced because it depends on the exact sequence of operations.
We use exact numeric types for every monetary and quantity value, with precision and scale chosen deliberately. Rounding is applied explicitly at defined points with a stated rule rather than left to whatever the language does by default, because rounding behaviour is a business decision. On systems where quantities reconcile across stages — a factory, a warehouse, a ledger — this is not a refinement, it is the foundation the whole reconciliation depends on.
- Exact numeric types for all money and quantity values
- Precision and scale chosen deliberately per column
- Rounding rules explicit and applied at defined points
- The prerequisite for any system that has to reconcile
Tune from evidence, not from instinct
Performance work that starts from a hunch usually makes things worse. Adding an index has a write cost, and the query that feels slow to a user is frequently not the one consuming the server's resources. Without measurement you are optimising a guess.
We start from execution plans for the queries that actually run, statistics on which indexes are used and which are being maintained for nothing, and where time is genuinely being spent. Changes are made one at a time with recorded before-and-after timings. Often the answer is not an index at all — it is a query rewritten to let the planner do its job, aggregation pushed into the database instead of looped in application code, or analytical load moved off the primary entirely.
- Execution plans and index usage statistics before changing anything
- Unused indexes removed — they cost writes and buy nothing
- One change at a time, with measured before and after
- Analytical load separated from transactional load
When PostgreSQL is not the right store
PostgreSQL handles a wider range of workloads than most teams expect — including many that people reach for a specialised store to solve. But not all of them:
- Caching and ephemeral state? Use Redis. Session data, rate-limit counters and short-lived caches do not belong in a durable relational store.
- Very large-scale analytics? A columnar warehouse will outperform it substantially on terabyte-scale aggregation. Postgres is excellent for operational reporting, not for a data warehouse.
- Full-text search at serious scale? Built-in search is genuinely good and removes the need for a separate service in most cases — but a dedicated search engine wins on relevance tuning and very large corpora.
- Already running SQL Server successfully? Migrating for its own sake rarely pays. See our honest position on SQL Server.
What We Pair It With
Applications
Caching
Async
Platform
Operations
Frequently Asked Questions
PostgreSQL for anything with real integrity requirements. It has stronger constraint support, exact numeric types that matter for financial data, better handling of complex queries, and JSONB for the parts of a model that genuinely vary. MySQL is perfectly capable for simpler read-heavy workloads, but for the transactional business systems we build, PostgreSQL's guarantees are the reason we choose it.
Related Expertise
Python & Django
The application layer we pair with Postgres by default.
Microsoft SQL Server
The other side of the comparison, and honest migration advice.
Fintech
Exact decimal arithmetic and append-only financial history.
E-Commerce
Atomic stock reservation and order integrity under load.
Dashboards & Analytics
Reporting layers that do not fight transactional load.
Custom Software Development
Where data modelling sits in how we scope work.
Data Model That Has To Hold Up?
Money that must reconcile, stock that must not oversell, history that will be audited. Tell us what the data has to guarantee and we'll design a schema that makes the wrong state impossible.
Serving startups, factories and enterprises across India, the US, UK, Australia & Europe.