PostgreSQL - Administration
Configuration, replication, indexing (B-tree, GIN, GiST, BRIN), VACUUM, EXPLAIN ANALYZE, extensions, backups
1Which PostgreSQL configuration file contains main server parameters like shared_buffers and work_mem?
Which PostgreSQL configuration file contains main server parameters like shared_buffers and work_mem?
Answer
The postgresql.conf file contains the main PostgreSQL server configuration parameters, including memory allocations (shared_buffers, work_mem), connection settings, logging, and performance tuning. This file is read at server startup and some parameters require a full restart to take effect.
2Which PostgreSQL parameter defines the amount of shared memory used for data caching?
Which PostgreSQL parameter defines the amount of shared memory used for data caching?
Answer
shared_buffers defines the amount of memory PostgreSQL uses for caching data. This parameter is crucial for performance as it determines the size of the buffer pool shared across all connections. The typical recommendation is to set it between 25% and 40% of total server RAM for a dedicated PostgreSQL server.
3What is the main difference between streaming replication and logical replication in PostgreSQL?
What is the main difference between streaming replication and logical replication in PostgreSQL?
Answer
Streaming replication transmits changes at the WAL (Write-Ahead Log) level in binary format, creating an exact copy of the primary database. Logical replication decodes changes into logical SQL operations, allowing selective replication of specific tables and data transformation during replication. Logical replication offers more flexibility but with slightly higher overhead.
Which PostgreSQL index type is most suitable for full-text search and JSONB columns?
In which case is the BRIN (Block Range Index) particularly effective?
+17 interview questions
Other Data Engineering interview topics
Linux & Shell - Fundamentals
Git & GitHub - Fundamentals
Advanced Python for Data Engineering
Docker - Fundamentals
Google Cloud Platform - Fundamentals
CI/CD and Code Quality
Docker Compose
FastAPI - Data APIs
Advanced SQL for Data Engineering
Data Lake - Architecture and Ingestion
BigQuery for Data Engineering
Data Modeling for Data Engineering
Fivetran & Airbyte - Data Ingestion
dbt - Fundamentals
Apache Airflow - Fundamentals
Kubernetes - Fundamentals
dbt - Advanced Features
ETL / ELT / ETLT Patterns
Apache Airflow - Advanced
Airflow + dbt - Pipeline Orchestration
PySpark - Large-Scale Processing
Google Pub/Sub - Data Streaming
Apache Beam & Dataflow
Kubernetes - Production and Scaling
Terraform - Infrastructure as Code
NoSQL Databases
Modern Data Architecture
Monitoring and Observability
IAM and Data Security
Master Data Engineering for your next interview
Access all questions, flashcards, technical tests, code review exercises and interview simulators.
Start for free