How to enable pg_stat_statements for Ecto PSQL Extras?

You need to install and enable the pg_stat_statements extension in your PostgreSQL database. Check if it's enabled by running EctoPSQLExtras.query(:extensions, YourApp.Repo), as shown in the README. This involves database-level configuration, which may require superuser privileges.

Ecto PSQL Extras vs Heroku PG Extras: which should I use?

Ecto PSQL Extras is the Elixir-specific port, ideal for Elixir apps without Heroku dependency, offering native API integration. Heroku PG Extras is tied to Heroku's platform and lacks direct Elixir support, so choose based on your stack and deployment environment.

How to integrate Ecto PSQL Extras with Phoenix LiveDashboard?

Add it as a dependency and configure Phoenix.LiveDashboard to display SQL metrics; it's an optional dependency, so follow the LiveDashboard setup. The README includes an example image of the diagnose report in the dashboard UI.

What are the most useful Ecto PSQL Extras queries for fixing slow databases?

Start with missing_fk_indexes to find unindexed foreign keys, cache_hit to check buffer efficiency, outliers to identify slow queries, and bloat for table maintenance. The diagnose report combines these for a holistic view.

Can Ecto PSQL Extras be used safely in production?

Yes, but use caution: most queries are read-only, but operations like kill_all can disrupt services. Ensure proper database permissions and avoid running destructive commands during peak hours without testing.

How to run Ecto PSQL Extras queries from a custom Elixir script?

Call the API functions programmatically, e.g., EctoPSQLExtras.cache_hit(Repo, format: :raw) to get raw Postgrex.Result data for processing. This allows integration into tasks, cron jobs, or custom dashboards.

Does Ecto PSQL Extras work with older PostgreSQL versions?

It's tested with PostgreSQL 11 through 15, as per the development section, but some queries might rely on newer features. Verify compatibility for your version, especially if using advanced extensions or statistics.

Open-Awesome

ecto_psql_extras

MITElixir

Elixir library providing PostgreSQL database performance insights like locks, index usage, cache hit ratios, and vacuum stats.

GitHub

395 stars36 forks0 contributors

What is ecto_psql_extras?

Ecto PSQL Extras is an Elixir library that provides a comprehensive set of queries to analyze and optimize PostgreSQL database performance. It helps developers identify issues like missing indexes, lock contention, inefficient cache usage, and bloat, offering insights similar to Heroku PG Extras but for any PostgreSQL-backed Elixir application.

Target Audience

Elixir developers and DevOps engineers working with PostgreSQL databases who need to monitor, diagnose, and optimize database performance in Phoenix or Ecto-based applications.

Value Proposition

It offers a native Elixir API for deep PostgreSQL introspection without relying on Heroku's tooling, integrates seamlessly with Phoenix.LiveDashboard, and includes a diagnostic report to quickly surface performance bottlenecks.

Overview

Ecto PostgreSQL database performance insights. Locks, index usage, buffer cache hit ratios, vacuum stats and more.

Use Cases

Best For

Identifying missing foreign key indexes and constraints in PostgreSQL schemas
Analyzing buffer cache hit ratios to determine if a database instance is properly sized
Detecting and removing unused or duplicate indexes to improve write performance
Monitoring long-running queries and lock contention in production databases
Generating a comprehensive diagnose report for PostgreSQL performance health checks
Integrating PostgreSQL metrics into a Phoenix.LiveDashboard for real-time monitoring

Not Ideal For

Teams using databases other than PostgreSQL (e.g., MySQL, MongoDB)
Environments requiring graphical user interfaces without code integration, such as DBAs preferring tools like pgAdmin
Projects with minimal database interactions where performance tuning is low-priority
Applications with strict security policies that restrict ad-hoc SQL query execution due to data exposure risks

Pros & Cons

Pros

Comprehensive Query Suite

Includes over 20 specialized queries ported from Heroku PG Extras, covering cache hit ratios, index usage, locks, and vacuum statistics, as detailed in the README with examples like cache_hit and outliers.

Seamless Elixir Integration

Offers a clean, idiomatic API that works directly with Ecto repositories, shown in usage examples like EctoPSQLExtras.cache_hit(YourApp.Repo), making it easy to embed in Phoenix apps or monitoring tasks.

Diagnostic Report Feature

The diagnose method runs a set of checks and prints a report highlighting areas needing investigation, providing a quick start for performance analysis without manual query execution.

Phoenix LiveDashboard Compatibility

Is an optional dependency for Phoenix.LiveDashboard, allowing SQL metrics to be displayed in a web UI, as mentioned in the README with an example image.

Cons

Dependency on pg_stat_statements

Key queries like calls and outliers require the pg_stat_statements extension to be enabled, adding setup overhead and potential configuration issues, as admitted in the README installation notes.

Limited Output Formats by Default

Default ASCII table output may not suit automated systems; raw data requires explicit format options (e.g., format: :raw), complicating integration into scripts or dashboards without extra code.

Potentially Dangerous Operations

Includes commands like kill_all that can terminate all database connections, posing risks in production if misused without proper safeguards or oversight.

No Built-in Automation

Lacks features for scheduled queries, alerting, or historical tracking, requiring additional tooling for proactive performance monitoring and trend analysis.

Open Source Alternative To

ecto_psql_extras is an open-source alternative to the following products:

Heroku pg_extras

A Heroku Postgres extension that provides additional diagnostic and maintenance commands for database management.

Frequently Asked Questions

Related Projects

ecto

A toolkit for data mapping and language integrated query.

PostgreSQL driver for Elixir

Event store using PostgreSQL for persistence

Stars1,162

Forks155

Last commit4 months ago

redix

Fast, pipelined, resilient Redis driver for Elixir. 🛍

Stars1,140

Forks125

Last commit15 hours ago

Community-curated · Updated weekly · 100% open source

Found a gem we're missing?

Open-Awesome is built by the community, for the community. Submit a project, suggest an awesome list, or help improve the catalog on GitHub.

Submit a project Star on GitHub