Hello PostgreSQL Community,
I would like to introduce a PostgreSQL extension called Pg_dbscanner.
Pg_dbscanner analyzes table schemas and existing indexes and provides
recommendations for suitable index types based on column data types and
other available metadata.
1. What Problem It Solves
Choosing the right index type in PostgreSQL can require knowledge of
different index access methods and an understanding of the data stored in
the columns.
For example, a table may contain:
-
Text columns
-
Integer columns
-
Date or timestamp columns
-
Large tables with naturally ordered data
Users may not always know which index type would be most suitable or
whether an existing index matches the expected use case.
Pg_dbscanner aims to provide these recommendations directly from PostgreSQL.
2. What This Extension Does
-
Inspects the schema of a user-defined table
-
Examines column data types
-
Detects existing indexes
-
Recommends potentially suitable index types
-
Compares existing indexes with the recommendations
-
Reports whether the current index matches the suggestion
-
Provides the results at query time without modifying the schema
3. Key Features
-
No schema changes required
-
Works with existing user-defined tables
-
Provides a quick index audit
-
Identifies missing or potentially inefficient indexes
-
Recommends index types based on a heuristic ruleset
-
Implemented as a native PostgreSQL C extension
-
Advisory only — it does not automatically create or modify indexes
For example, based on the column characteristics, the extension can suggest
index types such as GIN for suitable text/search workloads or BRIN for
large, naturally ordered date/timestamp columns.
4. Requirements
-
PostgreSQL 17.0
-
PostgreSQL development/build environment
-
C compiler and standard PostgreSQL extension build tools
5. Example Usage
The extension provides a function:
suggest_indexes(table_name)
For example:
SELECT suggest_indexes('table_one');
The result provides information such as:
-
Column name
-
Data type
-
Existing index type
-
Recommended index type
-
Whether the existing index matches the recommendation
This allows users to quickly review the indexing strategy of a table
without manually inspecting PostgreSQL system catalogs.
6. Implementation
Pg_dbscanner is implemented in C and integrates directly with PostgreSQL.
It uses PostgreSQL catalog information such as pg_index, pg_class, and
pg_attribute to inspect table and index metadata and apply a
heuristic-based recommendation system.
The extension only provides recommendations. It does not automatically
create indexes or change the user's schema.
7. Feedback Requested
I would appreciate feedback from the PostgreSQL community on:
-
The overall extension design
-
The index recommendation approach
-
The heuristic rules used for different data types
-
Performance considerations
-
Additional index types or use cases that should be supported
-
Possible improvements for making the extension more useful and
production-ready
I’m sharing Pg_dbscanner as a starting point for discussion and would
welcome suggestions and feedback from the community.
Thank you!
Regards,
Lakshmi
