Tag: PostgreSQL

For a Ruby on Rails 8 application, the choice of database depends on your specific needs, but here’s a breakdown of the best options and when to use each:

PostgreSQL (Highly Recommended)

Best overall choice for most Rails apps.

Why:

• First-class support in Rails.
• Advanced features like full-text search, JSONB support, CTEs, window functions.
• Strong consistency and reliability.
• Scales well vertically and horizontally (with tools like Citus).

• Used by: GitHub, Discourse, Basecamp, Shopify.

Use if:

• You’re building a standard Rails web app or API.
• You need advanced query features or are handling complex data types (e.g., JSON).

SQLite (For development/testing only)

• Lightweight, file-based.
• Fast and easy to set up.
• But not recommended for production.

Use if:

• You’re building a quick prototype or local dev/testing app.
• NOT for multi-user production environments.

MySQL / MariaDB

• Also supported by Rails.
• Can work fine for simpler applications.
• Lacks some advanced features (like robust JSON support or full Postgres-style indexing).
• Not the default in many modern Rails setups.

Use if:

• Your team already has MySQL infrastructure or legacy systems.
• You need horizontal scaling with Galera Cluster or similar setups.

Others (NoSQL like MongoDB, Redis, etc.)

• Use Redis for caching and background job data (not as primary DB).
• Use MongoDB or other NoSQL only if your data model really demands it (e.g., unstructured documents, event sourcing).

Recommendation Summary:

If you’re starting fresh or building something scalable and modern with Rails 8, go with PostgreSQL.

Let’s break that down:

💬 What does “robust JSON support” mean?

PostgreSQL supports a special column type: json and jsonb, which lets you store structured JSON data directly in your database — like hashes or objects.

Why it matters:

• You can store dynamic data without needing to change your schema.
• You can query inside the JSON using SQL (->, ->>, @>, etc.).
• You can index parts of the JSON — for speed.

🔧 Example:

You have a products table with a specs column that holds tech specs in JSON:

specs = {
  "color": "black",
  "brand": "Libas",
  "dimensions": {"chest": "34", "waist": "30", "shoulder": "13.5"}
}

You can query like:

SELECT * FROM products WHERE specs->>'color' = 'black';

Or check if the JSON contains a value:

SELECT * FROM products WHERE specs @> '{"brand": "Libas"}';

You can even index specs->>'color' to make these queries fast.

💬 What does “full Postgres-style indexing” mean?

PostgreSQL supports a wide variety of powerful indexing options, which improve query performance and flexibility.

⚙️ Types of Indexes PostgreSQL supports:

• B-Tree Indexes: B-tree indexes are more suitable for single-value columns.
• When to Use GIN Indexes: When you frequently search for specific elements within arrays, JSON documents, or other composite data types.
• Example for GIN Indexes: Imagine you have a table with a JSONB column containing document metadata. A GIN index on this column would allow you to quickly find all documents that have a specific author or belong to a particular category. 

Why does this matter for our shopping app?

• We can store and filter products with dynamic specs (e.g., kurtas, shorts, pants) without new columns.
• Full-text search on product names/descriptions.
• Fast filters: color = 'red' AND brand = 'Libas' even if those are stored in JSON.
• Index custom expressions like LOWER(email) for case-insensitive login.

💬 What are Common Table Expressions (CTEs)?

CTEs are temporary result sets you can reference within a SQL query — like defining a mini subquery that makes complex SQL easier to read and write.

WITH recent_orders AS (
  SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '7 days'
)
SELECT * FROM recent_orders WHERE total > 100;

• Breaking complex queries into readable parts.
• Re-using result sets without repeating subqueries.

In Rails (via with from gems like scenic or with_cte):

Order
  .with(recent_orders: Order.where('created_at > ?', 7.days.ago))
  .from('recent_orders')
  .where('total > ?', 100)

💬 What are Window Functions?

Window functions perform calculations across rows related to the current row — unlike aggregate functions, they don’t group results into one row.

🔧 Example: Rank users by their score within each team:

SELECT
  user_id,
  team_id,
  score,
  RANK() OVER (PARTITION BY team_id ORDER BY score DESC) AS rank
FROM users;

Use cases:

• Ranking rows (like leaderboards).
• Running totals or moving averages.
• Calculating differences between rows (e.g. “How much did this order increase from the last?”).

🛤 In Rails:

Window functions are available through raw SQL or Arel. Here’s a basic example:

User
  .select("user_id, team_id, score, RANK() OVER (PARTITION BY team_id ORDER BY score DESC) AS rank")

CTEs and Window functions are fully supported in PostgreSQL, making it the go-to DB for any Rails 8 app that needs advanced querying.

JSONB Support

JSONB stands for “JSON Binary” and is a binary representation of JSON data that allows for efficient storage and retrieval of complex data structures.

This can be useful when you have data that doesn’t fit neatly into traditional relational database tables, such as nested or variable-length data structures.

Absolutely — storing JSON in a relational database (like PostgreSQL) can be super powerful when used wisely. It gives you schema flexibility without abandoning the structure and power of SQL. Here are real-world use cases for using JSON columns in relational databases:

Here are real-world use cases for using JSON columns in relational databases:

🔧 1. Flexible Metadata / Extra Attributes

Let users store arbitrary attributes that don’t require schema changes every time.

Use case: Product variants, custom fields

t.jsonb :metadata

{
  "color": "red",
  "size": "XL",
  "material": "cotton"
}

=> Good when:

• You can’t predict all the attributes users will need.
• You don’t want to create dozens of nullable columns.

🎛️ 2. Storing Settings or Preferences

User or app settings that vary a lot.

Use case: Notification preferences, UI layout, feature toggles

{
  "email": true,
  "sms": false,
  "theme": "dark"
}

=> Easy to store and retrieve as a blob without complex joins.

🌐 3. API Response Caching

Store external API responses for caching or auditing.

Use case: Storing Stripe, GitHub, or weather API responses.

t.jsonb :api_response

=> Avoids having to map every response field into a column.

📦 4. Storing Logs or Events

Use case: Audit trails, system logs, user events

{
  "action": "login",
  "timestamp": "2025-04-18T10:15:00Z",
  "ip": "123.45.67.89"
}

=> Great for capturing varied data over time without a rigid schema.

📊 6. Embedded Mini-Structures

Use case: A form builder app storing user-created forms and fields.

{
  "fields": [
    { "type": "text", "label": "Name", "required": true },
    { "type": "email", "label": "Email", "required": false }
  ]
}

=> When each row can have nested, structured data — almost like a mini-document.

🕹️ 7. Device or Browser Info (User Agents)

Use case: Analytics, device fingerprinting

{
  "browser": "Safari",
  "os": "macOS",
  "version": "17.3"
}

=> You don’t need to normalize or query this often — perfect for JSON.

JSON vs JSONB in PostgreSQL

Use jsonb over json unless you need to preserve order or whitespace.

• jsonb is binary format → faster and indexable
• You can do fancy stuff like:

SELECT * FROM users WHERE preferences ->> 'theme' = 'dark';

Or in Rails:

User.where("preferences ->> 'theme' = ?", 'dark')

store and store_accessor

They let you treat JSON or text-based hash columns like structured data, so you can access fields as if they were real database columns.

🔹 store

• Used to declare a serialized store (usually a jsonb, json, or text column) on your model.
• Works best with key/value stores.

👉 Example:

Let’s say your users table has a settings column of type jsonb:

# migration
add_column :users, :settings, :jsonb, default: {}

Now in your model:

class User < ApplicationRecord
  store :settings, accessors: [:theme, :notifications], coder: JSON
end

You can now do this:

user.theme = "dark"
user.notifications = true
user.save

user.settings
# => { "theme" => "dark", "notifications" => true }

🔹 store_accessor

A lightweight version that only declares attribute accessors for keys inside a JSON column. Doesn’t include serialization logic — so you usually use it with a json/jsonb/text column that already works as a Hash.

👉 Example:

class User < ApplicationRecord
  store_accessor :settings, :theme, :notifications
end

This gives you:

• user.theme, user.theme=
• user.notifications, user.notifications=

🤔 When to Use Each?

If you’re using PostgreSQL with jsonb columns — it’s more common to just use store_accessor.

Querying JSON Fields

User.where("settings ->> 'theme' = ?", "dark")

Or if you’re using store_accessor:

User.where(theme: "dark")

💡 But remember: you’ll only be able to query these fields efficiently if you’re using jsonb + proper indexes.

🔥 Conclusion:

• PostgreSQL can store, search, and index inside JSON fields natively.
• This lets you keep your schema flexible and your queries fast.
• Combined with its advanced indexing, it’s ideal for a modern e-commerce app with dynamic product attributes, filtering, and searching.

To install and set up PostgreSQL on macOS, you have a few options. The most common and cleanest method is using Homebrew. Here’s a step-by-step guide: