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

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.

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