CloudPloy

PostgreSQL Hosting for Laravel and PHP on CloudPloy

PostgreSQL is the preferred database for Laravel applications that need advanced data types, reliable JSON storage, full-text search, or strict SQL compliance. CloudPloy provisions and manages PostgreSQL as a first-class service alongside your PHP application - same server, same dashboard, automated backups included. This guide covers everything from initial setup to production performance tuning.

PostgreSQL vs MySQL: When to Choose Postgres

Both databases work well with Laravel. PostgreSQL is the better choice when your application needs any of the following:

Use Case PostgreSQL Advantage
JSON/JSONB columns JSONB is indexed and fully queryable - faster than MySQL's JSON type
Full-text search Built-in tsvector/tsquery with ranking, no plugin needed
Array columns Native array types that MySQL does not support
Complex queries Better query planner for JOINs and window functions
Geospatial data PostGIS extension for geographic queries
Strict SQL compliance Stricter type checking catches bugs that MySQL silently ignores
Concurrent writes MVCC implementation avoids read/write lock contention

MySQL remains a solid choice for simpler applications and is slightly easier to configure for WordPress. For new Laravel projects, PostgreSQL is increasingly the recommended default.

Part 1: Setting Up PostgreSQL on CloudPloy

When creating a new application in the CloudPloy dashboard, select PostgreSQL as the database type. CloudPloy automatically:

  1. Provisions a PostgreSQL instance on your server (same machine as your app by default)
  2. Creates a dedicated database user with a strong generated password
  3. Injects the connection credentials as environment variables into your application container
  4. Configures automated daily backups with 7-day retention
  5. Sets up connection monitoring in the CloudPloy dashboard

The environment variables injected are:

DB_CONNECTION=pgsql
DB_HOST=127.0.0.1
DB_PORT=5432
DB_DATABASE=your_app_db
DB_USERNAME=your_app_user
DB_PASSWORD=generated_secure_password

Part 2: Laravel Configuration

database.php

Laravel's default PostgreSQL configuration in config/database.php works out of the box with CloudPloy's environment variables. Verify this section is present and uses env() calls:

'pgsql' => {
    'driver'   => 'pgsql',
    'host'     => env('DB_HOST', '127.0.0.1'),
    'port'     => env('DB_PORT', '5432'),
    'database' => env('DB_DATABASE', 'forge'),
    'username' => env('DB_USERNAME', 'forge'),
    'password' => env('DB_PASSWORD', ''),
    'charset'  => 'utf8',
    'prefix'   => '',
    'schema'   => 'public',
    'sslmode'  => 'prefer',
},

Set the default connection in your .env file:

DB_CONNECTION=pgsql

Enabling PostgreSQL-Specific Laravel Features

Several Laravel features only work with PostgreSQL. Enable them in your migrations:

// In a migration - PostgreSQL-specific column types
Schema::create('products', function (Blueprint $table) {
    $table->id();
    $table->string('name');

    // JSONB column (indexed, queryable)
    $table->jsonb('metadata')->nullable();

    // Array column (PostgreSQL only)
    $table->text('tags')->array()->nullable();

    // Full-text search vector (generated column)
    $table->tsvector('search_vector')->nullable();

    $table->timestamps();
});

Part 3: Schema Migrations Best Practices

PostgreSQL is stricter than MySQL about schema changes. Follow these rules to avoid failed migrations in production:

Adding Columns Safely

Adding a non-nullable column without a default forces PostgreSQL to rewrite the entire table - on large tables this takes minutes and locks writes. Always add with a default or as nullable:

// Safe - nullable column, no table rewrite
$table->string('phone')->nullable();

// Safe - column with default, no table rewrite on PostgreSQL 11+
$table->boolean('is_active')->default(true);

// Dangerous on large tables - avoid
$table->string('required_field');

Adding Indexes Concurrently

Standard CREATE INDEX locks the table for reads and writes. Use CONCURRENTLY for production tables to avoid downtime. In Laravel migrations:

// Standard index - locks the table (fine for small tables or initial creation)
$table->index('user_id');

// Concurrent index - no table lock (required for large production tables)
// Must run outside a transaction - use a raw statement
DB::statement('CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders (user_id)');

Note: CREATE INDEX CONCURRENTLY cannot run inside a transaction, so it cannot be inside a standard Laravel migration (which wraps everything in a transaction). Use the DB::unprepared() method and disable transaction wrapping:

public function up(): void
{
    DB::unprepared('CREATE INDEX CONCURRENTLY idx_orders_created_at ON orders (created_at DESC)');
}

public function down(): void
{
    DB::unprepared('DROP INDEX CONCURRENTLY IF EXISTS idx_orders_created_at');
}

Part 4: PostgreSQL-Specific Laravel Features

JSONB Columns and Querying

JSONB columns are stored in a binary format that supports indexing and efficient querying:

// Storing JSON data
Product::create({
    'name'     => 'Widget Pro',
    'metadata' => {'color' => 'blue', 'weight' => 1.5, 'tags' => ['sale', 'new']},
});

// Querying JSON fields with Laravel
Product::where('metadata->color', 'blue')->get();
Product::whereJsonContains('metadata->tags', 'sale')->get();
Product::whereJsonLength('metadata->tags', '>', 1)->get();

// Add a GIN index for fast JSON queries
DB::statement("CREATE INDEX idx_products_metadata ON products USING gin(metadata)");

Full-Text Search

PostgreSQL's built-in full-text search avoids the need for Elasticsearch or Algolia for basic search requirements:

// Add a GIN index for full-text search
DB::statement("
    ALTER TABLE articles ADD COLUMN search_vector tsvector
    GENERATED ALWAYS AS (
        to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))
    ) STORED
");
DB::statement("CREATE INDEX idx_articles_search ON articles USING gin(search_vector)");

// Searching with ranking
$results = DB::select("
    SELECT id, title,
           ts_rank(search_vector, query) AS rank
    FROM articles,
         to_tsquery('english', ?) AS query
    WHERE search_vector @@ query
    ORDER BY rank DESC
    LIMIT 20
", [implode(' & ', array_filter(explode(' ', $searchTerm)))]);

Window Functions for Analytics

// Running total per user
DB::select("
    SELECT
        user_id,
        order_date,
        amount,
        SUM(amount) OVER (
            PARTITION BY user_id
            ORDER BY order_date
            ROWS UNBOUNDED PRECEDING
        ) AS running_total
    FROM orders
    ORDER BY user_id, order_date
");

Part 5: Performance Tuning

PostgreSQL's default configuration is deliberately conservative to run on minimal hardware. For a production application you need to tune these key parameters. CloudPloy applies sensible defaults based on your server size, but you can customize them in App > Database > Configuration.

Key Parameters by Server Size

Parameter 2GB RAM Server 8GB RAM Server 32GB RAM Server
shared_buffers 512MB 2GB 8GB
effective_cache_size 1.5GB 6GB 24GB
work_mem 4MB 16MB 64MB
maintenance_work_mem 128MB 512MB 2GB
max_connections 100 200 400
wal_buffers 16MB 64MB 256MB

shared_buffers is the most impactful single setting - set it to 25% of total RAM. effective_cache_size is a hint to the query planner about available OS cache (set to 75% of total RAM). work_mem is allocated per sort operation per query - keep it modest as it multiplies with concurrent connections.

Finding Slow Queries

Enable pg_stat_statements to track query performance over time. CloudPloy enables this extension automatically. Query the top slow queries from your database terminal:

-- Top 10 slowest queries by total execution time
SELECT
    LEFT(query, 80) AS query_preview,
    calls,
    ROUND(total_exec_time::numeric, 2) AS total_ms,
    ROUND(mean_exec_time::numeric, 2) AS mean_ms,
    ROUND(stddev_exec_time::numeric, 2) AS stddev_ms
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

EXPLAIN ANALYZE

For any slow query, use EXPLAIN (ANALYZE, BUFFERS) to see the execution plan and identify missing indexes:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.*, u.email
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending'
  AND o.created_at > NOW() - INTERVAL '7 days'
ORDER BY o.created_at DESC;

Look for Seq Scan nodes on large tables - these indicate a missing index. A Bitmap Heap Scan or Index Scan means the query planner is using an index.

Part 6: Connection Pooling with PgBouncer

Laravel's PHP-FPM creates a new database connection for each request. PostgreSQL handles connections more expensively than MySQL - each connection spawns a new OS process. At 200+ concurrent requests without connection pooling, you will hit PostgreSQL's max_connections limit and see "too many connections" errors.

CloudPloy offers PgBouncer as a connection pooler. Enable it in App > Database > Connection Pooling. PgBouncer sits between your app and PostgreSQL, maintaining a small pool of real connections and multiplexing hundreds of app connections through them.

PgBouncer Modes

Mode How it Works Best For
Transaction pooling Connection returned to pool after each transaction Most web apps (recommended)
Session pooling Connection held for the entire session Apps using session-level features (SET, advisory locks)
Statement pooling Connection returned after every statement Read-only analytics workloads

Transaction pooling is incompatible with prepared statements in some configurations. If you use Laravel's default prepared statement caching, disable it when using PgBouncer transaction mode:

// config/database.php - disable prepared statements for PgBouncer transaction mode
'pgsql' => {
    // ... other settings
    'options' => {
        PDO::ATTR_EMULATE_PREPARES => true,
    },
},

Part 7: Backups and Point-in-Time Recovery

CloudPloy takes daily logical backups (pg_dump) of your PostgreSQL database automatically, with 7-day retention on standard plans and 30-day retention on Pro plans. Backups are stored in a separate location from your server.

Manual Backup

To take an on-demand backup before a risky migration:

  1. Go to App > Backups > Create Backup in the CloudPloy dashboard
  2. Select Database Only for a faster backup
  3. Click Create - the backup completes within minutes for most databases

Point-in-Time Recovery

For Pro plan servers, CloudPloy enables PostgreSQL WAL archiving, allowing you to restore to any point in time within your retention window (not just daily backup snapshots). This is critical for high-transaction applications where a daily backup gap would mean unacceptable data loss.

To restore to a specific time, contact CloudPloy support with your target timestamp. For example: "restore to 2026-03-28 14:32:00 UTC" and the support team can provision a restore to that exact moment.

Part 8: Common Issues and Troubleshooting

"could not serialize access due to concurrent update"

This error occurs when using SERIALIZABLE isolation level with concurrent writes. Switch to READ COMMITTED (the default) unless you specifically need serializable transactions, or implement retry logic in your application for serialization failures.

"ERROR: column X is of type Y but expression is of type Z"

PostgreSQL enforces strict type checking. This usually means you are passing a string where an integer or enum is expected. PostgreSQL requires an explicit cast: CAST(? AS integer) or ?::integer. In Laravel, ensure your Eloquent model casts are defined correctly.

Long-running VACUUM operations

PostgreSQL autovacuum reclaims space from deleted rows. On busy tables with many updates/deletes, autovacuum may run continuously. Monitor vacuum activity with:

SELECT schemaname, relname, n_dead_tup, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

If a table has millions of dead tuples and autovacuum cannot keep up, run a manual VACUUM ANALYZE table_name from the CloudPloy database terminal during a low-traffic window.

Connection timeout under load

If you see "remaining connection slots are reserved for non-replication superuser connections", you have hit max_connections. Enable PgBouncer (see Part 6 above) - this is the correct fix. Do not simply increase max_connections as each PostgreSQL connection uses 5-10MB of RAM, and a high value degrades query planner performance.

Frequently Asked Questions

Can I use PostgreSQL with WordPress on CloudPloy?

WordPress does not officially support PostgreSQL - it is built for MySQL/MariaDB. There are community plugins (PG4WP) that add PostgreSQL compatibility but they are not recommended for production use. For WordPress sites, use MySQL. For Laravel/Symfony applications, use PostgreSQL.

Can I connect to my PostgreSQL database from a local tool like TablePlus or pgAdmin?

Yes, via SSH tunnel. CloudPloy's PostgreSQL instances are not exposed to the public internet by default for security reasons. Connect your local tool through an SSH tunnel using your server's SSH credentials. In TablePlus: Connection > Use SSH Tunnel, enter your server's IP and SSH key, then connect to 127.0.0.1:5432.

What PostgreSQL version does CloudPloy use?

CloudPloy currently provisions PostgreSQL 16 by default, with support for PostgreSQL 14 and 15 for applications with compatibility requirements. Major version upgrades can be performed through the dashboard with a maintenance window.

How do I run raw SQL migrations safely in production?

For large tables, wrap expensive DDL operations in a transaction where possible, and use concurrent index creation for new indexes. Always test migrations on your staging environment first. CloudPloy's staging environments are clones of production infrastructure, giving accurate timing estimates for migration duration.

Need help with a specific PostgreSQL issue on CloudPloy? Contact support or browse the documentation for Laravel-specific deployment guides.