I’m Ujjwal Rupakheti, a full-stack developer in Itahari offering database design and optimization in Nepal. I design PostgreSQL and MySQL schemas for new apps, find and fix slow queries in existing ones, add the right indexes, and set up migrations and backups you can actually restore.
bookings
payments
idempotency_keys
01The basics
Database design is deciding how your data is stored: the tables, relationships, keys and rules that stop bad data getting in. Optimization is making queries on that data fast and cheap as it grows, mostly through better indexes, better queries and fewer round trips between your app and the database.
A schema built from how the business works: clear tables, foreign keys, unique rules, money stored as whole numbers and dates stored with their time zone. Designed together with the screens and reports, so nothing needs a workaround later.
Slow pages traced back to the queries behind them, then fixed with the right index, a rewritten query or a small change to the data model. Measured before and after, so you can see the difference for yourself.
02Warning signs
Most database problems announce themselves slowly. Pages that were fast at launch get slower every month, reports time out at month-end, and totals stop matching between screens. If two or more of these sound familiar, a review usually pays for itself.
03Slow queries
By measuring first. I find the few queries that take most of the time, read their plans with EXPLAIN ANALYZE, and change the index, the query or the data model, in that order of preference. Then I measure again and keep a check so the problem doesn’t return.
A day of real traffic with pg_stat_statements or MySQL’s slow query log, ranked by total time.
EXPLAIN ANALYZE shows full scans, bad row estimates, sorts spilling to disk and joins in the wrong order.
Composite indexes in the order the query filters, partial indexes for common filters, unused ones removed.
N+1 queries from ORMs fixed, only the needed columns selected, OFFSET paging swapped for keyset paging.
When a query can’t be fixed, a counter or summary table updated as data changes.
Timings compared on the same data, and a check added so the slow version can’t return.
Before Example plan
Seq Scan on payments Filter: (provider_txn_id = $1) Rows Removed by Filter: every other row
After Same query, one index
Index Scan using payments_provider_txn_id_key Index Cond: (provider_txn_id = $1)
The right index can turn a query that takes seconds into one that takes milliseconds. The wrong ones slow down every insert and update and waste memory, so I add indexes only for queries the logs show are slow, and remove the ones nothing uses.
On a live database I create indexes without locking the table where the database allows it, take a fresh backup first, and run every change as a versioned migration at a quiet time, with a written rollback plan.
04Choosing
I default to PostgreSQL for new apps because of its strict constraints, JSON support, full-text search and extensions. I work in MySQL when an app or host already uses it. For a small app on one server, SQLite in WAL mode is often enough, and much simpler to run and back up.
| Database | When I choose it | Where I used it |
|---|---|---|
| PostgreSQL | Most new apps and SaaS | Eknas, HeliGo Nepal, Eutaa, Pustau, HamroYatra |
| MySQL | Existing apps, Java or PHP stacks | CivicSathy: 14 tables with foreign keys |
| SQLite (WAL) | Small products on one server | Nimtto, Jhino |
| Supabase | Auth, storage and real time on Postgres | RecruitFlow |
I reach the database through Prisma or Drizzle in TypeScript projects, and through prepared statements in a DAO layer in Java, as in CivicSathy. Every query is parameterised, never built by joining strings.
05Patterns
Good database design is mostly small decisions that stop expensive problems later. Each of these solved a specific problem a business would otherwise discover the hard way.
amount_paisa BIGINTHeliGo Nepal stores every amount in paisa. Prices, commissions, refunds and settlements split without rounding errors, so the books always balance.
idempotency_key UNIQUEIn Eknas, each bill carries a key. A retried request finds the stored result and returns it, instead of adding loyalty points twice.
revision INTIn Jhino, every saved item carries a revision number. When two people save at once, the older write is refused and the changes are merged, not overwritten.
draft ≠ publishedNimtto autosaves drafts every 0.7 seconds with optimistic versioning, and keeps them separate from the published snapshot guests see.
BS → AD, 1970–2100Pustau converts Bikram Sambat to AD using published month lengths, verified for every day from 1970 to 2100.
haversine(…) < 200 mCivicSathy warns citizens about similar complaints within about 200 metres, using a Haversine distance query in MySQL.
06Data safety
A backup only counts if you’ve restored it. I set up automatic daily backups with a retention window, test a restore, and run every schema change as a versioned migration so each environment matches.
07Process
Four steps, with every change tested on a copy before it touches production. You get a short written report with before-and-after timings at the end.
Slow-query logs, the schema and table sizes from a normal busy day.
Read-only accessQuery plans read and problems ranked by the time they cost.
Worst firstIndexes, query rewrites and model changes, tested on a copy first.
Versioned migrationsBefore-and-after timings, what changed and what to watch next.
In writing08Pricing
Database design is included in every app I build. A review of an existing database is quoted after I’ve seen the schema and a day of slow-query logs, because the work depends on how many queries need fixing and whether the data model has to change.
| Project | Price | Timeline |
|---|---|---|
| Database review and fixesSlow queries, indexes, a written report | Quoted after a review | Agreed in writing |
| Migration between databasesFor example MySQL to PostgreSQL, with checks | Quoted per project | Short freeze on writes |
| SaaS product with its databaseSchema, migrations and backups included | NPR70,000 | 6–12 weeks |
Hosting and backup storage are billed to you directly. Clients abroad are quoted in USD. All prices are on my services page, and the API side is on my API development page.
Speed, safety, backups and cost, answered before you ask.
Ask me on WhatsAppTurn on slow query logging, pg_stat_statements in PostgreSQL or the slow query log in MySQL, and let it run through a busy day. In most apps a handful of queries cause most of the load. EXPLAIN ANALYZE on those queries shows whether they scan whole tables, miss an index or return far more rows than the page needs.
Database design is included in every app I build; a SaaS product’s first version starts from NPR 70,000. Optimization of an existing database is quoted after I’ve seen the schema and a day of slow-query logs, because the work depends on how many queries need fixing and whether the data model has to change.
For a new app I usually choose PostgreSQL, because of its strict constraints, JSON support, full-text search and extensions. MySQL is a sound choice when your app, framework or hosting already uses it. For a small app on one server, SQLite in WAL mode is often enough and much simpler to back up.
The right index can turn a query that takes seconds into one that takes milliseconds. The wrong ones slow down every insert and update and waste memory. I add indexes only for queries the logs show are slow, check the query plan before and after, and remove indexes nothing uses.
Yes. I work on a copy first, take a fresh backup before any change, create indexes without locking tables where the database allows it, and run schema changes as versioned migrations at a quiet time, with a written rollback plan.
Automatic daily backups, kept for at least two weeks, stored away from the main server, and restored as a test at least once. My own products keep 14 daily copies. A backup that has never been restored shouldn’t be trusted, so the restore test is part of every launch.
As whole numbers of the smallest unit, such as paisa, in an integer column, or as a fixed-precision NUMERIC, never as a floating-point number. HeliGo Nepal keeps its whole ledger in paisa, so commissions, refunds and settlements always add up to exactly what the customer paid.
Enough that each fact is stored once, so a customer’s phone or a product’s price never disagrees between tables. For most business apps that means a well-normalised core, plus deliberate copies such as counters or summary tables for reports, each with a clear rule for how it stays up to date.
Yes, when there’s a clear reason to. I map the types and constraints, move the data with a script that can be re-run, compare row counts and totals, and switch over with a short freeze on writes. Often fixing the slow queries in MySQL is the cheaper answer, and I’ll tell you if it is.
Yes. RecruitFlow runs on Supabase for its database, sign-in, file storage and real-time chat, with an admin client used only inside server routes. The same rules apply to any hosted Postgres: careful access policies, indexes driven by real queries, and backups you’ve tested.
Send a short note about your app, the database you use and what has become slow. I reply within one working day with questions or a first estimate.
Ujjwal RupakhetiReplies within one working day
What’s going on?
Opens WhatsApp with your message started.