PostgreSQL Extensions

3 min read Updated 2 days ago

PostgreSQL Extensions

PostgreSQL databases in Ploi Cloud support extensions for specialized use cases.

Available Extensions

Ploi Cloud supports three PostgreSQL extensions:

  • pgvector - Store and search vector embeddings for AI/ML applications
  • PostGIS - Work with geographic and location data
  • pg_cron - Schedule tasks to run inside your database

Enabling Extensions

Extensions can be enabled when creating a PostgreSQL service:

  1. Navigate to your application
  2. Click "Add Service"
  3. Select "PostgreSQL" as your database type
  4. Choose PostgreSQL version 16
  5. In the extensions section, check the boxes for the extensions you need
  6. Complete the service creation

Note: Extensions are only available for PostgreSQL version 16 and must be selected during service creation. They cannot be added or removed after the service is created.

Using Extensions in Your Application

Once enabled, extensions are automatically installed and available in your database. You can use them immediately in your SQL queries.

pgvector Example

Store and search vector embeddings:

-- Create a table with vector column
CREATE TABLE items (
    id bigserial PRIMARY KEY, 
    embedding vector(3)
);

-- Insert vector data
INSERT INTO items (embedding) VALUES 
    ('[1,2,3]'), 
    ('[4,5,6]');

-- Find similar vectors
SELECT * FROM items 
ORDER BY embedding <-> '[3,1,2]' 
LIMIT 5;

PostGIS Example

Work with geographic data:

-- Create a table with geographic data
CREATE TABLE places (
    id serial PRIMARY KEY, 
    name text, 
    location geography(POINT)
);

-- Insert location data
INSERT INTO places (name, location) VALUES 
    ('Office', ST_GeogFromText('POINT(-73.935242 40.730610)'));

-- Find places within 1km
SELECT name FROM places 
WHERE ST_DWithin(
    location, 
    ST_GeogFromText('POINT(-73.935 40.730)'), 
    1000
);

pg_cron Example

Schedule database tasks:

-- Schedule a cleanup job to run daily at 2 AM
SELECT cron.schedule(
    'cleanup-job', 
    '0 2 * * *', 
    'DELETE FROM logs WHERE created_at < NOW() - INTERVAL ''30 days'''
);

-- View scheduled jobs
SELECT * FROM cron.job;

-- Remove a scheduled job
SELECT cron.unschedule('cleanup-job');

Best Practices

  • pgvector: Use appropriate vector dimensions for your use case. Smaller dimensions are faster but less accurate.
  • PostGIS: Create spatial indexes on geography columns for better query performance.
  • pg_cron: Schedule maintenance tasks during low-traffic periods to minimize impact.

Technical Details

When extensions are enabled:

  1. A custom PostgreSQL image is built with the required extensions
  2. An initialization script automatically creates the extensions in your database
  3. The extensions are immediately available for use

The platform handles all the complex setup, so you can focus on using the extensions in your application.