Inspecting Table and Indexes in pgvector
Introduction: Why Inspect Table and Indexes?
Now that you have learned how to create and manage indexes in pgvector, it is important to know how to inspect your tables and indexes. This step is essential for understanding how much space your data and indexes are using and how effectively your indexes are being used by the database.
While you already know how to create and monitor the progress of index creation, inspecting the current state of your tables and indexes gives you a clear picture of your database’s health and performance. This knowledge will help you make better decisions when it comes to optimizing and scaling your vector search engine.
In this lesson, I will show you how to check the size of your tables and indexes, view index definitions, and analyze index usage statistics using simple SQL queries.
Checking Table and Index Size
After creating indexes, it is a good idea to check how much space your tables and their indexes are using. This helps you understand the storage impact of your data and the indexes you have added. In PostgreSQL, you can use the pg_total_relation_size function to get the total size of a table, including all its indexes and any associated data.
For example, to check the total size of the products table, you can run the following SQL command:
The pg_size_pretty function makes the output easier to read by converting the size into a human-friendly format, such as MB or GB. If you run this command, you might see output like:
This tells you that the products table, along with all its indexes, uses about 1208 kilobytes of storage. Knowing this information is useful when you are planning for scaling or when you want to see how much space your indexes are adding to your database.
Viewing Index Definitions
Once you know how much space your table and indexes are using, it is also helpful to see which indexes exist on your table and how they are defined. This can help you verify that the correct indexes are in place and understand their structure.
To view all indexes on the products table, you can use the following SQL query:
This command will return a list of all indexes on the products table, along with their definitions. For example, the output might look like this:
The first row, products_pkey, is the primary key index that PostgreSQL automatically creates for the product_id column. This index enforces uniqueness and provides efficient lookups for primary key queries. The other rows show the vector indexes you have created on the embedding column. By reviewing this output, you can confirm which indexes are present and see the exact SQL used to create them. This is especially useful if you are working in a team or if you need to document your database structure.
