# PostgreSQL
PostgreSQL (often called Postgres) is a powerful, open-source object-relational database system. Known for reliability, feature robustness, and standards compliance, it has over 35 years of active development.
PostgreSQL handles workloads ranging from single-machine applications to large-scale data warehouses with many concurrent users. It's the database of choice for many startups and enterprises.
## Key Characteristics
- **ACID compliant**: Full transaction support with strong data integrity
- **Extensible**: Custom data types, functions, operators, and extensions
- **Standards compliant**: Close adherence to SQL standards
- **Concurrent**: MVCC (Multi-Version Concurrency Control) for high performance
- **Reliable**: Proven track record in production environments
- **Open source**: PostgreSQL License (permissive, similar to MIT/BSD)
## Key Features
- **Advanced data types**: JSON/JSONB, arrays, hstore, geometric, network types
- **Full-text search**: Built-in text search with ranking
- **Partitioning**: Table partitioning for large datasets
- **Replication**: Streaming replication, logical replication
- **Foreign data wrappers**: Query external data sources
- **Window functions**: Advanced analytical queries
- **CTEs**: Common Table Expressions and recursive queries
- **JSONB**: Efficient JSON storage with indexing
## When to Use PostgreSQL
**Good for:**
- Web applications with complex queries
- Data warehousing and analytics
- Geospatial applications (with PostGIS)
- Applications requiring strict data integrity
- Multi-tenant SaaS applications
- Time-series data (with TimescaleDB)
**Consider alternatives for:**
- Simple key-value storage (use Redis)
- Embedded/mobile applications (use [[SQLite]])
- Extreme write throughput (consider specialized databases)
## Basic Usage
### Command Line (psql)
```bash
# Connect to database
psql -U username -d dbname -h localhost
# Run SQL file
psql -U username -d dbname -f script.sql
# Quick query
psql -U username -d dbname -c "SELECT * FROM users;"
```
### Common psql Commands
```sql
\l -- List databases
\c dbname -- Connect to database
\dt -- List tables
\d tablename -- Describe table
\du -- List users/roles
\q -- Quit
```
### Create Database and Table
```sql
CREATE DATABASE myapp;
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email VARCHAR(255) UNIQUE NOT NULL,
name VARCHAR(100),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
metadata JSONB
);
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_metadata ON users USING GIN(metadata);
```
### JSONB Queries
```sql
-- Insert JSON data
INSERT INTO users (email, name, metadata)
VALUES ('
[email protected]', 'Alice', '{"role": "admin", "tags": ["active"]}');
-- Query JSON fields
SELECT * FROM users WHERE metadata->>'role' = 'admin';
SELECT * FROM users WHERE metadata @> '{"tags": ["active"]}';
```
## Connection URLs
```
postgresql://user:password@host:5432/dbname
postgres://user:password@host:5432/dbname?sslmode=require
```
## Popular Extensions
- **PostGIS**: Geospatial data support
- **TimescaleDB**: Time-series data
- **pg_trgm**: Fuzzy text matching
- **uuid-ossp**: UUID generation
- **pgcrypto**: Cryptographic functions
- **pg_stat_statements**: Query performance tracking
## Performance Tips
- Use `EXPLAIN ANALYZE` to understand query plans
- Create indexes for frequently queried columns
- Use connection pooling (PgBouncer, pgpool)
- Configure `shared_buffers`, `work_mem` appropriately
- Use `VACUUM` and `ANALYZE` regularly
- Consider partitioning for large tables
## Managed PostgreSQL Services
- **[[Supabase]]**: PostgreSQL with realtime and auth
- **Neon**: Serverless PostgreSQL
- **AWS RDS**: Amazon managed PostgreSQL
- **Google Cloud SQL**: Google managed PostgreSQL
- **Azure Database**: Microsoft managed PostgreSQL
- **Heroku Postgres**: Simple managed PostgreSQL
## References
- Official website: https://www.postgresql.org/
- Documentation: https://www.postgresql.org/docs/
- https://en.wikipedia.org/wiki/PostgreSQL
## Related
- [[Database]]
- [[Database Management Systems (DBMS)]]
- [[Relational Databases (RDBMS)]]
- [[SQL]]
- [[SQLite]]
- [[DuckDB]]
- [[ACID]]
- [[Write-Ahead Logging (WAL)]]
- [[Supabase]]
- [[Vector Store]]