12 November 2020

#Postgresql

#PostgreSql

Key Concepts


Level Topic Subtopics
Basic Installation & Setup Download, initdb, configuration, pg_ctl, pgAdmin
Architecture Overview Processes (postmaster, bgwriter, wal writer), memory, WAL, storage engine
Data Types Numeric, Text, Boolean, Date/Time, UUID, Arrays
Schema & Tables CREATE, ALTER, DROP, constraints, tablespaces
Basic Queries SELECT, INSERT, UPDATE, DELETE
Constraints PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK
psql Client Meta commands (\d, \i, \dt), scripting
Import/Export COPY, pg_dump, pg_restore, \copy
Basic Functions String, Math, Aggregate, Conversion
Transactions BEGIN, COMMIT, ROLLBACK, savepoints
Intermediate Joins & Subqueries INNER, LEFT, RIGHT, FULL, Nested queries, CTEs
Indexing B-Tree, Hash, GIN, GiST, covering indexes
Views Simple Views, Materialized Views, refresh strategies
Triggers & Rules BEFORE/AFTER triggers, INSTEAD OF triggers, Rules
Functions & PL/pgSQL SQL functions, PL/pgSQL basics, error handling
Window Functions RANK, ROW_NUMBER, PARTITION BY, LEAD, LAG
Sequences & Identity SERIAL, BIGSERIAL, GENERATED ALWAYS, currval(), nextval()
JSON & JSONB Storage, operators, indexing (GIN), functions
Error Handling EXCEPTION blocks, raise notice/error/warning
Data Modeling Normalization, relationships, schema design
Advanced Query Optimization EXPLAIN, ANALYZE, query planner
Partitioning Range, List, Hash, default partitions
Table Inheritance Parent-child tables, constraints, performance
Locks & Concurrency Row locks, Deadlocks, MVCC
Vacuum & Analyze Autovacuum, statistics, bloat removal
Extensions PostGIS, pg_stat_statements, hstore, uuid-ossp
Full Text Search tsvector, tsquery, dictionaries, ranking
Parallel Query Parallel scan, joins, aggregate
Advanced Security Row-Level Security (RLS), Policies, GRANT/REVOKE
Large Objects BYTEA, TOAST, pg_largeobject API
Expert High Availability Streaming replication, Logical replication, failover
Backup & Recovery pg_dump, PITR, WAL archiving, pgBackRest, Barman
Sharding & Scaling Citus, FDWs, partitioned scaling
Advanced Indexing BRIN, Bloom, Expression indexes, Partial indexes
Monitoring & Tuning pg_stat_statements, logs, auto_explain, tuning params
System Catalogs pg_class, pg_attribute, pg_index, pg_roles
Advanced PL/pgSQL Packages, dynamic SQL, performance best practices
Cross-DB Integration dblink, postgres_fdw, foreign data wrappers
Migration Tools pg_upgrade, logical replication for migration, data migration strategies
Enterprise Deployment Kubernetes, cloud deployment, scaling strategies

Interview question

Basic Level

  1. What is PostgreSQL and how is it different from other RDBMS like MySQL or Oracle?
  2. What are the main features of PostgreSQL?
  3. Explain the architecture of PostgreSQL.
  4. How do you install PostgreSQL on Linux and Windows?
  5. What is psql in PostgreSQL?
  6. How do you create a new database in PostgreSQL?
  7. How do you list all databases in PostgreSQL?
  8. What are schemas in PostgreSQL?
  9. How do you connect to a PostgreSQL database using psql?
  10. Explain the difference between CHAR, VARCHAR, and TEXT in PostgreSQL.
  11. How do you create a table in PostgreSQL?
  12. What are the different data types available in PostgreSQL?
  13. How do you insert data into a PostgreSQL table?
  14. How do you update and delete data in PostgreSQL?
  15. What are sequences in PostgreSQL?
  16. How do you create a sequence in PostgreSQL?
  17. What is a primary key in PostgreSQL?
  18. How do you define a foreign key in PostgreSQL?
  19. What are indexes in PostgreSQL?
  20. How do you create an index in PostgreSQL?
  21. What is the difference between DELETE and TRUNCATE?
  22. What is the purpose of the RETURNING clause in PostgreSQL?
  23. How do you enable case-insensitive search in PostgreSQL?
  24. Explain the difference between NULL and an empty string.
  25. How do you backup and restore a PostgreSQL database?

Intermediate Level

  1. What are PostgreSQL system catalogs?
  2. How does PostgreSQL handle transactions?
  3. Explain the concept of MVCC (Multi-Version Concurrency Control).
  4. What is the difference between COMMIT and ROLLBACK?
  5. How do you implement foreign key constraints with cascading actions?
  6. What is a materialized view in PostgreSQL?
  7. How do you refresh a materialized view?
  8. Explain the difference between VIEW and MATERIALIZED VIEW.
  9. What are window functions in PostgreSQL?
  10. How do you use ROW_NUMBER(), RANK(), and DENSE_RANK()?
  11. What are PostgreSQL extensions? Give examples.
  12. What is the pgAdmin tool used for?
  13. How do you monitor queries in PostgreSQL?
  14. What is the EXPLAIN command used for?
  15. How do you optimize queries in PostgreSQL?
  16. Explain the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.
  17. How do you implement recursive queries in PostgreSQL?
  18. What is the difference between NOW() and CURRENT_DATE?
  19. Explain COALESCE() function in PostgreSQL.
  20. How do you use JSON and JSONB data types in PostgreSQL?
  21. How do you create and query an array column in PostgreSQL?
  22. What are PostgreSQL operators for pattern matching?
  23. Explain IS DISTINCT FROM operator in PostgreSQL.
  24. How do you grant and revoke privileges in PostgreSQL?
  25. What is the role of pg_hba.conf?

Advanced Level

  1. Explain Write-Ahead Logging (WAL) in PostgreSQL.
  2. How does PostgreSQL ensure data consistency?
  3. What are the different types of indexes in PostgreSQL?
  4. When should you use GIN vs BTREE indexes?
  5. Explain Partial Indexes in PostgreSQL.
  6. What is a covering index in PostgreSQL?
  7. How do you implement full-text search in PostgreSQL?
  8. Explain the difference between TO_CHAR() and TO_DATE().
  9. How does PostgreSQL handle concurrency and locking?
  10. What are advisory locks in PostgreSQL?
  11. How does VACUUM work in PostgreSQL?
  12. What is the difference between VACUUM and VACUUM FULL?
  13. What is ANALYZE used for?
  14. How does PostgreSQL query planner work?
  15. What are parallel queries in PostgreSQL?
  16. Explain logical vs physical replication.
  17. How do you configure replication in PostgreSQL?
  18. What are hot standby servers in PostgreSQL?
  19. Explain Point-In-Time Recovery (PITR) in PostgreSQL.
  20. How do you implement partitioning in PostgreSQL?
  21. Explain the difference between range and list partitioning.
  22. What is a foreign data wrapper (FDW)?
  23. How do you connect PostgreSQL with other databases using FDW?
  24. What are stored procedures in PostgreSQL?
  25. How do you write PL/pgSQL functions?

Expert Level

  1. Explain PostgreSQL?s process architecture (postmaster, background workers, autovacuum).
  2. How do you handle deadlocks in PostgreSQL?
  3. What strategies can be used for PostgreSQL performance tuning?
  4. How do you tune work_mem, shared_buffers, and effective_cache_size?
  5. What are PostgreSQL tablespaces?
  6. How do you create and use a tablespace?
  7. What is sharding in PostgreSQL? How can it be implemented?
  8. How does PostgreSQL differ from distributed databases like CockroachDB or Citus?
  9. What is the difference between synchronous and asynchronous replication?
  10. How do you set up synchronous replication?
  11. How do you monitor replication lag in PostgreSQL?
  12. How do you implement high availability (HA) in PostgreSQL?
  13. Explain connection pooling in PostgreSQL.
  14. What is PgBouncer and how is it used?
  15. How do you implement partition pruning in PostgreSQL?
  16. Explain JIT (Just-In-Time) compilation in PostgreSQL.
  17. How do you debug performance issues in PostgreSQL queries?
  18. What are generated columns in PostgreSQL?
  19. How do you implement Row-Level Security (RLS)?
  20. How do you use event triggers in PostgreSQL?
  21. Explain logical decoding in PostgreSQL.
  22. What is WAL archiving and how is it configured?
  23. How does PostgreSQL handle large objects (LOBs)?
  24. What are common PostgreSQL anti-patterns to avoid?
  25. Compare PostgreSQL with NoSQL databases in terms of scalability and flexibility.

Related Topics


-
Labels: , ,