MCQs on Partitioning and Sharding | PostgreSQL Database
Dive into PostgreSQL Partitioning and Sharding to understand declarative partitioning, inheritance-based partitioning, sharding setup with FDWs, and techniques for partition maintenance and optimizing performance in large-scale databases.
Declarative Partitioning in PostgreSQL
- What is declarative partitioning in PostgreSQL?
- A) A way to manually partition tables using indexes
- B) A method of splitting tables into sub-tables based on specific rules
- C) A technique for distributing data across multiple servers
- D) A process for setting up foreign keys
- Which of the following statements is true about declarative partitioning?
- A) It allows you to define partitions based on columns with specific ranges.
- B) It is only available in PostgreSQL versions 9.4 and earlier.
- C) It requires the creation of multiple schemas.
- D) Partitioning is not supported in PostgreSQL.
- How do you create a partitioned table in PostgreSQL?
- A) Use the CREATE TABLE command with the PARTITION BY clause
- B) Use the CREATE PARTITION command
- C) Use the CREATE SCHEMA command
- D) Use the ALTER TABLE command
- Which of the following is a valid partitioning strategy in PostgreSQL?
- A) Partition by hash
- B) Partition by random number
- C) Partition by range
- D) Partition by text length
- What happens when a partitioned table is queried in PostgreSQL?
- A) All partitions are scanned.
- B) Only relevant partitions are queried, improving performance.
- C) No indexing is used.
- D) The query is ignored.
- Which of the following is a limitation of declarative partitioning in PostgreSQL?
- A) It cannot handle large data volumes.
- B) It only supports range partitioning.
- C) Foreign keys cannot reference partitioned tables.
- D) It does not allow indexes on partitions.
- In which scenario would you use declarative partitioning in PostgreSQL?
- A) When data can be split into separate ranges or lists
- B) When multiple servers are required
- C) When tables need to be stored in different schemas
- D) When performance tuning is not a priority
- What does the PARTITION BY RANGE clause in PostgreSQL define?
- A) The data type of the partition
- B) The method of indexing partitions
- C) The method for dividing data into ranges based on a column
- D) The storage location for partitioned tables
Inheritance-Based Partitioning
- What is inheritance-based partitioning in PostgreSQL?
- A) A method of table partitioning that uses child tables
- B) A way of partitioning tables based on inheritance hierarchy
- C) A feature to enable database inheritance with parent-child relationships
- D) A method of dividing data using inheritance patterns
- In inheritance-based partitioning, what role does the parent table serve?
- A) It holds actual data
- B) It is a template for the child tables
- C) It is only used for indexing
- D) It is the only table that is queried
- Which command is used to create a child table in inheritance-based partitioning?
- A) CREATE CHILD TABLE
- B) CREATE TABLE INHERITS
- C) CREATE PARTITION
- D) CREATE TABLE AS
- Which PostgreSQL version introduced inheritance-based partitioning?
- A) PostgreSQL 8.0
- B) PostgreSQL 9.0
- C) PostgreSQL 9.4
- D) PostgreSQL 10.0
- How do you enforce constraints across all child tables in inheritance-based partitioning?
- A) Use triggers on each child table
- B) Use the CHECK constraint on the parent table
- C) Constraints cannot be used in inheritance-based partitioning
- D) Use foreign keys only
- How does inheritance-based partitioning differ from declarative partitioning?
- A) It uses foreign keys in partitions
- B) It is based on inheritance instead of partitioning rules
- C) It automatically creates indexes on child tables
- D) It does not require partitioning keys
- Which is a disadvantage of inheritance-based partitioning?
- A) It does not allow child tables to have different structures
- B) It does not support joins across child tables
- C) It requires all partitions to be in the same database schema
- D) It can be slower due to lack of partition pruning
- In inheritance-based partitioning, how are queries handled across parent and child tables?
- A) Queries access only the parent table.
- B) Queries can automatically include child tables.
- C) Child tables must be queried separately.
- D) Parent tables cannot be queried directly.
Setting Up Sharding with FDWs
- What is sharding in PostgreSQL?
- A) Splitting large tables into smaller tables across different databases
- B) Storing all data in a single table
- C) Indexing tables for better query performance
- D) Creating partitions within the same database
- Which feature of PostgreSQL is used for sharding?
- A) Foreign Data Wrappers (FDWs)
- B) Table inheritance
- C) Query parallelism
- D) Table partitioning
- How do Foreign Data Wrappers (FDWs) help in sharding PostgreSQL databases?
- A) By partitioning data within a single database
- B) By connecting different PostgreSQL instances for distributing data
- C) By creating redundant copies of data
- D) By storing data in cloud-based storage systems
- Which FDW is typically used for sharding in PostgreSQL?
- A) postgres_fdw
- B) file_fdw
- C) mysql_fdw
- D) oracle_fdw
- What does the postgres_fdw extension allow you to do in PostgreSQL?
- A) Connect to other PostgreSQL databases for distributed queries
- B) Store data in foreign file systems
- C) Encrypt communication between databases
- D) Partition a table into multiple databases
- How does FDW sharding differ from traditional table partitioning?
- A) FDWs allow data to be split across multiple servers and databases
- B) FDWs create partitions based on column values
- C) FDWs do not require foreign keys
- D) FDWs only work with remote databases
- What is a major challenge of setting up sharding with FDWs?
- A) The need for manual data partitioning
- B) Synchronization of data across servers
- C) Limited support for multi-column partitioning
- D) Lack of indexing capabilities
- How does sharding improve PostgreSQL performance?
- A) By reducing data redundancy
- B) By distributing data across multiple servers
- C) By consolidating all queries in one server
- D) By simplifying complex query optimization
Partition Maintenance and Performance
- Which of the following is crucial for maintaining partition performance?
- A) Ensuring partitions are balanced in size
- B) Ensuring partitions are indexed properly
- C) Reducing the number of partitions
- D) All of the above
- What is partition pruning in PostgreSQL?
- A) Automatically splitting data into smaller partitions
- B) Eliminating unnecessary partitions during queries
- C) Merging smaller partitions into larger ones
- D) Automatically updating data in partitions
- How can the performance of partitioned tables be improved?
- A) By creating indexes on partitioned columns
- B) By using a single large partition for all data
- C) By disabling foreign keys in partitions
- D) By reducing the number of queries
- What is a common strategy for partition maintenance in PostgreSQL?
- A) Creating new partitions over time and dropping old ones
- B) Merging all partitions into one
- C) Regularly defragmenting the database
- D) Adding more indexes to every partition
- Which type of partitioning is most effective for time-series data?
- A) Range partitioning
- B) Hash partitioning
- C) List partitioning
- D) Composite partitioning
- What happens if a partition in PostgreSQL is not properly maintained?
- A) Query performance degrades significantly
- B) Data redundancy increases
- C) Tables become locked
- D) Foreign keys are lost
Answer Key
| Qno | Answer |
|---|---|
| 1 | B) A method of splitting tables into sub-tables based on specific rules |
| 2 | A) It allows you to define partitions based on columns with specific ranges. |
| 3 | A) Use the CREATE TABLE command with the PARTITION BY clause |
| 4 | C) Partition by range |
| 5 | B) Only relevant partitions are queried, improving performance. |
| 6 | C) Foreign keys cannot reference partitioned tables. |
| 7 | A) When data can be split into separate ranges or lists |
| 8 | C) The method for dividing data into ranges based on a column |
| 9 | A) A method of table partitioning that uses child tables |
| 10 | B) It is a template for the child tables |
| 11 | B) CREATE TABLE INHERITS |
| 12 | C) PostgreSQL 9.4 |
| 13 | B) Use the CHECK constraint on the parent table |
| 14 | B) It is based on inheritance instead of partitioning rules |
| 15 | D) It can be slower due to lack of partition pruning |
| 16 | B) Queries can automatically include child tables. |
| 17 | A) Splitting large tables into smaller tables across different databases |
| 18 | A) Foreign Data Wrappers (FDWs) |
| 19 | B) By connecting different PostgreSQL instances for distributing data |
| 20 | A) postgres_fdw |
| 21 | A) Connect to other PostgreSQL databases for distributed queries |
| 22 | A) FDWs allow data to be split across multiple servers and databases |
| 23 | B) Synchronization of data across servers |
| 24 | B) By distributing data across multiple servers |
| 25 | D) All of the above |
| 26 | B) Eliminating unnecessary partitions during queries |
| 27 | A) By creating indexes on partitioned columns |
| 28 | A) Creating new partitions over time and dropping old ones |
| 29 | A) Range partitioning |
| 30 | A) Query performance degrades significantly |