Engineering for PostgreSQL® databases

The database under it,
cared for like the app

We set up and look after PostgreSQL® databases, with each one on a supported version, backups restored on a schedule, the slowest queries fixed and a standby kept in step, so you know your data comes back, and how long it takes.

Thanks, we’ll be in touch soon.

{{ heroStatus }}

You’re in good company

A few of the companies we’ve worked with.

When the backup has never been restored

PostgreSQL is an open-source relational database. A server holds databases, and each database holds tables: the rows of your data, kept right by keys and constraints, with indexes that lead a query, a request for data, to its rows. Roles connect to it, each with only the rights it has been granted. Every change is recorded in a log, the write-ahead log, and backups, standbys and restores all build on it.

A backup job can report success every night for years. Whether that backup restores, and how long a restore takes, is something you only learn by restoring it.

PostgreSQL offers more than one way to keep a copy: a dump, written as the commands that rebuild the data; a copy of the server’s files; and continuous archiving, a base backup kept with the log written after it, which can bring a server back to a chosen moment since that backup. Each brings back something different and takes its own time: a dump can generally be loaded into a newer version, while a copy of the files or an archive brings back the whole server as it was.

So the real work is a restore tried on a schedule, to a server kept apart, with how long it took written down.

What we build

Databases set up to keep running, schemas and queries that hold up as the app grows, and backups restored before anyone needs them.

  • Set up to keep running

    A database on a cloud’s managed service or on your own servers, its settings matched to its work, with a standby kept in step and the switch to it rehearsed and written down.

  • Moved from the database you have now

    Tables moved over a few at a time, the data in each checked against the database you have now, and each app switched over once its results match.

  • Schemas that keep the data right

    Tables, keys and constraints that refuse what doesn’t fit, with every change written as a migration, reviewed, and applied in steps the app can run through.

  • Queries that stay quick

    The slowest queries found from the database’s own statistics, their plans read, indexes added where they help, and vacuuming tuned for the busiest tables.

  • Backups restored to a moment

    Base backups kept with the log written after them, restores tried on a separate server on a schedule, and how long each one took written down.

  • Upgrades ahead of each end date

    Minor releases, the fixes between versions, applied as they come, and each move to a new major version, the yearly release with new features, rehearsed on a copy first, with each extension, an add-on the database uses, checked.

  • Access kept to what each app needs

    A role for each app with only the rights it needs, row-level security where customers share tables, and connections encrypted.

  • Watched once it’s running

    The slowest queries, how far each standby trails, how quickly the disk fills, and alerts your team sees and acts on.

  • AI where it helps

    Search by meaning, with vectors kept beside the rows they describe and read under the same roles, through an extension from a project of its own, where it answers a real question.

    AI Services

Brought back to just before the mistake

With a base backup and the log written after it, a server can be brought back to a chosen moment since that backup: a time, a named point, or the end of one transaction.

A restore brings back the whole server as it was at that moment, not one table, and nothing written after it is there.

So restoring over the live database would undo the work done since.

So we restore to a server kept apart, bring back only what’s missing, and keep everything written since. And we try it on a schedule, so how long it takes is known before it’s needed.

Each change has a button that restores a separate server to just before it. Then copy back what the live table is missing.

Furniture orders at Copperbeech Studio

What changed, in order

  1. A desk lamp order added
  2. The chair order changed from pine to oak
  3. Every order deleted by mistake
  4. A bookcase order added
The live table
OrderDetail

A separate server, kept apart from the live one

{{ copyWhy }}

{{ rsLine }}

{{ againWhy }}

{{ rsSay }}

Every end date published, every move rehearsed

The PostgreSQL project releases a new major version about once a year, supports each one for a set period, and publishes the date of its final release well ahead. Between them come minor releases, on a regular schedule, with fixes, and the project advises always running the latest one.

A minor release never changes how the data is stored, so applying one takes a restart with the new programs, not a dump and reload. A major version can change how the data is stored, so moving to one takes the upgrade tool the project ships, a dump and reload, or replication to a new server, and each extension needs a version built for the new major version.

After its final release, a version gets no more fixes. A cloud’s managed service follows its own calendar for each version.

So we apply each minor release as it comes, and move each database to a newer major version ahead of its final release, rehearsed on a copy first, with every extension checked.

Start with the last restore, and the slowest query

The review covers the versions in use, and each one’s final release date; whether minor releases are applied as they come; the extensions in use, and whether each has a version for the next major release; backups: how they’re taken, where they’re kept, when one was last restored, and how long it took; the archived log, and how far back a restore can reach; standbys: how far each trails, and whether the switch to one has been tried; connections: how many the apps open, and whether a pooler, a service that shares connections among them, sits in between; the slowest queries, from the database’s own statistics, and their plans; indexes never used, and indexes missing; vacuuming, and tables grown well past the rows they hold; roles: which apps connect as the owner or a superuser, how passwords are stored, and whether connections are encrypted; row-level security where customers share tables; settings changed from their defaults, and why; and monitoring, alerts and disk growth.

The first step is one of these, each tried on a copy before anything changes: a restore to a separate server, timed; the minor releases brought up to date; the next major upgrade rehearsed, with every extension checked; the slowest queries’ plans read, and fixed where the fix is in the database; or each app moved to its own role, with only the rights it needs.

What you receive is a written review and a proposal for that first step.

Questions worth asking

  1. What does a managed service still leave to us?

    A cloud’s managed service runs the servers for you, and usually takes the backups and applies the minor releases. The schema, the queries, the roles, restores tried on a schedule, and when to move to a new major version stay with your team, and that’s where we work.

  2. Can we upgrade without a long stop?

    Often the stop is short. The project’s upgrade tool can upgrade a server in place, and replication to a new server keeps the old one answering until the switch. We rehearse it on a copy first, and plan the switch with you.

  3. How do we know our backups will restore?

    By restoring them: on a schedule, to a server kept apart, with how long each restore took written down. A backup job that reports success doesn’t tell you how long the restore will take.

  4. Why are some of our queries slow?

    Often a missing index, a plan chosen from out-of-date statistics, or tables that have grown well past the rows they hold. The database’s own statistics show which queries take the longest; we read their plans, fix what’s in the database, and hand the rest of the app’s speed to Performance & Optimization.

    Performance & Optimization

  5. Can we keep documents and search in the same database?

    Often, yes. PostgreSQL stores JSON documents beside its tables and has full-text search of its own, and extensions add more. Where another kind of store fits the work better, we’ll say so.

  6. Can it search by meaning, for AI features?

    With an extension, yes: vectors kept beside the rows they describe, read under the same roles. The extension comes from a project of its own, not from PostgreSQL itself, and we check that it fits your data before it’s turned on.

  7. What does it cost to work with you?

    That depends on the database and the first step you choose. Before any work starts, you get a written proposal with the scope, the price or rate, and how you pay.

    Pricing

  8. Can we hire developers for our own team?

    Yes. Within 48 hours of your request, we introduce developers for PostgreSQL databases, in your time zone, and you meet them before choosing who joins.

    Developers for your team

Postgres, PostgreSQL and the Slonik Logo are trademarks or registered trademarks of the PostgreSQL Community Association of Canada, and used with their permission. Webair is independent of the PostgreSQL Community Association of Canada and the PostgreSQL Global Development Group, and isn’t sponsored by, endorsed by or affiliated with either.

Let’s build it better

Tell us about the databases behind your apps, and when a backup was last restored, and we’ll suggest where to begin, in a short call.

Thanks, we’ll be in touch soon.

{{ ctaStatus }}