Inspecting Table and Indexes in pgvector

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.

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:

SELECT pg_size_pretty(pg_total_relation_size('products'));

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:

 pg_size_pretty 
----------------
 1208 kB
(1 row)

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.

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:

SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'products';

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:

         indexname          |                                                indexdef                                                 
----------------------------+---------------------------------------------------------------------------------------------------------
 products_pkey              | CREATE UNIQUE INDEX products_pkey ON public.products USING btree (product_id)
 ivfflat_products_embedding | CREATE INDEX ivfflat_products_embedding ON public.products USING ivfflat (embedding) WITH (lists='100')
 hnsw_products_embedding    | CREATE INDEX hnsw_products_embedding ON public.products USING hnsw (embedding vector_cosine_ops)
(3 rows)

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.

Knowing which indexes exist is only part of the picture. It is also important to understand how often your indexes are being used. PostgreSQL keeps track of index usage statistics, which can help you identify indexes that are heavily used as well as those that are rarely or never used. This information is valuable when deciding whether to keep, modify, or remove certain indexes.

To check index usage statistics for the products table, you can run the following SQL query:

SELECT * FROM pg_stat_user_indexes WHERE relname = 'products';

This will return a table with statistics for each index, including how many times each index has been scanned. You might see output similar to the following:

 relid | indexrelid | schemaname | relname  |        indexrelname        | idx_scan |         last_idx_scan         | idx_tup_read | idx_tup_fetch 
-------+------------+------------+----------+----------------------------+----------+-------------------------------+--------------+---------------
 16786 |      16793 | public     | products | products_pkey              |     3000 | 2025-06-07 17:59:48.867154+00 |         3294 |          3000
 16786 |      16838 | public     | products | ivfflat_products_embedding |        0 |                               |            0 |             0
 16786 |      16839 | public     | products | hnsw_products_embedding    |        0 |                               |            0 |             0
(3 rows)

The idx_scan column shows how many times each index has been used in queries. The last_idx_scan column shows the timestamp of the last time the index was used, if it has ever been used. In this example, the products_pkey index has been used 3,000 times, with the last usage on 2025-06-07. The vector indexes have not been used yet (idx_scan is 0), so their last_idx_scan values are empty. If you see that an index has a very low or zero value for idx_scan, it might not be needed, or it may not be used as expected. On the other hand, a high value indicates that the index is actively helping your queries. This kind of inspection is a key part of ongoing database optimization.

In this lesson, you learned how to inspect your tables and indexes in PostgreSQL. You now know how to check the total size of a table and its indexes, view the definitions of all indexes on a table, and analyze how often each index is used. These skills are essential for understanding the impact of your indexing choices and for making informed decisions about optimization and scaling.

In the next set of practice exercises, you will get hands-on experience running these inspection queries yourself. This will help you become more comfortable with monitoring and managing your vector search database as it grows.