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
Extensions: the extensions bundled with PostgreSQL, such as pg_trgm, uuid-ossp, hstore and pgcrypto
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)	Any number of cores, e.g. 0.5, 1, 2	0.5
Memory	Any amount in GB, at least the type's minimum	1 GB
Disk	Any amount in GB	10 GB
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;

Logical Replication

Every PostgreSQL add-on runs with logical replication on (wal_level=logical), so a change-data-capture reader can follow its row changes: for example the Streaming Postgres CDC node, and the Postgres CDC to LLM Alerts template, which sets up its publication when it installs and creates its replication slot on its first start. An add-on created before this takes it the next time it is started or restarted.

A replication slot keeps the database's change log until the slot is read. When you remove whatever reads a slot, drop it so the disk does not fill:

SELECT pg_drop_replication_slot('strongly_alerts_slot');

Popular Extensions

The add-on runs the standard PostgreSQL image, so the extensions bundled with PostgreSQL are available and your add-on user can enable them. Common ones:

pg_trgm: Trigram matching for fuzzy text search
uuid-ossp: UUID generation
hstore: Key-value store within PostgreSQL
pgcrypto: Hashing and encryption functions

Extensions that are not part of PostgreSQL itself, such as PostGIS or pgvector, are not installed.

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;

Backups

A PostgreSQL backup is a pg_dumpall of every database on the add-on, including roles (backup.sql).

Back up now: click Backup Now on the status card, or Back Up Now on the Backup tab, while the add-on is running.
Automatic: on the Backup tab turn on Enable Automatic Backups, choose a Backup Schedule (Hourly, Daily, Weekly or Monthly) and a Retention (3, 7, 14 or 30 backups), and click Save Configuration. Older backups beyond the retention count are deleted automatically.
History: the Backup tab lists every backup with its status, size and any error.
Restore: click Restore next to a succeeded backup in Backup History and confirm. The backup is loaded back into this add-on while it keeps running: every database and role is dropped and recreated from the backup. Open connections are closed, and queries fail until the restore finishes. Data written after the backup is lost. See Restoring a backup.

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;

-- See what is running now, longest first
SELECT pid, now() - query_start AS duration, state, query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY duration DESC;

Monitoring

The Metrics tab on the add-on details page measures the running add-on live: CPU, memory and disk use against its size, network traffic, open and new connections, response time, and instance health and uptime. See Metrics. The Logs tab shows its recent log output.

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​

Logical Replication​

Popular Extensions​

Enabling Extensions​

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​