Aiven for PostgreSQL

Aiven for PostgreSQL is a fully managed, high-performance relational database service that offers maximum flexibility with advanced extensions including pgvector for AI-powered search, PostGIS for geospatial queries, and TimescaleDB for time-series data.

Overview

PostgreSQL is a powerful open-source relational database ideal for organizations needing well-structured tabular data storage. Aiven for PostgreSQL provides enterprise-grade reliability, automated operations, and advanced features out of the box.

Why Choose Aiven for PostgreSQL

AI-Powered Search

Built-in pgvector extension for semantic search and similarity matching using vector embeddings

Advanced Extensions

PostGIS for location queries, TimescaleDB for time-series, and 50+ extensions available

High Availability

Multi-node clusters with automatic failover and 99.99% SLA on business plans

Native JSON Support

Store and query nested datasets with native jsonb format for flexible schemas

Key Features

pgvector for AI Applications

Enable AI-powered similarity search and vector operations:

Store ML-generated embeddings directly in PostgreSQL
Perform semantic search across text, images, and more
Support for exact and approximate nearest neighbor search
Index types: IVFFlat and HNSW for performance

Use Cases:

Recommendation systems
Semantic document search
Image similarity detection
Fraud detection through pattern matching

Example:

-- Enable pgvector
CREATE EXTENSION vector;

-- Create table with vector column
CREATE TABLE items (
  id SERIAL PRIMARY KEY,
  name TEXT,
  embedding vector(1536)
);

-- Create HNSW index for fast similarity search
CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops);

-- Find similar items
SELECT name, 1 - (embedding <=> '[0.1, 0.2, ...]') AS similarity
FROM items
ORDER BY embedding <=> '[0.1, 0.2, ...]'
LIMIT 10;

PostGIS for Geospatial Data

Advanced spatial database capabilities:

Store points, lines, polygons, and complex geometries
Spatial indexing with GiST and SP-GiST
Distance calculations and proximity queries
Map projections and coordinate transformations

Example:

CREATE EXTENSION postgis;

CREATE TABLE locations (
  id SERIAL PRIMARY KEY,
  name TEXT,
  location GEOGRAPHY(POINT, 4326)
);

-- Find locations within 1000 meters
SELECT name 
FROM locations 
WHERE ST_DWithin(
  location, 
  ST_GeogFromText('POINT(-122.4194 37.7749)'), 
  1000
);

TimescaleDB for Time-Series

Optimized for time-series and IoT data:

Automatic partitioning by time
Continuous aggregates for real-time rollups
Data retention policies
Compression for storage efficiency

Example:

CREATE EXTENSION timescaledb;

CREATE TABLE sensor_data (
  time TIMESTAMPTZ NOT NULL,
  sensor_id INTEGER,
  temperature DOUBLE PRECISION,
  humidity DOUBLE PRECISION
);

SELECT create_hypertable('sensor_data', 'time');

-- Query with time-bucketing
SELECT time_bucket('1 hour', time) AS hour,
       sensor_id,
       avg(temperature) AS avg_temp
FROM sensor_data
WHERE time > NOW() - INTERVAL '24 hours'
GROUP BY hour, sensor_id;

Connection Pooling

Built-in PgBouncer for efficient connection management:

Reduce connection overhead
Session, transaction, and statement pooling modes
Handle thousands of client connections
Automatic connection recycling

Cross-Region Disaster Recovery (CRDR)

Multi-region replication for business continuity:

Asynchronous replication across regions
Failover to secondary region
Switchover for planned maintenance
RPO (Recovery Point Objective) in seconds

Getting Started

Create PostgreSQL Service

Deploy a PostgreSQL service through Aiven Console, CLI, or API:

avn service create my-pg \
  --service-type pg \
  --cloud aws-us-east-1 \
  --plan business-4

Choose from multiple service plans:

Hobbyist: Single node for development
Startup: Single node for small apps
Business: High availability with standby
Premium: Enhanced HA with 2 standby nodes

Enable Extensions

Enable extensions you need for your use case:

CREATE EXTENSION IF NOT EXISTS pgvector;      -- Vector search
CREATE EXTENSION IF NOT EXISTS postgis;       -- Geospatial
CREATE EXTENSION IF NOT EXISTS timescaledb;   -- Time-series
CREATE EXTENSION IF NOT EXISTS pg_stat_statements; -- Query stats

Create Database and Tables

Set up your database schema:

CREATE DATABASE myapp;
\c myapp

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email TEXT UNIQUE NOT NULL,
  name TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

Connect Your Application

Use the connection string from the service overview to connect your application.

Connection Examples

Python
Node.js
Java
Go
Ruby

import psycopg2
from psycopg2.extras import RealDictCursor

# Connection parameters
conn = psycopg2.connect(
    host='pg-service.aivencloud.com',
    port=12345,
    dbname='defaultdb',
    user='avnadmin',
    password='your-password',
    sslmode='require'
)

# Execute query
with conn.cursor(cursor_factory=RealDictCursor) as cur:
    cur.execute("SELECT version();")
    result = cur.fetchone()
    print(result['version'])

# Insert data
with conn.cursor() as cur:
    cur.execute(
        "INSERT INTO users (email, name) VALUES (%s, %s)",
        ('user@example.com', 'John Doe')
    )
    conn.commit()

conn.close()

Using SQLAlchemy:

from sqlalchemy import create_engine, text

engine = create_engine(
    'postgresql://avnadmin:password@pg-service.aivencloud.com:12345/defaultdb?sslmode=require'
)

with engine.connect() as conn:
    result = conn.execute(text("SELECT * FROM users"))
    for row in result:
        print(row)

const { Client } = require('pg');

const client = new Client({
  host: 'pg-service.aivencloud.com',
  port: 12345,
  database: 'defaultdb',
  user: 'avnadmin',
  password: 'your-password',
  ssl: {
    rejectUnauthorized: true,
    ca: fs.readFileSync('./ca.pem').toString()
  }
});

await client.connect();

// Query data
const res = await client.query('SELECT * FROM users WHERE id = $1', [1]);
console.log(res.rows[0]);

// Insert data
await client.query(
  'INSERT INTO users (email, name) VALUES ($1, $2)',
  ['user@example.com', 'Jane Doe']
);

await client.end();

Using Prisma:

// schema.prisma
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  createdAt DateTime @default(now())
}

// client.js
const { PrismaClient } = require('@prisma/client');
const prisma = new PrismaClient();

const user = await prisma.user.create({
  data: {
    email: 'user@example.com',
    name: 'John Doe'
  }
});

import java.sql.*;

public class PostgreSQLExample {
    public static void main(String[] args) {
        String url = "jdbc:postgresql://pg-service.aivencloud.com:12345/defaultdb?sslmode=require";
        String user = "avnadmin";
        String password = "your-password";

        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            // Query data
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM users");
            
            while (rs.next()) {
                System.out.println(rs.getString("name"));
            }

            // Insert data
            PreparedStatement pstmt = conn.prepareStatement(
                "INSERT INTO users (email, name) VALUES (?, ?)"
            );
            pstmt.setString(1, "user@example.com");
            pstmt.setString(2, "John Doe");
            pstmt.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

package main

import (
    "database/sql"
    "fmt"
    "log"
    _ "github.com/lib/pq"
)

func main() {
    connStr := "host=pg-service.aivencloud.com port=12345 user=avnadmin password=your-password dbname=defaultdb sslmode=require"
    db, err := sql.Open("postgres", connStr)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // Query data
    rows, err := db.Query("SELECT id, email, name FROM users")
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()

    for rows.Next() {
        var id int
        var email, name string
        if err := rows.Scan(&id, &email, &name); err != nil {
            log.Fatal(err)
        }
        fmt.Printf("%d: %s (%s)\n", id, name, email)
    }

    // Insert data
    _, err = db.Exec(
        "INSERT INTO users (email, name) VALUES ($1, $2)",
        "user@example.com", "John Doe",
    )
    if err != nil {
        log.Fatal(err)
    }
}

require 'pg'

conn = PG.connect(
  host: 'pg-service.aivencloud.com',
  port: 12345,
  dbname: 'defaultdb',
  user: 'avnadmin',
  password: 'your-password',
  sslmode: 'require'
)

# Query data
result = conn.exec('SELECT * FROM users')
result.each do |row|
  puts "#{row['name']}: #{row['email']}"
end

# Insert data
conn.exec_params(
  'INSERT INTO users (email, name) VALUES ($1, $2)',
  ['user@example.com', 'John Doe']
)

conn.close

High Availability

Aiven for PostgreSQL provides multiple levels of availability:

Business Plan
Premium Plan
CRDR (Cross-Region)

1 Primary + 1 Standby Node

Synchronous replication to standby
Automatic failover in case of primary failure
Read replicas for read scaling
14 days backup retention
99.99% SLA

Failure Handling:

Primary fails → Standby promoted automatically
Standby fails → Primary continues serving
Service URI remains constant (only IP changes)

Multi-Region Disaster Recovery

Asynchronous replication to another region
Failover for regional outages
Switchover for planned maintenance
RPO typically under 1 minute

Setup CRDR:

avn service cross-region-replica-create \
  --project myproject \
  --service my-pg \
  --destination-cloud aws-eu-west-1

Performance Optimization

Indexing Strategies

Choose the right index type for your queries:

-- B-tree (default) - most common
CREATE INDEX idx_email ON users(email);

-- GiST for full-text search
CREATE INDEX idx_content_search ON articles USING GiST(to_tsvector('english', content));

-- GIN for JSONB and arrays
CREATE INDEX idx_metadata ON products USING GIN(metadata);

-- BRIN for large time-series tables
CREATE INDEX idx_created_at ON events USING BRIN(created_at);

-- Partial index for specific conditions
CREATE INDEX idx_active_users ON users(email) WHERE active = true;

Query Optimization

Use EXPLAIN ANALYZE to optimize queries:

-- Analyze query performance
EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM users WHERE email LIKE '%@example.com';

-- Use pg_stat_statements for query stats
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Connection Pooling

Configure PgBouncer for optimal connection management:

Session mode: For applications that need session-level features
Transaction mode: Best for most web applications
Statement mode: Maximum connection reuse

Update via Aiven Console or API:

avn service update my-pg \
  -c pgbouncer.pool_mode=transaction \
  -c pgbouncer.max_client_conn=1000

Shared Buffers Tuning

PostgreSQL automatically tunes shared_buffers based on available memory:

Business-4: 1 GB shared_buffers
Business-8: 2 GB shared_buffers
Business-16: 4 GB shared_buffers

Monitor buffer usage:

SELECT * FROM pg_stat_bgwriter;

Backup and Recovery

Automatic Backups

Continuous WAL archiving
Point-in-time recovery (PITR)
Encrypted backups in object storage
Retention: 2-30 days based on plan

Manual Backups

On-demand backup creation
Fork services from backups
Export backups for migration
Cross-region backup storage

Restore to Point in Time:

avn service update my-pg \
  --restore-point-in-time "2024-03-04T10:30:00Z"

Fork a Service:

avn service create my-pg-fork \
  --service-type pg \
  --cloud aws-us-east-1 \
  --plan business-4 \
  --fork-from my-pg

Monitoring and Observability

Key Metrics

Performance Metrics
Resource Metrics
Query Insights

Connections: Active and idle connections
Transactions per second: Database throughput
Cache hit ratio: Buffer efficiency (target >99%)
Query latency: Average query execution time
Replication lag: Delay between primary and standby

Enable pg_stat_statements:

CREATE EXTENSION pg_stat_statements;

-- Top 10 slowest queries
SELECT 
  substring(query, 1, 50) AS query_snippet,
  calls,
  mean_exec_time,
  total_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

Integration with Aiven Services

avn service integration-create \
  --integration-type metrics \
  --source-service my-pg \
  --dest-service my-grafana

avn service integration-create \
  --integration-type logs \
  --source-service my-pg \
  --dest-service my-opensearch

Security Best Practices

Authentication and Authorization

Use strong passwords for database users
Implement least-privilege access control
Create separate users for applications
Use certificate authentication where possible
Rotate credentials regularly

-- Create read-only user
CREATE USER readonly_user WITH PASSWORD 'strong_password';
GRANT CONNECT ON DATABASE myapp TO readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

Network Security

Enable VPC peering for private connectivity
Use IP allowlisting to restrict access
Configure AWS PrivateLink for secure connections
Always use SSL/TLS connections
Implement application-level encryption for sensitive data

Audit Logging

Enable pgaudit for compliance:

CREATE EXTENSION pgaudit;

-- Audit all DDL and DCL statements
ALTER SYSTEM SET pgaudit.log = 'ddl, role';

-- Audit specific tables
ALTER TABLE sensitive_data SET (pgaudit.log = 'read, write');

Use Cases

Web Applications
AI/ML Applications
Geospatial Apps
IoT and Time-Series

Traditional web apps with transactional data:

E-commerce platforms
Content management systems
User authentication and profiles
Session storage

Migration to Aiven

Prepare Source Database

Document current schema and extensions
Identify custom configurations
Plan for minimal downtime

Use aiven-db-migrate

Built-in migration tool for PostgreSQL:

avn service migration-start my-pg \
  --source-host source-db.example.com \
  --source-port 5432 \
  --source-username postgres \
  --source-password xxx \
  --source-dbname mydb

Verify Migration

Check row counts
Verify indexes and constraints
Test application connectivity
Monitor replication lag

Cutover

Stop writes to source
Wait for replication to complete
Update application connection strings
Verify application functionality

Apache Kafka

Stream changes with Debezium CDC to Kafka

Apache Flink

Real-time processing with Flink PostgreSQL connector

Grafana

Visualize PostgreSQL data and metrics

OpenSearch

Full-text search with PostgreSQL data

Resources

Migration Support: Aiven provides free migration assistance for Enterprise customers. Contact support for help migrating your PostgreSQL databases.

Get Started

Platform

Services

Developer Tools

Integrations

Aiven for PostgreSQL

Overview

Why Choose Aiven for PostgreSQL

AI-Powered Search

Advanced Extensions

High Availability

Native JSON Support

Key Features

Getting Started

Connection Examples

High Availability

Performance Optimization

Backup and Recovery

Automatic Backups

Manual Backups

Monitoring and Observability

Key Metrics

Integration with Aiven Services

Security Best Practices

Use Cases

Migration to Aiven

Apache Kafka

Apache Flink

Grafana

OpenSearch

Resources

​Overview

​Why Choose Aiven for PostgreSQL

AI-Powered Search

Advanced Extensions

High Availability

Native JSON Support

​Key Features

​Getting Started

​Connection Examples

​High Availability

​Performance Optimization

​Backup and Recovery

Automatic Backups

Manual Backups

​Monitoring and Observability

​Key Metrics

​Integration with Aiven Services

​Security Best Practices

​Use Cases

​Migration to Aiven

​Related Services

Apache Kafka

Apache Flink

Grafana

OpenSearch

​Resources

Overview

Why Choose Aiven for PostgreSQL

Key Features

Getting Started

Connection Examples

High Availability

Performance Optimization

Backup and Recovery

Monitoring and Observability

Key Metrics

Integration with Aiven Services

Security Best Practices

Use Cases

Migration to Aiven

Related Services

Resources