# What is ⁠PostgreSQL?
*PostgreSQL is a robust, open-source object-relational database system known for advanced features, scalability, and support for complex queries.*
By [Rohit Lakhotia](https://scaleengineer.com/authors/rohit-lakhotia)
Published: 2024-06-24
Canonical: https://scaleengineer.com/blog/what-is-postgresql
---
## **How Figma Hacked Postgres Into Scalability**

To handle growing data demands, Figma shifted from vertical partitioning to horizontal sharding, splitting data across multiple databases\. This nine\-month effort improved performance and scalability, ensuring efficient data management and minimal disruptions\.

Read the full blog here: [How Figma's Databases Team Lived to Tell the Scale \| Figma Blog](https://www.figma.com/blog/how-figmas-databases-team-lived-to-tell-the-scale/)

[Embedded content](https://youtube.com/embed/P-6s40csV5Q)

Have you ever wondered what makes modern applications so reliable and efficient in handling vast amounts of data? Behind the scenes of many data\-driven services lies a robust **Relational Database Management System \(RDBMS\)\. **One standout in this field is **PostgreSQL**, an open\-source powerhouse renowned for its **reliability, scalability**, and advanced features\. Whether you're developing a web application, managing a data warehouse, or exploring [IoT](/blog/what-is-iot-in-simple-words) solutions, PostgreSQL offers a versatile and powerful platform that meets diverse needs\. Let’s discover more about **PostgreSQL** in this blog today\.

**PostgreSQL **is one of the most trusted names in **open\-source relational databases**\. Its development started back in **1986 **at **UC Berkeley** under **Michael Stonebraker**\.

*Like other relational databases, it stores data in tables with columns and rows and uses SQL \(Structured Query Language\) to read and write data\.*

PostgreSQL is technically an **object\-relational database**, meaning:

- It can create custom data types to store objects with properties

![](https://media.beehiiv.com/cdn-cgi/image/fit=scale-down,format=auto,onerror=redirect,quality=80/uploads/asset/file/55c3b9af-6959-4b7d-9d89-18f16025a0ae/image.png?t=1718823660)

- Supports advanced features like inheritance and [polymorphism](/glossaries/polymorphism)\.

![](https://media.beehiiv.com/cdn-cgi/image/fit=scale-down,format=auto,onerror=redirect,quality=80/uploads/asset/file/ecebb849-ca81-4659-9c5a-a30edf2953f0/image.png?t=1718823755)

When writing data, PostgreSQL uses fully **ACID\-compliant transactions** to ensure data integrity\. It also features **Multi\-Version Concurrency Control \(MVCC\)**, which allows multiple transactions to run simultaneously by giving each one a snapshot of the database, preventing traffic jams or locks\.

Developers love PostgreSQL for its **extensibility**\.

![](https://media.beehiiv.com/cdn-cgi/image/fit=scale-down,format=auto,onerror=redirect,quality=80/uploads/asset/file/bda8d0e7-57ed-4ed5-8785-53eb5a68d9a7/image.png?t=1718899074)

You can reuse queries by writing **stored procedures**, and it supports languages beyond SQL, such as **Python and C\.** PostgreSQL has a robust ecosystem of extensions like **PostGIS for geospatial data** used by apps like **Uber, **and **Citus** for scaling and distributing the database, and **PGEmbedding** for giving AI chatbots long\-term memory\. To get started, you can download and install [PostgreSQL](https://www.postgresql.org/) locally\.

### Why PostgreSQL is Best for You?

**Key Features:**

- **User\-defined Types**: Customize your own data types\.
- **Table Inheritance**: Use table inheritance for better data management\.
- **Sophisticated Locking Mechanism**: Ensures data consistency\.
- **Foreign Key Referential Integrity**: Maintains database integrity\.
- **Views, Rules, Subquery**: Powerful query capabilities\.
- **Nested Transactions \(Savepoints\)**: Manage transactions more effectively\.
- **Multi\-Version Concurrency Control \(MVCC\)**: Allows multiple transactions without conflicts\.
- **Asynchronous Replication**: For high availability\.
- **Native Microsoft Windows Server Version**: Compatible with Windows servers\.
- **Tablespaces**: Organize data storage efficiently\.
- **Point\-in\-time Recovery**: Restore data to a specific point in time\.

### CRUD Operations in PostgreSQL

To add new records to a PostgreSQL table, you first define the table structure\.

**Create and Insert Records**: To create a table and insert records

```
CREATE TABLE users ( 
  id SERIAL PRIMARY KEY, 
  name VARCHAR (100), 
  email VARCHAR (100), 
  age INT 
); 

INSERT INTO users (name, email, age) VALUES ('John Smith', 'john.smith@example.com', 32);
```

**Read**: To retrieve records

```
SELECT * FROM users; SELECT * FROM users WHERE age > 30;
```

**Update**: To update a record

```
UPDATE users SET email = 'new.email@example.com' WHERE name = 'John Smith';
```

**Delete**: To delete a record

```
DELETE FROM users WHERE name = 'John Smith';
```

### Architecture

Its architecture is divided into two primary components: the **client **and the **server**\.

The client sends requests to the server, which processes these requests and sends responses back\. Typically, the client and server communicate over a **TCP/IP **network if they are on different hosts\.

### Key Components of PostgreSQL Architecture

![](https://media.beehiiv.com/cdn-cgi/image/fit=scale-down,format=auto,onerror=redirect,quality=80/uploads/asset/file/feea49ef-1246-447a-bfda-de4a0dbc605a/image.png?t=1718900663)

**1\. Shared Memory** Shared memory is crucial for efficient data handling and consists of:

- **Shared Buffers**: These minimize disk I/O by holding frequently accessed data\. The default size has increased from 32MB in older versions to 128MB in versions 9\.3 and later\.
- **WAL Buffers**: Temporarily store changes before they are written to WAL \(Write Ahead Log\) files, aiding in data recovery\.
- **Work Memory**: Allocates memory for sorting operations and hash tables during data writes\. The default has increased from 1MB to 4MB in newer versions\.
- **Maintenance Work Memory**: Allocates memory for maintenance tasks like VACUUM and CREATE INDEX, with the default size increasing from 16MB to 64MB\.

**2\. Background Processes** PostgreSQL uses several background processes for various tasks:

- **Background Writer**: Regularly performs checkpoint processing\.
- **WAL Writer**: Periodically writes and flushes WAL data to persistent storage\.
- **Logging Collector**: Writes log data to files\.
- **Autovacuum Launcher**: Manages vacuum operations on tables\.
- **Archiver**: Copies WAL log files to a specified directory if archive mode is enabled\.
- **Stats Collector**: Gathers statistics on database activity\.
- **Checkpointer**: Writes all dirty pages from memory to disk and cleans the buffer area\.

**3\. Data Files / Data Directory Structure** PostgreSQL databases are organized into a cluster, with a specific directory structure:

- **pg\_version**: Contains database version information\.
- **Base**: Holds database subdirectories\.
- **Global**: Contains cluster\-wide tables\.
- **pg\_xlog**: Stores WAL files\.
- **pg\_clog, pg\_multixact, pg\_notify**: Store various status and transaction data\.
- **pg\_stat\_tmp**: Holds temporary statistics files\.

### Why PostgreSQL is Unique?

**Unique Features:**

- **Pioneered MVCC**: PostgreSQL was the first to implement Multi\-Version Concurrency Control \(MVCC\)\.
- **Custom Functions**: Add custom functions in languages like C/C\+\+, Python, and Java\.
- **Extensibility**: Define custom data types, index types, and functional languages\.
- **Custom Plugins**: Enhance and modify the system with custom plugins to meet specific needs\.

### PostgreSQL vs MySQL: A Quick Comparison

**Similarities:**

1. **Relational Database Management Systems \(RDBMS\)**: Both organize data into tables\.
2. **[SQL](/glossaries/structured-query-language)**** Support**: Both use Structured Query Language \(SQL\) for interacting with the database\.
3. **JSON Support**: Both can store and transport data using JSON \(JavaScript Object Notation\)\.

**Differences:**

| Feature | PostgreSQL | MySQL |
| --- | --- | --- |
| **Type** | Object\-Relational Database Management System **\(ORDBMS\)** | Relational Database Management System **\(RDBMS\)** |
| **Complex Queries** | Excellent support for **complex queries** | Good for **simpler queries** and transactions |
| **Extensibility** | Highly extensible with custom functions and types | Limited extensibility |
| **Concurrency Control** | Multi\-Version Concurrency Control \(MVCC\) | Combination of MVCC and locking mechanisms |
| **Performance** | Strong for complex queries and large datasets | Optimized for read\-heavy operations and speed |
| **Scalability** | Vertical and some horizontal scalability | Primarily vertical scalability |
| **High Availability** | Asynchronous and synchronous replication | Replication available, less robust |
| **Ease of Use** | Steeper learning curve due to advanced features | Easy to set up and use |

**Conclusion:**

- **PostgreSQL**: Choose PostgreSQL for** enterprise applications** that need robust support for **complex queries, multiple data types, **and** high concurrency\.**
- **MySQL**: Opt for MySQL if you need a fast, easy\-to\-use database for **small **to **medium\-sized** **web applications**\.

### Common Use Cases of PostgreSQL

**1\) Robust Database in the LAPP Stack** **LAPP **stands for **Linux, Apache, PostgreSQL, **and** PHP \(or Python and Perl\)\. **PostgreSQL is often used as a reliable [back\-end](/glossaries/backend) database for dynamic websites and web applications\.

**2\) General\-Purpose Transaction Database** Both large corporations and startups use PostgreSQL as their main database to support various applications and products\.

**3\) Geospatial Database** With the **PostGIS** extension, PostgreSQL supports geospatial databases, making it ideal for** geographic information systems \(GIS\)** for apps like **Uber **and** Citus\.**

### Language Support

PostgreSQL supports a wide range of popular programming languages, including:

- Python
- Java
- C\#
- C/C\+\+
- Ruby
- [JavaScript](/glossaries/javascript) \([Node\.js](/glossaries/node.js)\)
- Perl
- Go
- Tcl

### Large Scale users of PostgreSQL

Several companies have built products and solutions using PostgreSQL\. A few of those companies are** Apple, Fujitsu, Red Hat, Cisco, Juniper Network, etc**

By now, you must have had a clear idea of **PostgreSQL**, from its meaning to working you know all of it now\. In a nutshell, **PostgreSQL is a powerful, open\-source object\-relational database management system known for its advanced features, scalability, and support for complex queries\.** So, as you navigate through the vast expanse of the internet, stay informed, stay secure, and embrace the adventure of the digital realm\!

**Congratulations\! You've just advanced another step in your tech journey\. Keep progressing\!**