PostgreSQL

PostgreSQL Integration

Connect Querri to your PostgreSQL database to run queries, analyze data, and create visualizations.

Overview

The PostgreSQL connector supports:

• PostgreSQL 9.6+: All modern PostgreSQL versions
• AWS RDS PostgreSQL: Managed PostgreSQL on AWS
• Azure Database for PostgreSQL: Managed PostgreSQL on Azure
• Google Cloud SQL for PostgreSQL: Managed PostgreSQL on GCP
• Heroku Postgres: Heroku’s managed PostgreSQL

Setup

Prerequisites

• PostgreSQL database server
• Database credentials with appropriate permissions
• Network access to the database

IP Whitelisting

Important: For cloud-hosted databases or databases behind firewalls, whitelist the following IP address:

18.189.33.77

This is Querri’s outbound IP address. Add this to your database firewall rules or security group.

Creating the Connector

1. Navigate to Connectors

   • Go to Settings → Connectors
   • Click “Add Connector”

2. Select PostgreSQL

   • Choose “PostgreSQL” from the database connectors

3. Configure Connection

Host: your-database-host.com
Port: 5432 (default PostgreSQL port)
Database: your_database_name
Username: your_username
Password: your_password

Optional Settings:

• SSL Mode:
  • require - Always use SSL (recommended)
  • prefer - Use SSL if available
  • disable - Don’t use SSL
• Schema: Default is public
• Connection Timeout: Default is 30 seconds

4. Test Connection

   • Click “Test Connection” to verify
   • If successful, click “Save”

5. Use in Projects

   • Create a new step → “Database Query”
   • Select your PostgreSQL connector
   • Write your SQL query

Network Configuration

AWS RDS Security Groups

Add inbound rule:

Type: PostgreSQL
Protocol: TCP
Port: 5432
Source: 18.189.33.77/32
Description: Querri Access

Azure PostgreSQL Firewall

Add firewall rule:

Rule Name: Querri
Start IP: 18.189.33.77
End IP: 18.189.33.77

Google Cloud SQL

Add authorized network:

Name: Querri
Network: 18.189.33.77/32

Heroku Postgres

Heroku Postgres allows connections from any IP by default. No configuration needed.

Database User Setup

Read-Only User (Recommended)

Create a dedicated read-only user for Querri:

-- Create user
CREATE USER querri_readonly WITH PASSWORD 'secure_password_here';

-- Grant connect permission
GRANT CONNECT ON DATABASE your_database TO querri_readonly;

-- Grant schema usage
GRANT USAGE ON SCHEMA public TO querri_readonly;

-- Grant select on all tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO querri_readonly;

-- Grant select on future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO querri_readonly;

Read-Write User (If Needed)

For data updates or inserts:

-- Create user
CREATE USER querri_readwrite WITH PASSWORD 'secure_password_here';

-- Grant connect permission
GRANT CONNECT ON DATABASE your_database TO querri_readwrite;

-- Grant schema usage
GRANT USAGE ON SCHEMA public TO querri_readwrite;

-- Grant table permissions
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO querri_readwrite;

-- Grant sequence permissions (for auto-increment columns)
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO querri_readwrite;

-- Grant future permissions
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO querri_readwrite;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT USAGE, SELECT ON SEQUENCES TO querri_readwrite;

Example Queries

Basic SELECT

SELECT
    customer_id,
    customer_name,
    email,
    created_at
FROM customers
WHERE status = 'active'
ORDER BY created_at DESC
LIMIT 100;

Aggregate Query

SELECT
    DATE_TRUNC('month', order_date) as month,
    COUNT(*) as order_count,
    SUM(total_amount) as total_revenue,
    AVG(total_amount) as avg_order_value
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month DESC;

JOIN Query

SELECT
    c.customer_name,
    c.email,
    COUNT(o.order_id) as order_count,
    SUM(o.total_amount) as lifetime_value
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE c.status = 'active'
GROUP BY c.customer_id, c.customer_name, c.email
HAVING COUNT(o.order_id) > 0
ORDER BY lifetime_value DESC
LIMIT 50;

Window Functions

SELECT
    product_name,
    category,
    sales_amount,
    RANK() OVER (PARTITION BY category ORDER BY sales_amount DESC) as rank_in_category,
    SUM(sales_amount) OVER (PARTITION BY category) as category_total
FROM product_sales
WHERE sale_date >= '2024-01-01';

Performance Optimization

Use Indexes

Ensure frequently queried columns have indexes:

CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_date ON orders(order_date);
CREATE INDEX idx_customers_email ON customers(email);

Limit Result Sets

Always use LIMIT for large tables:

SELECT * FROM large_table
LIMIT 1000;

Use Query Parameters

For repeated queries with different values, use parameters:

SELECT * FROM orders
WHERE customer_id = {{customer_id}}
AND order_date BETWEEN {{start_date}} AND {{end_date}};

Set parameters when executing the step.

EXPLAIN for Query Plans

Analyze query performance:

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 123;

Common Use Cases

Customer Analysis

SELECT
    customer_segment,
    COUNT(*) as customer_count,
    AVG(lifetime_value) as avg_ltv,
    SUM(lifetime_value) as total_ltv
FROM customers
GROUP BY customer_segment
ORDER BY total_ltv DESC;

Sales Trends

SELECT
    DATE_TRUNC('day', order_date) as date,
    COUNT(*) as orders,
    SUM(total_amount) as revenue
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY DATE_TRUNC('day', order_date)
ORDER BY date;

Inventory Status

SELECT
    product_name,
    current_stock,
    reorder_level,
    CASE
        WHEN current_stock <= reorder_level THEN 'Reorder Needed'
        WHEN current_stock <= reorder_level * 1.5 THEN 'Low Stock'
        ELSE 'Adequate'
    END as stock_status
FROM inventory
WHERE current_stock <= reorder_level * 2
ORDER BY current_stock ASC;

Troubleshooting

Connection Timeout

Problem: Cannot connect to database

Solutions:

1. Verify IP 18.189.33.77 is whitelisted
2. Check database host and port are correct
3. Verify database is running and accessible
4. Check SSL mode settings
5. Verify network connectivity

Authentication Failed

Problem: Invalid credentials error

Solutions:

1. Double-check username and password
2. Verify user exists: SELECT * FROM pg_user WHERE usename = 'your_username';
3. Check user has connect permission
4. Verify database name is correct

Permission Denied

Problem: Permission denied on table/schema

Solutions:

1. Grant necessary permissions (see Database User Setup)
2. Verify schema name is correct
3. Check table ownership
4. Run: \dp table_name to see table permissions

SSL Connection Error

Problem: SSL connection failed

Solutions:

1. Try different SSL modes (prefer, require, disable)
2. Verify server SSL certificate is valid
3. Check PostgreSQL SSL configuration
4. For RDS, ensure SSL is enabled

Query Timeout

Problem: Query takes too long to execute

Solutions:

1. Add indexes to improve query performance
2. Reduce result set size with LIMIT
3. Optimize query with EXPLAIN ANALYZE
4. Increase query timeout in connector settings
5. Consider creating a materialized view

Security Best Practices

Use Read-Only Credentials

Create dedicated read-only user for analytics:

• Prevents accidental data modification
• Limits potential security impact
• Easier to audit and monitor

Enable SSL

Always use SSL for production databases:

SSL Mode: require

Restrict Permissions

Grant only necessary permissions:

-- Only SELECT on specific tables
GRANT SELECT ON TABLE orders, customers TO querri_readonly;

-- Only specific columns
GRANT SELECT (customer_id, email, created_at) ON customers TO querri_readonly;

Use Strong Passwords

• Minimum 16 characters
• Mix of letters, numbers, symbols
• Unique for each connector
• Store securely

Regular Audits

Monitor database access:

-- View active connections
SELECT * FROM pg_stat_activity
WHERE usename = 'querri_readonly';

-- View recent queries
SELECT * FROM pg_stat_statements
WHERE userid = (SELECT oid FROM pg_user WHERE usename = 'querri_readonly');

Connection Pooling

Querri uses connection pooling for efficiency:

• Min connections: 1
• Max connections: 10
• Idle timeout: 5 minutes
• Reuse: Connections are reused across queries

PostgreSQL Versions

Tested and supported versions:

• PostgreSQL 16.x ✓
• PostgreSQL 15.x ✓
• PostgreSQL 14.x ✓
• PostgreSQL 13.x ✓
• PostgreSQL 12.x ✓
• PostgreSQL 11.x ✓ (limited support)
• PostgreSQL 10.x ✓ (limited support)
• PostgreSQL 9.6.x ✓ (limited support)

Support

PostgreSQL Resources

• PostgreSQL Documentation: postgresql.org/docs
• PostgreSQL Wiki: wiki.postgresql.org
• Community Forum: postgresql.org/list

Querri Support

• Email: support@querri.com
• Documentation: /guides/connectors/

Next Steps

• Data Connectors Guide
• Working with Data
• Creating Projects