Databases

Where a site is fast or slow: its database.

We install, tune, replicate and back up the database behind your application, and we prove the backups by restoring them.

Get a quote See hosting plans
Runs on and built with
  • MySQL
  • MariaDB
  • PostgreSQL
  • Redis
  • MongoDB
  • Elasticsearch

Seven engines, drawn as one schema

Each box is a database we install, tune and back up. The lines are how they work together in the systems we run - most applications need two or three of them, not one.

SQLiteembedded
Used for
Apps and tools with one file of data and one writer at a time
We tune
journal_mode=WAL
Backups
An online copy of the file, never a copy of a file in use
MySQLrelational
Used for
Shops, WordPress and most PHP applications
We tune
innodb_buffer_pool_size
Backups
Full backups plus binary logs, to any point in time
Why we run MySQL
Redisin memory
Used for
Cache, sessions, queues and counters
We tune
maxmemory-policy
Backups
Snapshots and an append-only file
PostgreSQLrelational
Used for
Accounting, ERP, reports and complex queries
We tune
shared_buffers · work_mem
Backups
Base backups plus archived WAL, to any point in time
MariaDBrelational
Used for
The same work as MySQL, and Galera clusters
We tune
innodb_buffer_pool_size
Backups
mariabackup plus binary logs, to any point in time
OpenSearch · Elasticsearchsearch
Used for
Search across products and articles, filters, logs
We tune
JVM heap · shards
Backups
Snapshots; the index can be rebuilt from the main database
MongoDBdocuments
Used for
Records whose shape keeps changing: events, catalogues, content
We tune
wiredTiger cacheSizeGB
Backups
A replica set, with the oplog for a point in time
  1. When an app outgrows one file, it moves to a database server.
  2. MariaDB began as a fork of MySQL; most applications move between the two unchanged.
  3. Redis sits in front: repeated answers and sessions stay in memory.
  4. The same cache in front of PostgreSQL applications.
  5. The search index is fed from the main database, never the other way round.
  6. PostgreSQL's JSONB covers many document jobs before MongoDB is needed.

Which database for which job

The application usually decides, and we follow it. Where the choice is open, this is how we choose.

The job What we reach for Also works Why
A shop, a WordPress site or a PHP application MySQL · MariaDB PostgreSQL What the application was built and tested on, with the widest support.
Accounting, ERP and reports with heavy joins PostgreSQL MySQL 8 Strict transactions, window functions and a planner made for complex queries.
Cache, sessions, queues and rate limits Redis Memcached Answers from memory. Keep there what can be rebuilt if it is lost.
Search across products or articles OpenSearch · Elasticsearch MySQL · PostgreSQL full-text Relevance, spelling mistakes, filters and facets that a LIKE query cannot give. For a small catalogue the database's own full-text search is enough.
A mobile app, a desktop tool or a small internal service SQLite —Nothing else No server to run: one file, backed up by copying it safely.
Events, logs or records whose shape keeps changing MongoDB PostgreSQL JSONB Flexible documents; PostgreSQL when they also need joins and transactions.
Reads that outgrow one server MySQL · MariaDB · PostgreSQL replicas —Nothing else Reads spread across replicas while writes stay on one primary.

Five engines side by side

What each engine does by itself, taken from its own documentation. The word beside each marker says how far it goes.

Comparison of MySQL, MariaDB, PostgreSQL, Redis and MongoDB
Capability MySQL MariaDB PostgreSQL Redis MongoDB
Used for Shops, WordPress and most PHP applications The same work as MySQL, and Galera clusters Accounting, ERP, reports and complex queries Cache, sessions, queues and counters Records whose shape keeps changing: events, catalogues, content
Data model Tables and rows, queried with SQL. Tables and rows, queried with SQL. Tables and rows, queried with SQL, with a JSONB type for documents. Keys holding strings, hashes, lists, sets, sorted sets or streams. JSON-like documents in collections, with MongoDB's own query language.
Transactions Supported InnoDB, the default engine: commit, rollback and crash recovery. Supported InnoDB is the default engine here too. Supported Every change is a transaction that commits or rolls back. Partly A block of commands runs one after another without interruption, but there is no rollback. Supported Across several documents, on replica sets and sharded clusters.
Where the working data lives On disk; InnoDB keeps a buffer pool of the busiest data in memory. On disk; InnoDB keeps a buffer pool of the busiest data in memory. On disk; shared buffers keep the busiest data in memory. In memory; snapshots and an append-only file write it to disk. On disk; WiredTiger keeps a cache of the busiest data in memory.
Replication built in Supported Source to replicas, asynchronous or semisynchronous. Supported Source to replicas, and Galera Cluster for synchronous multi-primary. Supported Streaming replication; hot standby replicas can answer read queries. Supported Leader to replicas, asynchronous by default; replicas can serve reads. Supported Replica sets: several servers holding the same data, with automatic failover.
Spreading data over several servers Add-on NDB Cluster, a separate engine that partitions data automatically. Add-on The Spider storage engine, which shards tables across MariaDB servers. Partly Partitioning by range, list or hash splits a table inside one server. Supported Redis Cluster splits the keys across nodes. Supported Sharding is built in, by collection.
Point-in-time restore Supported to any second the log covers Supported to any second the log covers Supported to any second the log covers Partly to the last write the append-only file holds Supported to any second the log covers

Scroll sideways to see all five engines.

  • Supported the engine does it itself
  • Partly with a caveat
  • Add-on a separate component or engine

Features vary by version, and each engine's documentation for the version you run has the last word.

Sources: MySQL Reference Manual MariaDB Knowledge Base PostgreSQL Documentation Redis Documentation MongoDB Manual

Why we run MySQL

Five layers where a database gets faster

A slow database is rarely fixed in one place. These are the layers in the order they are usually looked at: the cheapest change first, the costliest last.

Five tuning layers, in the usual order of work: queries, schema and indexes, engine settings, cache, hardware. The same five layers are described in the numbered list that follows. Queries Schema and indexes Engine settings Cache Hardware
The numbers are the usual order of work.
  1. Queries

    The slow query log names the worst queries, and EXPLAIN shows why each one is slow.

    slow_query_log · pg_stat_statements · EXPLAIN ANALYZE
  2. Schema and indexes

    A table shaped for the questions it answers, and the right index, turn a read of every row into a short lookup - the example below is one index doing that.

    CREATE INDEX · ADD INDEX
  3. Engine settings

    Memory is divided between the database, the application and the system, then each engine's buffers are set to fit.

    innodb_buffer_pool_size · shared_buffers · work_mem · wiredTiger cacheSizeGB
  4. Cache

    Repeated answers and sessions stay in memory, so the database is asked less often.

    maxmemory-policy
  5. Hardware

    More memory, faster disks or more processor cores, once the layers above have been used up.

    RAM · CPU · disk I/O
Tuning the PHP side as well

One slow query, found and fixed

Most slow pages are one query. The slow query log names it, EXPLAIN shows why it is slow, and one index is often the whole fix.

SELECT id, total, created_at
FROM orders
WHERE customer_id = 4821
ORDER BY created_at DESC
LIMIT 20;
An example query on an example table

Before

type
ALL
key
NULL
Extra
Using where; Using filesort

Reads every row of the table, then sorts what it found.

The fix: one index

ALTER TABLE orders
  ADD INDEX idx_customer_created (customer_id, created_at);

After

type
ref
key
idx_customer_created
Extra
Backward index scan

Reads only this customer's rows, already in order.

A backup counts once it has been restored

A nightly copy alone loses the day. With the change log kept as well, we can bring the database back to the second before a mistake.

Nightly full backup00:00 Restored to here14:31:59 A DELETE without WHERE14:32:00
Every change, written to the log as it happens Replayed onto the backup
An example day. The times are invented.
  1. Restore the last full backup

    On a separate server, so the live database is left as it is while we work.

  2. Replay the log to the moment before

    Binary logs for MySQL and MariaDB, archived WAL for PostgreSQL, the oplog for MongoDB.

  3. Check it, then bring the data back

    Either the lost rows are copied back, or the restored copy takes over - your decision, with the difference in front of you.

Point-in-time recovery depends on the engine:

  • MySQL · MariaDB · PostgreSQL · MongoDBto any second the log covers
  • Redisto the last write the append-only file holds
  • SQLiteto the last copy of the file
  • OpenSearch · Elasticsearchto the last snapshot, then rebuilt from the source

Restore tests are part of the care we agree with you: onto a separate server, on a set schedule, with every result written down. A backup nobody has restored is a hope, not a backup.

The rest of the work

What a database needs from the day it is installed to the day it is upgraded, and the settings each step touches.

  • Installation and sizing

    The version your application supports, from the vendor's own repository. Memory is divided between the database, the application and the system before anything else is set.

    innodb_buffer_pool_size · shared_buffers · maxmemory · -Xmx
  • Indexes and slow queries

    The slow query log is read, the worst queries explained, and indexes added or removed with the reason recorded.

    slow_query_log · pg_stat_statements · EXPLAIN ANALYZE
  • Replication

    A second copy that follows the first, for spreading reads and for taking over when the primary fails.

    GTID · Galera · streaming replication · replica set
  • Security

    Listening only on the private network, one user per application with only the rights it needs, and encrypted connections wherever traffic leaves the server.

    bind-address · TLS · GRANT · SCRAM · ACL
  • Monitoring

    Connections, replication lag, disk space and slow queries, with an alert before a limit is reached rather than after.

    max_connections · Seconds_Behind_Source · pg_stat_replication
  • Upgrades

    Rehearsed on a copy first, replicas before the primary, and the way back written down before we start.

    pg_upgrade · mariadb-upgrade · rolling restart

Tell us what your database is doing

Which engine, how large, where it runs and what worries you about it. We look first, then quote the setup or the care after one conversation.

Get a quote See hosting plans

Related services

PHP speed OPcache, PHP-FPM pools, caches and queries: where the milliseconds go, and how we take them back.Managed service We run the server for you: patches, monitoring, backups, incidents.Dedicated servers A whole machine, provisioned in hours, managed by the same team.Laravel Applications built on Laravel, with queues, schedules and deploys that do not take the site down.
MickeyAnswers in seconds