Database Design for Web Apps: The 9 Rules I Wish I Knew Earlier

Database design for web applications is the single highest-leverage decision you will make in the first two weeks of a project, and it is also the decision most teams treat as an afterthought. I am Khaled Ahmed, a senior full stack developer from Cairo, and across 25+ production projects in seven countries I have watched brilliant apps die because someone picked the wrong primary key, forgot an index, or stored money in a FLOAT column. This guide is the rulebook I wish I had been handed in year one.

Databases are where web apps go to die. Bad schema decisions in week 1 become $50,000 migration projects in year 3. The nine rules below are not theory — they are the patterns that kept a Laravel app I shipped in 2021 alive through 2 million users, and they are the patterns I now apply by reflex to every new build, from a small Egyptian SaaS to a multi-tenant fintech in Switzerland.

What database design actually means for a modern web application

When developers say "database design for web applications," they usually mean three different things at once. The first is logical modeling: which entities exist, what attributes they carry, how they relate. The second is physical design: what columns and indexes get created, what storage engine, what isolation level. The third — and the one most overlooked — is operational design: how migrations roll out, how backups run, how connection pooling is configured, how the schema evolves without downtime.

A good schema makes the next ten features easy to build. A bad schema turns every new feature into a multi-week archaeological dig through legacy tables. The cost is rarely visible on day one. It compounds. By month 18 you are running EXPLAIN ANALYZE on every query before you ship, you are afraid to add a column, and your team velocity has quietly collapsed.

Why 80% of web app performance problems start at the schema layer

I keep a running tally of every "the app is slow" engagement I have taken over five years. The breakdown is remarkably stable: roughly 60% of issues trace back to missing or wrong indexes, 15% to schema shapes that force N+1 queries, 10% to overly wide tables that thrash the buffer cache, and only the remaining 15% are application-level bugs, network latency, or genuine infrastructure problems. Eighty-five percent of performance is decided in your schema.

This is why I tell every founder I work with: spend the extra week. Get the schema right before you write the first controller. You can refactor a React component in a day. You cannot easily change the primary key strategy of a 50-million-row table without coordinated downtime, careful planning, and usually a paid consultant. If your app is already slow and you want a second opinion, my guide on why your website loads slowly walks through the diagnostic order I run.

Rule 1: Normalize to 3NF first, denormalize only with measured evidence

Start in third normal form (3NF). Every non-key column depends on the key, the whole key, and nothing but the key. No repeating groups, no transitive dependencies, no columns that duplicate data that lives elsewhere. This is the boring, unsexy starting point — and it is correct 90% of the time.

I cannot count how many junior developers I have seen denormalize on day one because a senior somewhere told them "joins are slow." Joins are not slow. Joins on indexed foreign keys against modern PostgreSQL or MySQL InnoDB are blisteringly fast. What is slow is a join across a table with no index on the join column, or a join that returns 5 million rows because someone forgot a WHERE clause.

Denormalize only when you have measured a real query problem. The measurement happens with EXPLAIN ANALYZE, not with a hunch. Common legitimate denormalization patterns include: storing a precomputed count on a parent table (with a database trigger to keep it in sync), copying a rarely changing attribute onto a child table to avoid joins in hot paths, and adding a materialized view for an analytics dashboard. Each of these adds operational cost. Each must be justified by numbers.

How to read an EXPLAIN ANALYZE plan before denormalizing

Before you reshape a single table, learn to read your query planner output. In PostgreSQL the magic incantation is EXPLAIN (ANALYZE, BUFFERS) — the BUFFERS flag tells you how many pages were read from disk versus the shared buffer cache, which is the single most useful signal for diagnosing slow queries.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.id, u.email, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at >= NOW() - INTERVAL '30 days'
GROUP BY u.id, u.email
ORDER BY order_count DESC
LIMIT 50;

Things to look for: Seq Scan on a large table (almost always a missing index), high "rows removed by filter" numbers (your index is not selective enough), nested loops over thousands of rows (you probably want a hash or merge join, which usually means an index on the join key), and any node whose actual time is more than 10x its estimated time (the planner statistics are stale — run ANALYZE).

Rule 2: Use integer primary keys internally and UUIDs/ULIDs as public IDs

Auto-increment BIGINT for the internal primary key. Public-facing UUID (or better, ULID/UUID v7) for URLs, APIs, and anything a customer ever sees. Never expose internal sequential IDs to the outside world. If your URL is /users/5, you have just told the world you have five users — and you have given attackers a trivial enumeration vector.

The integer primary key gives you compact storage, fast B-tree index lookups, excellent primary key clustering on InnoDB (which physically orders rows on disk by PK), and tiny foreign keys. A BIGINT is 8 bytes. A UUID stored as a string is 36 bytes. Multiplied across every foreign key in every row in every table, that adds up to gigabytes of wasted RAM and slower joins.

UUID v4 vs UUID v7 vs ULID: which one to pick in 2026

UUID v4 is fully random. This is great for unpredictability but terrible for index locality — every insert lands in a random spot in the B-tree, fragmenting your index and causing page splits. On a high-write table this kills performance.

UUID v7 (standardized in RFC 9562, May 2024) and ULID solve this by prefixing the value with a millisecond timestamp. New IDs are roughly monotonically increasing, so they cluster at the right edge of the B-tree, dramatically improving insert performance and reducing index bloat. In 2026 my default is UUID v7 if your database driver supports it, otherwise ULID. Avoid v4 for primary keys or any heavily indexed column.

// Node.js with uuid v9+
import { v7 as uuidv7 } from 'uuid';
const publicId = uuidv7(); // 0190d4a2-... time-ordered

// Laravel 11+ with built-in support
$user = User::create([
    'public_id' => (string) Str::uuid7(),
    'email' => $request->email,
]);

Rule 3: Index every foreign key, WHERE column, and ORDER BY column

This is the most violated rule I encounter. People assume that defining a FOREIGN KEY constraint also creates an index. In PostgreSQL it does not. In MySQL InnoDB it does, but only for the constraint itself, and not necessarily in the most useful order for your queries.

The default rule is brutally simple: every column you ever join on, filter on, or sort by needs an index. Run through your slow query log monthly. Any query taking more than 100ms gets an EXPLAIN review. Any sequential scan on a table bigger than 10,000 rows is a red flag.

• Foreign keys: always indexed, no exceptions
• Columns in WHERE clauses: indexed if selectivity is good (returns less than 5% of the table)
• Columns in ORDER BY: indexed, ideally in the same direction as the query
• Columns used in GROUP BY: candidate for indexing on large aggregation queries
• Columns referenced in JOIN ON clauses: indexed on both sides

Composite indexes, covering indexes, and partial indexes explained

A composite index covers multiple columns in a specific order. The leftmost prefix matters: an index on (tenant_id, created_at) helps queries that filter on tenant_id alone, and queries that filter on both, but not queries that filter on created_at alone. Design composite indexes from your actual query patterns, not from a wish list.

A covering index includes all columns needed by a query, allowing PostgreSQL to satisfy the query from the index alone without touching the heap. Use INCLUDE in PostgreSQL 11+ to add non-key columns to the index payload. A partial index applies only to rows matching a WHERE clause, which is gold for soft-deleted rows or rare-but-queried states.

-- Composite index for multi-tenant pagination
CREATE INDEX idx_orders_tenant_created
  ON orders (tenant_id, created_at DESC);

-- Covering index that avoids heap lookup
CREATE INDEX idx_users_email_lookup
  ON users (email) INCLUDE (id, status, last_login_at);

-- Partial index: only active subscriptions
CREATE INDEX idx_subscriptions_active
  ON subscriptions (user_id)
  WHERE status = 'active';

Partial indexes are wildly underused. If 95% of your subscriptions are cancelled but every query asks for active ones, a partial index on the 5% is 20x smaller, 20x faster to scan, and cheaper to maintain. This is one of the highest-ROI tricks in PostgreSQL schema design and it never shows up in tutorials.

Rule 4: When soft deletes help and when they pollute every query

Soft deletes (a deleted_at timestamp column) are convenient. You can "undelete," you preserve referential integrity, you have an audit trail. They are also poison if applied indiscriminately. Every query in your codebase now needs a WHERE deleted_at IS NULL clause, every JOIN must filter both sides, and every developer who forgets will leak deleted data into the UI.

My rule: use soft deletes only when there is a real compliance, audit, or recovery requirement. For everything else, real deletes are simpler. If you do use soft deletes, enforce them with a partial unique index (so a "deleted" email can be re-registered), wrap them in a base query scope your ORM applies automatically, and add a partial index on (deleted_at IS NULL) to keep your hot path fast.

If you cannot articulate the specific regulatory or product reason a particular table needs soft deletes, you should not be using them on that table. "Just in case" is not an answer — it is a tax on every future query.

Rule 5: Treat migrations as code (zero-downtime migration patterns)

Every schema change goes through a migration file. Version controlled. Reviewed. Reversible where possible. Tested in staging against a copy of production. No manual ALTER TABLE statements run from a psql shell in production. Ever. If your deployment process allows a human to type DDL into a production database, you do not have a deployment process; you have a accident waiting to happen.

Database migration best practices for zero-downtime deploys follow a predictable pattern. Adding a column is always safe if it is nullable or has a default that does not require a table rewrite. Removing a column needs a multi-step deploy: stop reading the column, deploy, stop writing the column, deploy, drop the column, deploy. Renaming is the most dangerous — prefer add-new-column / dual-write / backfill / read-from-new / drop-old over a single ALTER RENAME.

// Laravel migration: zero-downtime column addition
public function up(): void
{
    Schema::table('orders', function (Blueprint $table) {
        // Nullable with default = no table rewrite on PG >= 11
        $table->string('currency', 3)->default('USD')->nullable();
        $table->index(['tenant_id', 'created_at']);
    });
}

public function down(): void
{
    Schema::table('orders', function (Blueprint $table) {
        $table->dropIndex(['tenant_id', 'created_at']);
        $table->dropColumn('currency');
    });
}

For larger systems, look into tools like pt-online-schema-change for MySQL or pg_repack for PostgreSQL, which let you alter large tables without holding long locks. And always test migrations on a copy of production data — not a 1,000-row dev seed. A migration that runs in 200ms locally may take 90 minutes on a 200GB production table.

Rule 6: Pick the right column type (VARCHAR, TEXT, ENUM, JSONB, DECIMAL)

Defaulting to VARCHAR(255) for every string column is lazy and wasteful. The right type makes your data self-documenting, your storage compact, and your validation automatic. Here is my cheat sheet:

• VARCHAR(n): when n actually means something — country code VARCHAR(2), currency code VARCHAR(3), phone number VARCHAR(20). The limit is documentation.
• TEXT: unbounded user content like blog post bodies, comments, descriptions. In PostgreSQL TEXT has identical performance to VARCHAR without the length check.
• ENUM (or CHECK constraint with allowed values): for fixed sets like status, role, type. Easier to migrate via CHECK; safer for evolving sets.
• JSONB: for semi-structured data where the shape varies — user preferences, integration metadata, feature flags. Index specific paths with expression indexes; never store anything you query frequently as JSON if it could be a column.
• DECIMAL/NUMERIC: money, percentages, exact quantities. Always. No exceptions.
• TIMESTAMPTZ: for any time value. Never store TIMESTAMP without time zone for user-facing data.
• UUID: for public IDs (see Rule 2). Native UUID type, not VARCHAR(36).

Why FLOAT for money is the most expensive bug in fintech

I have personally been called in to fix two production fintech systems where a developer stored monetary amounts in FLOAT or DOUBLE columns. In both cases the accounting team noticed when the daily reconciliation reports started disagreeing with the payment processor by a few cents per thousand transactions. Across millions of transactions that compounded into thousands of dollars of "missing money" that nobody could trace.

FLOAT and DOUBLE are binary approximations. 0.1 + 0.2 is not exactly 0.3 in IEEE 754, and your customers will eventually find every edge case. Use DECIMAL(19, 4) for money, or store amounts in the smallest unit (cents, satoshis) as a BIGINT. Both work. Both are exact. FLOAT is never an acceptable choice for money — not in MVP, not "for now," not ever.

Rule 7: Put constraints in the database, not just the application

NOT NULL. UNIQUE. CHECK. FOREIGN KEY. These belong on the columns, not in your Laravel form request or Zod schema. The database is the last writer in your system and the first reader of every recovery. Your application is not the only thing touching the data — backups, scripts, future microservices, ad-hoc data fixes, and the inevitable "I will just run this SQL once" all bypass application validation.

Referential integrity is not a nice-to-have. It is the contract that prevents orphan rows, duplicate primary entities, and impossible states. ACID compliance gives you the guarantees that make the rest of your application possible — atomicity for multi-row updates, consistency through constraints, isolation through transactions, durability through the write-ahead log (WAL). Throwing constraints out because "the app validates" is throwing out three of those four letters.

CREATE TABLE invoices (
    id            BIGSERIAL PRIMARY KEY,
    public_id     UUID NOT NULL UNIQUE DEFAULT gen_random_uuid(),
    tenant_id     BIGINT NOT NULL REFERENCES tenants(id) ON DELETE RESTRICT,
    invoice_no    VARCHAR(32) NOT NULL,
    amount_cents  BIGINT NOT NULL CHECK (amount_cents >= 0),
    currency      CHAR(3) NOT NULL CHECK (currency ~ '^[A-Z]{3}$'),
    status        VARCHAR(20) NOT NULL
                  CHECK (status IN ('draft','sent','paid','void')),
    issued_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    paid_at       TIMESTAMPTZ,
    UNIQUE (tenant_id, invoice_no),
    CHECK (paid_at IS NULL OR paid_at >= issued_at)
);

Notice how much this schema tells you without reading a single line of application code: invoices belong to tenants, invoice numbers are unique per tenant, amounts cannot be negative, currency codes follow ISO format, status is enumerated, and you cannot be paid before you were issued. This is database design as documentation.

Rule 8: Design for 100x scale (partitioning, sharding, read replicas)

Schema decisions made for 1,000 users break at 100,000. You do not need to implement read replicas on day one, but you need to design as if you will. That means: every query is scoped by tenant_id (or user_id) from day one, every table that grows linearly with users has a partitioning candidate column, every write is idempotent so you can retry it on a replica failover.

Table partitioning splits a huge table into smaller chunks by some key — usually time (one partition per month) or tenant. PostgreSQL has had native declarative partitioning since version 10 and it is excellent in 2026. Read replicas offload reporting and read-heavy endpoints from your primary write node. Connection pooling (PgBouncer for Postgres, ProxySQL for MySQL) prevents your app from exhausting database connections under load.

Scalable database design is not about premature optimization; it is about avoiding decisions that are expensive to reverse. A tenant_id column added on day one costs nothing. Adding tenant_id to a 100-million-row table after the fact is a multi-week project with downtime. For ecommerce builds specifically, my ecommerce development guide walks through the schema patterns that scale from 100 SKUs to 100,000.

Rule 9: Backups are not real until you have restored them

Automated daily backups. Point-in-time recovery via WAL archiving. Quarterly restore drills where you actually spin up a new instance from yesterday's backup, run smoke tests, and verify row counts. If you have never restored a backup, you have hope, not a backup strategy.

I have seen three production disasters in five years where the team "had backups" — except the backup script had been silently failing for six months because someone rotated an S3 credential, or the backups existed but were missing a critical schema, or restoring took 14 hours and the team had assumed it would take one. Test the entire restore loop. Document the RTO (recovery time objective) and RPO (recovery point objective). Tell your customers what those numbers actually are.

1. Automated daily full backups, stored in a different region than your primary
2. Continuous WAL archiving for point-in-time recovery (RPO under 5 minutes)
3. Weekly logical dumps in addition to physical backups (different failure modes)
4. Quarterly restore drill into a clean environment with row-count verification
5. Documented runbook with exact commands a sleep-deprived on-call engineer can run at 3am
6. Monitoring on backup success, backup size deltas, and last-successful-backup age

Common database design mistakes that kill startups in year 3

The mistakes that kill startups are not the ones that hurt on day one — they are the ones that compound silently. Here is my list of the killers I see most often when I am called in for a schema audit:

• No tenant_id from day one — adding multi-tenancy to a single-tenant schema after product-market fit is a 6-month project
• VARCHAR(255) for everything — by year three, you have no idea which strings are emails, slugs, codes, or free-form text
• String enums without CHECK constraints — typos in production data that crash every query that filters on status
• FLOAT for money — covered above, never not a disaster
• No created_at / updated_at columns — debugging is impossible without timestamps
• Indexes added reactively after a customer complains — by then you have already lost the customer
• Application-only constraints — the first bulk import script bypasses them and corrupts your data
• JSON columns for things that should be columns — you cannot index, validate, or report on what is buried in a JSON blob
• UUID v4 as primary key on a high-write table — index fragmentation that slows inserts by 10x
• No migration framework — production and staging schemas drift; nobody knows the source of truth

Relational vs NoSQL vs NewSQL: how to choose for a web app

The honest answer in 2026: for 95% of web applications, a relational database (PostgreSQL or MySQL) is the correct default. NoSQL is not faster, not more scalable, and not simpler for the typical web app shape — it just trades problems you understand (joins, constraints) for problems you do not (eventual consistency, application-side joins, schema-on-read chaos).

Pick a document database (MongoDB, DynamoDB) when your data is genuinely document-shaped (CMS content, event logs, session blobs) and you need horizontal scale beyond what a single relational primary can serve. Pick a key-value store (Redis, Memcached) for caching and session storage — not as your system of record. Pick a NewSQL database (CockroachDB, Spanner, YugabyteDB) when you need ACID guarantees plus horizontal write scale across regions, which is rare and expensive.

Default to PostgreSQL. With JSONB columns, full-text search, vector extensions (pgvector), and excellent partitioning, Postgres in 2026 handles workloads that would have demanded a polyglot persistence stack in 2018. The fewer databases in your stack, the fewer 3am pages.

PostgreSQL vs MySQL vs SQLite for production web apps

PostgreSQL is my default. Better type system, better constraints, better partitioning, better JSON support, better extensions (PostGIS, pgvector, TimescaleDB), better community in the data engineering world. The downsides — slightly more memory hungry, slightly trickier replication setup — are manageable and shrinking with every release.

MySQL (specifically InnoDB) is excellent for read-heavy workloads, has rock-solid replication, and remains the default for WordPress and a huge number of legacy stacks. If your team already runs MySQL well, do not switch for the sake of switching. Both will serve you for years.

SQLite is criminally underrated for small-to-medium apps. It now has WAL mode, JSON support, full-text search, and with Litestream you can replicate it to S3 for backup. For internal tools, MVPs, or single-server SaaS up to a few thousand users, SQLite is a legitimate production choice that saves you operational complexity. The choice between hosting options matters too — see my guide on choosing web hosting in 2026 for the deployment side of the decision.

Database design checklist for new projects (copy-paste ready)

This is the literal checklist I run through with every new project, before writing a single migration. If you cannot answer "yes" to all of these, you are not ready to start.

1. Have I drawn an entity-relationship diagram and shown it to a second engineer?
2. Does every table have an integer primary key and a UUID public_id (where exposed)?
3. Does every table have created_at and updated_at TIMESTAMPTZ columns with defaults?
4. Is every foreign key explicitly declared and indexed?
5. Are all monetary amounts stored as DECIMAL or BIGINT-of-smallest-unit?
6. Is every status/type/role column constrained to a known set via CHECK or ENUM?
7. Is every column annotated with NOT NULL unless it genuinely needs to be nullable?
8. Does the schema include tenant_id (or equivalent isolation key) on every multi-tenant table?
9. Are migrations checked into the repo and run automatically in CI against a fresh database?
10. Is there a backup strategy with a documented RPO/RTO and a scheduled restore drill?
11. Have I run EXPLAIN on the top 10 expected queries against representative data?
12. Have I configured connection pooling appropriate for my hosting environment?

Real case study: a Laravel app schema that survived 0 to 2M users

In early 2021 I built the backend for an Egyptian education platform. The brief was simple: tutors, students, classes, payments. We made every decision in this article. Integer PK plus UUID public_id on every table. Tenant_id on day one even though we launched single-tenant. DECIMAL for prices. CHECK constraints on every enum. Composite indexes designed around the three known query patterns.

By Q4 2023 the platform had crossed 2 million registered users and 180 million class-attendance rows. We had grown the team from one developer (me) to seven. The schema had received 47 migrations. We had added partitioning to the attendance table at 50 million rows in a planned 4-hour maintenance window — the only scheduled downtime in three years. Average API response time at 2M users: 84ms. P99: 340ms.

What did we not have to do? We never had to migrate primary keys. Never had to add tenant isolation after the fact. Never had a money-rounding bug. Never lost data we could not recover within RPO. The week of design work in January 2021 saved us, conservatively, six months of remediation work over the following three years. The Laravel + React combination has been my go-to stack for this kind of build — if you are starting fresh, my guide to building a SaaS MVP with Laravel and React in 2026 captures the modern version of the playbook.

Cost of a bad schema: migration projects, downtime, and lost revenue

Let me put numbers on this so it lands. A typical schema-rescue engagement I quote starts at $15,000 for a 200-table system, plus an additional $30,000-$60,000 for the actual data migration and code refactoring. Multiply by 2 if the system handles money. Multiply by 3 if it cannot tolerate downtime.

That is just the consulting cost. Add the opportunity cost: a team of four engineers spending three months on migration is roughly $150,000 of fully loaded salary that is not building features. Add the revenue cost of any required downtime — for a SaaS at $500K ARR, every hour of outage is roughly $60 in direct refunds and far more in churn risk.

The total cost of a bad schema for a year-3 startup is rarely under $100,000 and often crosses $500,000. The cost of doing it right in week one is one extra week of design time. The math is not subtle.

When to hire a database consultant vs DIY

You can absolutely DIY database design if you have a senior engineer on the team who has shipped at least three production systems and is willing to slow down for the schema work. Most early-stage teams do not have that person — they have full-stack generalists who are excellent at React and shaky on B-tree indexes.

Hire a consultant (me or otherwise) when: you are about to start a project that handles money, regulated data, or multi-tenant data; you are seeing query times grow superlinearly with table size; you are about to take a funding round and want a clean technical due diligence; or you have a migration project that scares you. A two-week schema review and prioritized fix list typically pays for itself within 90 days in reduced infrastructure bills alone, never mind the avoided rewrites. The same logic applies when deciding between a freelance developer versus an agency for the underlying build.

Frequently asked questions about database design for web apps

How long should I spend on database design before writing code?

For a new web application, plan to spend 1-2 weeks on data modeling before writing the first migration. Draw the ERD, list the top 20 expected queries, sketch the indexes, review with a second engineer. This investment pays back 10-20x over the lifetime of the application. Skipping it is the single most expensive shortcut you can take.

Should I use an ORM or write raw SQL?

Use an ORM (Eloquent, Prisma, Drizzle, SQLAlchemy) for 90% of CRUD operations — the productivity gain is enormous and the generated SQL is fine for typical queries. Drop to raw SQL or query builders for reports, complex joins, window functions, and anything where you need precise control of the query plan. Always log slow queries regardless of which layer produced them.

Do I need to learn database internals to design a good schema?

You need to understand four things: how B-tree indexes work, how the query planner chooses an execution plan, how MVCC handles concurrent reads and writes, and how transactions provide isolation. You do not need to read the PostgreSQL source code. Two weekends of focused reading covers it.

How do I handle schema changes in a team of multiple developers?

Every change is a migration file in git, named with a timestamp prefix, reviewed in a pull request, run automatically in CI against a fresh database, and applied in production via the same deployment pipeline as application code. No exceptions for "just a quick column add." The discipline is what prevents schema drift between developers and environments.

Is NoSQL really easier than SQL for beginners?

Short term, yes. Long term, no. NoSQL hides the schema enforcement, which feels liberating until the day you realize three different parts of your app are writing three different shapes into the same collection. Then you are debugging a problem that SQL solves automatically with column definitions and CHECK constraints. Beginners should learn SQL first — it teaches you to think about data correctly.

How often should I review my database schema?

I recommend a formal schema review every quarter for any production application. Walk through table sizes, index usage statistics (pg_stat_user_indexes is your friend), slow query logs, and any tables that have grown 2x or more since the last review. Most performance regressions show up first as table growth, second as index bloat, and third as user complaints. You want to catch them in that order.

What about vector columns and AI workloads in 2026?

PostgreSQL with the pgvector extension handles semantic search and embedding storage natively, which means you can keep your AI features in the same database as your application data. This is a huge simplification compared to running a separate vector DB. For most web apps adding AI features, pgvector is sufficient up to tens of millions of embeddings. Dedicated vector databases earn their keep above that scale or when you need very specific index types.

The future of web app databases: vector columns, edge SQL, and AI agents

Three trends are reshaping web app database design as we move through 2026. First, vector columns are becoming table stakes — pgvector, MySQL vector indexes, and SQLite extensions mean every database is now an embeddings store. Second, edge SQL (Turso, Cloudflare D1, Neon) is making it possible to colocate read replicas with users worldwide, dropping read latency from 200ms to 20ms for global apps. Third, AI agents are starting to write SQL and design schemas, which raises the bar for human-designed schemas: yours need to be clean enough that an agent can reason about them without getting lost.

None of this changes the nine rules above. If anything, it raises the cost of breaking them. An AI agent reading a well-designed schema with named constraints, typed columns, and clear foreign keys can generate correct queries on the first try. The same agent given a sea of VARCHAR(255) columns and untyped JSON blobs is just as confused as the human developer who inherited that mess.

Hire me for a schema audit or a fresh build

I run two kinds of database engagements. The first is a one-week schema audit: I review your existing tables, indexes, queries, and migration history, then deliver a prioritized fix list with effort estimates. The second is greenfield design: I work alongside your team for 2-4 weeks to model the domain, design the schema, set up migrations and backups, and ship the first set of indexes. Both come with a 30-day follow-up.

If you are starting a new project and want the database done right the first time, or if you are already feeling the pain of decisions made too quickly, get in touch for a free 30-minute consultation. I will look at your current schema or your planned domain model and tell you honestly whether you need help or whether you have it under control. You can also see the full range of work I take on at the services page. Related reading from the blog that pairs well with this guide: API design best practices for 2026, Next.js performance optimization in 2026, the website security checklist, WordPress versus Laravel, React versus Vue in 2026, web development trends for 2026, how much a website costs in 2026, mobile-first web design, and progressive web apps in 2026. Get the schema right and everything else gets easier.