Module: Pcrd::Demo::Schema

Defined in:

lib/pcrd/demo/schema.rb

Overview

DDL for the demo source database.

The listings table is intentionally ordered to maximize padding waste —booleans and smallints are interleaved with 8-byte types (timestamps, double precision) throughout the column list. This creates a compelling before/after for ‘pcrd analyze`.

The migration target will reorder columns optimally (bigint IDs, 8-byte types first, then 4-byte, then 2-byte, then 1-byte, then variable).

Constant Summary

DROP_SQL =

<<~SQL.freeze
  DROP TABLE IF EXISTS listings CASCADE;
  DROP TABLE IF EXISTS agents   CASCADE;
  DROP TABLE IF EXISTS users    CASCADE;
SQL

LISTINGS_FK_DDL =

Column ordering is deliberately poor to demonstrate padding analysis: booleans and smallints scattered among 8-byte types waste ~22 bytes/row. FK from listings → agents; executed after both tables are created.

<<~SQL.freeze
  ALTER TABLE listings
    ADD CONSTRAINT listings_agent_fk
    FOREIGN KEY (agent_id) REFERENCES agents(id);
SQL

LISTINGS_DDL =

<<~SQL.freeze
  CREATE TABLE listings (
    id                  integer           NOT NULL GENERATED ALWAYS AS IDENTITY,
    active              boolean           NOT NULL DEFAULT true,
    list_price          numeric(10,2)     NOT NULL,
    bedrooms            smallint,
    has_garage          boolean           NOT NULL DEFAULT false,
    listed_at           timestamp         NOT NULL DEFAULT now(),
    square_feet         integer,
    is_featured         boolean           NOT NULL DEFAULT false,
    price_per_sqft      real,
    longitude           double precision,
    year_built          smallint,
    created_at          timestamp         NOT NULL DEFAULT now(),
    bathrooms           smallint,
    is_new_construction boolean           NOT NULL DEFAULT false,
    latitude            double precision,
    description         text,
    address_line1       varchar(255)      NOT NULL,
    city                varchar(100)      NOT NULL,
    state_code          char(2)           NOT NULL,
    zip_code            varchar(10),
    agent_id            integer,
    PRIMARY KEY (id)
  );
SQL

USERS_DDL =

<<~SQL.freeze
  CREATE TABLE users (
    id          integer       NOT NULL GENERATED ALWAYS AS IDENTITY,
    is_admin    boolean       NOT NULL DEFAULT false,
    email       varchar(255)  NOT NULL,
    first_name  varchar(100),
    last_name   varchar(100),
    created_at  timestamp     NOT NULL DEFAULT now(),
    PRIMARY KEY (id),
    UNIQUE (email)
  );
SQL

AGENTS_DDL =

<<~SQL.freeze
  CREATE TABLE agents (
    id              integer       NOT NULL GENERATED ALWAYS AS IDENTITY,
    user_id         integer       NOT NULL REFERENCES users(id),
    license_number  varchar(50),
    active          boolean       NOT NULL DEFAULT true,
    commission_rate numeric(5,4)  NOT NULL DEFAULT 0.0300,
    hired_at        date,
    created_at      timestamp     NOT NULL DEFAULT now(),
    PRIMARY KEY (id)
  );
SQL

SAMPLE_CONFIG =

A sample pcrd.config.yml that works with the demo schema. Written to disk by ‘pcrd demo setup` if no config exists.

<<~YAML.freeze
  # pcrd.config.yml — generated by `pcrd demo setup`
  #
  # Source: the original database (running demo schema)
  # Target: the new cluster with the improved schema

  source:
    host: localhost
    port: 5433
    database: pcrd_source
    user: postgres
    password: postgres

  target:
    host: localhost
    port: 5434
    database: pcrd_target
    user: postgres
    password: postgres

  migrate:
    tables:
      - name: users
        columns:
          id:
            type: bigint

      - name: agents
        columns:
          id:
            type: bigint
          user_id:
            type: bigint
          commission_rate:
            type: numeric(7,4)

      - name: listings
        optimize_column_order: true
        columns:
          id:
            type: bigint
          agent_id:
            type: bigint
          list_price:
            type: numeric(18,4)
            rename: list_price_precise
          listed_at:
            type: timestamptz
          created_at:
            type: timestamptz
        add_columns:
          - name: updated_at
            type: timestamptz
            default: "now()"

  analyze:
    tables:
      - listings
      - users
      - agents

  verify:
    sample_size: 1000

  cutover:
    sequence_buffer: 1000
YAML