The Four Factors of Production-Ready PostgreSQL

A database isn't production-ready just because your application can query it. To be truly ready for production, your PostgreSQL setup must be able to survive node failures, block unauthorized network access, handle sudden connection spikes without crashing, and tell you exactly why a query is running slow. Let’s explore these four factors and how Aiven puts being production-ready on easy mode.

1. Provisioning & Security: Keeping Out of the News

The first step in a production environment is ensuring you don't end up in the news for the wrong reasons. You don’t want to be the next CrowdStrike.

Network security and proper access controls are non-negotiable.

Here’s the checklist for the core things you need to do:

• Isolate with VPCs: A Virtual Private Cloud (VPC) is a secure, isolated private network within a public cloud. In production, you never want your database accessible to the public internet.

• Use VPC Peering: VPC peering is a networking connection between two VPCs. This allows your application to communicate with your Aiven PostgreSQL database securely, without ever traversing the public internet.

• Enforce Zero-Trust IP Allowlisting: Even when operating inside a VPC, you should enforce zero-trust principles. Update your service to only accept connections from a highly specific IP address or range.

• Scope Your User Roles: Never give your application the avnadmin or any superuser password. Instead, create scoped users. As an example a user for your application backend and a separate read-only user for your analysts.

Aiven makes it easy for you to accomplish this however you feel most comfortable, such as through the console, CLI, Terraform or Kubernetes operator.

2. High Availability & Disaster Recovery: Helping you sleep better

When things fail, and they eventually will, your database needs to handle it gracefully. At Aiven, your choice of plan determines your availability.

While Hobbyist, Developer and Startup plans offer single nodes with limited availability, production workloads should use Business or Premium plans. Whether you should choose Business or Premium depends largely on two things, your tolerance for node failures and the length of the point-in-time recovery (PITR) backup retention you need.

Aiven provides active monitoring and recovery for failed nodes. When the primary fails, Aiven’s management software automatically detects the failure and promotes one of the standbys. Then the connection is re-routed to this new primary node. The old failed primary will then be replaced by another standby node therefore ensuring high availability.

That takes care of fault tolerance, but when it comes to resilience, Aiven also has you covered in case of a disaster with Cross-region disaster recovery (CRDR).

While these things don’t typically receive a lot of hype, its boring features like these that really help you sleep better at night while being on-call.

3. Performance & Scalability: Making it "Blazingly Fast"

Performance issues usually stem from poorly written queries, missing index strategies, or suboptimal access methods.

Here’s the checklist for the core things you should to do:

• Track Workloads with at least pg_stat_statements: To track overall performance problems, pg_stat_statements is an invaluable extension. It provides statement-level statistics, including the number of executions, execution times (min, max, average), and blocks read or written.

• Implement Connection Pooling: PostgreSQL allocates a local memory area for each client backend process. Aiven uses PgBouncer, a lightweight, open-source connection pooler that is the de facto standard. PgBouncer is fully managed and available at no extra cost on all Aiven services. Using PgBouncer prevents idle connection flooding, saves memory and CPU and bypasses connection limit issues. This can lead to higher transactions per second (TPS) with lower latencies.

• Dig Into Explain Plans: To identify exactly why a statement is slow, use statement-based analysis. You can view a dry run of the explain plan using the EXPLAIN command. For more granular runtime details while executing the statement, use EXPLAIN ANALYZE.

This checklist only scratches the surface of what you can do to improve your performance and scalability. To make this easier, Aiven offers an AI database optimizer to help you out. For a more personal touch, you can always reach out to us.

4. Observability & Maintenance: You Can’t Fix What You Can’t See

Finally, proper maintenance and observability tools are required to keep the database healthy over time.

• Embrace AUTOVACUUM: In PostgreSQL, garbage collection is essential and not an optional maintenance task. VACUUM resolves PostgreSQL MVCC side effects by removing dead tuples, freeing up storage space, and protecting against XID rollover using FREEZE. In summary, keep AUTOVACUUM activated as it is by default.

• Monitor Everything: Aiven integrates with powerful tools for metrics, logs, and traces. You can use Thanos, a highly scalable, Prometheus-compatible time series database, alongside Aiven for Grafana, which provides fully managed dashboards and visualization. Additionally, Aiven for OpenSearch can be utilized for deep log analysis.

As we’ve seen from these four factors of production-ready PostgreSQL, being production-ready is having a strong foundation in place being ready for whatever comes your way. If you want to put your production-ready PostgreSQL on easy mode, give Aiven a try.