What is PostgreSQL?
PostgreSQL is an open-source, object-relational database known for reliability, extensibility, and SQL standards compliance.
2. Is PostgreSQL relational or NoSQL?
PostgreSQL is primarily a relational database but supports NoSQL features such as JSON/JSONB, arrays, and key-value storage.
3. What are the key features of PostgreSQL?
ACID compliance, MVCC, extensibility, rich indexing, JSON support, full-text search, and high concurrency.
4. What is MVCC?
Multi-Version Concurrency Control allows concurrent reads and writes without locking by maintaining multiple row versions.
5. Default PostgreSQL port?
6. What is a schema?
A logical namespace to organize database objects like tables, views, and functions.
7. Database vs schema?
A database contains schemas; schemas contain objects.
8. What is a tablespace?
Defines the physical disk location where database objects are stored.
9. What are PostgreSQL extensions?
Extensions add features to PostgreSQL, such as postgis, uuid-ossp, and pg_stat_statements.
10. What is JSONB?
A binary JSON format that supports indexing and faster querying than JSON.
11. JSON vs JSONB?
JSON stores text; JSONB stores binary and supports indexing.
12. What is VACUUM?
Reclaims space occupied by dead tuples created due to updates and deletes.
13. What is AUTOVACUUM?
Background process that automatically runs VACUUM and ANALYZE.
14. What is ANALYZE?
Collects table statistics used by the query planner.
15. What are dead tuples?
Old row versions left behind due to MVCC.
16. What is WAL?
Write-Ahead Logging ensures durability by logging changes before writing data files.
17. What is a checkpoint?
Writes dirty pages from memory to disk to limit WAL replay time.
18. What is replication?
Copying data from a primary server to one or more replicas.
19. Types of replication?
Streaming replication and logical replication.
20. What is streaming replication?
Continuously streams WAL records from primary to standby servers.
21. What is logical replication?
Replicates data at table level, allowing selective replication.
22. What is a primary key?
A unique, non-null identifier for table rows.
23. What is a foreign key?
A constraint that enforces referential integrity between tables.
24. What are constraints?
Rules such as NOT NULL, UNIQUE, CHECK, PRIMARY KEY, and FOREIGN KEY.
25. What is an index?
A data structure that improves query performance by reducing scans.
26. Types of indexes?
B-tree, Hash, GIN, GiST, BRIN, SP-GiST.
27. Default index type?
B-tree.
28. When to use GIN index?
For JSONB, arrays, and full-text search.
29. When to use BRIN index?
For very large tables with naturally ordered data.
30. What is EXPLAIN?
Displays the query execution plan.
31. What is EXPLAIN ANALYZE?
Executes the query and shows actual runtime statistics.
32. What is a CTE?
A Common Table Expression defined using the WITH clause.
33. Are CTEs always materialized?
Before PostgreSQL 12 yes; from PostgreSQL 12 onwards, they may be inlined unless forced.
34. What is a transaction?
A group of SQL operations executed as a single unit.
35. Transaction properties?
ACID: Atomicity, Consistency, Isolation, Durability.
36. Isolation levels supported?
Read Committed, Repeatable Read, Serializable.
37. Does PostgreSQL allow dirty reads?
No.
38. What is SERIAL?
A pseudo-type that creates an auto-incrementing integer.
39. What replaced SERIAL?
GENERATED AS IDENTITY.
40. What is a sequence?
A database object that generates unique numeric values.
41. What is UPSERT?
Insert or update using ON CONFLICT.
42. What is ON CONFLICT?
Handles unique constraint violations during INSERT.
43. What is partitioning?
Splitting a table into smaller, manageable pieces.
44. Partitioning types?
Range, List, Hash.
45. What is a view?
A virtual table defined by a query.
46. What is a materialized view?
A view that stores query results physically.
47. How to refresh a materialized view?
Using REFRESH MATERIALIZED VIEW.
48. What is full-text search?
Text searching using tsvector and tsquery.
49. What is TOAST?
Mechanism to store large column values out of line.
50. What is pg_stat_activity?
A system view showing active database connections.
51. How to find long-running queries?
Query pg_stat_activity.
52. What is connection pooling?
Reusing database connections to reduce overhead.
53. Popular PostgreSQL poolers?
PgBouncer and PgPool-II.
54. What is work_mem?
Memory per query used for sorting and hashing.
55. What is shared_buffers?
Memory for caching data pages.
56. What is effective_cache_size?
Planner hint for available OS cache.
57. What is a role?
An entity that can own objects and have privileges.
58. How is authentication configured?
Using pg_hba.conf.
59. What is pg_hba.conf?
Controls client authentication rules.
60. What is SSL in PostgreSQL?
Encrypts client-server communication.
61. How to back up PostgreSQL?
Using pg_dump or pg_basebackup.
62. pg_dump vs pg_dumpall?
pg_dump backs up one DB; pg_dumpall backs up all DBs and roles.
63. What is PITR?
Point-in-Time Recovery using WAL logs.
64. What is a deadlock?
Two transactions waiting on each other indefinitely.
65. How are deadlocks handled?
PostgreSQL detects and aborts one transaction.
66. What is an aggregate function?
Functions like SUM, COUNT, AVG.
67. What is a window function?
Performs calculations across related rows without collapsing them.
68. Example of window function?
ROW_NUMBER with OVER clause.
69. WHERE vs HAVING?
WHERE filters rows; HAVING filters grouped results.
70. What is NULL?
Represents unknown or missing value.
71. Boolean values in PostgreSQL?
true or false.
72. What is RETURNING clause?
Returns affected rows from INSERT, UPDATE, or DELETE.
73. What is logical decoding?
Extracting logical changes from WAL.
74. What is pg_stat_statements?
Tracks query execution statistics.
75. What is search_path?
Defines schema lookup order.
76. What is a temporary table?
A table visible only within a session.
77. What is an UNLOGGED table?
Faster writes but not crash-safe.
78. What is COPY?
High-performance bulk data import/export command.
79. What is psql?
PostgreSQL interactive command-line tool.
80. Command to list databases?
\l
81. Command to list tables?
\dt
82. What is HOT update?
An update that avoids index changes.
83. What is a composite index?
An index on multiple columns.
84. What is index-only scan?
Query satisfied entirely from index data.
85. What is CLUSTER?
Physically reorders table data based on an index.
86. What is a trigger?
A function executed automatically on data changes.
87. What languages can functions use?
PL/pgSQL, SQL, Python, and others.
88. What is PL/pgSQL?
Procedural language for PostgreSQL functions.
89. What is a cursor?
Allows row-by-row query processing.
90. What is LISTEN/NOTIFY?
Asynchronous notification system.
91. What is DISTINCT ON?
PostgreSQL-specific way to pick first row per group.
92. What is ILIKE?
Case-insensitive pattern matching.
93. What is a lateral join?
Allows subqueries to reference outer query columns.
94. What is pg_repack?
Rebuilds tables and indexes without downtime.
95. What is pg_upgrade?
Upgrades PostgreSQL clusters quickly.
96. Does PostgreSQL support sharding?
Not natively; achieved via extensions or external tools.
97. Is PostgreSQL horizontally scalable?
Reads scale via replicas; writes require sharding.
98. What is a superuser?
A role with unrestricted database access.
99. Why is PostgreSQL preferred?
Stability, performance, extensibility, and strong SQL compliance.
100. PostgreSQL vs MySQL?
PostgreSQL offers richer features, stricter standards, and better concurrency