postgresql

-- Basic query debugging with EXPLAIN
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- Output shows query plan:
-- Seq Scan on users (cost=0.00..25.00 rows=1 width=100)
--   Filter: (email = 'test@example.com'::text)

-- Import CSV with COPY (fastest method)
COPY users (username, email, age, created_at)
FROM '/path/to/users.csv'
WITH (
  FORMAT csv,
  HEADER true,

class AddConstraintsToUsers < ActiveRecord::Migration[6.1]
  def change
    # Null constraints
    change_column_null :users, :email, false
    change_column_null :users, :username, false

class AddStatusToPosts < ActiveRecord::Migration[6.1]
  # Use change for automatic rollback
  def change
    # Add column without default to avoid table lock
    add_column :posts, :status, :string

production:
  primary:
    adapter: postgresql
    url: <%= ENV['DATABASE_URL'] %>
    pool: <%= ENV.fetch("RAILS_MAX_THREADS", 5) %>
  primary_replica:

-- Install postgres_fdw extension
CREATE EXTENSION IF NOT EXISTS postgres_fdw;

-- Create foreign server
CREATE SERVER remote_db
  FOREIGN DATA WRAPPER postgres_fdw

# In rails console
query = Post.joins(:author)
           .where(published_at: 1.week.ago..Time.current)
           .where(users: { status: 'active' })
           .order(created_at: :desc)

-- Basic VACUUM (reclaims dead tuple space)
VACUUM users;

-- VACUUM all tables in database
VACUUM;

-- Basic LATERAL join
SELECT
  u.username,
  recent.order_id,
  recent.total,
  recent.created_at

-- Create materialized view
CREATE MATERIALIZED VIEW user_statistics AS
SELECT
  users.id,
  users.username,
  COUNT(DISTINCT orders.id) AS order_count,

-- Connection statistics
SELECT
  count(*) AS total_connections,
  count(*) FILTER (WHERE state = 'active') AS active,
  count(*) FILTER (WHERE state = 'idle') AS idle,
  count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_tx,

-- Basic EXPLAIN
EXPLAIN
SELECT * FROM users WHERE email = 'alice@example.com';

-- EXPLAIN with cost and row estimates
-- Output shows: Seq Scan on users (cost=0.00..15.50 rows=1 width=100)