From: | Muhammad Usman Khan <usman(dot)k(at)bitnine(dot)net> |
---|---|
To: | "Wong, Kam Fook (TR Technology)" <kamfook(dot)wong(at)thomsonreuters(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: How to Copy/Load 1 billions rows into a Partition Tables Fast |
Date: | 2024-10-15 03:39:19 |
Message-ID: | CAPnRvGt12Kf4aBaU8kHKG-0w0BW-o0LnNgw8ZFeY4F+wvzcCWA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
There are many methods to achieve this and one of them is pg_bulkload
utility as described in previous email but I always preferred using python
multiprocessing which I think is more efficient. Below is the code which
you can modify as per your requirement:
import multiprocessing
import psycopg2
def insert_partition(date_range):
conn = psycopg2.connect("dbname=your_db user=your_user
password=your_password")
cur = conn.cursor()
query = f"""
INSERT INTO partitioned_table (column1, column2, ...)
SELECT column1, column2, ...
FROM source_table
WHERE partition_key BETWEEN '{date_range[0]}' AND '{date_range[1]}';
"""
cur.execute(query)
conn.commit()
cur.close()
conn.close()
if __name__ == "__main__":
ranges = [
('2024-01-01', '2024-03-31'),
('2024-04-01', '2024-06-30'),
# Add more ranges as needed
]
pool = multiprocessing.Pool(processes=4) # Adjust based on CPU cores
pool.map(insert_partition, ranges)
pool.close()
pool.join()
On Mon, 14 Oct 2024 at 22:59, Wong, Kam Fook (TR Technology) <
kamfook(dot)wong(at)thomsonreuters(dot)com> wrote:
> I am trying to copy a table (Postgres) that is close to 1 billion rows
> into a Partition table (Postgres) within the same DB. What is the fastest
> way to copy the data? This table has 37 columns where some of which are
> text data types.
>
> Thank you
> Kam Fook Wong
>
>
> This e-mail is for the sole use of the intended recipient and contains
> information that may be privileged and/or confidential. If you are not an
> intended recipient, please notify the sender by return e-mail and delete
> this e-mail and any attachments. Certain required legal entity disclosures
> can be accessed on our website:
> https://www.thomsonreuters.com/en/resources/disclosures.html
>
From | Date | Subject | |
---|---|---|---|
Next Message | Juan Rodrigo Alejandro Burgos Mella | 2024-10-15 04:27:57 | Re: How to Copy/Load 1 billions rows into a Partition Tables Fast |
Previous Message | Adrian Klaver | 2024-10-14 23:38:01 | Re: Changing postgres User |