PostgreSQL vs MariaDB for Transactional CRUD Apps

When a CRUD app outgrows a prototype

A prototype CRUD app usually feels fast because the data is small, the team is tiny, and traffic is predictable. You can get away with simple queries, a couple of indexes, and manual schema tweaks. Then the app gets real users, real workflows, and real deadlines.

Growth changes the workload. Lists and dashboards get opened all day. More people edit the same records. Background jobs start writing in batches. That’s when “it worked yesterday” turns into slow pages, random timeouts, and lock waits during peak hours.

You’ve probably crossed the line if you’re seeing things like list pages that slow down after page 20, releases that include data backfills (not just new columns), more “flex fields” for metadata and integration payloads, or support tickets that say “saving takes forever” during busy times.

That’s when comparing PostgreSQL and MariaDB stops being a brand preference and becomes a practical question. For transactional CRUD workloads, the details that usually decide the outcome are indexing options as queries get more complex, migration safety once tables are large, JSON storage and querying, and query features that reduce application-side work.

This stays focused on those database behaviors. It doesn’t go deep on server sizing, cloud pricing, or vendor contracts. Those matter, but they’re often easier to change later than a schema and query style your product depends on.

Start with your app requirements, not the database brand

The better starting point isn’t “PostgreSQL vs MariaDB.” It’s your app’s day-to-day behavior: create records, update a few fields, list filtered results, and stay correct when many people click at once.

Write down what your busiest screens do. How many reads happen for each write? When do spikes occur (morning logins, month-end reporting, big imports)? Capture the exact filters and sorts you rely on, because those drive index design and query patterns later.

Then define your non-negotiables. For many teams that means strict consistency for money or inventory, an audit trail for “who changed what,” and reporting queries that won’t fall apart every time the schema evolves.

Operational reality matters just as much as features. Decide whether you’ll run a managed database or self-host, how quickly you must restore from backups, and what your tolerance is for maintenance windows.

Finally, define “fast enough” in a few clear targets. For example: p95 API latency under normal load (200 to 400 ms), p95 under peak concurrency (maybe 2x normal), maximum acceptable lock waits during updates (under 100 ms), and backup and restore time limits.

Indexing fundamentals that drive CRUD speed

Most CRUD apps feel fast until tables hit millions of rows and every screen becomes “a filtered list with sorting.” At that point, indexing is the difference between a 50 ms query and a 5 second timeout.

B-tree indexes are the default workhorse in both PostgreSQL and MariaDB. They help when you filter on a column, join on keys, and when your ORDER BY matches the index order. The real performance difference usually comes down to selectivity (how many rows match) and whether the index can satisfy both filtering and sorting without scanning extra rows.

As apps mature, composite indexes matter more than single-column ones. A common pattern is multi-tenant filtering plus a status plus a time sort, like (tenant_id, status, created_at). Put the most consistent filter first (often tenant_id), then the next filter, then the column you sort by. This tends to beat separate indexes that the optimizer can’t combine efficiently.

Differences show up with “smarter” indexes. PostgreSQL supports partial and expression indexes, which can be great for focused screens (for example, indexing only “open” tickets). They’re powerful, but they can surprise teams if queries don’t match the predicate exactly.

Indexes aren’t free. Every insert and update must also update each index, so it’s easy to improve one screen and quietly slow down every write.

A simple way to stay disciplined:

• Add an index only for a real query path (a screen or API call you can name).
• Prefer one good composite index over many overlapping ones.
• Recheck indexes after feature changes and remove dead weight.
• Plan for upkeep: PostgreSQL needs regular vacuum/analyze to avoid bloat; MariaDB also relies on good statistics and occasional cleanup.
• Measure before and after, instead of trusting intuition.

Indexing for real screens: lists, search, and pagination

Most CRUD apps spend their time on a few screens: a list with filters, a search box, and a details page. Your database choice matters less than whether your indexes match those screens, but the two engines do give you different tools once tables grow.

For list pages, think in this order: filter first, then sort, then paginate. A common pattern is “all tickets for account X, status in (open, pending), newest first.” A composite index that starts with the filter columns and ends with the sort column usually wins.

Pagination deserves special care. Offset pagination (page 20 with OFFSET 380) gets slower as you scroll because the database still has to walk past earlier rows. Keyset pagination is steadier: you pass the last seen value (like created_at and id) and ask for “next 20 older than that.” It also reduces duplicates and gaps when new rows arrive mid-scroll.

PostgreSQL has a useful option for list screens: “covering” indexes using INCLUDE, which can enable index-only scans when the visibility map allows it. MariaDB can also do covering reads, but you typically achieve it by putting the needed columns directly into the index definition. That can make indexes wider and more expensive to maintain.

You probably need better indexes if a list endpoint slows down as the table grows even though it returns only 20 to 50 rows, sorting gets slow unless you remove ORDER BY, or I/O jumps during simple filters. Longer queries also tend to increase lock waits during busy periods.

Example: an orders screen that filters by customer_id and status and sorts by created_at usually benefits from an index starting with (customer_id, status, created_at). If you later add “search by order number,” that’s typically a separate index, not something you bolt onto the list index.

Migrations: keeping releases safe as data grows

Migrations stop being “change a table” pretty quickly. Once real users and real history exist, you also need to handle data backfills, tightening constraints, and cleaning up old data shapes without breaking the app.

A safe default is expand, backfill, contract. Add what you need in a way that doesn’t disrupt existing code, copy or compute data in small steps, then remove the old path only after you’re confident.

In practice that usually means adding a new nullable column or table, backfilling in batches while keeping writes consistent, validating later with constraints like NOT NULL, foreign keys, and unique rules, and only then removing old columns, indexes, and code paths.

Not all schema changes are equal. Adding a column is often low risk. Adding an index can still be expensive on large tables, so plan it for low traffic and measure. Changing a column type is often the riskiest because it may rewrite data or block writes. A common safer pattern is: create a new column with the new type, backfill, then switch reads and writes over.

Rollbacks also change meaning at scale. Rolling back schema is sometimes easy; rolling back data often isn’t. Be explicit about what you can undo, especially if a migration includes destructive deletes or lossy transforms.

JSON support: flexible fields without future pain

JSON fields are tempting because they let you ship faster: extra form fields, integration payloads, user preferences, and notes from external systems can all fit without a schema change. The trick is deciding what belongs in JSON and what deserves real columns.

In both PostgreSQL and MariaDB, JSON usually works best when it’s rarely filtered and mostly displayed, stored for debugging, kept as a “settings” blob per user or tenant, or used for small optional attributes that don’t drive reporting.

Indexing JSON is where teams get surprised. Querying a JSON key once is easy. Filtering and sorting on it across large tables is where performance can collapse. PostgreSQL has strong options for indexing JSON paths, but you still need discipline: choose a few keys you truly filter on and index those, then keep the rest as unindexed payload. MariaDB can query JSON too, but complex “search inside JSON” patterns often become fragile and harder to keep fast.

JSON also weakens constraints. It’s harder to enforce “must be one of these values” or “always present” inside an unstructured blob, and reporting tools generally prefer typed columns.

A rule that scales: start with JSON for unknowns, but normalize into columns or child tables when you (1) filter or sort on it, (2) need constraints, or (3) see it show up in dashboards every week. Storing an order’s full shipping API response as JSON is often fine. Fields like delivery_status and carrier usually deserve real columns once support and reporting depend on them.

Query features that show up in mature apps

Early on, most CRUD apps run on simple SELECT, INSERT, UPDATE, and DELETE. Later, you add activity feeds, audit views, admin reports, and search that needs to feel instant. That’s where the choice starts to look like a feature tradeoff.

CTEs and subqueries help keep complex queries readable. They’re useful when you build a result in steps (filter orders, join payments, calculate totals). But readability can hide cost. When a query gets slow, you may need to rewrite a CTE as a subquery or join and then re-check the execution plan.

Window functions matter the first time someone asks for “rank customers by spend,” “show running totals,” or “latest status per ticket.” They often replace messy application loops and cut down the number of queries.

Idempotent writes are another grown-up requirement. When retries happen (mobile networks, background jobs), upserts let you write safely without double-creating records:

• PostgreSQL: INSERT ... ON CONFLICT
• MariaDB: INSERT ... ON DUPLICATE KEY UPDATE

Search is the feature that sneaks up on teams. Built-in full-text search can cover product catalogs, knowledge bases, and support notes. Trigram-like searching is useful for type-ahead and typo tolerance. If search becomes core (complex ranking, many filters, heavy traffic), an external search tool can be worth the extra moving parts.

Example: an order portal starts with “list orders.” A year later it needs “show each customer’s latest order, rank by monthly spend, and search by misspelled names.” Those are database capabilities, not just UI work.

Transactions, locks, and concurrency under load

When traffic is low, most databases feel fine. Under load, the difference is often about how well you handle concurrent changes to the same data, not raw speed. Both PostgreSQL and MariaDB can run a transactional CRUD workload, but you still need to design for contention.

Isolation in plain language

A transaction is a group of steps that should succeed together. Isolation controls what other sessions can see while those steps run. Higher isolation avoids surprising reads, but it can increase waiting. Many apps start with defaults and tighten isolation only for the flows that truly need it (like charging a card and updating an order).

What actually causes lock pain

Locking problems in CRUD apps usually come from a few repeat offenders: hot rows that everyone updates, counters that change on every action, job queues where many workers try to claim the same “next job,” and long transactions that hold locks while other work (or user time) passes.

To reduce contention, keep transactions short, update only the columns you need, and avoid network calls inside a transaction.

A habit that helps is retrying on conflicts. If two support agents save edits to the same ticket at the same time, don’t fail silently. Detect the conflict, reload the latest row, and ask the user to re-apply changes.

To spot issues early, watch for deadlocks, long-running transactions, and queries that spend time waiting instead of running. Make slow query logs part of your routine, especially after releases that add new screens or background jobs.

Operations that become important after launch

After launch, you’re no longer optimizing only for query speed. You’re optimizing for recovery, safe change, and predictable performance.

A common next step is adding a replica. The primary handles writes, and a replica can serve read-heavy pages like dashboards or reports. This changes how you think about freshness: some reads can lag by seconds, so your app needs to know which screens must read from the primary (for example, “order just placed”) and which can tolerate slightly older data (for example, weekly summaries).

Backups are only half the job. What matters is whether you can restore quickly and correctly. Schedule regular test restores into a separate environment, then validate basics: the app can connect, key tables exist, and critical queries return expected results. Teams often discover too late that they backed up the wrong thing, or that restore time is far beyond their downtime budget.

Upgrades also stop being “click and hope.” Plan a maintenance window, read compatibility notes, and test the upgrade path with a copy of production data. Even minor version bumps can change query plans or behavior around indexes and JSON functions.

Simple observability pays off early. Start with slow query logs and top queries by total time, connection saturation, replication lag (if you use replicas), cache hit ratio and I/O pressure, and lock waits and deadlock events.

How to choose: a practical evaluation process

If you’re stuck, stop reading feature lists and run a small trial with your own workload. The goal isn’t a perfect benchmark. It’s avoiding surprises when tables hit millions of rows and your release cycle speeds up.

1) Build a mini test that looks like production

Pick a slice of your app that represents real pain: one or two key tables, a few screens, and the write paths behind them. Collect your top queries (the ones behind list pages, detail pages, and background jobs). Load realistic row counts (at least 100x your prototype data, with a similar shape). Add the indexes you think you’ll need, then run the same queries with the same filters and sorting and capture timings. Repeat while writes are happening (a simple script inserting and updating rows is enough).

A quick example is a “Customers” list that filters by status, searches by name, sorts by last activity, and paginates. That single screen often reveals whether your indexing and planner behavior will age well.

2) Rehearse migrations like a real release

Create a staging copy of the dataset and practice changes you know are coming: adding a column, changing a type, backfilling data, adding an index. Measure how long it takes, whether it blocks writes, and what rollback really means when data has already changed.

3) Use a simple scorecard

After testing, score each option on performance for your real queries, correctness and safety (constraints, transactions, edge cases), migration risk (locking, downtime, recovery options), ops effort (backup/restore, replication, monitoring), and team comfort.

Pick the database that reduces risk for your next 12 months, not the one that wins one micro-test.

Common mistakes and traps

The most expensive database problems often start as “quick wins.” Both databases can run a transactional CRUD app, but the wrong habits will hurt either one once traffic and data grow.

A common trap is treating JSON as a shortcut for everything. A flexible “extras” field is fine for truly optional data, but core fields like status, timestamps, and foreign keys should stay as real columns. Otherwise you end up with slow filters, awkward validation, and painful refactors when reporting becomes a priority.

Indexing has its own trap: adding an index for every filter you see on a screen. Indexes speed reads, but they slow writes and make migrations heavier. Index what users actually use, then validate with measured load.

Migrations can bite when they lock tables. Big-bang changes like rewriting a large column, adding a NOT NULL with a default, or creating a large index can block writes for minutes. Break risky changes into steps and schedule them when the app is quiet.

Also, don’t rely on ORM defaults forever. Once a list view goes from 1,000 rows to 10 million, you need to read query plans, spot missing indexes, and fix slow joins.

Quick warning signs: JSON fields used for primary filtering and sorting, an index count that grows without measuring write performance, migrations that rewrite large tables in one deploy, and pagination without stable ordering (which leads to missing and duplicate rows).

Quick checklist before you commit

Before you pick a side, do a quick reality check based on your busiest screens and your release process.

• Can your top screens stay fast at peak load? Test the slowest list page with real filters, sorting, and pagination, and confirm your indexes match those exact queries.
• Can you ship safe schema changes? Write down an expand-backfill-contract plan for the next breaking change.
• Do you have a clear rule for JSON vs columns? Decide which JSON keys must be searchable or sortable and which are truly flexible.
• Are you depending on specific query features? Check upsert behavior, window functions, CTE behavior, and whether you need functional or partial indexes.
• Can you operate it after launch? Prove you can restore from backup, measure slow queries, and baseline latency and lock waits.

Example: from simple order tracking to a busy customer portal

Picture a customer portal that starts simple: customers log in, view orders, download invoices, and open support tickets. In week one, almost any transactional database feels fine. Pages load fast, and the schema is small.

A few months later, the growth moments show up. Customers ask for filters like “orders shipped last 30 days, paid by card, with partial refund.” Support wants quick exports to CSV for weekly reviews. Finance wants an audit trail: who changed an invoice status, when, and from what to what. Query patterns get wider and more varied than the original screens.

That’s where the decision becomes about specific features and how they behave under real load.

If you add flexible fields (delivery instructions, custom attributes, ticket metadata), JSON support matters because you’ll eventually want to query inside those fields. Be honest about whether your team will index JSON paths, validate shapes, and keep performance predictable as the JSON grows.

Reporting is another pressure point. The moment you join orders, invoices, payments, and tickets with lots of filters, you’ll care about composite indexes, query planning, and how easy it is to evolve indexes without downtime. Migrations also stop being “run a script on Friday” and become part of every release, because a small schema change can touch millions of rows.

A practical way forward is to write down five real screens and exports you expect in six months, include audit history tables early, benchmark with realistic data size using your slowest queries (not a hello-world CRUD), and document team rules for JSON usage, indexing, and migrations.

If you want to move fast without hand-building every layer, AppMaster (appmaster.io) can generate production-ready backends, web apps, and native mobile apps from a visual model. It also nudges you to treat screens, filters, and business processes as real query workloads early, which helps you catch indexing and migration risks before they hit production.