ESC
Type to search guides, tutorials, and reference documentation.

Data Warehousing

Columnar storage and data skipping, dimensional modelling, grain, slowly changing dimensions, the semantic layer, lakehouse tradeoffs and the failure modes that produce wrong totals.

When your analytical workloads need to scan historical data without impacting operational systems, a data warehouse is the appropriate layer. It stores structured data optimized for read-heavy, aggregate-heavy access patterns—typically across months or years—rather than the row-level writes of an OLTP database. This matters when reporting queries are slowing down your production database, when you need to retain years of history without bloating transaction tables, or when you're building dashboards that scan billions of rows. The sibling pages cover the surrounding layers: Architecture for high-level component relationships, Dba for day-to-day database administration, Pipelines for the movement of data into the warehouse, and Streaming for real-time event flows.

Creating the Schema

Data warehouses typically organize around a fact-and-dimension model. In PostgreSQL, you can create a partitioned fact table that separates storage by time ranges, which is the primary mechanism for query pruning and management:

CREATE SCHEMA analytics;
SET search_path TO analytics;

CREATE TABLE analytics.sales (
    sale_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    customer_id BIGINT NOT NULL,
    sale_time TIMESTAMPTZ NOT NULL,
    amount NUMERIC(12,2) NOT NULL,
    quantity INTEGER NOT NULL
) PARTITION BY RANGE (sale_time);

After creating the partitioned table, you add partition ranges. Each partition lives as a separate physical table, and the planner prunes irrelevant partitions when queries filter on the partitioning column:

CREATE TABLE analytics.sales_2024_01 PARTITION OF analytics.sales
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE analytics.sales_2024_02 PARTITION OF analytics.sales
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

Choosing the distribution key poorly is a common root cause of performance issues. If the key has low cardinality—such as region with only a handful of values—one node or partition processes a disproportionate share of the work, creating skew. Swap the distribution key to a high-cardinality column like user_id or transaction_id to distribute load evenly. Conversely, if the warehouse uses a shared-nothing architecture with a hash distribution, an evenly distributed key is essential; a poorly chosen key produces the same skew problem regardless of partitioning.

Bulk Loading Data

Analytical workloads ingest data in batches, making the COPY command the standard mechanism for bulk loads. The psql client reads from a file or stdin and pushes data directly into the table, bypassing the transaction overhead of individual INSERT statements:

psql -h warehouse -U admin -d analytics -c \
  "COPY analytics.sales FROM '/data/sales_2024.csv' \
   WITH (FORMAT csv, HEADER true, DELIMITER ',')"

If the source file resides on the client machine rather than the warehouse host, psql's \copy meta-command copies from the local filesystem:

\copy analytics.sales FROM '/local/path/sales_2024.csv' CSV HEADER

A frequent failure mode is omitting HEADER true when the CSV includes a column-names row. The importer treats the first data row as column names, shifting all values and producing either a column-count mismatch error or silently corrupted data. Another common issue is column-order mismatch between the file and the table definition, which triggers ERROR: column count mismatch without indicating the specific row at fault.

For production-scale loads where the file is remote or transformation is needed, many teams use orchestration tools (Airflow, Prefect, Dagster) that invoke platform-specific loaders—bq load for BigQuery, COPY INTO for Snowflake—but the SQL COPY syntax is portable across PostgreSQL-based warehouses and remains the fallback for ad-hoc or smaller batches.

Querying for Analysis

Warehouse queries scan many rows and compute aggregates rather than fetching a single row by primary key. Window functions are the primary tool for analytical calculations such as running totals, percentiles, and year-over-year comparisons.

To compute daily revenue and order counts filtered to a date range:

SELECT 
    date_trunc('day', sale_time) AS day,
    sum(amount) AS daily_revenue,
    count(*) AS order_count
FROM analytics.sales
WHERE sale_time >= '2024-01-01' AND sale_time < '2024-02-01'
GROUP BY day
ORDER BY day;

Filtering with WHERE before GROUP BY reduces the row set the aggregation operates on, which is critical for performance on large tables.

For a running total of revenue ordered by time:

SELECT 
    sale_id,
    sale_time,
    amount,
    sum(amount) OVER (ORDER BY sale_time ROWS UNBOUNDED PRECEDING) AS running_total
FROM analytics.sales
WHERE sale_time >= '2024-01-01'
ORDER BY sale_time;

The ORDER BY inside the window specification is required; omitting it produces undefined results across runs. ROWS UNBOUNDED PRECEDING frames the window from the first row through the current row. For frame types that handle ties differently, RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW includes all rows with the same ordering value, which changes the result when the ordering column has duplicates.

Materialized Views for Repeated Queries

Queries that run on a schedule—daily revenue, month-to-date totals, KPI snapshots—benefit from materialized views, which store the result set physically and refresh on a schedule rather than recomputing each execution. In PostgreSQL:

CREATE MATERIALIZED VIEW analytics.daily_revenue AS
SELECT 
    date_trunc('day', sale_time) AS day,
    sum(amount) AS daily_revenue,
    count(*) AS order_count
FROM analytics.sales
GROUP BY day
WITH DATA;

To refresh the view:

REFRESH MATERIALIZED VIEW analytics.daily_revenue;

A sharp edge: if the underlying table receives heavy concurrent writes during refresh, the command can stall or raise snapshot isolation errors. REFRESH MATERIALIZED VIEW CONCURRENTLY builds the new version while serving the old one, but it takes longer and may not reflect the very latest data. Many teams schedule refreshes during low-write windows, or accept the staleness of CONCURRENTLY in exchange for uninterrupted service.

Partition Management and Vacuum

In time-partitioned warehouses, old partitions must be dropped or archived explicitly. PostgreSQL does not auto-prune partitions:

DROP TABLE analytics.sales_2023;

Before dropping, verify no active queries reference the partition; the drop will block or fail depending on concurrent activity. A related maintenance task is VACUUM, which reclaims space from deleted rows and updates visibility maps. In a warehouse that regularly drops entire partitions, a monthly VACUUM ANALYZE keeps the query planner's statistics accurate and ensures COUNT(*) and other aggregate estimates remain reliable.

The sharpest production edge in self-managed PostgreSQL warehouses is transaction ID wrap-around. PostgreSQL uses a 32-bit transaction ID counter; if too many transactions occur without vacuuming, the counter wraps, and the database becomes read-only until vacuum runs. This never occurs in pure columnar cloud warehouses, which manage transaction IDs differently, but it is a real and disruptive failure mode for on-premise or IaaS PostgreSQL deployments used as warehouses.

ETL/ELT with dbt

Many teams transform data inside the warehouse using dbt (data build tool), which lets you define models as SQL files, apply tests and documentation, and version-control the transformation logic. Real commands:

# Initialize a dbt project linked to the warehouse
dbt init my_warehouse

# Compile models (SQL + Jinja -> executable SQL)
dbt compile

# Run all models defined in models/
dbt run

# Test that models pass assertions defined in .yml files
dbt test

# Generate and serve documentation site
dbt docs generate && dbt docs serve

A model file lives in models/sales/revenue_daily.sql:

-- dbt model: revenue_daily
select
    date_trunc('day', sale_time) as day,
    sum(amount) as daily_revenue,
    count(*) as order_count
from {{ ref('stg_sales') }}
group by day
order by day

The {{ ref('stg_sales') }} macro resolves to a reference against another model or source, enabling dependency graphs. If models circularly reference each other, dbt run fails with Recursion detected in model graph, which is the tool's mechanism for catching infinite dependencies before they reach the warehouse.

Common Failure Modes

  • Skew: When the distribution key has low cardinality, one node or partition processes most of the work. Swap the key to a high-cardinality column like user_id or transaction_id. If the warehouse uses a shared-nothing hash distribution, an uneven key produces silent performance degradation that is difficult to diagnose without query plan analysis.
  • Temp space exhaustion: Large sorts and hashes spill to temporary storage. When the warehouse runs out of temp space, errors such as out of memory for query execution or could not extend temporary file: No space left on device appear. Pre-allocating temp disk, increasing the temp directory size, or rewriting the query to reduce the working set (earlier filtering, smaller aggregations) resolves the issue.
  • Schema drift: When a source system adds a column, the COPY command may reject the file if the column count doesn't match the table, or silently drop the new column if using INSERT ... SELECT without explicit column lists. Always specify columns: INSERT INTO analytics.sales (sale_id, product_id, sale_time, amount) SELECT ... rather than INSERT INTO analytics.sales SELECT .... This practice also makes the intent clear to future maintainers.
  • Cost overruns: In cloud warehouses, each scanned byte and each compute second incurs cost. A query missing a WHERE clause on the partitioning column can scan the entire history and produce a surprise bill. Before deploying analytical queries to production dashboards, run EXPLAIN ANALYZE and verify the query plan shows partition pruning (e.g., Partition excluded: sales_2023). If the plan scans all partitions, add or refine the filter predicate.

This page was rewritten on 18 October 2026. It replaced a templated version that was not specific to its own title. How these pages are written and checked. If anything here is wrong, tell us at [email protected] and we will correct it.