> For the complete documentation index, see [llms.txt](https://www.anandisheladiya.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://www.anandisheladiya.com/skills-and-experience/database/postgresql.md).

# PostgreSQL

**What is PostgreSQL?**

PostgreSQL is a powerful, open-source, object-relational database management system (ORDBMS) known for its reliability, feature richness, and extensibility. It is widely used in both small and large-scale applications, providing ACID (Atomicity, Consistency, Isolation, Durability) compliance, advanced indexing, and support for structured and unstructured data.

### **Key Features of PostgreSQL**

**1. ACID Compliance**

Ensures reliable transactions, maintaining data integrity even during failures.

**2. Extensibility**

* Supports custom data types, operators, and functions.
* Extensions like PostGIS for geospatial data.

**3. Advanced Indexing**

* B-Trees, Hash, GIN (Generalized Inverted Index), GiST (Generalized Search Tree).
* Improves performance for searching and filtering.

**4. JSON & NoSQL Support**

* Supports **JSONB** format for efficient NoSQL-like storage.
* Hybrid approach for relational and document-based data.

**5. Full-Text Search**

* Enables advanced searching capabilities with ranking and filtering.

**6. High Availability & Scalability**

* **Replication**: Streaming and logical replication.
* **Partitioning**: Efficient data sharding for handling large datasets.

**7. Stored Procedures & Functions**

* Supports procedural languages like PL/pgSQL, PL/Python, and PL/Perl.
* Automates complex logic directly in the database.

**8. Security Features**

* Role-based access control (RBAC).
* Data encryption (SSL/TLS) and row-level security.

### **PostgreSQL Architecture Overview**

A **typical PostgreSQL system architecture** consists of:

1. **Client Layer**: Applications (web, mobile, or desktop) that interact with the database via APIs or SQL queries.
2. **Query Processing Engine**: Parses, optimizes, and executes SQL queries.
3. **Storage Manager**: Manages data storage, indexing, and transactions.
4. **Write-Ahead Logging (WAL)**: Ensures durability by logging changes before applying them.
5. **Background Processes**: Handles vacuuming, checkpoints, and replication

<figure><img src="/files/DzRompJ0Qql8NYuBfvPx" alt=""><figcaption></figcaption></figure>

### **Use Cases of PostgreSQL**

* **Web Applications** – Used by companies like Instagram, Reddit, and Spotify.
* **Data Warehousing** – Handles large-scale analytics and reporting.
* **Geospatial Applications** – GIS systems with PostGIS extension.
* **Finance & Banking** – Ensures transactional integrity and security.

### **Creating a User Table in PostgreSQL**

To define a **user table**, you can use the following SQL statement:

```sql
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

```

#### **Inserting Data**

To insert a user into the table:

```sql
INSERT INTO users (username, email, password)
VALUES ('john_doe', 'john@example.com', crypt('securepassword', gen_salt('bf')));

```

#### **Retrieving Users**

To fetch all users:

```sql
SELECT * FROM users;

```

#### **Updating a User**

To update a user's email:

```sql
UPDATE users
SET email = 'newemail@example.com', updated_at = NOW()
WHERE username = 'john_doe';

```

#### **Deleting a User**

To delete a user:

```sql
DELETE FROM users WHERE username = 'john_doe';

```

### **Conclusion**

PostgreSQL is a **versatile and robust** database that balances SQL and NoSQL features, making it ideal for **enterprise applications, data analytics, and large-scale distributed systems**. With its **advanced indexing, extensibility, and high availability**, PostgreSQL remains a top choice for developers and organizations worldwide.