PostgreSQL is easy to start with and harder to run well as it grows: vacuum, replication, connection limits and major-version upgrades all need attention. Our DBAs look after PostgreSQL on your own servers and on managed services such as Amazon RDS. We take on the monitoring, tuning, backups and upgrades, and we answer when something goes wrong.
What we cover
Monitoring
Many PostgreSQL outages are announced well in advance: tables bloating because autovacuum cannot keep up, transaction ID age creeping towards wraparound, replicas falling behind, connections running out or a disk filling with write-ahead log. We monitor each of these, using pg_stat_statements and the statistics views for queries and pgBadger for the logs, and we act on the warnings before they become incidents.
Performance tuning
We find the queries that cost the most time, read their plans with EXPLAIN ANALYZE and fix the cause: a missing or unused index, a query your developers can rewrite, statistics that need more detail, or memory settings such as shared_buffers and work_mem sized to the workload. We tune autovacuum table by table where the defaults are not enough, add connection pooling when the application opens more connections than the server can handle, and on Amazon RDS we manage the same settings through parameter groups.
Backup and recovery
A nightly pg_dump is a start, but it cannot take you back to the minute before a bad update. We set up base backups with continuous write-ahead log archiving for point-in-time recovery, or check that a managed service's automated backups and retention match what you need. Then we restore on a schedule, to a separate server, to show that recovery works and to measure how long it takes.
High availability
We set up streaming replication with one or more standbys, read-only replicas that take reporting load off the primary, and a failover process that has been tested rather than assumed. On Amazon RDS and Aurora we configure Multi-AZ deployments and read replicas, and we test what the application sees when a failover happens.
Patching and upgrades
Minor releases carry security and bug fixes, and we apply them on a schedule you approve. Major versions change behavior and need more care: we check extensions such as PostGIS for compatibility, rehearse the upgrade on a copy with pg_upgrade or logical replication, compare query plans and performance, and agree a window and a way back. On managed services we plan around the provider's own upgrade deadlines.
On call
When PostgreSQL stops accepting connections, a replica breaks or a query locks a busy table, you reach DBAs who already know your databases and how they are built. We find the cause, fix it or guide your team through the fix, and send a short write-up of what happened and what will stop it happening again. The hours we cover, and how to reach us outside them, are agreed in writing at the start of the engagement.
How we work
We usually start with a health check: configuration, roles and access rules, backups (with a test restore), replication, vacuum health and the slowest queries, in a written report ranked by risk. After that you choose ongoing remote DBA cover from named DBAs, a project such as an upgrade or a move to a managed service, or help when you need it. Access is through the route your security team approves, with named accounts and only the privileges the work needs.
Case studies
Client names are withheld. We are happy to share details on a call.
DeliveredFixed within hours
Not disclosed
A client's in-house team had worked an unstable production database for some time without finding the cause. Our DBAs joined a secure screen-share, found the root cause and gave them a step-by-step plan to recover and to prevent a repeat.
Root cause found and fixed within hoursPrevention plan handed to the client's teamQuestions
Yes. On a managed service the provider runs the servers and some of the patching, but the database is still yours to tune, secure and recover. We manage the parameter groups, security groups, replicas and backups there, with the same monitoring and tuning as on your own servers.
The releases the PostgreSQL project currently supports, and older ones that are still in production. If you are on a release that no longer gets fixes, we help you keep it stable and plan the upgrade.
PostgreSQL keeps old versions of rows until vacuum removes them. If autovacuum falls behind, tables and indexes bloat and queries slow down, and in the worst case the database stops accepting writes to protect itself from transaction ID wraparound. Tuning autovacuum for your busiest tables is one of the most valuable things a PostgreSQL DBA does.
Yes. Our heterogeneous migration team converts the schema, the stored procedures and the data, keeps the two databases in step during the move and validates them table by table. Once you are on PostgreSQL, the DBAs on this page can run it.
Yes. We tune spatial indexes and queries, and we check PostGIS and your other extensions for compatibility before every major upgrade.
From the blog
March 7, 2023
February 27, 2023
November 16, 2022
Related services
Move to the cloud, build for it
Trusted data, reports and analytics agents
Support, security and monitoring from one MSP
On-site IT support across New Jersey, New York City and Philadelphia. Remote services wherever you are. Areas we serve →
Tell us where your databases run and what worries you. We suggest where to start: a health check, a project or ongoing cover.
A few lines is enough. The engineers who do this work will reply within one business day.
Or email it.services@princetonits.com or call +1 (212) 507-9350.
Princeton IT Services