PostgreSQL Essentials: What Every Developer Should Know

PostgreSQL: Beyond Basic SQL

PostgreSQL isn't just a SQL database — it's a Swiss Army knife. JSON columns, full-text search, window functions, CTEs, and JSONB indexing mean you often don't need Redis, Elasticsearch, or a separate analytics database.

Data Types Worth Knowing

Type	Use Case	Example
`uuid`	Primary keys (better than serial for distributed systems)	`gen_random_uuid()`
`jsonb`	Semi-structured data (indexed, queryable)	`{"tags": ["dev", "sql"]}`
`text`	Strings (no length limit, same performance as varchar)	Use text, not varchar
`timestamptz`	Timestamps (always use WITH time zone)	`now()`
`integer[]`	Arrays (native support)	`{1, 2, 3}`
`citext`	Case-insensitive text (for emails)	No `LOWER()` needed

JSONB: The Document Store Inside Postgres

-- Store JSON
CREATE TABLE products (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL,
  metadata jsonb DEFAULT '{}'
);

-- Query JSON fields
SELECT name, metadata->>'color' AS color
FROM products
WHERE metadata->>'category' = 'electronics';

-- Query nested JSON
SELECT * FROM products
WHERE metadata @> '{"tags": ["sale"]}';

-- Index JSON for fast queries
CREATE INDEX idx_metadata ON products USING GIN (metadata);

CTEs (Common Table Expressions)

-- Readable subqueries
WITH active_users AS (
  SELECT id, name, email
  FROM users
  WHERE last_login > now() - interval '30 days'
),
user_orders AS (
  SELECT user_id, COUNT(*) as order_count
  FROM orders
  GROUP BY user_id
)
SELECT u.name, u.email, COALESCE(o.order_count, 0) as orders
FROM active_users u
LEFT JOIN user_orders o ON u.id = o.user_id
ORDER BY orders DESC;

Window Functions

-- Rank users by spending
SELECT 
  name,
  total_spent,
  RANK() OVER (ORDER BY total_spent DESC) as rank,
  DENSE_RANK() OVER (ORDER BY total_spent DESC) as dense_rank
FROM users;

-- Running total
SELECT 
  date,
  revenue,
  SUM(revenue) OVER (ORDER BY date) as running_total
FROM daily_revenue;

-- Compare to previous row
SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) as prev_month,
  revenue - LAG(revenue) OVER (ORDER BY month) as growth
FROM monthly_revenue;

Indexes That Matter

-- B-tree (default, for equality and range queries)
CREATE INDEX idx_users_email ON users (email);

-- Partial index (only index what you query)
CREATE INDEX idx_active_users ON users (email)
WHERE active = true;

-- Composite index (order matters!)
CREATE INDEX idx_orders_user_date ON orders (user_id, created_at DESC);

-- GIN index (for JSONB, arrays, full-text search)
CREATE INDEX idx_tags ON posts USING GIN (tags);

-- Check if your query uses the index
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

Performance Tips

Always use EXPLAIN ANALYZE to check query plans, not EXPLAIN alone
Use timestamptz, never timestamp (without timezone)
Use text instead of varchar(n) — same performance, no arbitrary limits
Add indexes on foreign keys — PostgreSQL doesn't do this automatically
Use UPSERT: INSERT ... ON CONFLICT DO UPDATE
Vacuum: PostgreSQL needs VACUUM to reclaim dead rows. Autovacuum handles this, but monitor it.

Format your SQL: SQL Formatter — paste messy SQL, get clean formatted output.