PostgreSQL engineering
If you are choosing the database for a system that does not exist yet, this is the page for that decision. PostgreSQL is where we start, and the reasoning below covers when another engine fits better. Where a database already exists, the answer turns on what your team runs today.
01Capabilities
What we put inside the database, and why
PostgreSQL is the default we have to be argued out of on a new build, because it does more of the job inside the database than the alternatives and leaves less correctness to application code. This page owns the greenfield decision, which normally sits inside a wider custom software build. An existing estate is a different conversation, covered on our MySQL page and our SQL Server page.
- Correctness
Transactional cores that must stay correct
Ledgers, bookings, orders, clinical records. Places where a wrong row is worse than a slow page, and where check constraints, foreign keys and real transaction isolation earn their cost. This is the shape of work behind most fintech systems we design.
- Model
Domain models the database enforces
Enums, ranges, arrays, JSONB where structure is genuinely partial, and constraints that make invalid states unrepresentable rather than merely discouraged. Every rule you push into the schema is a rule three services cannot each get subtly wrong. Where several systems share that data, the boundaries are a platform integration question as much as a schema one.
- Reporting
Reporting alongside the transactional load
Materialised views, window functions and read replicas so operational reporting stops fighting the write path. PostgreSQL takes you a long way here, and where the questions outgrow it, a warehouse takes over the analytics.
- Search
Search and vector features without a second system
Full-text search and pgvector embeddings inside the database you already operate. For many products this removes an entire piece of infrastructure, which matters more than a benchmark, and it is often the cheapest route to an AI feature that has to ship this quarter. All of it built under an ISO 27001:2022 certified information-security management system.
02Case studies
A product with PostgreSQL underneath it
One product in knowledge management, where PostgreSQL holds the state behind a web workspace and a browser extension.
See all case studies
03Fit
PostgreSQL, or the store the workload needs
A default is worth having because it stops every project relitigating the same decision, and it earns trust by naming where another engine fits. The six rows below give our recommendation for each situation, from PostgreSQL itself to SQL Server, a warehouse or a managed service.
| Your situation | What we recommend |
|---|---|
| A new transactional system where a wrong row costs more than a slow page | Use PostgreSQLConstraints, transactional DDL and a strong query planner mean most products never outgrow it, and nothing forces your hand early. |
| A Microsoft estate: .NET services, Active Directory, licences you already pay for | Different pageOur SQL Server page. Identity, tooling and licensing dominate the total cost here, and no feature comparison changes that. |
| You think you need a document store, because the shape of the data keeps moving | Read that page firstOur MongoDB page sets out both sides. JSONB in PostgreSQL covers more of this ground than teams expect, and sometimes it does not. |
| An existing MySQL database on a current version, with no engine-level pain | Stay on MySQLMigrating a healthy database is one of the more expensive ways to feel modern. See MySQL. |
| Analytics is the product: full-table scans, many analysts at once, terabytes | Add a warehouseA column-oriented warehouse is a different engine for a different question, with PostgreSQL still holding the transactional truth. |
| A small team, a tight first release, and nobody whose job is database operations | Buy it managedManaged PostgreSQL from your own cloud provider, not a cluster you run. Buy backups, patching and failover instead of staffing an on-call rota you cannot fill. |
Scope
What we own on PostgreSQL work is the data model and the growth path. Whether the constraints really prevent bad states, whether the query plans survive the next order of magnitude, and whether your team can operate what we hand over. We work under an ISO 27001:2022 certified information-security management system. unicrew has been building systems like these since 2012, with 100+ senior in-house engineers across six countries. Where the right answer is a smaller database now and a warehouse later, that is the design we propose, before you pay for the larger one.
Most PostgreSQL performance work is modelling work. An index is a short-term fix, so we bring the schema in line with how the product works now. Queries then work with the schema rather than around it.
Oleksandr TrofimovChief Technology Officer, unicrew04Delivery
Four things we settle before the second developer joins
All four are cheap to decide now and expensive to retrofit once features are spread across the schema.
- Model the domain, then decide what the database enforcesWhich invariants live in the schema and which live in application code, written down and agreed. Left implicit, that boundary always drifts towards the application, and the database ends up holding states nobody intended to allow.
- Fix the migration workflow earlyOne migration tool, migrations in version control, forward-only in production, tested against a copy of real data. This is dull until the first release that has to be rolled back at 11pm, and then it is the whole game.
- Plan for the next order of magnitude, not the lastIndex strategy, partitioning where the access pattern is time-shaped, and connection pooling before the connection count becomes the outage. Cheap to design in, disruptive to add once the tables are large.
- Instrument it, and rehearse the restoreQuery statistics, slow query visibility, replication lag alerting, and a restore somebody has actually performed and timed. We do not consider a database delivered until the recovery path has been exercised rather than described, and every migration goes through the same QA and test automation practice we use on our own builds.
05Stack
The stores this decision sits against
The engines that come up in the same conversation, each with its own page if that is the decision you are actually making.
06Questions
What buyers ask when choosing a store
What comes up before anyone commits a schema to anything. Ask yours on the call and the answer will be specific to your system.
Yes, and it works well when your side owns the product decisions and wants depth on the data layer. Our engineers own the schema as well as the code, because the schema is the decision you live with for years. An architect outside the delivery team reviews the model, and every migration runs through the same QA practice as our own work. Team extension is managed teams; handing over the result is data engineering.
Because more of the correctness work can live in the database. Richer types, real check constraints, transactional DDL so a failed migration does not leave you half-migrated, and a planner that copes better with the reporting queries every product eventually grows. None of that makes MySQL a bad database. On an existing MySQL system, we recommend staying put.
For most products yes, and for longer than you expect. Read replicas, materialised views and well-chosen indexes cover operational reporting comfortably. It stops being true when the questions become genuinely analytical: scanning most of a large table, many analysts at once, or history kept for years. That is a column-oriented problem, and the right answer is Snowflake or an equivalent.
A good idea when they remove a system rather than add a capability nobody asked for. pgvector lets a product ship semantic search without operating a separate vector database, and for catalogue-sized workloads that is the right trade. The test we apply is whether the extension keeps the data next to the transactions it belongs to. If it turns the database into a second application, it does not earn its place.
A scoping call with an engineer. For a new system we want to hear what the data has to guarantee, what the reporting will look like in two years, and what your team already runs in production. You get back a written recommendation with the trade-offs, sized to what the system has to guarantee. Most engagements start within two to four weeks.
Three shapes, and which fits depends on how settled the scope is. Time and materials is billed hourly and quoted per project, which suits work still moving. Fixed price is outcome based, and we only offer it once the first read is done, because a fixed number on a system nobody has opened is a guess with a contract around it. Team extension is billed monthly per engineer.
Picking the store for something that does not exist yet?
Tell us what the system has to guarantee and what your team already runs. You get a straight recommendation with the reasoning, PostgreSQL or the store that fits better.