PostgreSQL #

PostgreSQL is the most feature-rich open-source database available today — and the top choice for modern Ruby on Rails applications. Unlike MySQL, which prioritizes speed, PostgreSQL prioritizes SQL standard compliance, strict ACID reliability, and advanced features that don’t exist in other databases: JSONB data types that can be queried and indexed, native arrays, UUID primary keys, built-in full-text search, LISTEN/NOTIFY for event-driven architecture, and COPY for extremely fast bulk imports. The pg gem is a mature, fast PostgreSQL driver for Ruby that supports all modern PostgreSQL features. This article covers everything from basic connections to the unique PostgreSQL features that make it the best choice for Ruby applications.

Installation #

# Ubuntu / Debian — install the PostgreSQL library
sudo apt install libpq-dev

# macOS with Homebrew
brew install libpq
brew link --force libpq  # so the pg gem can find it

# CentOS / RHEL / Fedora
sudo dnf install postgresql-devel

# Install the gem
gem install pg

# Gemfile
gem 'pg',     '~> 1.5'    # PostgreSQL driver
gem 'sequel', '~> 5.75'   # optional query builder

Connection and Connection Pool #

require 'pg'

# Basic connection
conn = PG.connect(
  host:     "localhost",
  port:     5432,
  dbname:   "store_db",
  user:     "appuser",
  password: ENV["PG_PASSWORD"],
  connect_timeout: 10,
  sslmode:  "require"     # "disable", "allow", "prefer", "require", "verify-full"
)

puts "PostgreSQL #{conn.server_version}"
conn.close

# Connection via URL (more concise)
conn = PG.connect(ENV["DATABASE_URL"])
# DATABASE_URL=postgresql://appuser:***@localhost:5432/store_db

# With a block — auto-close
PG.connect(dbname: "store_db", user: "appuser", password: ENV["PG_PASSWORD"]) do |conn|
  result = conn.exec("SELECT version()")
  puts result.first["version"]
end

# Connection Pool with the connection_pool gem
require 'connection_pool'

POOL = ConnectionPool.new(size: 10, timeout: 5) do
  PG.connect(
    host:     ENV.fetch("PG_HOST", "localhost"),
    dbname:   ENV.fetch("PG_DATABASE", "store_db"),
    user:     ENV.fetch("PG_USER", "appuser"),
    password: ENV["PG_PASSWORD"]
  )
end

POOL.with { |conn| conn.exec("SELECT 1") }

Basic Queries #

SELECT with exec and exec_params #

require 'pg'

conn = PG.connect(dbname: "store_db", user: "appuser", password: ENV["PG_PASSWORD"])

# exec — query without parameters (be careful of SQL injection!)
result = conn.exec("SELECT * FROM products LIMIT 10")

# Iterate the result
result.each do |row|
  puts "#{row['id']}: #{row['name']} - Rp #{row['price']}"
end

# Access like a Hash
puts result.first["name"]     # => "Laptop"
puts result.first["price"]    # => "15000000"  (String! needs conversion)
puts result.first["price"].to_i  # => 15000000

# exec_params — parameterized query (THE CORRECT WAY)
# PostgreSQL uses $1, $2, ... as placeholders
result = conn.exec_params(
  "SELECT * FROM products WHERE price < $1 AND active = $2",
  [5_000_000, true]
)

result.each { |r| puts "#{r['name']}: #{r['price']}" }

# Metadata
puts result.ntuples   # number of rows
puts result.nfields   # number of columns
puts result.fields.inspect  # ["id", "name", "price", "stock", "active"]

# Type conversion — pg returns all values as Strings
# Use a type map for automatic conversion
conn.type_map_for_results = PG::BasicTypeMapForResults.new(conn)

result = conn.exec_params("SELECT id, price, active FROM products WHERE id = $1", [1])
row = result.first
puts row["id"].class    # => Integer (not String!)
puts row["price"].class # => BigDecimal
puts row["active"].class # => TrueClass / FalseClass

Complete CRUD with Parameterized Queries #

class ProductPostgresRepository
  def initialize(conn)
    @conn = conn
    # Enable type mapping for automatic conversion
    @conn.type_map_for_results = PG::BasicTypeMapForResults.new(@conn)
    @conn.type_map_for_queries = PG::BasicTypeMapForQueries.new(@conn)
  end

  # CREATE — use RETURNING to get the newly inserted row
  def create(name:, price:, stock:, category_id:, description: nil)
    result = @conn.exec_params(
      <<~SQL,
        INSERT INTO products (name, price, stock, category_id, description, active, created_at)
        VALUES ($1, $2, $3, $4, $5, true, NOW())
        RETURNING *
      SQL
      [name, price, stock, category_id, description]
    )
    result.first
  end

  # READ
  def find(id)
    result = @conn.exec_params(
      "SELECT p.*, c.name AS category_name FROM products p
       LEFT JOIN categories c ON p.category_id = c.id
       WHERE p.id = $1",
      [id]
    )
    result.first
  end

  def find_all(active: true, limit: 50, offset: 0, sort: "created_at DESC")
    # Sanitize the sort column — don't use parameters for ORDER BY
    safe_column = %w[name price stock created_at].include?(sort.split.first) ? sort : "created_at DESC"
    result = @conn.exec_params(
      "SELECT * FROM products WHERE active = $1 ORDER BY #{safe_column} LIMIT $2 OFFSET $3",
      [active, limit, offset]
    )
    result.to_a
  end

  def search_text(keyword)
    # PostgreSQL full-text search
    result = @conn.exec_params(
      <<~SQL,
        SELECT *, ts_rank(to_tsvector('english', name || ' ' || COALESCE(description, '')),
                          plainto_tsquery('english', $1)) AS rank
        FROM products
        WHERE to_tsvector('english', name || ' ' || COALESCE(description, ''))
              @@ plainto_tsquery('english', $1)
          AND active = true
        ORDER BY rank DESC
        LIMIT 20
      SQL
      [keyword]
    )
    result.to_a
  end

  # UPDATE
  def update(id, **attributes)
    return false if attributes.empty?

    valid_columns = %i[name price stock description active]
    attributes = attributes.slice(*valid_columns)
    return false if attributes.empty?

    set_clause = attributes.keys.each_with_index.map { |k, i| "#{k} = $#{i + 1}" }.join(", ")
    values     = attributes.values + [id]

    result = @conn.exec_params(
      "UPDATE products SET #{set_clause}, updated_at = NOW() WHERE id = $#{values.length} RETURNING *",
      values
    )
    result.first
  end

  # Soft DELETE
  def deactivate(id)
    result = @conn.exec_params(
      "UPDATE products SET active = false, updated_at = NOW() WHERE id = $1 RETURNING id",
      [id]
    )
    result.ntuples > 0
  end
end

Transactions #

# Basic transaction
conn.transaction do |c|
  c.exec_params(
    "UPDATE accounts SET balance = balance - $1 WHERE id = $2",
    [500_000, 1]
  )
  c.exec_params(
    "UPDATE accounts SET balance = balance + $1 WHERE id = $2",
    [500_000, 2]
  )
  # If an exception occurs → automatic ROLLBACK
  # If successful → automatic COMMIT
end

# Savepoints — nested transactions
conn.transaction do |c|
  c.exec("SAVEPOINT sp_start")

  begin
    c.exec_params("INSERT INTO audit_log (action) VALUES ($1)", ["transaction_started"])
    c.exec("SAVEPOINT sp_operation")

    # Risky operation
    c.exec_params("UPDATE products SET stock = stock - $1 WHERE id = $2", [1, product_id])

    c.exec("RELEASE SAVEPOINT sp_operation")
  rescue PG::Error => e
    c.exec("ROLLBACK TO SAVEPOINT sp_operation")
    raise e
  end
end

# Isolation level — important for concurrency
conn.transaction(isolation: :serializable) do |c|
  # Serializable prevents phantom reads, non-repeatable reads, and dirty reads
  # Slower but the safest for financial operations
  balance = c.exec_params("SELECT balance FROM accounts WHERE id = $1 FOR UPDATE", [1]).first["balance"].to_i
  raise "Insufficient balance" if balance < 500_000
  c.exec_params("UPDATE accounts SET balance = balance - $1 WHERE id = $2", [500_000, 1])
end

PostgreSQL’s Unique Data Types #

PostgreSQL has data types that don’t exist in other databases — one of the main reasons it’s chosen for modern applications:

JSONB — JSON That Can Be Indexed and Queried #

# Store a JSON document
conn.exec_params(
  "INSERT INTO product_details (product_id, specifications) VALUES ($1, $2::jsonb)",
  [1, JSON.generate({ processor: "Intel i7", ram: "16GB", storage: "512GB SSD" })]
)

# Query inside JSONB
result = conn.exec(
  "SELECT name, specifications->>'processor' AS processor
   FROM product_details pd
   JOIN products p ON p.id = pd.product_id
   WHERE specifications->>'ram' = '16GB'"
)

# JSONB operators
# -> returns JSON, ->> returns text
# @> checks containment
# ? checks if a key exists
result = conn.exec(
  "SELECT * FROM product_details
   WHERE specifications @> '{\"ram\": \"16GB\"}'::jsonb"
)

# ActiveRecord with JSONB
class ProductDetail < ApplicationRecord
  # The specifications column is jsonb
  store_accessor :specifications, :processor, :ram, :storage

  scope :with_ram, ->(ram) {
    where("specifications->>'ram' = ?", ram)
  }
end

detail = ProductDetail.new
detail.processor = "Intel i7"
detail.ram       = "16GB"
detail.save!

ProductDetail.with_ram("16GB")

Arrays — Native Array Columns #

# Create a table with an array column
conn.exec(
  "CREATE TABLE IF NOT EXISTS articles (
    id      SERIAL PRIMARY KEY,
    title   TEXT NOT NULL,
    tags    TEXT[],          -- text array
    scores  INTEGER[]        -- integer array
  )"
)

# Insert with an array
conn.exec_params(
  "INSERT INTO articles (title, tags) VALUES ($1, $2)",
  ["Ruby Tips", ["ruby", "programming", "tips"]]
)

# Query with array operators
# ANY — check whether a value is in the array
result = conn.exec(
  "SELECT * FROM articles WHERE 'ruby' = ANY(tags)"
)

# @> — array containment
result = conn.exec(
  "SELECT * FROM articles WHERE tags @> ARRAY['ruby', 'tips']"
)

# ActiveRecord with arrays
class Article < ApplicationRecord
  # tags is TEXT[] in PostgreSQL
  def self.with_tag(tag)
    where("? = ANY(tags)", tag)
  end

  def add_tag(new_tag)
    update(tags: (tags + [new_tag]).uniq)
  end
end

article = Article.create!(title: "Learning Ruby", tags: ["ruby", "basics"])
Article.with_tag("ruby")

UUID — Safer Primary Keys #

# Enable the pgcrypto or uuid-ossp extension
conn.exec("CREATE EXTENSION IF NOT EXISTS pgcrypto")

# Create a table with a UUID primary key
conn.exec(
  "CREATE TABLE IF NOT EXISTS sessions (
    id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id    INTEGER NOT NULL,
    token      TEXT NOT NULL,
    expires_at TIMESTAMPTZ NOT NULL
  )"
)

# Insert — the id is filled automatically
result = conn.exec_params(
  "INSERT INTO sessions (user_id, token, expires_at)
   VALUES ($1, $2, NOW() + INTERVAL '7 days')
   RETURNING id",
  [1, SecureRandom.hex(32)]
)
puts result.first["id"]  # => "550e8400-e29b-41d4-a716-446655440000"

# ActiveRecord with UUID
class Session < ApplicationRecord
  # config/initializers/generators.rb:
  # config.generators { |g| g.orm :active_record, primary_key_type: :uuid }

  # or in the model:
  self.primary_key = :id   # uuid type in the migration
end

hstore — Key-Value Store #

# Enable the hstore extension
conn.exec("CREATE EXTENSION IF NOT EXISTS hstore")

# Create a table with an hstore column
conn.exec(
  "CREATE TABLE IF NOT EXISTS configurations (
    id      SERIAL PRIMARY KEY,
    name    TEXT NOT NULL,
    settings hstore
  )"
)

# Insert hstore
conn.exec_params(
  "INSERT INTO configurations (name, settings) VALUES ($1, $2::hstore)",
  ["email", "host => smtp.gmail.com, port => 587, tls => true"]
)

# Query hstore
conn.exec("SELECT settings->'host' AS smtp_host FROM configurations WHERE name = 'email'")

# ActiveRecord with hstore
class Configuration < ApplicationRecord
  store_accessor :settings, :host, :port, :tls
end

COPY — Ultra-Fast Bulk Import/Export #

COPY is the fastest way to insert or export large amounts of data:

# COPY FROM — import CSV into PostgreSQL
conn.copy_data("COPY products (name, price, stock, category_id) FROM STDIN WITH CSV") do
  File.foreach("products.csv") do |line|
    conn.put_copy_data(line)
  end
end
puts "Import done"

# COPY TO — export data to CSV
File.open("export_products.csv", "w") do |f|
  conn.copy_data("COPY (SELECT name, price, stock FROM products WHERE active = true) TO STDOUT WITH CSV HEADER") do
    while (data = conn.get_copy_data)
      f.write(data)
    end
  end
end

# Benchmark comparison
# INSERT one by one: 10,000 rows ≈ 10 seconds
# INSERT batch:      10,000 rows ≈ 1 second
# COPY:              10,000 rows ≈ 0.1 seconds (100x faster than regular INSERT!)

LISTEN/NOTIFY — Real-time Events #

PostgreSQL has a built-in pub/sub mechanism that can be used for real-time notifications:

require 'pg'

# PUBLISHER — send a notification from one connection
def send_notification(conn, channel, payload = nil)
  if payload
    conn.exec_params("NOTIFY #{conn.escape_identifier(channel)}, $1", [payload])
  else
    conn.exec("NOTIFY #{conn.escape_identifier(channel)}")
  end
end

# Database trigger for auto-notify when data changes
conn.exec(<<~SQL)
  CREATE OR REPLACE FUNCTION notify_product_changed()
  RETURNS TRIGGER AS $$
  BEGIN
    PERFORM pg_notify('product_changed',
      json_build_object(
        'action',    TG_OP,
        'id',        NEW.id,
        'name',      NEW.name,
        'updated',   NEW.updated_at
      )::text
    );
    RETURN NEW;
  END;
  $$ LANGUAGE plpgsql;

  DROP TRIGGER IF EXISTS trg_product_changed ON products;
  CREATE TRIGGER trg_product_changed
    AFTER INSERT OR UPDATE ON products
    FOR EACH ROW EXECUTE FUNCTION notify_product_changed();
SQL

# SUBSCRIBER — listen for notifications in a separate thread
Thread.new do
  listener = PG.connect(dbname: "store_db", user: "appuser", password: ENV["PG_PASSWORD"])
  listener.exec("LISTEN product_changed")
  puts "Listening for product changes..."

  loop do
    listener.wait_for_notify(10) do |channel, pid, payload|
      data = JSON.parse(payload)
      puts "Product changed: #{data['action']} - #{data['name']} (ID: #{data['id']})"
      # Invalidate cache, broadcast to WebSocket, etc.
    end
  end
ensure
  listener.exec("UNLISTEN product_changed")
  listener.close
end

# Test — update a product and watch the notification appear
sleep 1
conn.exec_params("UPDATE products SET price = price + 1000 WHERE id = $1", [1])
conn.exec("COMMIT")
sleep 2

ActiveRecord with PostgreSQL in Rails #

# config/database.yml
default: &default
  adapter: postgresql
  encoding: unicode
  pool:     <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  username: <%= ENV["PG_USER"] || "postgres" %>
  password: <%= ENV["PG_PASSWORD"] %>
  host:     <%= ENV["PG_HOST"] || "localhost" %>
  port:     <%= ENV["PG_PORT"] || 5432 %>
  timeout:  5000
  # PostgreSQL connection options
  variables:
    statement_timeout: 10000   # query timeout 10 seconds
    lock_timeout:      5000    # lock timeout 5 seconds

development:
  <<: *default
  database: store_development

test:
  <<: *default
  database: store_test

production:
  <<: *default
  url:  <%= ENV["DATABASE_URL"] %>

# Migrations with PostgreSQL features
class CreateProductsTable < ActiveRecord::Migration[7.1]
  def change
    enable_extension "pgcrypto"   # for gen_random_uuid()
    enable_extension "unaccent"   # for accent-free full-text search

    create_table :products, id: :uuid, default: "gen_random_uuid()" do |t|
      t.string      :name,        null: false, limit: 200
      t.text        :description
      t.decimal     :price,       null: false, precision: 15, scale: 2
      t.integer     :stock,       null: false, default: 0
      t.boolean     :active,      null: false, default: true
      t.references  :category,    null: false, foreign_key: true, type: :uuid
      t.text        :tags,        array: true, default: []   # Array
      t.jsonb       :metadata,    default: {}               # JSONB
      t.tsvector    :search_vector                          # Full-text search

      t.timestamps
    end

    # Standard indexes
    add_index :products, :active
    add_index :products, :price
    add_index :products, [:category_id, :active]

    # GIN indexes for arrays and JSONB — far faster than B-tree for these
    add_index :products, :tags,     using: :gin
    add_index :products, :metadata, using: :gin

    # GiST or GIN for full-text search
    add_index :products, :search_vector, using: :gin

    # Partial index — only index active rows
    add_index :products, :price, where: "active = true", name: "idx_products_price_active"
  end
end

# Models with PostgreSQL features
class Product < ApplicationRecord
  belongs_to :category

  # Scopes for arrays
  scope :with_tag, ->(tag) { where("? = ANY(tags)", tag) }
  scope :with_all_tags, ->(tags) { where("tags @> ARRAY[?]::text[]", tags) }

  # Scopes for JSONB
  scope :with_color, ->(color) {
    where("metadata @> ?", { color: color }.to_json)
  }

  # Full-text search
  scope :search, ->(query) {
    where(
      "to_tsvector('english', name || ' ' || COALESCE(description, '')) @@ plainto_tsquery('english', ?)",
      query
    )
    .order(
      Arel.sql("ts_rank(to_tsvector('english', name || ' ' || COALESCE(description, '')), plainto_tsquery('english', #{connection.quote(query)})) DESC")
    )
  }

  before_save :update_search_vector

  private

  def update_search_vector
    self.search_vector = Product.connection.execute(
      "SELECT to_tsvector('english', #{Product.connection.quote(name + ' ' + description.to_s)})"
    ).first["to_tsvector"]
  end
end

# Usage
Product.with_tag("laptop")
Product.with_all_tags(["laptop", "gaming"])
Product.with_color("black")
Product.search("light gaming laptop")

EXPLAIN ANALYZE — Debugging Slow Queries #

# Query performance analysis
def explain(conn, query, *params)
  explain_sql = "EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) #{query}"
  result = conn.exec_params(explain_sql, params)
  result.each { |r| puts r["QUERY PLAN"] }
end

explain(conn,
  "SELECT * FROM products WHERE category_id = $1 AND active = true ORDER BY price",
  2
)

# Example output:
# Sort  (cost=156.23..162.34 rows=2445 width=...) (actual time=2.345..2.567 rows=234)
#   Sort Key: price
#   Sort Method: quicksort  Memory: 45kB
#   ->  Bitmap Heap Scan on products  (cost=48.23..124.56 rows=2445 width=...)
#         Recheck Cond: (category_id = 2)
#         Filter: active
#         Heap Blocks: exact=89
#         ->  Bitmap Index Scan on idx_products_category  (cost=0.00..47.62)
#               Index Cond: (category_id = 2)
# Planning Time: 0.456 ms
# Execution Time: 3.123 ms

# In Rails — use the bullet gem for N+1, and the pg_query gem for EXPLAIN parsing

Sequel with PostgreSQL #

require 'sequel'

DB = Sequel.connect(
  adapter:  "postgres",
  host:     "localhost",
  database: "store_db",
  user:     "appuser",
  password: ENV["PG_PASSWORD"],
  max_connections: 10
)

# Standard queries
DB[:products].all
DB[:products].where(active: true).order(Sequel.desc(:created_at)).limit(10).all

# PostgreSQL-specific with Sequel
# JSONB
DB[:product_details].where(
  Sequel.pg_json_op(:metadata).get_text("color") => "black"
).all

# Array containment
DB[:products].where(
  Sequel.pg_array(:tags).contains(["ruby", "programming"])
).all

# Full-text search
DB[:products].where(
  Sequel.lit("to_tsvector('english', name) @@ plainto_tsquery('english', ?)", "laptop")
).all

# UPSERT (INSERT ON CONFLICT)
DB[:products].insert_conflict(
  target: :sku,
  update: { price: Sequel[:excluded][:price], updated_at: Sequel::CURRENT_TIMESTAMP }
).insert(sku: "SKU001", name: "Pro Laptop", price: 15_000_000)

# Transactions
DB.transaction(isolation: :serializable) do
  DB[:accounts].where(id: 1).update(balance: Sequel[:balance] - 500_000)
  DB[:accounts].where(id: 2).update(balance: Sequel[:balance] + 500_000)
end

PostgreSQL ↔ Ruby Data Types #

PostgreSQL          Ruby (pg gem + type_map)    Description
────────────────────────────────────────────────────────────────
INTEGER / BIGINT    Integer                     Integer
SMALLINT            Integer
NUMERIC / DECIMAL   BigDecimal                  High precision
REAL / FLOAT8       Float                       Floating point
BOOLEAN             true / false                Native boolean
TEXT / VARCHAR      String
CHAR(n)             String                      Fixed-length
BYTEA               String (encoding: BINARY)   Binary data
UUID                String                      UUID string
DATE                Date                        Date only
TIMESTAMP           Time                        Local time
TIMESTAMPTZ         Time (with offset)          Time with TZ
INTERVAL            String / PG::Interval       Time duration
JSON                String                      Raw JSON
JSONB               String → parse to Hash      Indexed JSONB
TEXT[]              Array of String             Native array
INTEGER[]           Array of Integer
HSTORE              Hash                        Key-value store
TSVECTOR            String                      Full-text vector
POINT / LINE        PG::Point, etc.             Geometry types
INET / CIDR         String                      IP address / network

Summary #

exec_params not exec — always use exec_params with $1, $2, ... placeholders for any input you don’t control; this prevents SQL injection and is more efficient because PostgreSQL can cache the execution plan.
RETURNING * for data after INSERT/UPDATE — PostgreSQL supports RETURNING, which returns the newly modified row; far cleaner than a separate SELECT after INSERT.
Type mapping for automatic conversion — conn.type_map_for_results = PG::BasicTypeMapForResults.new(conn) so integers, booleans, and floats don’t need manual conversion from String.
JSONB for semi-structured data — store attributes that vary per record as JSONB; it can be indexed with GIN and queried with the ->, ->>, and @> operators.
Native arrays beat junction tables for simple data — tags TEXT[] is faster for small arrays that don’t need complex relations.
COPY for bulk imports — 100x faster than one-by-one INSERT for thousands of rows; use it for seed data, migrations, or CSV imports.
LISTEN/NOTIFY for real-time events — combine with database triggers for cache invalidation or WebSocket broadcasts without needing Redis Pub/Sub for simple cases.
GIN indexes for JSONB, arrays, and full-text search — B-tree isn’t effective for these data types; use add_index :table, :column, using: :gin.
Partial indexes for frequently filtered columns — WHERE active = true on an index drastically reduces its size if most data is filtered.
EXPLAIN ANALYZE before deploying — analyze the execution plan for the queries you write; a Seq Scan on a large table is a signal that you need an index.

← Previous: Oracle Next: MongoDB →