Documentation - SmartSQL

Requirements

Before setting up SmartSQL, ensure your PostgreSQL environment meets these requirements:

🐘

PostgreSQL Version

PostgreSQL 14 or higher

SmartSQL requires modern PostgreSQL features for optimal performance monitoring.

🌐

Network Access

Database must be publicly accessible

SmartSQL connects to your database over secure SSL connections. Ensure your database accepts external connections.

Database Configuration

Configure your PostgreSQL instance to enable query statistics collection:

1

Enable pg_stat_statements

Add the following configuration to your postgresql.conf file:

postgresql.conf

shared_preload_libraries = 'pg_stat_statements'

2

Configure Query Tracking

Add these additional settings to track all queries:

postgresql.conf

pg_stat_statements.track = all
compute_query_id = on

3

Restart PostgreSQL

Restart your PostgreSQL service to apply the configuration changes:

Terminal

sudo systemctl restart postgresql

Create Read-Only User

Create a dedicated read-only user for SmartSQL to ensure secure database access:

1

Create the SmartSQL User

Connect to your PostgreSQL database and create a new user:

SQL

CREATE USER smartsql_monitor WITH PASSWORD 'your_secure_password_here';

Security Note: Replace 'your_secure_password_here' with a strong, unique password. Store this password securely as you'll need it for the SmartSQL connection.

2

Grant Required Permissions

Grant the necessary read permissions for monitoring. Run these commands for each schema you want to monitor:

SQL

-- Grant connection to database
GRANT CONNECT ON DATABASE your_database_name TO smartsql_monitor;

-- Grant usage on schema (repeat for each schema you want to monitor)
GRANT USAGE ON SCHEMA public TO smartsql_monitor;
GRANT USAGE ON SCHEMA your_other_schema TO smartsql_monitor;

-- Grant select on all tables in schema (repeat for each schema)
GRANT SELECT ON ALL TABLES IN SCHEMA public TO smartsql_monitor;
GRANT SELECT ON ALL TABLES IN SCHEMA your_other_schema TO smartsql_monitor;

-- Grant select on future tables (repeat for each schema)
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO smartsql_monitor;
ALTER DEFAULT PRIVILEGES IN SCHEMA your_other_schema GRANT SELECT ON TABLES TO smartsql_monitor;

-- Grant access to statistics views
GRANT SELECT ON pg_stat_statements TO smartsql_monitor;
GRANT SELECT ON pg_stat_user_tables TO smartsql_monitor;
GRANT SELECT ON pg_stat_user_indexes TO smartsql_monitor;

3

Enable pg_stat_statements Extension

Create the pg_stat_statements extension in your database:

SQL

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Connect to SmartSQL

Once your database is configured, connect it to SmartSQL:

1

Sign Up for SmartSQL

Create your SmartSQL account if you haven't already:

Join Waitlist

2

Add Database Configuration

In your SmartSQL dashboard, add a new database configuration using a PostgreSQL connection string:

Connection String Format

postgresql://smartsql_monitor:your_secure_password_here@your-database-host.com:5432/your_database_name

Replace the following values in the connection string:

Username

smartsql_monitor

Password

your_secure_password_here

Host

your-database-host.com

Port

5432

Database Name

your_database_name

Verify Setup

Confirm that SmartSQL is successfully monitoring your database:

✓

Connection Status

Check that your database shows as "Connected" in the SmartSQL dashboard.

✓

Query Collection

Verify that query statistics are being collected by running some test queries and checking the dashboard.

✓

Optimization Alerts

Configure notification preferences to receive alerts about optimization opportunities.

🎉

Setup Complete!

SmartSQL is now monitoring your PostgreSQL database and will begin identifying optimization opportunities. You'll receive notifications when performance improvements are available.

Join Waitlist

Optimization Types

SmartSQL analyzes your database queries and provides two main types of optimizations. Important: No optimizations are automatically applied to your database. You maintain full control over which optimizations to implement.

1. Query Re-writes

Our AI analyzes your SQL queries and suggests more efficient alternatives that produce the same results with better performance. These optimizations include:

Converting inefficient subqueries to JOINs
Optimizing complex WHERE clauses
Restructuring queries to use indexes more effectively
Eliminating redundant operations
Improving query execution plans

Example Query Re-write

Original Query

SELECT * FROM orders 
WHERE user_id IN (
  SELECT id FROM users 
  WHERE active = true
);

Optimized Query

SELECT o.* FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE u.active = true;

2. Suggested Indexes

SmartSQL identifies missing indexes that could significantly improve query performance. Our recommendations are based on:

Query patterns and frequency analysis
Column selectivity and cardinality
Join conditions and filtering criteria
Sort operations and grouping requirements
Existing index coverage analysis

Example Index Suggestion

Suggested Index

CREATE INDEX idx_orders_user_id_created_at 
ON orders(user_id, created_at) 
WHERE status = 'active';

This partial index optimizes queries filtering by user_id and date range for active orders.

Empirical Validation

SmartSQL runs EXPLAIN ANALYZE on your database to empirically determine if each optimization actually improves performance compared to the original query. You'll see concrete metrics showing:

Execution time improvements
Reduced resource usage
Better query plans
Lower I/O operations

⚠️

Full Control Over Implementation: SmartSQL never automatically applies optimizations to your database. Every suggested optimization requires your explicit review and approval. You can:

Review each optimization recommendation
Test suggestions in a staging environment
Apply optimizations selectively
Schedule implementations during maintenance windows

Need Help?

If you encounter any issues during setup, our support team is here to help:

📧

Email Support

Send us detailed questions about your setup

Contact Support

SmartSQL Documentation