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


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://www.anandisheladiya.com/skills-and-experience/database/postgresql.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.