The data warehouse in your email archive: teaching DuckDB to speak Outlook

Despite the advent of Slack, Teams, and other collaboration tools, email remains the communications backbone of the business world. It is estimated that 80% of Fortune 500 companies use Microsoft 365, which means that Exchange and Outlook have been quietly archiving nearly 30 years of institutional knowledge.

Somewhere in your organization’s file server may lie dozens…or even hundreds…or even thousands of PST files. PST is the file format used by Outlook/Exchange to write archives of messages, contacts, and more. Maybe they are the archives of departed employees. Maybe it’s retained compliance data. Or as it happens, maybe nobody remembers.

Unfortunately, these files are often (and incorrectly) binned as “unstructured data”. PSTs are not just structured, they are a small single-file database in and of themselves. But unlike your data warehouse, nobody is querying them. 30 years of business decisions, customer relationships, and insights just…sit there.

We decided to fix this by releasing duckdb-pst (free, open-source), and today we’ll show you how we did it!

What’s in a PST?

A PST file is more complex than you might expect: it’s a full-fledged storage system with multiple layers. For this article, we’re going to focus on the messaging layer to keep things digestible (a complete PST deep-dive could easily be several articles long). Read the official MS-PST spec for all the details.

The messaging layer exposes two main primitives: folders and messages. Folders create hierarchies (think: Inbox > Projects > Q4), while messages represent any “PIM” (Personal Information Management) object: emails, contacts, appointments, tasks, and more. Throughout this article, when we say “message”, we’re referring to any of these data types within the archive.

Simply put: to tabularize a PST, we need to crawl all folders and emit their messages as rows.

Reading a PST

There exist many commercial solutions for reading PST files, but our open source options are:

• libpst
• java-libpst
• microsoft-pst-sdk

We decided to go with microsoft-pst-sdk, as it was released by Microsoft in 2010 shortly after they made an official specification public. It has one killer (albeit incomplete) feature the other libraries lack: write support. While we are not going to make use of it in this article, we liked the idea of having a foundation for pursuing writes in the future.

Why DuckDB?

Any data architect will tell you that choosing a database is about tradeoffs. DuckDB is novel in its packaging: it’s as portable as SQLite (single process, no server), but delivers read-heavy analytical query performance that rivals clustered solutions like Spark (even on terabyte-scale datasets). While this varies based on data, format, and scale – developers are finding that their “big data” analytical query can be served by their laptop (due to the overlap of modern hardware capabilities, and DuckDB’s excellent engineering).

Like Spark and other big data query engines, DuckDB can read ‘data lake’ file formats (e.g., Parquet) in-place and apply optimizations like column and row-group pruning (using statistics to skip irrelevant parts of files without loading them into memory). But unlike Spark, deployment is just “start DuckDB”.

This is a perfect fit for PST files: they are historical archives. They are immutable and do not change. Analysts & engineers should be able to query PSTs as they would any other data in their data lake: with SQL. They shouldn’t have to worry about building a bespoke import pipeline just to look for signal in these archives.

The vision is to run queries like this:

SELECT COUNT(*)
FROM read_pst_messages('enron/*.pst')
WHERE conversation_topic LIKE 'Financial%';

How we’ll build it

In this post, we’ll walk through building a production-ready DuckDB extension in three phases:

1. Getting Started (Hatch) - Setting up the development environment and scaffolding the extension
2. Basic Implementation (Waddle) - Implementing a working table function that reads PST files
3. Performance Optimizations (Fly) - Achieving 10-100x speedups through statistics pushdown, concurrent planning, and late materialization

By the end, you’ll understand both the DuckDB extension API and the optimization techniques that make analytical query engines fast.

Let’s start with setup.

Building the extension

Hatch: Getting Started

Before we can read PST files, we need to set up a development environment. DuckDB provides an extension template as a starting point that you can use to quickly scaffold a repo. The sample extension should build out of the box, and comes with a ScalarFunction example. The PST extension will be implemented as a TableFunction (we want to present PSTs as tables).

Here are some tips for scaffolding a new extension that you may find helpful:

• Make sure to follow the ‘speedy builds’ guidance regarding ccache and ninja. On macOS, this setup is pretty simple:

brew install ninja ccache
export CC="ccache clang -Qunused-arguments"
export CXX="ccache clang++ -Qunused-arguments"

• Set up clangd as your language server and add this CMake directive to CMakeLists.txt:

# Export compile_commands.json
set(CMAKE_EXPORT_COMPILE_COMMANDS ON)

• Build the Doxygen docs for DuckDB (inside the submodule duckdb/ dir, find them in build/docs):

cd duckdb/
doxygen .

Fossies also has a mirror of the API docs. They are very helpful for discovering how table functions are used and implemented in core extensions.

• You may want to use other extensions during development. The PST extension schema grew to have many columns, so we used duckdb-ui regularly to test changes to our serialization layer.

if(CMAKE_BUILD_TYPE STREQUAL "Debug")
    duckdb_extension_load(httpfs
        GIT_URL https://github.com/duckdb/duckdb-httpfs
        GIT_TAG main
        APPLY_PATCHES
    )
endif()

Waddle: Implementing the Table Function

With our environment set up, let’s implement the core table function. We’ll start with a basic implementation that reads PST files and outputs rows, then tackle schema handling, property reading, and string conversion challenges.

TableFunction

The basic flow for a TableFunction looks something like this:

• TableFunction::bind
  • Handle parameters, bind schema, and return a FunctionData
  • PST: Resolve files from the provided path, pack into bind data.
• TableFunction::cardinality
  • Given a bind data, provide a cardinality estimate for the number of rows to be output by the query.
  • PST: Count all of the node IDs in a file.
• TableFunction::init_global
  • Make a GlobalTableFunctionState shared by all threads during the table function read.
  • PST: A queue of files to process.
• TableFunction::init_local
  • Make a LocalTableFunctionState tracking a single thread’s progress.
  • PST: A queue of PST node IDs to process, which may represent a whole file or some part of it.
    • Think: parallelize reads of large files.
• TableFunction::function
  • Called repeatedly with a TableFunctionInput (bind data, global and local state) until 0 rows are emitted.
  • Rows are loaded into a DataChunk up to STANDARD_VECTOR_SIZE.
  • Columns are ordered in accordance to the output schema from the bind step.
  • PST: Spool the iterator into rows until it runs out. If the data chunk STANDARD_VECTOR_SIZE limit is reached, that’s ok: the same local state is used for subsequent invocations.

With a basic table function implemented, the last step is to call RegisterFunction via the extension loader. In order to support different schemas, we opted to register UDTFs with the naming scheme read_pst_{messages,folders,etc} instead of using a parameter.

We were quickly able to read the PST SDK unit test files. This is a screenshot of the first working build, which only read folders:

Schema Handling

The table function binding API gives you mutable refs to a vector<string> and vector<LogicalType> which positionally represent the output projection. You must append the names and types in order to tell DuckDB what your DataChunk will look like. We wished to reuse the same table function with several schemas (congruent to the underlying PST iterator type).

So we made a schema.hpp with our schemas, and used the StructType functions to iterate over the schema during binding.

static const auto FOLDER_SCHEMA = LogicalType::STRUCT({
    {"pst_path", LogicalType::VARCHAR},
    {"pst_name", LogicalType::VARCHAR},
    {"parent_folder_id", LogicalType::UINTEGER},
    {"folder_id", LogicalType::UINTEGER},
    {"folder_name", LogicalType::VARCHAR},
    {"subfolder_count", LogicalType::UINTEGER},
    {"message_count", LogicalType::BIGINT},
    {"unread_message_count", LogicalType::BIGINT},
});

Unfortunately, this alone didn’t improve our row serializer. It assumed the order of the projection and schema definition was the same, and still required us to identify columns by their positional ordinal. There will be hundreds of fields to serialize, so this design will not scale:

template <>
idx_t PSTConcreteIteratorState<pst::folder_iterator, folder>::emit_rows(DataChunk &output) {
    // ...
    output.SetValue(0, i, Value(current_file().path));
    output.SetValue(1, i, Value(utils::from_wstring(current_pst()->get_name())));
}

To solve the ordinal issue, we made an enum that corresponds to the StructType schema definition that we could use in our row serialization routine. Combined with X macros, we were able to come up with a solution that allowed us to arbitrarily move columns around as we saw fit, without having to worry about keeping the enum and schema declarations in sync:

#define SCHEMA_CHILD(name, type) {#name, type},
#define SCHEMA_CHILD_NAME(name, ...) name,

#define FOLDER_CHILDREN(LT)                                                    \
  LT(container_class, LogicalType::VARCHAR)                                    \
  LT(display_name, LogicalType::VARCHAR)                                       \
  LT(subfolder_count, LogicalType::UINTEGER)                                   \
  LT(message_count, LogicalType::BIGINT)                                       \
  LT(unread_message_count, LogicalType::BIGINT)

// PST_CHILDREN now contains the filename and other "pst-level" attributes
enum class FolderProjection {
  PST_CHILDREN(SCHEMA_CHILD_NAME) FOLDER_CHILDREN(SCHEMA_CHILD_NAME)
};

inline const auto FOLDER_SCHEMA = LogicalType::STRUCT(
    {PST_CHILDREN(SCHEMA_CHILD) FOLDER_CHILDREN(SCHEMA_CHILD)});

This helped clean up our serialization code a lot, and as a side-effect gave us our first optimization: projection pushdown! TableFunctionInitInput::column_ids is provided during state init, which has a list of column IDs that map a query’s projection to the table function’s declared schema (the binding operation at the beginning of this section):

auto schema_col = column_ids[col_idx];
switch (schema_col) {
    case static_cast<int>(schema::FolderProjection::container_class):
        output.SetValue(
            column_index, row_number,
            from_prop<std::string>(col_type, prop_bag, PR_CONTAINER_CLASS_A));
        break;
    case static_cast<int>(schema::FolderProjection::display_name):
        output.SetValue(
            column_index, row_number,
            from_prop<std::string>(col_type, prop_bag, PR_DISPLAY_NAME_A));
        break;
}

MAPI Properties

The PST SDK exposes a good starting set of attributes readable out of folders, messages, and recipients. We want to be as comprehensive as possible; to do so we have to read additional attributes. An example getter from pstsdk::message looks like this:

// property access
//! \brief Get the display name of the PST
//! \returns The display name
std::wstring get_name() const
    { return get_property_bag().read_prop<std::wstring>(0x3001); }

As it happens, 0x3001 actually has a name! The WINE project has a nice little “index” of all of these properties in mapitags.h. If we look for 0x3001:

#define PR_DISPLAY_NAME_W PROP_TAG(PT_UNICODE,0x3001)
#define PR_DISPLAY_NAME_A PROP_TAG(PT_STRING8,0x3001)

We modified mapitags.h and included it in the PST SDK, so we can easily grep for these properties and read them when they are needed. At this point we decided to instead couple row serialization to property bags directly. We generally know which runtime type the underlying property is (and if we don’t, we call get_prop_type), so we could specify it in the template argument for read_prop and be on our way (instead of having to write a bunch of getters). Another good reason to do this is outlined in the next section.

String Handling

PSTs are primarily read/written on Windows, whose native string encoding uses two byte wide chars (wchar_t). And so, we immediately encounter this upon calling the PST SDK constructor, which takes an std::wstring. Your *nix box’s libstd seemingly has wstrings, wchars, and all the rest – so what’s the big deal?

Many developers assume “Unicode” refers to UTF-8, but this is not accurate. In the *nix world, UTF-8 is the dominant character encoding. Each codepoint is 1 to 4 bytes long, depending on the character. Windows was one of the first operating systems to adopt Unicode, and did so with UCS-2, a fixed-width (2 byte) encoding. Therefore on Windows, wchar_t is 2 bytes. Later, Windows began supporting UTF-16LE, which also uses 2 byte wchars but is variable-width like UTF-8 and can support the full range of Unicode codepoints. To further complicate things, it is generally assumed that sizeof(wchar_t) is platform defined. So on Linux, it is (generally) 4 bytes. We recommend Joel’s blog post aptly titled The Absolute Minimum Every Software Developer Absolutely, Positively Must Know About Unicode and Character Sets (No Excuses!) for the full scoop.

Internally, a PST string property can be PT_UNICODE (wchar_t), or PT_STRING8 (char, multibyte), but the PST SDK returns all strings as std::wstring regardless of the underlying type:

// Recipient
std::wstring get_name() const;
std::wstring get_email_address() const;

DuckDB’s VARCHAR encoding is UTF-8, so all strings that make up data chunk rows must be encoded this way. A first instinct was to take wstrings from the SDK and convert them back to UTF-8 using std::codecvt. However, in the case that the underlying string is PT_STRING8, this results in unnecessary conversions (string -> wstring -> string).

The PST SDK already uses iconv for its bytes_to_wstring method to convert from WCHAR_T to UTF-16LE (with 2 byte code points, in the case that there are no surrogate pairs). We assembled a full solution by calling the property bag read_prop<std::string>(...) for properties we knew were strings, and then introduced a bytes_to_string method that also uses iconv to go from UTF-16LE to UTF-8. Like this, we only convert when needed!

Logging & Error Handling

DuckDB has a duckdb_logs view that we can append to at runtime. Many PST operations are fallible, it is possible that: the file is not readable, a property guaranteed to be there is missing, or the encoding of a property is not what it should be (some PST writers do not follow the spec). Table functions must bind all of their output columns with at least a Value(nullptr), or DuckDB will crash. This is a good first step to achieving data quality and being able to identify issues in specific archives and messages.

As long as you have a ClientContext or ExecutionContext available, you can call the logger and your messages should show up in the logs view:

DUCKDB_LOG_ERROR(
    local_state.ec, "Failed to read column: %s (%s)\nError: %s",
    StructType::GetChildName(output_schema, schema_col),
    StructType::GetChildType(output_schema, schema_col).ToString(),
    e.what());

D select * from duckdb_logs() limit 100;
┌────────────┬──────────────────────┬──────────┬───────────┬────────────────────────────────────────────────────────────────────────────────┐
│ context_id │      timestamp       │   type   │ log_level │                                    message                                     │
│   uint64   │ timestamp with tim…  │ varchar  │  varchar  │                                    varchar                                     │
├────────────┼──────────────────────┼──────────┼───────────┼────────────────────────────────────────────────────────────────────────────────┤
│         23 │ 2026-01-10 14:46:5…  │ QueryLog │ INFO      │ select * from read_pst_messages('enron/*.pst', read_limit=1000);               │
│         23 │ 2026-01-10 14:46:5…  │          │ INFO      │ Planned 26 partitions (171 files)                                              │
│         23 │ 2026-01-10 14:46:5…  │          │ DEBUG     │ get_virtual_columns [PSTVirtualColumns]                                        │
│         24 │ 2026-01-10 14:46:5…  │          │ ERROR     │ Failed to read column: attachments (STRUCT(filename VARCHAR, mime_type VARCH…  │
│          · │          ·           │    ·     │   ·       │                                       ·                                        │
├────────────┴──────────────────────┴──────────┴───────────┴────────────────────────────────────────────────────────────────────────────────┤
│ 100 rows (40 shown)                                                                                                             5 columns │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

Additionally, DuckDB’s internal components also emit their own logs and traces. In later sections we’re going to use telemetry emitted by duckdb::FileSystem to optimize our reads.

Fly: Performance Optimizations

Our basic table function works, but it’s slow. In this section, we’ll apply query optimization techniques to achieve dramatic performance improvements. We tested these optimizations on the EDRM Enron V2 dataset¹ (77.4 GiB unarchived, 1.2 million messages across 171 files). Unless stated otherwise, queries run against files on local flash storage (M4 MacBook Air).

Statistics Pushdown

One of the first things to do with a pile of PSTs is count the messages inside them:

D select count(*) from read_pst_messages('enron/*.pst');
100% ▕██████████████████████████████████████▏ (00:01:03.08 elapsed)
┌────────────────┐
│  count_star()  │
│     int64      │
├────────────────┤
│    1227193     │
│ (1.23 million) │
└────────────────┘
Run Time (s): real 69.605 user 5.204728 sys 7.053940

1 minute for a count seems awful, especially considering that these files are on our local machine. Hold on – earlier we implemented TableFunction::cardinality, so we can run EXPLAIN queries and see an estimated number of output rows. This happens in only 7s!

D explain select count(*) from read_pst_messages('enron/*.pst');

┌───────────────────────────┐
│    UNGROUPED_AGGREGATE    │
│    ────────────────────   │
│        Aggregates:        │
│        count_star()       │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│     READ_PST_MESSAGES     │
│    ────────────────────   │
│         Function:         │
│     READ_PST_MESSAGES     │
│                           │
│   Projections: pst_path   │
│                           │
│      ~1,227,193 rows      │
└───────────────────────────┘
Run Time (s): real 7.190 user 0.087805 sys 0.464899

Aha! The query plan shows us that DuckDB materializes pst_path for every single row in order to produce the aggregate. In contrast, the cardinality estimate does not load into a DataChunk and simply enumerates PST node IDs. This has no serialization overhead, nor does it ever have to touch a DataChunk. Fortunately, we can implement TableFunction::get_partition_stats, which lets us put our counts into a PartitionStatistics struct letting DuckDB know if our count is an estimate or exact, and which row group it represents.

After doing so, the performance of our count(*) is roughly 10 times faster. The optimizer replaced UNGROUPED_AGGREGATE (and the rest of the query) with COLUMN_DATA_SCAN (scanning the single-row count ‘materialized’ during optimization).

D select count(*) from read_pst_messages('enron/*.pst');
┌────────────────┐
│  count_star()  │
│     int64      │
├────────────────┤
│    1227193     │
│ (1.23 million) │
└────────────────┘
Run Time (s): real 6.871 user 0.219563 sys 0.404564
D explain select count(*) from read_pst_messages('enron/*.pst');

┌───────────────────────────┐
│      COLUMN_DATA_SCAN     │
│    ────────────────────   │
│           ~1 row          │
└───────────────────────────┘
Run Time (s): real 6.816 user 0.225942 sys 0.428223

This is a fantastic demonstration of how important statistics-based optimizations are in modern query engines. File formats like Parquet have several layers of statistics that can be used to prune the number of rows read, allowing query optimizers to skip row groups within files or entire ranges of files altogether. Our use case is much simpler: PSTs were not designed for query engines, and do not have row groups or precomputed statistics. However, we can still apply these techniques by knowing how to structure our access patterns to avoid materializing rows unless absolutely necessary.

In the next sections we’re going to explore how to bring that number down to under 1 second.

Filesystem Integration

Up to this point, we only used duckdb::FileSystem to expand globs, before passing path strings to the PST SDK constructor so it could mount a given file. For testing, this is fine, however in an enterprise setting it may be the case that PSTs live on some kind of network storage like an S3 or ABFS bucket. Secondly, the FileSystem module emits structured logs with telemetry of what is being read (position, byte size, etc).

Both DuckDB and the PST SDK have a file handle concept {duckdb::FileHandle, pstsdk::file}. Conveniently, the PST file API requires all reads and writes to provide an offset and size (allowing us to avoid issues with syncing fseek/fread calls). We made a PST file subclass wrapping a DuckDB file handle (and a matching PST constructor). Let’s test these changes with some queries against a single file (easier to see patterns, less noise): enron/zl_horton-s_000.pst (~209M in size, ~17k messages).

D CALL enable_logging(level = 'trace');
D select * from read_pst_messages('enron/zl_horton-s_000.pst') where conversation_topic like 'Project%';
-- ~40 rows
D select sum(cast(message::JSON->>'bytes' as bigint)) total_bytes, count(*) 
  from duckdb_logs() where "type" = 'FileSystem';

┌─────────────┬──────────────┐
│ total_bytes │ count_star() │
│   int128    │    int64     │
├─────────────┼──────────────┤
│  87324696   │    41920     │
└─────────────┴──────────────┘

So we read about ~87M over ~40k calls. If we look a little closer, it looks like the header is being repeatedly read:

D select message::JSON message_json, message_json->>'fs' fs, message_json->>'path' path, 
  message_json->>'op' op, message_json->>'pos' pos, message_json->>'bytes' bytes 
  from duckdb_logs() where "type" = 'FileSystem' order by pos::bigint asc;

┌───────────────────────────────────────────────────────────────┬─────────────────┬───────────────────────────┬─────────┬─────────┬─────────┐
│                         message_json                          │       fs        │           path            │   op    │   pos   │  bytes  │
│                             json                              │     varchar     │          varchar          │ varchar │ varchar │ varchar │
├───────────────────────────────────────────────────────────────┼─────────────────┼───────────────────────────┼─────────┼─────────┼─────────┤
│ {"fs":"LocalFileSystem","path":"enron/zl_horton-s_000.pst",…  │ LocalFileSystem │ enron/zl_horton-s_000.pst │ READ    │ 0       │ 512     │
│ {"fs":"LocalFileSystem","path":"enron/zl_horton-s_000.pst",…  │ LocalFileSystem │ enron/zl_horton-s_000.pst │ READ    │ 0       │ 512     │
│ {"fs":"LocalFileSystem","path":"enron/zl_horton-s_000.pst",…  │ LocalFileSystem │ enron/zl_horton-s_000.pst │ READ    │ 0       │ 568     │
│ {"fs":"LocalFileSystem","path":"enron/zl_horton-s_000.pst",…  │ LocalFileSystem │ enron/zl_horton-s_000.pst │ READ    │ 0       │ 512     │
│ {"fs":"LocalFileSystem","path":"enron/zl_horton-s_000.pst",…  │ LocalFileSystem │ enron/zl_horton-s_000.pst │ READ    │ 0       │ 512     │
│ {"fs":"LocalFileSystem","path":"enron/zl_horton-s_000.pst",…  │ LocalFileSystem │ enron/zl_horton-s_000.pst │ READ    │ 0       │ 512     │
│ {"fs":"LocalFileSystem","path":"enron/zl_horton-s_000.pst",…  │ LocalFileSystem │ enron/zl_horton-s_000.pst │ READ    │ 0       │ 512     │
│ {"fs":"LocalFileSystem","path":"enron/zl_horton-s_000.pst",…  │ LocalFileSystem │ enron/zl_horton-s_000.pst │ READ    │ 0       │ 568     │
│ {"fs":"LocalFileSystem","path":"enron/zl_horton-s_000.pst",…  │ LocalFileSystem │ enron/zl_horton-s_000.pst │ READ    │ 0       │ 512     │
│ {"fs":"LocalFileSystem","path":"enron/zl_horton-s_000.pst",…  │ LocalFileSystem │ enron/zl_horton-s_000.pst │ READ    │ 0       │ 568     │
│ {"fs":"LocalFileSystem","path":"enron/zl_horton-s_000.pst",…  │ LocalFileSystem │ enron/zl_horton-s_000.pst │ READ    │ 0       │ 568     │
│ {"fs":"LocalFileSystem","path":"enron/zl_horton-s_000.pst",…  │ LocalFileSystem │ enron/zl_horton-s_000.pst │ READ    │ 0       │ 568     │
│ {"fs":"LocalFileSystem","path":"enron/zl_horton-s_000.pst",…  │ LocalFileSystem │ enron/zl_horton-s_000.pst │ READ    │ 0       │ 512     │
|                                                                ...                                                                        |
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

duckdb-pst mounts PST files more than once: first during planning/state init (to break files out into partitions that feed worker threads), then several more times when spooling rows. Each partition represents a subset of some PST file, so it is possible that the same file is having different ranges read out of it by separate threads. When a PST file is mounted, its header is read and validated. We see the 512/568b reads repeatedly as the SDK tries to mount the PST as either an ANSI PST or Unicode PST (one will succeed).

We added a PST copy constructor which copies its underlying PST header into a new instance, skipping this initial read and validation step. Thread local workers now mount PSTs with this copy constructor, sourcing the data from a shared pointer present in the input partition they dequeue. Additionally, we used the wVer field in the header to determine if a PST is Unicode/ANSI, instead of trying both.

D select sum(cast(message::JSON->>'bytes' as bigint)) total_bytes, count(*) 
  from duckdb_logs() where "type" = 'FileSystem';

┌─────────────┬──────────────┐
│ total_bytes │ count_star() │
│   int128    │    int64     │
├─────────────┼──────────────┤
│  87215642   │    41674     │
└─────────────┴──────────────┘

This saves us ~100KB in read bytes and culls 246 read calls. Repeating the query for all of enron/*.pst shows savings of about ~44MB culling 33,582 read calls (over the ~77G size of the corpus). While not the most eye-watering savings, this change does improve the extension by skipping some I/O and compute involved with rereading the header. And, to boot, we can now read files from any DuckDB supported object storage provider!

Concurrent Planning

The most obvious remaining bottleneck in our query performance lies in the initial setup. Previously, plan_input_partitions would serially process each PST file one-by-one, mounting it, reading all node IDs, and creating partitions before moving to the next file. With 171 files, this was slow.

The diagram below shows our optimized approach. We extracted the per-file logic into a new plan_file_partitions function (shown in green), which can now be called concurrently using std::future. This allows us to plan all files in parallel instead of serially.

This concurrent planning optimization delivered another 10x speedup on our count query:

D select count(*) from read_pst_messages('enron/*.pst');
┌────────────────┐
│  count_star()  │
│     int64      │
├────────────────┤
│    1227193     │
│ (1.23 million) │
└────────────────┘
Run Time (s): real 0.611 user 0.457262 sys 0.675394

Parameters & Sane Defaults

One of the first things you may want to do with a pile of PST files is look around, probably with duckdb-ui so you get this nice notebook-style interface:

Initially we noticed that the UI was sluggish, due to the fact that we were loading entire messages and attachment bodies. Some messages also have both a plaintext and HTML body, somewhat exacerbating the issue. We added read_body_size_bytes, defaults to 1M, truncates body and body_html columns to this size. If it’s 0, read the whole thing. Since attachments bodies are byte blobs, we also added read_attachment_body with a default of false. It made the UI driven explore workflow a lot more responsive!

Consider now the query:

select * from read_pst_messages('enron/*.pst') limit 1000;
Run Time (s): real 0.890 user 1.920718 sys 0.191922

Maybe you want to look at an arbitrary 1000 records. Or maybe your e-discovery product has a trial offering, in which they can only import the first 1000 messages from a directory of PSTs. The table function init process is unfortunately unaware of the limit expression, and plans partitions for the whole directory. You can actually see this in the explain plan:

D explain select node_id from read_pst_messages('enron/*.pst') limit 1000;

┌───────────────────────────┐
│      STREAMING_LIMIT      │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│     READ_PST_MESSAGES     │
│    ────────────────────   │
│         Function:         │
│     READ_PST_MESSAGES     │
│                           │
│    Projections: node_id   │
│                           │
│      ~1,227,193 rows      │
└───────────────────────────┘
Run Time (s): real 0.102 user 0.615367 sys 0.140524

To solve for this (albeit slightly contrived) use-case, we introduced a read_limit parameter, which brings the same query down from ~1.9s to ~0.3s. Notice the change in plan showing 1000 rows for the READ_PST_MESSAGES cardinality estimate, without a STREAMING_LIMIT parent:

D select * from read_pst_messages('enron/*.pst', read_limit=1000);
Run Time (s): real 0.162 user 0.309551 sys 0.026927
D explain select node_id from read_pst_messages('enron/*.pst', read_limit=1000);

┌───────────────────────────┐
│     READ_PST_MESSAGES     │
│    ────────────────────   │
│         Function:         │
│     READ_PST_MESSAGES     │
│                           │
│    Projections: node_id   │
│                           │
│        ~1,000 rows        │
└───────────────────────────┘

Small considerations like these amount to a better overall user experience!

MAPI <-> Schema Coupling

Items inside a PST (regardless of “what” they are) all have a node_id that uniquely identifies them within a PST file. That node can be looked up from the PST’s database handle and used to construct a pstsdk::property_bag. A property bag can hold any arbitrary set of properties. pstsdk::message and pstsdk::folder both encapsulate property bags, but differ in the properties their getters read from the property bag (excluding the folder’s hierarchy ops). PSTs have an object model: a standard message has the type IPM.Note, but there exist many IPM item types. The PST SDK does not expose classes for each of these item types; it uses the same pstsdk::message for all of them. For example, nothing really makes IPM.Contact special, other than the fact that a message bag property PR_MESSAGE_CLASS is set to the string IPM.Contact as a hint for the client program to say “present me as a contact” (i.e., look up contact properties on this bag). Clients can even specify their own custom types.

This model let us reuse the same function for all 6 supported MAPI types. To differentiate between output schemas, we named the UDTFs according to the scheme read_pst_{messages,contacts,appointments,...}. The planning layer only cares about node_id, and it’s not until the serialization layer that anything actually reads a property bag. The serialization layer is effectively these two functions:

template <typename Item>
void into_row(PSTReadLocalState &local_state, duckdb::DataChunk &output,
              Item &item, idx_t row_number);
template <typename Item>
void set_output_column(PSTReadLocalState &local_state,
                       duckdb::DataChunk &output, Item &item, idx_t row_number,
                       idx_t column_index);

To keep the UDTF/schema/serde behavior consistent through the extension, we introduced a “typed bag” struct templated over a message class enum, congruent to each defined UDTF:

// Typed bags enclose a "companion object", where this means "a folder of notes" (PR_CONTAINER_CLASS).
// (The default companion type is pstsdk::message, where the message class defines that specific object (PR_MESSAGE_CLASS))
template void into_row<pst::TypedBag<pst::MessageClass::Note, pstsdk::folder>>(...);
// read_pst_messages()
template void into_row<pst::TypedBag<pst::MessageClass::Note>>(...);
// read_pst_appointments()
template void into_row<pst::TypedBag<pst::MessageClass::Appointment>>(...);

Objects like contacts and appointments consist of the base message attributes plus some other specialized ones. So to output a contact, we call both the Note and Contact specializations. Folders also get some special treatment. To facilitate this we wrote some type traits that we could use with C++17 constexpr:

// If message-like, bind IPM.Note base attributes
if constexpr (!pst::is_folder_bag_v<Item>) {
    set_output_column<pstsdk::message>(
        local_state, output, *item.sdk_object, row_number, col_idx);
}

// If reading as note, we are already done
if constexpr (pst::is_base_msg_bag_v<Item>)
    continue;

Filtering on MAPI Type

The simplest approach here would be to not do any filtering: call read_pst_appointments to output the appointment schema, or read_pst_contacts to output the contact schema. The user could then filter on the message_class column (shared across all output schemas) to read only those objects. Since this felt unintuitive, we decided to apply the filter during planning (so that the node_ids read by the table function are already filtered to be of the correct type):

D select count(*) from read_pst_messages('enron/*.pst');
Run Time (s): real 0.602 user 0.477368 sys 0.737954

D select count(*) from read_pst_contacts('enron/*.pst');
┌──────────────┐
│ count_star() │
│    int64     │
├──────────────┤
│      0       │
└──────────────┘
Run Time (s): real 11.105 user 14.305252 sys 14.615962

This too feels a bit slow. Unfortunately, there is no way to assert message class other than to compare against the PR_MESSAGE_CLASS string. The type filter uses a message_class function in our typed bag helper, let’s see if we can make it faster:

auto maybe_msg_class =
    bag.read_prop_if_exists<std::string>(PR_MESSAGE_CLASS_A);

auto klass = BASE_CLASS;

// ["IPM.Foo" -> enum]
if (maybe_msg_class) {
    auto maybe_klass = MESSAGE_CLASS_MAP.find(*maybe_msg_class);
    if (maybe_klass != MESSAGE_CLASS_MAP.end()) {
        auto &[_name, k] = *maybe_klass;
        klass = k;
    }
}

An abbreviated call stack for reading PR_MESSAGE_CLASS_A looks like this:

• read_prop<std::string>(id) (deserialize vector, copy into runtime string)
  • get_value_variable(id) (return byte vector)
    • (…data structure read ops, non-negotiable)
    • heap_impl::read(id) (copy into byte vector from some offset in a data block)

When optimizing, start at the top with simple changes. Since this filter compares the same strings over and over again, maybe we don’t actually care about reading an std::string. What if we use memcmp directly to make our assertion/ It will cut out one copy into the target string, and also avoid any potential iconv calls. Frustratingly, because of the string nuances mentioned earlier, we’ll need to have static constants for both the wstring and ANSI message classes. We now have something that looks like this:

auto klass_type = bag.get_prop_type(PR_MESSAGE_CLASS_A);
auto klass_size = bag.size(PR_MESSAGE_CLASS_A);
auto klass_buf = bag.get_value_variable(PR_MESSAGE_CLASS_A);

if (klass_type == pstsdk::prop_type_string) {
    for (auto &[s, sz, klass] : MESSAGE_CLASS_CONST_ENTRIES_A) {
        // Bonus, nothing to compare if size mismatch
        if (sz != klass_size)
            continue;
        if (!memcmp(s, klass_buf.data(), klass_size))
            return klass;
    }
}
// ...

D select count(*) from read_pst_contacts('enron/*.pst');
Run Time (s): real 6.457 user 13.754120 sys 8.609450

We tried another optimization by implementing a pstsdk::heap_impl::locate function to return the pointer to the page/offset instead of copying the prop value to a vector, but this didn’t yield significant performance improvements. To improve further, we’d likely need to profile our PST B-Tree lookups, which is a bit outside the scope of this article.

Regardless, a ~40% performance improvement by skipping string ops is still welcome!

Late Materialization

Consider this exploratory query and its plan:

select * from read_pst_messages('enron/*.pst') 
where conversation_topic LIKE 'Project%' order by creation_time asc limit 50;

Run Time (s): real 164.637 user 169.394119 sys 158.030263

The query asks for all of the columns, so they get materialized during execution, then the filter is applied to that data. Wouldn’t it make more sense to only read conversation_topic and creation_time, collecting the node_ids of those rows before expanding the projection? This is the premise behind late materialization: only decode entire rows if requested and necessary!

1. Inventory

DuckDB exposes a late materialization API for table functions, but in order to be able to “actually” use LM, these things need to be true:

• Columns can be selectively read from storage
  • True; one of the first things we implemented
• There exists a unique identifier for each row, where we can quickly retrieve it from storage
  • True; earlier we mentioned node_id uniquely identifies objects within a PST. That node_id is looked up in a BTree, which is effectively an index for PST objects!

Let’s sketch out a list of TODOs by starting with the LM optimized plan (so we have something to point to):

The single scan is rewritten by a hash join that calls the table function twice: once on the right (“build side”), decoding a minimum set of columns needed to satisfy the filter and order, then once on the left side using the row IDs from the right scan to filter the whole table. The JOIN criteria uses new internal columns to identify the rows for the result set which are to be materialized by the left hand side. The optimizer pushes down the join criteria as filters into the left scan letting us implement our own pruning: note how READ_PST_MESSAGES on the left side scans only 15 partitions from 6 files (0.7% of partitions) versus all 2167 partitions from 171 files on the right side.

From what we see here, we need to implement:

• Virtual columns & per-row unique IDs
• Handle filter pushdown at the table function level
• Implement partition and row level pruning

2. Row IDs

TableFunction::get_row_id_columns tells DuckDB how to uniquely identify a row. node_id is unique within a PST but not unique across PSTs, so we can’t use it as a row ID:

D select pst_path, node_id from read_pst_messages('enron/*.pst') order by node_id asc limit 3;
┌─────────────────────────────────┬─────────┐
│            pst_path             │ node_id │
│             varchar             │ uint32  │
├─────────────────────────────────┼─────────┤
│ enron/zl_slinger-r_000.pst      │ 2097188 │
│ enron/zl_scholtes-d_000.pst     │ 2097188 │
│ enron/zl_gilbertsmith-d_000.pst │ 2097188 │
└─────────────────────────────────┴─────────┘

(pst_path, node_id) would make a decent composite key, but we can do a little better to avoid dealing with the path string. Recall that PSTReadGlobalState has a vector of PSTInputPartition. Each input partition belongs to one file, and contains a unique set of node IDs. Instead of the path, we can simply use the queue position ordinal. A reasonable concern is that this order depends on the order of how e.g. a directory of PSTs is enumerated. When DuckDB rewrites a query to take advantage of LM, it reuses the bind data for the table function when invoking the left hand side of the hash join. The bind data phase is when we plan our input partitions, so that position ordinal does not change.

For purposes of partition/row pruning, we thought it easiest to introduce two new virtual columns: __partition and __node_id via TableFunction::get_virtual_columns. These are queryable-but-hidden metadata columns that do not show up in a SELECT/DESCRIBE unless explicitly requested, and are usually computed on the fly (albeit node_id is actually stored). For example, reading Hive-partitioned Parquet reads produce virtual columns for the directory structure, and are used by the query engine (if possible) to prune paths that can be skipped for a given query.

3. Filter pushdown

TableFunction::filter_pushdown toggles whether or not the global init routine’s TableFunctionInitInput is pushed down any filters by the optimizer. The init input receives a populated TableFilterSet for the scan, which is a map of column ID to TableFilter objects that e.g. give you a nice vector for an IN (...) filter versus having to work with the expression API directly. This is simple: we just enable it, then handle filters in global init.

We struggled a bit to understand the API for determining filter pushdown. Our interpretation was:

• TableFunction::pushdown_expression helps tell DuckDB whether or not an arbitrary Expression filter can be pushed down.
  • Specifically: you would see this as a TableFilterType::EXPRESSION during global init.
  • PST: All our filters should be constant comparisons or IN (...) ops. No arbitrary expressions. We return false.
• TableFunction::pushdown_complex_filter is called before global state init to let a table function handle a set of arbitrary filter expressions (removing it from the vector once handled). This works, but there’s a problem…

Neither of these let us fully control which filters got pushed down into the table function. pushdown_complex_filter appears to be intended for our use case, but when the LM optimizer rewrites the plan (and builds the new left-hand-side LOGICAL_GET), it makes a new logical get by copying the bind data, return types, etc out of the initial scan. The join filter optimizer then pushes filters down into our table scan, but does not call pushdown_complex_filter again.

Not all query expressions are the same nor do they all trigger the same rewrite & pushdown rules. To illustrate the issue, consider these two queries and the relevant sections of the plan. The only difference is that the first query uses ILIKE.

The default plan for the ILIKE query is correct:

D explain analyze select * from read_pst_messages('/Users/mach/workspace/duckdb-pst/enron/*.pst') 
  where conversation_topic ILIKE 'Project%' order by creation_time asc limit 50;

The default plan for the LIKE query is not (we do not handle conversation_topic filters, so it returns all rows). DuckDB turned our filter into a range so that it can be pruned using zonemaps (but sadly, we cannot take advantage of this):

D explain analyze select * from read_pst_messages('/Users/mach/workspace/duckdb-pst/enron/*.pst') 
  where conversation_topic LIKE 'Project%' order by creation_time asc limit 50;

To fix this we ended up writing an optimizer rule to split non virtual column filters out of the table scan and into a separate filter node. An optimizer rule lets us inspect & make changes to the logical plan before execution. Here is our rewritten plan for the LIKE query:

4. Pruning

The read pipeline uses a PSTInputPartition struct to box a file path and a vector of PST node IDs to read. Filters on __partition prune an entire partition struct altogether, while __node_id filters prune the vector of node IDs inside a partition. For simplicity, we opted to express this all in terms of sets of node_id: no node IDs means “skip” this partition, else, read the reduced set returned by filtering:

set<node_id> PSTInputPartition::prune(idx_t schema_col,
                                      const unique_ptr<TableFilter> &filter) {
  set<node_id> filtered;

  switch (filter->filter_type) {
    // ...
  }

  return filtered;
}

To actually apply the filter predicate, we must unpack each TableFilter variant and handle it explicitly for each of our columns. This is a recursive structure, here is an example filter expression to illustrate:

__partition = 5 AND __node_id = 42
                 │
         CONJUNCTION_AND
                / \
               /   \
CONSTANT_COMPARISON   CONSTANT_COMPARISON
 (__partition = 5)     (__node_id = 42)

How we handle the comparison with regard to our set<node_id> depends on the column. For a partition ID, we either keep or drop all of the nodes, and for a node_id, we either add or drop it from the set:

  case TableFilterType::CONSTANT_COMPARISON: {
    auto &constant_filter = filter->Cast<ConstantFilter>();
    switch (schema_col) {
    case schema::PST_VCOL_NODE_ID:
      for (auto &nid : nodes) {
        if (constant_filter.Compare(Value::UINTEGER(nid))) {
          filtered.insert(nid);
        }
      }
      break;
    case schema::PST_VCOL_PARTITION_INDEX:
      if (constant_filter.Compare(Value::UBIGINT(partition_index))) {
        filtered.insert(nodes.begin(), nodes.end());
      }
      break;
    }
    break;
  }

Then, for higher level operators with child filters, we recurse into them to get their node ID sets then apply them as needed. Here is how we handle OR by unioning the sets given to us by child filters:

case TableFilterType::CONJUNCTION_OR: {
  auto &or_filter = filter->Cast<ConjunctionOrFilter>();

  for (auto &child_filter : or_filter.child_filters) {
    auto pruned_child = prune(schema_col, child_filter);
    filtered.insert(pruned_child.begin(), pruned_child.end());
  }

  break;
}

For the most part, complex expressions like conjunctions recursively sink into CONSTANT_COMPARISON or IN_FILTER and percolate their node IDs upward. While our pruning implementation is simpler due to the PST problem space (Parquet and others have row-group & file level stats with faster pruning than our NID by NID filtering), other file readers also have to evaluate their table filters and probably do so in a similar fashion.

5. Results

Finally, putting all of these pieces together shows us the power of this optimization. The LM-optimized query shows a >90% improvement in query time!

-- Stock plan
select * from read_pst_messages('enron/*.pst') where conversation_topic LIKE 'Project%' order by creation_time asc limit 50;
Run Time (s): real 164.637 user 169.394119 sys 158.030263
-- LM
select * from read_pst_messages('enron/*.pst') where conversation_topic LIKE 'Project%' order by creation_time asc limit 50;
Run Time (s): real 11.961 user 22.370687 sys 7.294064

To learn more about LM outside of this toy problem, we recommend Andrew Lamb & Qiwei Huang’s arrow-rs/Parquet LM blog post.

What we learned

Depending on how hard you look, unstructured data may have a structure that shares similarities to storage formats used by database engines. By understanding what those parallels are, we were able to take a fun “what if” thought and make it real. Then, through systematic optimization, by integrating popular learnings from query engines and databases, we made it fast. To summarize these improvements:

Try it out

The extension is available as a DuckDB community extension. Getting started is as easy as:

INSTALL pst FROM community;
LOAD pst;
SELECT * FROM read_pst_messages('your_archive.pst') LIMIT 10;

The extension is also available on GitHub at intellekthq/duckdb-pst. We’d love to hear how you’re using duckdb-pst! Share your use cases, questions, or contributions by opening an issue or pull request.

What’s Next

Stay tuned for the next post in this series, where we instruct AI to use this extension and recreate the Enron internal wiki!

You are an editor responsible for writing pages in Enron's corporate wiki. Using the `duckdb-pst` tool, you have access to millions of internal emails from across the company.

Your task: Create comprehensive wiki pages about key people, projects, and initiatives at Enron by mining insights from email conversations.

For each page:
1. Query relevant emails using SQL to find messages about the topic
2. Identify key participants, timelines, and decision points
3. Extract technical details, project status, and outcomes
4. Create a well-structured wiki page with sections for overview, timeline, key people, and outcomes

Example queries you can run:
- Find all emails mentioning a specific project name
- Get conversation threads between specific executives
- Identify the most active participants in a discussion topic
- Extract meeting notes and action items from calendar appointments

Available tools:
- read_pst_messages('enron/*.pst') - Access to 1.2 million email messages
- read_pst_contacts('enron/*.pst') - Employee contact information
- read_pst_appointments('enron/*.pst') - Meeting schedules and notes
- read_pst_tasks('enron/*.pst') - Task assignments and project tracking

Begin by asking me which topic or person you'd like to research first.