Data Modeling and Schema Design Questions

Partitioning by date improves performance by pruning irrelevant partitions, improving I/O, and enabling parallel processing and easier maintenance.
For 10 years of daily transaction lines use RANGE partitioning by month (e.g., partition per month) or per-day if query patterns require very fine pruning. Monthly partitions balance number of partitions (~120) vs partition size; daily partitions (~3650) can be heavier to manage.
Scheme: RANGE on order_date using monthly boundaries. Benefits: most queries filter by month/day so planner prunes partitions; maintenance (drop/archive old partitions) is straightforward; backfill and bulk loads target single partitions. Use partitioned indexes or global indexes depending on DB. Choose daily only if queries frequently filter by exact day and single-day partitions are required for performance.

Direct answer

Storing created_at as TIMESTAMP WITHOUT TIME ZONE means the column silently records whatever local time the writer happened to be in, with no record of which timezone that was; the fix is to change the column to TIMESTAMPTZ and normalize all writes to UTC at ingestion, not to try to reconstruct timezone information after the fact from a column that never captured it.

Structured elaboration

• The risk across regions: if application servers in different regions write to this column using their own local clock (or worse, a mix of local time and UTC depending on which service wrote the row), two rows with the identical stored value can represent two genuinely different real-world instants, and there is no way to tell which is which after the fact, because the timezone information was never captured.
• Normalizing for analysis: the only fully correct fix is to know, for each historical row, what timezone or UTC offset it was actually written in; if that's recoverable (e.g., from a separate region column on the same table, or from deployment history correlated with created_at's range), you can backfill a corrected UTC value. If it's not recoverable, the historical data has an irreducible ambiguity that should be documented, not silently "fixed" with a guessed offset.
• Schema change vs. enforce-at-ingestion: the schema itself should change to TIMESTAMPTZ (which in Postgres always stores UTC internally and converts to/from a timezone only at display time), and the application layer must be fixed to always write in UTC (or with an explicit, correct offset) going forward; changing only the column type without fixing the writers just moves the same ambiguity into a differently-typed column.

Worked example

-- schema fix, step 1: correct any KNOWN non-UTC subset FIRST, while the column is still naive,
-- so the later blanket conversion doesn't reinterpret it a second time.
-- Reinterpret the naive value as the writer's actual local time, then re-express it as the
-- equivalent naive UTC wall-clock value (this two-step "AT TIME ZONE" is what actually corrects it):
UPDATE events
SET created_at = (created_at AT TIME ZONE 'America/Los_Angeles') AT TIME ZONE 'UTC'
WHERE region = 'us-west' AND created_at < '2026-03-01';

-- schema fix, step 2: now that every row's naive value is genuinely UTC, do the blanket type change
ALTER TABLE events ALTER COLUMN created_at TYPE TIMESTAMPTZ USING created_at AT TIME ZONE 'UTC';

The USING clause is doing real work here: TIMESTAMP WITHOUT TIME ZONE AT TIME ZONE 'UTC' reinterprets the naive timestamp as if it were UTC and produces a correct TIMESTAMPTZ; if the true original offset was NOT UTC for some rows, that reinterpretation is silently wrong for exactly those rows, which is why any known region-specific writer needs its own explicit correction pass, run BEFORE the blanket conversion, not after. Running the targeted correction after the column is already TIMESTAMPTZ does not fix it: confirmed by executing both orderings, converting a naive '2026-02-15 10:00:00' value that was actually written in America/Los_Angeles (true UTC instant 2026-02-15 18:00:00+00) using the blanket-first, correct-second order in this answer's original text produced 2026-02-15 02:00:00+00, which is wrong in the opposite direction; running the correction first, as shown above, produced the correct 2026-02-15 18:00:00+00.

Trade-offs and pitfalls

• The single most dangerous mistake here is applying one blanket AT TIME ZONE conversion to the whole table when the true originating offset actually varied by region or by writer over time; that "fixes" the type but silently bakes in a wrong absolute time for every row that wasn't actually UTC to begin with.
• A closely related mistake is getting the ORDER of operations backwards: any known region-specific correction must be applied to the still-naive column before the blanket ALTER ... USING ... AT TIME ZONE 'UTC' runs, not after. Once the blanket conversion has already relabeled every naive value as UTC, a single further AT TIME ZONE step applied to the resulting TIMESTAMPTZ does not correctly undo and reapply the right offset; it needs an explicit reversal step, so it is simpler and less error-prone to fix known-wrong subsets first, while they're still naive, and only then run the one blanket conversion.
• If the true originating offset for some historical rows is genuinely unrecoverable, converting them anyway to satisfy the new column type without flagging the uncertainty produces confidently-wrong data, which is worse than leaving a documented gap; a data_quality_note or a separate timezone_confidence flag on the affected range is more honest.
• Going forward, enforcing UTC at the application/ingestion layer (not just at the database schema level) is what actually prevents recurrence; a schema type change alone doesn't stop a misconfigured writer from producing a naive local timestamp that then gets miscast as if it were UTC.

Problem: many LEFT JOINs with nullable surrogate FKs causing repeated NULLs and heavy joins. Schema fixes: 1) Replace nullable FK patterns by introducing a canonical 'unknown' sentinel row in the master table and default FK to that non-null value—removes NULLs and simplifies joins. 2) Denormalize frequently used master attributes into dimension tables or a flattened reporting table. 3) Create materialized views or precomputed denormalized reporting tables to avoid repeated joins. Query-level optimizations: 1) Use indexed partial joins or filtered indexes on non-sentinel keys. 2) Rewrite LEFT JOINs to INNER JOIN where FK guaranteed by ETL, or use COALESCE(master.attr, '<unknown>') to avoid null proliferation. 3) Use bitmap or covering indexes for common predicates. Result: fewer expensive LEFT JOINs, simpler queries, and improved planner estimates leading to faster execution.

Direct answer

A composite primary key works by combining two or more columns so that the combination is guaranteed unique, even if no single column is; here (order_id, product_id) guarantees a given product appears at most once per order, while still letting the same product appear on many different orders and the same order contain many different products.

Structured elaboration

CREATE TABLE order_items (
  order_id   INT NOT NULL,
  product_id INT NOT NULL,
  quantity   INT NOT NULL,
  price      NUMERIC(10,2) NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

Joining to orders and products is unaffected by the key being composite: orders joins on order_id, products joins on product_id, exactly as it would with a single-column key:

SELECT o.order_id, p.name, oi.quantity, oi.price
FROM order_items oi
JOIN orders   o ON o.order_id   = oi.order_id
JOIN products p ON p.product_id = oi.product_id;

Worked example

Verified in sqlite3: inserting (order_id=1, product_id=100) twice (say, two separate lines both adding the same product to the same order) fails on the second insert with a primary-key violation, confirming the composite key correctly prevents a duplicate line for the same product on the same order. If the business actually wants to allow two separate lines for the same product on the same order (for example, two different discount codes applied to two units of the same product), that's exactly when you'd prefer a surrogate order_item_id instead: the composite key as given structurally assumes one row per (order, product) pair, which is a real business rule, not just an implementation detail.

Trade-offs and pitfalls

• The composite key here is enforcing a genuine business rule (one line per product per order), not just providing uniqueness for its own sake; if that rule is ever wrong (the two-discount-codes scenario above, or wanting separate lines for auditability), the fix is a surrogate order_item_id plus, if the one-line-per-product rule still needs to hold in the common case, a separate UNIQUE(order_id, product_id) constraint that can be relaxed later without changing the primary key.
• A common mistake is adding a surrogate key "just in case" without checking whether the composite key was already expressing a real invariant worth keeping; dropping the composite uniqueness silently allows the exact duplicate-line bug the original design prevented.
• At scale, a composite key clusters the table by order_id first (in engines that physically order by the primary key), which is usually the right locality for "all items in this order," but means "all orders containing this product" needs a secondary index on product_id alone rather than relying on the primary key.

Problem: top-10 products by avg rating last 30 days under heavy writes. Goals: fast aggregate reads without slowing writes.
Schema changes and indexes:

• Add a write-optimized summary table reviews_agg(product_id, window_day date, review_count int, rating_sum int, rating_avg float) updated incrementally.
• Maintain recent-window rolling buckets, e.g., daily or hourly buckets, then compute 30-day averages by aggregating these buckets.
• For low-latency leaderboard, maintain a materialized view or in-memory cache (Redis/KeyDB) keyed by day and product with sorted sets for top-N.
  Implementation:
• On insert of review, write to reviews table (append-only) and asynchronously push a lightweight event to a background worker/queue (Kafka/RabbitMQ).
• Worker updates reviews_agg: increment count and sum for current day using atomic DB statements (UPSERT) or update via idempotent increments.
  Indexes:
• Primary key on reviews_agg(product_id, window_day) for fast upsert.
• Index on reviews_agg(window_day, rating_avg DESC) to compute top-N per day; or maintain precomputed leaderboard in cache (sorted set by avg).
  Why this helps:
• Heavy writes: main write stays append-only with minimal synchronous work; aggregation is handled asynchronously, avoiding write contention and expensive full-table scans.
• Aggregation: computing top-10 for last 30 days becomes a small aggregation over 30 rows per product (or precomputed daily scores), or merging top lists from cache shards.
  Edge cases:
• Ensure idempotency and eventual consistency; provide fallback exact query (slower) if aggregator lag is unacceptable. Use background workers with retries and metrics.