hey folks,im working on an erp migration tool,as the first step i need to load data from sources like netsuite,quickbooks,sage intaact etc data will be in millions and im looking for options for doing this and i have dlt and airbyte as my options,so just wanted to know for this usecase ie from source erp -> my staging db (postgres) airbyte is good or not and how much time can be expected for loading data to staging (~10M) @kapa.ai

Airbyte for ERP → Postgres Staging Connector Availability Airbyte does support NetSuite as a source (both a community connector via SuiteTalk REST API and an enterprise connector via SuiteAnalytics Connect). However, the knowledge sources don't explicitly confirm out-of-the-box connectors for QuickBooks or Sage Intacct, so you'd need to verify those in the Airbyte connector catalog. Postgres as a Staging Destination — Key Caveats Airbyte does support Postgres as a destination, but there are important limitations you should be aware of for your use case: > Postgres is not a data warehouse. Please only consider using Postgres as a destination for small data volumes (e.g. less than 10GB) or for testing purposes. [Postgres destination docs] [Postgres troubleshooting] At ~10M records, depending on the width of your tables, you could easily exceed this threshold. Specific risks include: • Performance degradation with large writes/updates, especially with typing and deduplication enabled. [Postgres troubleshooting] • Potential "lock-ups" on the destination DB during large syncs (a real-world case with ~30GB/9M records from IBM DB2 to Postgres showed the sync hanging indefinitely until Postgres config was tuned). [GitHub issue] • Postgres needs proper tuning (e.g.,

work_mem

,

tcp_keepalives_idle

) for large loads to succeed. [GitHub issue] Expected Load Time The knowledge sources don't provide a precise benchmark for ERP → Postgres at 10M records. What is available: • The Postgres source connector throughput is cited at ~*11 MB/second*. [low-latency tutorial] • A PyAirbyte demo loaded ~100K records in ~68 seconds (~1,700 records/sec). [PyAirbyte tutorial] • A community report showed a 2GB / 10M record Postgres table taking ~*17 minutes* for an initial load, which was considered slow. [forum post] So for ~10M records, you might expect somewhere in the range of 15–30+ minutes for the initial load, but this is highly dependent on record size, network, and destination DB performance. Recommendation Airbyte can work for your use case, but: 1. Postgres as staging is risky at 10M+ records — monitor CPU/memory closely and tune your Postgres config. 2. If your data grows beyond ~10GB, consider a proper data warehouse (BigQuery, Snowflake, Redshift) as the staging layer instead. 3. For incremental syncs after the initial load, Airbyte supports CDC, xmin, and cursor-based methods, which will be much faster than full refreshes. [low-latency tutorial] 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.

@kapa.ai what all issues can i face when using postgres also give me full code also

Issues You Can Face When Using Postgres as a Destination in Airbyte 1. Performance with Large Data Volumes Postgres is not a data warehouse. It is recommended only for data volumes under ~10GB or for testing. With 10M records, you may hit performance walls. [Postgres destination docs] 2. Database "Lock-ups" During Large Syncs When using typing and deduplication, large sync volumes can cause your Postgres destination to "lock up" and incur high usage costs. [Postgres troubleshooting] 3. Scaling IOPS Matters When scaling Postgres for larger data volumes, scaling IOPS (disk throughput) is as important as increasing memory and compute capacity. [Postgres troubleshooting] 4. Column/Table Name Length Limitations Postgres has a 63-byte name length limit. If your ERP data has long, nested column names, they will be truncated — causing duplicate column name errors. For example,

{63 byte name}_a

and

{63 byte name}_b

both truncate to

{63 byte name}

, which throws an error. [Postgres troubleshooting] 5. Disk Access Requirement The Airbyte Postgres destination relies on sending files to the database's

/tmp

file system for bulk loading. If your Postgres instance doesn't have access to

/tmp

, data loading will fail. [Postgres troubleshooting] 6. AWS Aurora Specific Issues If you're using AWS Aurora (Postgres-compatible), even it is not immune to slowdowns with large writes/updates over ~100GB. [Postgres troubleshooting] Setup Code for Postgres Destination Here is the SQL to create a dedicated Airbyte user with the required permissions: [Postgres destination docs]

CREATE USER airbyte_user WITH PASSWORD '<password>';
GRANT CREATE, TEMPORARY ON DATABASE <database> TO airbyte_user;

Setup Code for Postgres Source (if using Postgres as a source too) If you also need to read from a Postgres source with CDC, here's the full setup: [CDC tutorial]

-- Create schema and set search path
CREATE SCHEMA postgres;
SET search_path TO postgres;

-- Create a read-only user
CREATE USER airbyte PASSWORD 'password';

-- Grant schema access
GRANT USAGE ON SCHEMA postgres TO airbyte;

-- Grant read access to existing tables
GRANT SELECT ON ALL TABLES IN SCHEMA postgres TO airbyte;

-- Allow access to future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA postgres GRANT SELECT ON TABLES TO airbyte;

-- Grant replication and login permissions (required for CDC)
ALTER USER airbyte REPLICATION LOGIN;

-- Create a logical replication slot using pgoutput plugin
SELECT pg_create_logical_replication_slot('airbyte_slot', 'pgoutput');

-- Create a publication for the tables you want to sync
CREATE PUBLICATION pub1 FOR TABLE cars;

Regarding "Full Code" The knowledge sources only provide the SQL setup snippets above. There is no additional application-level code (e.g., Python scripts) available in the knowledge sources for this use case. I'd recommend checking the Airbyte API docs for programmatic connection setup if needed. 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.

no python code for building netsuite->postgres that code @kapa.ai

The knowledge sources do not contain a Python script for programmatically building a NetSuite → Postgres pipeline. However, based on the source code available in the knowledge sources, here is what is directly supported: NetSuite Source Entry Point This is the actual

run.py

from the NetSuite connector: [NetSuite source run.py]

import sys
from airbyte_cdk.entrypoint import launch
from .source import SourceNetsuite

def run():
    source = SourceNetsuite()
    launch(source, sys.argv[1:])

NetSuite Source Authentication (OAuth1 / TBA) This is the authentication and connection check logic from `source.py`: [NetSuite source.py]

def auth(self, config):
    realm = config["realm"].replace("-", "_").upper()
    return OAuth1(
        client_key=config["consumer_key"],
        client_secret=config["consumer_secret"],
        resource_owner_key=config["token_key"],
        resource_owner_secret=config["token_secret"],
        realm=realm,
        signature_method="HMAC-SHA256",
    )

Postgres Destination User Setup

CREATE USER airbyte_user WITH PASSWORD '<password>';
GRANT CREATE, TEMPORARY ON DATABASE <database> TO airbyte_user;

[Postgres destination docs] Beyond these snippets, the knowledge sources do not contain a full end-to-end Python script for setting up a NetSuite → Postgres connection via Airbyte programmatically. For that, I'd recommend checking the Airbyte API documentation or the PyAirbyte library directly. 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.