Normalize first
- Third normal form for data that many features write to
- One fact stored in one place, so updates cannot disagree
- Lookup tables instead of free-text status values
Database development
Database development services: design, tuning and upgrades
Most database trouble starts with a schema chosen in a hurry and a backup nobody has restored. Agnotic Technologies picks the database type for each workload, designs the schema, tunes queries from real plans, and runs changes, restores and upgrades without surprises.
Database type per workloadSchema and index designZero-downtime changesTested restores
Trusted by global innovators
Database development
What do database development services include?
Database development services cover choosing, designing and running the databases behind your software. Agnotic Technologies picks the database type for each workload, designs schemas and indexes from real queries, ships schema changes without downtime, sets up backups with point-in-time recovery and tested restores, and runs major version upgrades of PostgreSQL, MySQL and SQL Server.
This is engine-neutral work. It suits new products that need their first production schema, and live systems that have slowed down, become risky to change or fallen behind on versions.
Engine-specific design has its own pages: DynamoDB key design, Redis caching and Oracle migrations are linked at the end of this page, and moving a database into AWS or Azure is part of our cloud migration work.
Decision table
Which database type fits each workload?
The same question decides which big data databases suit a large workload: start from how data is written and read, not from its size alone. Many products end up with two types, a system of record plus a copy shaped for one job.
| Workload trait | Database type | Example engines | Main trade-off |
|---|---|---|---|
| Transactions that change several tables and must stay consistent | Relational | PostgreSQL, MySQL, SQL Server | Schema changes need planning, and scaling writes past one primary takes sharding or a distributed SQL engine |
| Records whose shape varies by customer, product or version | Document | MongoDB, Azure Cosmos DB for NoSQL | Joins move into the application, so some data is copied on purpose and kept in sync |
| Lookups by a known key at very high volume | Key-value | Amazon DynamoDB, Redis | Every query must be known in advance; ad hoc reporting needs another store |
| Large write volumes read back by partition, often across regions | Wide-column | Apache Cassandra | One table per query and no joins, so the model follows the reads |
| Questions about relationships several hops deep | Graph | Neo4j, Amazon Neptune | Fast traversals; bulk aggregates across every record are better run elsewhere |
| Time-stamped readings, metrics and events | Time series | TimescaleDB, InfluxDB | Built for appends and time-range queries; retention and downsampling are planned from the start |
| Full-text search, filters and relevance ranking | Search engine | OpenSearch, Elasticsearch | Usually a secondary copy fed from the system of record, so it needs a sync and a rebuild path |
Engine-level design for DynamoDB, Redis and Oracle lives on their own pages, linked below. Cassandra and Cosmos DB modeling follow the same rule: the reads decide the keys.
Schema design
How do we design a schema that lasts?
Most schema regrets come from choices made before anyone wrote down the queries. We settle these four areas with your team and record why.
Denormalize on purpose
- Copied columns only where a measured read path needs them
- A named owner and sync rule for every copy
- Read models or materialized views for heavy reports
Keys and constraints
- Primary keys chosen for stability, not meaning
- Foreign key, unique and check constraints enforced in the database
- Soft delete only where the business needs history
Types and tenancy
- Tight data types, with time zones stored explicitly
- Tenant ID on every tenant-owned table, or a schema or database per tenant when isolation demands it
- Row-level security where the engine supports it
Zero-downtime changes
How do schema changes ship without downtime?
We use the expand and contract pattern, so the old and new versions of your application can run against the same database during a release.
1.
Expand
Add the new column, table or index next to the old one. New columns start nullable or with a default, and PostgreSQL indexes are built concurrently so writes keep flowing.
Step 12.
Migrate
The application writes to both shapes while a batched backfill copies existing rows. Batches stay small to keep locks short and replica lag low.
Step 23.
Contract
Once reads use the new shape and the checks match, a later release drops the old column or table. Until then, every step has a tested rollback.
Step 3Query tuning
How do we tune a slow database?
We tune from evidence in the engine's own statistics, not from guesses about which index might help.
Find the queries that cost the most
pg_stat_statements in PostgreSQL, the slow query log or Performance Schema in MySQL, and Query Store in SQL Server show which statements use the most total time, not just the slowest single run.
Read the real plan
EXPLAIN ANALYZE or the actual execution plan shows where rows are scanned, sorted or misestimated. A badly wrong row estimate usually points to stale statistics or a skewed column.
Fix the cause
A missing or unused index, a query an ORM sends once per row, a function that hides a column from its index, or a lock held by a long transaction.
Prove it on realistic data
We test each change on a copy with production-like volume and read the plan again, because a fix that helps a small table can change nothing at full size.
Recovery design
Backups, replicas and failover we rehearse
A backup is only proven by a restore. We design these four layers together and rehearse them before go-live.
Backups
- Full or base backups on a schedule
- Continuous log archiving for point-in-time recovery
- Encrypted copies kept in a separate account or region
- Retention matched to your recovery point objective
Restore tests
- Scheduled restores into a scratch environment
- Timed against your recovery time objective
- Row counts and checksums compared with the source
- Runbook updated after every rehearsal
Replication
- Synchronous or asynchronous replicas chosen by risk
- Read replicas for reporting traffic
- Alerts on replica lag
- Replication slots and log growth watched
Failover
- Managed failover on Amazon RDS, Azure SQL or Cloud SQL, or a tested failover manager on self-run servers
- Connection strings and pools that follow the new primary
- Failover drills on a calendar
- Fencing so two primaries never accept writes
Upgrades
What goes wrong in major version upgrades?
PostgreSQL, MySQL and SQL Server upgrades fail in familiar ways. We check each one before the upgrade window.
Challenge
An extension, plugin or client driver the application depends on is not ready for the new version.
Agnotic approach
Inventory extensions, drivers and ORM versions first, and upgrade them in a separate release where needed.
Challenge
Query plans change on the new version and a few important queries get slower.
Agnotic approach
Capture plans and timings for the top queries, replay them on a restored copy running the new version, and compare.
Challenge
A SQL Server database moves to a new instance but jumps to the newest compatibility level untested.
Agnotic approach
Upgrade the instance first, then raise the database compatibility level as its own tested step with Query Store watching plans.
Challenge
Removed syntax, changed defaults and new reserved words break statements at run time.
Agnotic approach
Read the release notes for every version being skipped and run the full test suite against the new engine before the window.
Challenge
The upgrade overruns its window because the method was chosen late.
Agnotic approach
Choose between in-place upgrade, dump and restore, or logical replication to a new server early, and rehearse it on a full-size copy.
Who brings us database work?
Founders and engineering leads usually arrive with one of these.
Startups designing their first production schema
A design simple enough to ship now, with room to split tables or add a second database type later without a rewrite.
SaaS products slowing as tenants grow
Queries that were fine at launch now scan whole tables. We tune them and add tenant-aware indexes and partitioning.
Teams on an end-of-support version
A major version upgrade planned, rehearsed on a full copy and run inside an agreed window.
Products adding search or reporting
A search engine or reporting replica fed from the system of record, so the main database stays fast.
Companies merging two databases
Two schemas for the same customers or orders mapped, de-duplicated and moved with reconciled counts.
Scope and cost
What drives the cost of database development?
We do not publish prices or a databases calculator, because cost comes from your schema, data and traffic. These are the factors we size on the scoping call.
Databases and engines
One PostgreSQL database is a different job from several engines that must stay in sync.
Schema size and data volume
The number of tables and the size of the largest ones decide how long backfills, restores and upgrade rehearsals take.
Downtime you can accept
A maintenance window allows a simpler method; zero downtime needs expand and contract steps or replication.
Recovery targets
How much data you can afford to lose, and how fast you must be back, decide the backup and replica design.
Test coverage
Good automated tests make schema changes and upgrades faster to prove.
Regulated data
Encryption, audit logging and access reviews for regulated data add design and evidence work.
References
Engines and documentation we work from
Project and vendor documentation behind the design choices on this page.
PostgreSQLMySQLSQL ServerMongoDBOpenSearch
Facts and sources
Facts behind database upgrades and recovery
Support policies and behaviors from the PostgreSQL and SQL Server documentation that shape the plans on this page.
The PostgreSQL Global Development Group supports each major version for 5 years after its initial release, then ships a final minor version and the version reaches end of life.
Source: PostgreSQL: versioning policyPostgreSQL point-in-time recovery combines a file-system-level base backup with archived write-ahead log files, so a database can be restored to its state at any time since that base backup.
Source: PostgreSQL documentation: continuous archiving and point-in-time recoveryCREATE INDEX CONCURRENTLY builds a PostgreSQL index without taking locks that prevent concurrent inserts, updates or deletes, while a standard index build locks out writes until it finishes.
Source: PostgreSQL documentation: CREATE INDEXEach SQL Server version comes with a minimum of 10 years of support: five years of mainstream support, then five years of extended support that includes only security updates.
Source: Microsoft Learn: SQL Server end of support overview
Frequently Asked Questions
It depends on how many databases are involved, their size and how much downtime you can accept. A slow-query review is a short piece of work; an upgrade or a merge of two databases includes rehearsals on full copies. We agree the scope and first deliverable on the scoping call.
Most startups are well served by one relational database such as PostgreSQL, which handles transactions, JSON columns and basic full-text search well enough to start. Add a second database type only when a measured workload needs it.
Yes, most of our database work is on live systems. We start read-only, with query statistics, plans and the schema, then make changes through expand and contract migrations with a rollback for every step.
The same way as any other database: by how data is written and read. Wide-column, key-value and document databases spread writes across many nodes, while analytics over very large data usually belongs in a warehouse rather than the operational database.
No. A calculator cannot see your schema, data volume or downtime limits, and those drive most of the effort. We review them with you instead and give you a written scope after the scoping call.
Yes. We add change data capture, read replicas or scheduled exports, so search, reporting and other services get their own copy instead of querying the production database directly.
AI tools can draft a migration script or suggest an index, and that is where their part ends. A person reviews each change, reads its query plan and rehearses it on a restored copy before it reaches production.