PostgreSQL Online Test

1. What is SSL/TLS encryption used for in PostgreSQL security?

To encrypt data transmission between the client and server To encrypt data at rest To encrypt user passwords To create user accounts

2. What is the "hstore" extension used for in PostgreSQL's NoSQL support?

To provide key-value storage for semi-structured data To store binary data To manage database indexes To create new databases

3. Which PostgreSQL command is used to back up a database cluster, including all databases and configuration files?

pg_basebackup pg_dump pg_copy pg_restore

4. What is "slowly changing dimension" (SCD) in data warehousing with PostgreSQL?

It refers to handling changes in data over time, especially in dimension tables It is used to create new databases It optimizes query performance It enforces strong password requirements

5. Which PostgreSQL configuration parameter allows restricting the IP addresses from which the database server can accept connections?

"listen_addresses" "shared_buffers" "max_connections" "random_page_cost"

6. What is the purpose of "ANALYZE" in PostgreSQL's VACUUM command?

To update statistics about the table for query planning To physically reorganize the table data To delete old rows from the table To lock the table for exclusive access

7. What is the purpose of the "uuid-ossp" extension in PostgreSQL?

To generate universally unique identifiers (UUIDs) To create user accounts To optimize query performance To manage access control

8. What is the default port for PostgreSQL?

5432 3306 1521 27017

9. In PostgreSQL, what does the "OVER" clause do in the context of window functions?

Defines the window frame for the function Specifies the window size Orders the result set Groups the rows by a specific column

10. In PostgreSQL, what is the purpose of a foreign key constraint in a table?

To ensure referential integrity between tables To create an auto-incrementing column To enforce primary key constraints To define default values for columns

11. What is an ERD (Entity-Relationship Diagram) used for in database design?

To visually represent the structure and relationships of database entities To generate SQL code for table creation To store backups of database schemas To define user access permissions

12. What is the primary goal of high availability in a PostgreSQL database system?

To minimize downtime and ensure database availability To maximize data storage capacity To improve query performance To reduce the number of database connections

13. Which data type is used to store a time of day in PostgreSQL?

TIME DATETIME TIMESTAMP CLOCK

14. What is the purpose of PostgreSQL's "GRANT" and "REVOKE" statements in the context of security?

To assign or remove privileges on database objects to/from roles To create new tables To modify user passwords To log user activity

15. What is the purpose of "connection pooling" in PostgreSQL for managing large datasets?

To efficiently manage and reuse database connections, reducing overhead To increase the complexity of SQL queries To encrypt data during transmission To create new databases

16. In PostgreSQL's geospatial data management, what is "SRID" an acronym for?

Spatial Reference IDentifier Systematic Resource Identifier Server-Request ID Secure Repository ID

17. Which PostgreSQL authentication method allows access without a password but is restricted to local connections on Unix-like systems?

peer md5 trust scram-sha-256

18. What is the purpose of an index in PostgreSQL database design?

To speed up data retrieval by providing quick access to specific columns To store backup copies of data To define constraints for data integrity To define relationships between tables

19. What happens when a client application connects to a PostgreSQL database using connection pooling?

The connection is borrowed from the pool, and after use, it is returned to the pool for reuse The connection is permanently established and remains open The connection is encrypted for added security The connection is cached for faster access

20. What is the "pg_stat_statements" module used for in PostgreSQL tools and clients?

To track and log executed SQL statements for analysis and query optimization To encrypt data during transmission To create new databases To manage user roles