The Slow INSERT Problem

Loading 112,647 rows into a fact table should be a swift operation, typically taking seconds. Yet, for one developer building an e-commerce data warehouse using PostgreSQL, the `INSERT` query was dragging on for over 25 minutes, forcing cancellations. The data itself was validated, the SQL syntax was correct, and the dimension tables populated without issue. The bottleneck wasn't immediately apparent and turned out to be a more instructive discovery than anticipated.

This challenge arose during the construction of a star schema data warehouse. The goal was not merely a superficial table for simple `SELECT *` queries, but a comprehensive dimensional model. This model needed to be reproducible from scratch and capable of answering real-world business questions pertinent to e-commerce operations.

Understanding the Dataset

The project utilized the Brazilian E-Commerce Public Dataset by Olist, a real-world dataset encompassing marketplace orders from September 2016 to October 2018. This dataset comprises nine interconnected CSV files, providing a rich foundation for analysis. Key figures from the dataset include:

  • 99,441 orders and 112,650 sales lines.
  • 103,886 payments and 104,719 reviews.
  • 32,951 products and 3,095 sellers.

The data was structured to support a star schema, a common data warehousing design pattern. In a star schema, a central fact table contains quantitative transactional data, surrounded by several dimension tables that provide contextual attributes. For instance, a sales fact table might link to product, customer, and date dimension tables.

The initial approach involved populating dimension tables first, which completed rapidly. The issue manifested when attempting to load data into the main fact table. Standard `INSERT` statements were employed, assuming that with adequate hardware and optimized SQL, performance would be satisfactory. However, the prolonged execution time indicated a more fundamental problem.

Investigating the Bottleneck

With the data and SQL logic seemingly sound, the focus shifted to the database configuration and its interaction with the data loading process. The developer meticulously reviewed PostgreSQL settings, indexing strategies, and transaction management. Common culprits for slow inserts, such as missing indexes on foreign keys, poorly optimized joins, or excessive data volume, were ruled out. The sheer volume of 112,650 rows, while substantial, should not have resulted in such extreme delays on a modern system configured for analytical workloads.

The surprising detail here was not the data volume or the complexity of the schema, but the subtle impact of a specific PostgreSQL configuration parameter. It became clear that the problem lay not in the data itself, but in how PostgreSQL was handling the large volume of insertions within the context of its internal mechanisms.

The investigation led to a deeper examination of PostgreSQL's Write-Ahead Logging (WAL) mechanism and its associated settings. WAL is crucial for data durability, ensuring that committed transactions are safely recorded before being written to data files. However, inefficient WAL configuration can become a significant bottleneck during bulk data loading operations.

The Culprit: `fsync`

The root cause of the prolonged `INSERT` times was identified as the `fsync` parameter within PostgreSQL's configuration. `fsync` is a system call that forces data to be written from the operating system's cache to the physical disk. When `fsync` is enabled (which is the default for good reason – data durability), PostgreSQL ensures that every transaction commit is immediately flushed to disk. This provides strong guarantees against data loss in case of system crashes.

During a large bulk `INSERT` operation, enabling `fsync` for every single row or small batch of rows can lead to a massive number of disk write operations. Each commit requires a synchronous write to disk, serializing the write process and significantly slowing down the overall insertion speed. In essence, the database was spending an inordinate amount of time waiting for disk I/O to complete for each small write operation triggered by the transaction commits.

The dataset, while not excessively large in terms of raw storage, represented a high volume of individual write events due to the nature of the `INSERT` statements. The default `fsync = on` setting, while essential for production environments requiring maximum data safety, was proving to be a performance killer for this specific bulk loading scenario.

The Solution: Temporary Disablement of `fsync`

The solution involved temporarily disabling `fsync` during the bulk data loading phase. By setting `fsync = off` in `postgresql.conf` (or more granularly, within a session using `SET session_replication_role = 'replica';` which implicitly disables `fsync` for the duration of the session), the database could buffer writes and perform them in larger, more efficient batches. This significantly reduced the overhead associated with individual commit synchronizations to disk.

After disabling `fsync`, the same `INSERT` operations that previously took over 25 minutes completed in under 5 minutes. This represented a dramatic performance improvement, making the data loading process practical.

Crucially, the developer re-enabled `fsync` immediately after the data loading was complete. This step is non-negotiable for maintaining data integrity and durability in a production environment. The temporary disablement is a technique specifically for optimizing large-scale batch loading where the risk of immediate data loss is mitigated by other factors (e.g., the data source is still available, or a full backup is taken beforehand).

Rebuilding the Data Warehouse

With the performance bottleneck resolved, the process of building the data warehouse could proceed. The star schema was successfully populated with data from the Olist dataset. This included creating and populating the fact table (sales lines) and its associated dimension tables (products, sellers, customers, dates, etc.).

The experience highlighted a critical trade-off in database design and administration: performance versus durability. While `fsync = on` is the standard for robust production systems, understanding when and how to temporarily adjust such parameters can unlock significant performance gains for specific operations like bulk data ingestion. For a data warehouse, where large volumes of data are often loaded in batches, this optimization is particularly relevant.

The project demonstrated that even with a well-structured schema and correct SQL, database configuration can be the silent performance killer. The lesson learned is to look beyond the obvious when facing unexpected performance issues, particularly in high-volume data operations.

What this situation underscores is the importance of understanding the specific workload and its interaction with database internals. A data warehouse loading process is fundamentally different from a transactional OLTP workload, and its performance tuning requires a different set of considerations. The default settings in PostgreSQL are optimized for general-purpose use and maximum safety, but they are not always optimal for every specialized task.

The journey from a 25-minute `INSERT` to a swift data load provides a valuable case study for anyone building data warehouses or performing large-scale data migrations with PostgreSQL. It emphasizes that performance tuning is an iterative process, often revealing hidden complexities in database operations.