PostgreSQL
PostgreSQL is a powerful, open-source relational database system. Aptrics supports PostgreSQL connections for building semantic layers on top of your PostgreSQL databases, enabling governed metrics and self-service analytics.
Whether you're using PostgreSQL on-premises or in the cloud (via AWS RDS, Azure Database, Google Cloud SQL, or other managed services), Aptrics can connect and build analytics on your data.
Connection Setup
Prerequisites
Ensure you have a PostgreSQL database with at least version 12. Have your database host, port (default 5432), database name, and administrative credentials ready. Verify that your database is accessible from Aptrics (network connectivity and firewall rules).
Create Database User
Create a dedicated PostgreSQL user for Aptrics with limited permissions. This user should have SELECT permissions on the schemas and tables Aptrics needs to access, but not permission to modify data or schema.
Grant Appropriate Permissions
Execute the necessary GRANT commands to give the Aptrics user read-only access to your schemas and tables. If you want Aptrics to auto-discover tables, grant USAGE on schemas and SELECT on information_schema.
Configure Aptrics Connector
In your Aptrics environment, add a new PostgreSQL connector. Enter your database host, port, database name, username, and password. Test the connection and verify that Aptrics can access your tables.
Advanced Configuration
SSL/TLS Connections
For enhanced security, configure SSL/TLS connections between Aptrics and PostgreSQL. Upload your certificate authority (CA) certificate in the connector settings to ensure encrypted communication.
Connection Pooling
Aptrics automatically manages connection pooling to PostgreSQL. Configure pool size based on your query volume and database configuration to optimize performance and resource usage.
Query Optimization
For optimal performance, create appropriate indexes on your tables for the dimensions and metrics you use frequently. Aptrics generates standard SQL queries that benefit from well-designed indexes.