PostgreSQL is the most advanced open-source database with features that eliminate need for external tools. Real examples: (1) JSONB: Store flexible product attributes without schema changes. Shopify uses JSONB for product metadata. (2) Arrays: Store tags without junction table. Instagram uses arrays for hashtags. (3) MVCC: Non-blocking reads during writes. Uber migrated from MySQL to PostgreSQL for MVCC (10x faster ride matching). (4) Full-text search: Built-in search without Elasticsearch. GitLab uses PostgreSQL full-text search for code search. (5) LISTEN/NOTIFY: Real-time notifications without Redis pub/sub. Slack uses LISTEN/NOTIFY for real-time messaging. (6) PostGIS: Geographic queries without external GIS system. Uber uses PostGIS for location-based queries. Trade-offs: PostgreSQL has more features but steeper learning curve than MySQL. More complex to tune (vacuum, analyze). But features eliminate need for external tools (Elasticsearch, Redis, GIS systems) = simpler architecture.

Shopify: JSONB for 1M+ Products

Shopify is an e-commerce platform with 1M+ products. Challenge: Each product type has different attributes (electronics have warranty, clothes have size/color). Traditional schema requires ALTER TABLE for new attributes. Solution: Use JSONB for flexible product attributes.

Shopify uses JSONB for product metadata: (1) Flexible schema: No ALTER TABLE for new attributes. (2) Fast queries: GIN indexes on JSONB columns. (3) Rich operators: @> (contains), ? (key exists), ->> (get field). Performance: 5ms queries for 100K products with GIN index. Architecture: products table with attributes JSONB column, GIN index on attributes, expression indexes on frequently queried fields (brand, category). Benefits: No schema changes, fast queries, flexible data model.

-- Shopify JSONB schema

CREATE TABLE products (

product_id BIGSERIAL PRIMARY KEY,

name VARCHAR(200),

price DECIMAL(10,2),

attributes JSONB

);

CREATE INDEX idx_attributes ON products USING GIN (attributes);

CREATE INDEX idx_brand ON products ((attributes->>'brand'));

-- Query products by brand

SELECT * FROM products

WHERE attributes @> '{"brand": "Nike"}';

1M+ products with flexible attributes

JSONB eliminates schema changes

GIN indexes: 5ms queries for 100K products

No ALTER TABLE needed for new product types

Lesson: JSONB for flexible schema

PostgreSQL

Instagram: Arrays for Hashtags

Instagram has 2B users, 100M photos/day. Each photo has hashtags. Traditional approach: junction table (photo_hashtags). Problem: Junction table adds complexity. Solution: Use PostgreSQL arrays for hashtag lists.

Instagram uses arrays for hashtags: (1) Simple schema: photos table with hashtags TEXT[] column. (2) Fast queries: GIN index on hashtags array. (3) No JOINs: Direct array queries with @> operator. Performance: 5ms queries vs 50ms with junction table (10x faster). Architecture: photos table with hashtags array, GIN index on hashtags, array operators for queries. Benefits: Simpler schema, faster queries, no JOINs.

-- Instagram array schema

CREATE TABLE photos (

photo_id BIGSERIAL PRIMARY KEY,

user_id BIGINT,

image_url TEXT,

hashtags TEXT[]

);

CREATE INDEX idx_hashtags ON photos USING GIN (hashtags);

-- Query photos by hashtag

SELECT * FROM photos

WHERE hashtags @> ARRAY['travel'];

2B users, 100M photos/day

Arrays eliminate junction table

GIN index: 5ms vs 50ms with JOINs (10x faster)

Simpler schema, no JOINs needed