Redshift Partitioned Tables Explained: Spectrum, Partition Pruning, and Best Practices

What partitioned tables are

A partitioned table is a table whose underlying data is physically split into separate storage locations based on the values of one or more columns — the partition keys. When you run a query that filters on a partition key, the database skips storage locations that cannot contain matching rows. This is partition pruning.

In Redshift, partitioned tables are a Redshift Spectrum concept. You cannot partition a regular managed Redshift table. Partition pruning in Spectrum operates at the S3 folder level: whole folders are eliminated before Spectrum reads a single byte of data.

Sort keys are an entirely separate optimisation for internal managed tables. They sort rows on disk so that 1 MB blocks can be skipped using zone map metadata. Sort keys and partitioned tables apply to different table types and operate at different storage layers.

What “partitioned tables” means in Redshift

Amazon Redshift has two modes of querying data: managed tables (data loaded into Redshift’s own columnar storage) and external tables via Redshift Spectrum (data queried directly from S3 without loading it first). Partitioned tables are an external table feature.

External tables are defined in the AWS Glue Data Catalog, which acts as a metadata store. The actual rows live in S3. When you define an external table as partitioned, the Glue catalog stores the mapping from each partition key value to the corresponding S3 folder prefix. Spectrum reads those mappings at query time to decide which folders to open.

This pattern is central to data lake architectures on AWS: raw data lands in S3 in organised prefixes, and you query it in place without the cost and latency of a full COPY load into Redshift’s managed storage.

How partition pruning works

When Redshift Spectrum executes a query against an external table, it first checks the Glue Data Catalog for the list of registered partitions. If your query includes a WHERE clause on a partition column such as WHERE year = 2024 AND month = 6, Spectrum eliminates every partition whose key values don’t match. Only the matching S3 folders are opened and read.

This matters because Redshift Spectrum charges $5 per terabyte scanned from S3. A table with two years of daily partitions has over 700 partitions. A query filtered to a single month prunes roughly 96% of partitions and 96% of the scan cost before a single file is opened.

A practical example from scratch

Step 1 — Organise S3 data using Hive-style paths

Data written into S3 using Hive-style key=value folder names is recognised automatically by Glue crawlers, removing the need to register partitions by hand. A typical layout for a daily events dataset:

s3://my-data-lake/app-events/year=2024/month=01/day=01/events.parquet
s3://my-data-lake/app-events/year=2024/month=01/day=02/events.parquet
s3://my-data-lake/app-events/year=2024/month=02/day=01/events.parquet
s3://my-data-lake/app-events/year=2025/month=01/day=01/events.parquet

Step 2 — Create an external schema pointing to Glue

Before creating external tables, create an external schema in Redshift that maps to a Glue Data Catalog database. The IAM role attached to the cluster needs S3 read and Glue catalog access:

CREATE EXTERNAL SCHEMA spectrum
FROM DATA CATALOG
DATABASE 'analytics_db'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftSpectrumRole'
CREATE EXTERNAL DATABASE IF NOT EXISTS;

Step 3 — Create the partitioned external table

Partition columns go in the PARTITIONED BY clause, not in the main column list. They are derived from the folder path at query time, not stored in the data files:

CREATE EXTERNAL TABLE spectrum.app_events (
  event_id   BIGINT,
  user_id    BIGINT,
  event_type VARCHAR(50),
  page_url   VARCHAR(500)
)
PARTITIONED BY (year INT, month INT, day INT)
STORED AS PARQUET
LOCATION 's3://my-data-lake/app-events/';

Step 4 — Register a partition

After writing data to a new S3 folder, register the partition in the Glue catalog. Without this step, the data exists in S3 but is completely invisible to queries:

ALTER TABLE spectrum.app_events
ADD PARTITION (year=2024, month=1, day=1)
LOCATION 's3://my-data-lake/app-events/year=2024/month=1/day=1/';

Step 5 — Query with a partition filter

A query that filters on partition columns triggers pruning. The query below reads only the June 2024 folders. All other year and month partitions are skipped:

SELECT
  event_type,
  COUNT(*) AS event_count
FROM spectrum.app_events
WHERE year = 2024
  AND month = 6
GROUP BY event_type
ORDER BY event_count DESC;

When to use partitioned tables

• Large data lakes in S3. Any Spectrum external table over a few GB benefits from partitioning, as long as queries include a filter on the partition column.
• Time-series and event data. Application logs, clickstream events, IoT telemetry, and audit trails almost always include a date dimension. Year, month, and day are natural partition keys for this data.
• Log archives. Historical log data that you query occasionally but rarely need in full is a textbook case. Partition by date and queries for a specific period only read that period’s folders.
• Multi-tenant data. If tenant or region data lives in separate S3 folders for access-control or billing reasons, partitioning by that dimension gives you folder-level isolation with no query overhead when filtering by it.

When not to use partitioned tables

• Small tables. A dataset under a few hundred MB scans in seconds regardless of partitioning. Managing partition registration adds complexity with no meaningful return.
• Queries that never filter by the partition column. If every query does a full table scan with no date filter, partition pruning never fires and you still pay to scan the full dataset.
• High-cardinality partition keys. Partitioning by user_id, session_id, or any column with millions of distinct values creates millions of tiny partitions. Glue catalog metadata overhead and S3 small-file proliferation hurt performance rather than help it.
• Data that belongs in managed Redshift storage. If your working dataset is small enough to load into Redshift’s columnar storage, load it there instead. Sort keys and distribution keys will outperform Spectrum for that use case. See Loading Data into Redshift for when COPY is the better choice.

Choosing a good partition key

Time-based keys

Year, month, and day are the most common partition keys because most analytical queries include a date range filter. Partitioning at the day level works well when queries typically target a week or month of data. Partitioning at the month level is sufficient if your queries never narrow below a full month.

Cardinality and granularity

The right granularity is the coarsest level at which pruning still removes meaningful data. If every query spans at least a full day, partitioning by hour adds 24x the number of partitions without improving how much data gets skipped.

Very fine-grained partitioning also creates practical problems:

• Thousands of small files mean slow S3 LIST operations and Glue catalog lookups
• Parquet and ORC files below roughly 128 MB compress and encode poorly because there are not enough rows per file for the columnar format to be efficient
• Redshift Spectrum has a default limit of 10,000 partitions scanned per query, and highly partitioned tables can hit this ceiling

Low vs high cardinality

Good partition keys have low-to-moderate cardinality: year (a handful of values), month (12 per year), region (5 to 20 values). Columns with millions of distinct values such as order IDs or hashed identifiers are poor partition keys because the metadata overhead outweighs any pruning benefit.

Partitioned tables vs sort keys

Both can speed up date-range queries, which is why beginners often conflate them. They operate at completely different layers and are not interchangeable. For a full explanation of sort keys, VACUUM, and managed table tuning, see Redshift Performance Optimisation.

Best practices

• Use Hive-style partition paths. Writing data to year=2024/month=06/day=15/ prefixes means Glue crawlers can auto-discover and register new partitions without manual ALTER TABLE calls. Schedule the crawler to run after each data load and the catalog stays in sync automatically.
• Match partition granularity to your actual query patterns. If no query ever filters below the month level, partitioning by day adds 30x the number of partitions without any additional pruning benefit. Start at a coarser level and add granularity only if you have a specific need.
• Store data in Parquet or ORC. Both are columnar formats with built-in compression. They compound the savings from partition pruning: pruning skips folders, and column projection reduces the bytes read within the folders that remain. See ETL vs ELT for how a Glue job can convert CSV source data into Parquet as part of your load pipeline.
• Treat partition registration as a required pipeline step. Whether you use a Glue crawler, a Lambda trigger, or an ALTER TABLE call in a Step Functions workflow, register partitions immediately after writing data to S3. It is not optional.
• Monitor Spectrum scan volume. Query SVL_S3QUERY_SUMMARY in Redshift to see bytes scanned per Spectrum query. Unexpectedly high scan bytes almost always means a missing partition filter or a type mismatch between the filter value and the partition column type. This connects directly to Redshift pricing, where Spectrum scan costs are the most variable component of your bill.
• Decide upfront whether data belongs in Spectrum or managed storage. Querying the same dataset via Spectrum and also loading it into managed Redshift doubles your storage cost. The data warehouse vs data lake framing helps: large historical archives stay in S3 as Spectrum external tables; active working data gets loaded into managed storage where sort keys and distribution keys apply.