What is PostgreSQL?

Definition

PostgreSQL (often called "Postgres") is an advanced, open-source object-relational database system with over 35 years of active development. It has earned a strong reputation for reliability, data integrity, and correctness.

PostgreSQL extends standard SQL with features like complex queries, foreign keys, triggers, updatable views, and transactional integrity. It supports advanced data types including JSON/JSONB, arrays, hstore, and custom types.

Key Features

• ACID compliance: Full atomicity, consistency, isolation, and durability guarantees.
• JSONB support: Store and query JSON data efficiently, combining relational and document database capabilities.
• Advanced indexing: B-tree, hash, GIN, GiST, BRIN indexes for different query patterns.
• Full-text search: Built-in text search with ranking, stemming, and dictionaries.
• Extensibility: Custom data types, functions, operators, and index methods.
• Partitioning: Table partitioning for handling large datasets efficiently.
• Replication: Built-in streaming replication for high availability.

PostgreSQL with Python

PostgreSQL pairs exceptionally well with Python backends:

• asyncpg: High-performance async PostgreSQL driver for Python, ideal with FastAPI.
• psycopg: The most popular PostgreSQL adapter for Python (psycopg3 supports async).
• SQLAlchemy: Python ORM with excellent PostgreSQL-specific features.
• Django ORM: First-class PostgreSQL support including ArrayField, JSONField, and full-text search.
• Alembic: Database migration tool that works seamlessly with PostgreSQL.

Code Example

-- Create a table with JSONB and full-text search
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    metadata JSONB DEFAULT '{}',
    search_vector tsvector,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Create a GIN index for fast JSONB queries
CREATE INDEX idx_articles_metadata ON articles USING GIN (metadata);

-- Query JSONB data
SELECT * FROM articles
WHERE metadata->>'category' = 'python'
AND metadata->'tags' ? 'fastapi';

Frequently Asked Questions

PostgreSQL vs MySQL - which is better?

PostgreSQL is generally better for complex queries, data integrity, and advanced features (JSONB, arrays, full-text search). MySQL is simpler to set up and has better read performance for simple queries. For Python backends, PostgreSQL is the more common and recommended choice.

Is PostgreSQL free?

Yes, PostgreSQL is completely free and open-source under the PostgreSQL License, a permissive open-source license. There are no commercial restrictions on its use.