Develop AI Solutions by Using Azure Database for PostgreSQL AI-200 Practice Questions (Page 9)

Questions 41–45

1. 41foundation · easy

   Which method retrieves all rows from the result of a SELECT query executed with a psycopg2 cursor?

   • Areturn_rows()
   • Bfetchall()
   • Cread_all()
   • Dget_results()
   Reveal Solution Discussion Select an answer first
2. 42application · medium

   An e-commerce platform uses Azure Database for PostgreSQL to store product catalog data. The most common query filters products by category and then sorts by price. The category column has low cardinality and the price column has high cardinality. Which indexing strategy will best optimize this query?

   • ACreate a GIN index on the category column.
   • BCreate a BRIN index on the price column only.
   • CCreate separate B-tree indexes on category and price.
   • DCreate a composite B-tree index on (category, price).
   Reveal Solution Discussion Select an answer first
4. 43application · medium

   A media company stores image embeddings in Azure Database for PostgreSQL using pgvector. Each image has a unique ID, a vector embedding, and metadata such as upload date and content type. The application needs to retrieve embeddings for a batch of image IDs and also perform similarity searches. Which table design is most efficient?

   • AUse a vector column for the embedding and store metadata in a separate table without any indexes.
   • BStore all image data, including the embedding, in a single JSONB column.
   • CCreate separate tables for embeddings and metadata, and join them on every query.
   • DCreate a table with columns: image_id (UUID primary key), embedding vector(512), upload_date date, content_type text, and create a B-tree index on image_id and an HNSW index on embedding.
   Reveal Solution Discussion Select an answer first
5. 44foundation · easy

   Which index type in PostgreSQL is most suitable for optimizing queries that search for exact matches on a text column?

   • AGIN index
   • BHash index
   • CB-tree index
   • DBRIN index
   Reveal Solution Discussion Select an answer first
6. 45application · medium

   A retail analytics team is building a Python service that reads real-time inventory data from Azure Database for PostgreSQL. The service runs in a container on Azure Container Instances and must minimize connection setup overhead while handling bursts of concurrent queries. Which approach should the team use?

   • AOpen a new connection for each query and close it immediately after the query completes.
   • BUse a connection pooler such as PgBouncer in transaction mode, and configure the SDK to reuse pooled connections.
   • CSet the SDK's connection timeout to 5 seconds and rely on automatic retries for all failed queries.
   • DUse a single persistent connection for the entire service lifetime without any pooling.
   Reveal Solution Discussion Select an answer first