PostgreSQL

PostgreSQL is a powerful, open-source relational database system with strong support for ACID compliance, complex queries, and advanced data types.

Overview

Versions: 18, 17.6, 16.10 (default: 18)
Default Port: 5432
Cluster Support: No (Single node only)
Use Cases: Relational data, analytics, ACID compliance
Features: Extensions, backups, full-text search

Key Features

ACID Compliant: Full support for transactions with atomicity, consistency, isolation, and durability
Rich Extensions: PostGIS for geospatial data, pg_trgm for text search, and many more
Advanced Data Types: JSON/JSONB, arrays, hstore, and custom types
Full-Text Search: Built-in text search capabilities with ranking and stemming
Concurrent Access: Multi-version concurrency control (MVCC) for high performance
Foreign Data Wrappers: Query external data sources as if they were local tables

Resources

Choose the add-on's resources on the create form:

Setting	Options	Default
CPU (vCPU)	Free entry (e.g., 0.5, 1, 2)	0.5
Memory	1GB, 2GB, 4GB, 8GB, 16GB	1GB
Disk Space	1GB, 5GB, 10GB, 20GB, 50GB, 100GB	10GB
GPU Count	0-8 (0 for CPU-only)	0

Creating a PostgreSQL Add-on

Navigate to Add-ons and click Create Add-on
On the Create New Add-on page, select PostgreSQL as the type
Choose a version (18, 17.6, or 16.10)
Configure:
- Add-on Label (required): descriptive name (e.g., "main-database")
- Description (optional): purpose and notes
- Resources: CPU, memory, and disk for your workload
Optionally enable automatic backups:
- Schedule: Hourly, Daily, Weekly, or Monthly
- Retention: number of backups to keep (1-30, default 7)
Click Create Add-on

Connection Information

Once the add-on is running, the Connection tab of the add-on details page shows the internal host (for apps), port, database, username, password, and a ready-to-use connection string. The same details are exposed to your apps via STRONGLY_SERVICES.

Connection String Format

postgresql://username:password@host:5432/defaultdb

The default database name is defaultdb. Credentials are auto-generated during add-on creation: the username is a randomly generated string (e.g., user_a1b2c3d4) and the password is a 32-character random secret.

Accessing Connection Details

In STRONGLY_SERVICES, add-ons are grouped by type under services.addons, and each entry is one provisioned instance with connection and auth sections:

{
  "id": "addon-abc123defg",
  "name": "main-database",
  "type": "postgres",
  "category": "add-on",
  "status": "running",
  "version": "18",
  "connection": {
    "connection_string": "postgresql://user_a1b2c3d4:<password>@<internal-host>:5432/defaultdb",
    "uri": "postgresql://user_a1b2c3d4:<password>@<internal-host>:5432/defaultdb",
    "host": "<internal-host>",
    "port": 5432,
    "database": "defaultdb"
  },
  "auth": {
    "method": "username_password",
    "credentials": { "username": "user_a1b2c3d4", "password": "<password>" }
  },
  "limits": { "max_connections": 100, "storage_gb": 10 },
  "metadata": { "cpu": "0.5", "memory": "1GB", "disk": "10GB", "backup_enabled": false }
}

Python
Node.js
Go

import os
import json
import psycopg2

# Parse STRONGLY_SERVICES
services = json.loads(os.environ['STRONGLY_SERVICES'])

# Pick your PostgreSQL add-on by name (the label you gave it)
pg = next(
    a for a in services['services']['addons']['postgres']
    if a['name'] == 'main-database'
)

# Connect using the connection string
conn = psycopg2.connect(pg['connection']['connection_string'])

# Or connect using individual parameters
conn = psycopg2.connect(
    host=pg['connection']['host'],
    port=pg['connection']['port'],
    database=pg['connection']['database'],
    user=pg['auth']['credentials']['username'],
    password=pg['auth']['credentials']['password']
)

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

// Parse STRONGLY_SERVICES
const services = JSON.parse(process.env.STRONGLY_SERVICES);
const pg = services.services.addons.postgres
  .find(a => a.name === 'main-database');

// Connect using the connection string
const pool = new Pool({
  connectionString: pg.connection.connection_string
});

// Or connect using individual parameters
const pool2 = new Pool({
  host: pg.connection.host,
  port: pg.connection.port,
  database: pg.connection.database,
  user: pg.auth.credentials.username,
  password: pg.auth.credentials.password
});

// Query example
const result = await pool.query('SELECT * FROM users WHERE active = $1', [true]);

package main

import (
    "database/sql"
    "encoding/json"
    "os"
    _ "github.com/lib/pq"
)

type Connection struct {
    ConnectionString string `json:"connection_string"`
    Host             string `json:"host"`
    Port             int    `json:"port"`
    Database         string `json:"database"`
}

type Addon struct {
    Name       string     `json:"name"`
    Connection Connection `json:"connection"`
}

type Services struct {
    Services struct {
        Addons map[string][]Addon `json:"addons"`
    } `json:"services"`
}

func main() {
    var services Services
    json.Unmarshal([]byte(os.Getenv("STRONGLY_SERVICES")), &services)

    pg := services.Services.Addons["postgres"][0]

    // Connect using the connection string
    db, err := sql.Open("postgres", pg.Connection.ConnectionString)
    if err != nil {
        panic(err)
    }
    defer db.Close()
}

Common Operations

Creating Tables

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    username VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    metadata JSONB
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_metadata ON users USING GIN (metadata);

Full-Text Search

-- Add a full-text search column
ALTER TABLE articles ADD COLUMN search_vector tsvector;

-- Update the search vector
UPDATE articles SET search_vector =
    to_tsvector('english', title || ' ' || content);

-- Create an index
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);

-- Search
SELECT * FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgresql & database');

JSON Operations

-- Query JSONB data
SELECT * FROM users
WHERE metadata @> '{"premium": true}';

-- Update JSONB field
UPDATE users
SET metadata = metadata || '{"last_login": "2025-01-26"}'
WHERE id = 1;

-- Extract JSONB field
SELECT email, metadata->>'plan' as plan
FROM users;

Popular Extensions

PostgreSQL supports many extensions. Common ones include:

PostGIS: Geospatial data support
pg_trgm: Trigram matching for fuzzy text search
uuid-ossp: UUID generation
hstore: Key-value store within PostgreSQL
pg_stat_statements: Query performance tracking

Enabling Extensions

-- Enable an extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pg_trgm";

-- List installed extensions
SELECT * FROM pg_extension;

Backup & Restore

PostgreSQL add-ons use pg_dump for backups, creating complete SQL dumps of your database.

Backup Configuration

Tool: pg_dump
Format: .sql
Includes: Schema, data, indexes, constraints
Storage: the platform's S3 backup storage

Manual Backup

Go to the add-on details page
Open the Backup tab and click Create Manual Backup (the add-on must be running)
The Backup tab shows the last backup time when complete

Scheduled Backups

Configure during add-on creation or on the Backup tab:

Hourly: For critical data with frequent changes
Daily: Recommended for most production workloads
Weekly/Monthly: For less frequently changing data
Retention: number of backups to keep (3, 7, 14, or 30 on the Backup tab)

Performance Optimization

Connection Pooling

Use connection pooling for better performance:

from psycopg2 import pool

# Create a connection pool
connection_pool = pool.SimpleConnectionPool(
    minconn=1,
    maxconn=20,
    host='host',
    database='database',
    user='user',
    password='password'
)

# Get connection from pool
conn = connection_pool.getconn()
# Use connection
# ...
# Return to pool
connection_pool.putconn(conn)

Indexing Strategy

-- B-tree index (default, good for equality and range queries)
CREATE INDEX idx_users_email ON users(email);

-- GIN index (good for JSONB and full-text search)
CREATE INDEX idx_users_metadata ON users USING GIN (metadata);

-- Partial index (index only subset of rows)
CREATE INDEX idx_active_users ON users(email) WHERE active = true;

-- Analyze index usage
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
ORDER BY idx_scan;

Query Optimization

-- Use EXPLAIN to analyze queries
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user@example.com';

-- Use VACUUM to reclaim space
VACUUM ANALYZE users;

-- Monitor slow queries (requires pg_stat_statements extension)
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

Monitoring

The Metrics tab on the add-on details page shows:

CPU Usage: CPU utilization percentage
Memory Usage: memory utilization percentage
Disk Space: disk utilization percentage
Network I/O: current throughput
Request Stats: connections per minute and average response time
Instance Health: instance count and uptime

Best Practices

Use Indexes Wisely: Index frequently queried columns, but avoid over-indexing
Enable Connection Pooling: Reduce connection overhead
Regular VACUUM: Keep database healthy with regular maintenance
Monitor Query Performance: Use EXPLAIN ANALYZE for slow queries
Use Transactions: Wrap related operations in transactions
Backup Regularly: Enable daily backups for production databases
Use Prepared Statements: Prevent SQL injection and improve performance

Migration Guide

From MySQL to PostgreSQL

Key differences to note:

-- MySQL: AUTO_INCREMENT
-- PostgreSQL: SERIAL or IDENTITY
CREATE TABLE users (
    id SERIAL PRIMARY KEY
    -- or
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

-- MySQL: LIMIT offset, count
-- PostgreSQL: LIMIT count OFFSET offset
SELECT * FROM users LIMIT 10 OFFSET 20;

-- MySQL: CONCAT()
-- PostgreSQL: || operator
SELECT first_name || ' ' || last_name AS full_name FROM users;

-- MySQL: IF()
-- PostgreSQL: CASE WHEN
SELECT CASE WHEN age >= 18 THEN 'adult' ELSE 'minor' END FROM users;

Troubleshooting

Connection Issues

# Test connection
psql "postgresql://username:password@host:5432/database"

# Check connection limits
SELECT max_connections FROM pg_settings WHERE name = 'max_connections';

# View current connections
SELECT count(*) FROM pg_stat_activity;

Performance Issues

-- Find slow queries
SELECT pid, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC;

-- Check table bloat
SELECT schemaname, tablename,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

Disk Space Issues

-- Check database size
SELECT pg_size_pretty(pg_database_size(current_database()));

-- Check table sizes
SELECT tablename,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 10;

Support

For issues or questions:

Check add-on logs in the Logs tab of the add-on details page
Review PostgreSQL official documentation
Contact Strongly support through the platform

Overview​

Key Features​

Resources​

Creating a PostgreSQL Add-on​

Connection Information​

Connection String Format​

Accessing Connection Details​

Common Operations​

Creating Tables​

Full-Text Search​

JSON Operations​

Popular Extensions​

Enabling Extensions​

Backup & Restore​

Backup Configuration​

Manual Backup​

Scheduled Backups​

Performance Optimization​

Connection Pooling​

Indexing Strategy​

Query Optimization​

Monitoring​

Best Practices​

Migration Guide​

From MySQL to PostgreSQL​

Troubleshooting​

Connection Issues​

Performance Issues​

Disk Space Issues​

Support​

Related Documentation​