# PostgreSQL - Administration (Data Engineering)

> Configuration, replication, indexing (B-tree, GIN, GiST, BRIN), VACUUM, EXPLAIN ANALYZE, extensions, backups

- 20 interview questions
- Mid-Level
- [Interview Questions: Data Engineering](https://sharpskill.dev/en/technologies/data-engineering/interview-questions.md)

## 1. 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.

## 2. 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.

## 3. 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.

## 17 more questions available

- 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?

Sign up for free: https://sharpskill.dev/en/login

## Other Data Engineering interview topics

- [Linux & Shell - Fundamentals](https://sharpskill.dev/en/technologies/data-engineering/interview-questions/linux-shell-basics.md): 20 questions, Junior
- [Git & GitHub - Fundamentals](https://sharpskill.dev/en/technologies/data-engineering/interview-questions/git-github-fundamentals.md): 20 questions, Junior
- [Advanced Python for Data Engineering](https://sharpskill.dev/en/technologies/data-engineering/interview-questions/python-advanced-de.md): 25 questions, Junior
- [Docker - Fundamentals](https://sharpskill.dev/en/technologies/data-engineering/interview-questions/docker-fundamentals.md): 25 questions, Junior
- [Google Cloud Platform - Fundamentals](https://sharpskill.dev/en/technologies/data-engineering/interview-questions/gcp-fundamentals.md): 20 questions, Junior
- [CI/CD and Code Quality](https://sharpskill.dev/en/technologies/data-engineering/interview-questions/ci-cd-code-quality.md): 20 questions, Mid-Level
- [Docker Compose](https://sharpskill.dev/en/technologies/data-engineering/interview-questions/docker-compose.md): 20 questions, Mid-Level
- [FastAPI - Data APIs](https://sharpskill.dev/en/technologies/data-engineering/interview-questions/fastapi.md): 20 questions, Mid-Level
- [Advanced SQL for Data Engineering](https://sharpskill.dev/en/technologies/data-engineering/interview-questions/sql-advanced-de.md): 20 questions, Mid-Level
- [Data Lake - Architecture and Ingestion](https://sharpskill.dev/en/technologies/data-engineering/interview-questions/data-lake.md): 20 questions, Mid-Level
- [BigQuery for Data Engineering](https://sharpskill.dev/en/technologies/data-engineering/interview-questions/bigquery-de.md): 20 questions, Mid-Level
- [Data Modeling for Data Engineering](https://sharpskill.dev/en/technologies/data-engineering/interview-questions/data-modeling-de.md): 20 questions, Mid-Level
- [Fivetran & Airbyte - Data Ingestion](https://sharpskill.dev/en/technologies/data-engineering/interview-questions/fivetran-airbyte.md): 20 questions, Mid-Level
- [dbt - Fundamentals](https://sharpskill.dev/en/technologies/data-engineering/interview-questions/dbt-fundamentals-de.md): 20 questions, Mid-Level
- [Apache Airflow - Fundamentals](https://sharpskill.dev/en/technologies/data-engineering/interview-questions/airflow-fundamentals.md): 20 questions, Mid-Level
- [Kubernetes - Fundamentals](https://sharpskill.dev/en/technologies/data-engineering/interview-questions/kubernetes-fundamentals.md): 20 questions, Mid-Level
- [dbt - Advanced Features](https://sharpskill.dev/en/technologies/data-engineering/interview-questions/dbt-advanced-de.md): 20 questions, Senior
- [ETL / ELT / ETLT Patterns](https://sharpskill.dev/en/technologies/data-engineering/interview-questions/etl-elt-patterns.md): 20 questions, Senior
- [Apache Airflow - Advanced](https://sharpskill.dev/en/technologies/data-engineering/interview-questions/airflow-advanced.md): 20 questions, Senior
- [Airflow + dbt - Pipeline Orchestration](https://sharpskill.dev/en/technologies/data-engineering/interview-questions/airflow-dbt-integration.md): 20 questions, Senior
- [PySpark - Large-Scale Processing](https://sharpskill.dev/en/technologies/data-engineering/interview-questions/pyspark.md): 20 questions, Senior
- [Google Pub/Sub - Data Streaming](https://sharpskill.dev/en/technologies/data-engineering/interview-questions/pubsub-streaming.md): 20 questions, Senior
- [Apache Beam & Dataflow](https://sharpskill.dev/en/technologies/data-engineering/interview-questions/apache-beam-dataflow.md): 20 questions, Senior
- [Kubernetes - Production and Scaling](https://sharpskill.dev/en/technologies/data-engineering/interview-questions/kubernetes-advanced.md): 20 questions, Senior
- [Terraform - Infrastructure as Code](https://sharpskill.dev/en/technologies/data-engineering/interview-questions/terraform.md): 20 questions, Senior
- [NoSQL Databases](https://sharpskill.dev/en/technologies/data-engineering/interview-questions/nosql-databases.md): 20 questions, Senior
- [Modern Data Architecture](https://sharpskill.dev/en/technologies/data-engineering/interview-questions/data-architecture.md): 20 questions, Senior
- [Monitoring and Observability](https://sharpskill.dev/en/technologies/data-engineering/interview-questions/monitoring-observability.md): 20 questions, Senior
- [IAM and Data Security](https://sharpskill.dev/en/technologies/data-engineering/interview-questions/iam-security-de.md): 20 questions, Senior

---

Source: SharpSkill (https://sharpskill.dev), tech interview preparation for your real stack.
HTML version of this page: https://sharpskill.dev/en/technologies/data-engineering/interview-questions/postgresql-admin