|
Atul Goel wrote: > > We are a data based company and are migrating from Oracle to
> > Postgresql. For this purpose I am doing POC for the same. We have a
> > business requirement to send the Data in XML files to our clients. > > The file size of XMLs is around 700MB and is growing. > > > > I have been able to generate sample XML files and store the
> > same(Still to test for full files). > > > > However I have the following problems > > > > 1) It took 35 minutes to store 1 GB data in the TEXT column (create
> > table test_text_nearly_1_GB as select repeat('1', > > 1024*1024*1023):: text as nearly_1_GB) That's a fairly inefficient way to generate the data to insert. You'll probably find that much of the time was spent in repeat() not anywhere interesting to your query. I'd be more interested in how long COPY ... FROM took, personally. > > 2) It took 30 Minutes to write the same to OS using COPY command
> > Copy test_text_nearly_1_GB to '$HOME/test_text_nearly_1_GB.xml' That's a write rate of 34MB/min, or half a meg a second. Not pretty. Where's the load during the COPY? Mostly CPU? Or mostly disk I/O? Are you writing the output to the same disk the database is on? (Not that it should make this much difference). > > 3) Find a solution when the file size become > 1GB That's going to be interesting. Do you really need to store whole XML documents this size in the database, rather than broken up into structures that can be worked with usefully in the database? If so, PostgreSQL might not be your best choice. -- Craig Ringer Hi All, I am posting this request again with the correct subject. We are a data based company and are migrating from Oracle to Postgresql. For this purpose I am doing POC for the same. We have a business requirement to send the Data in XML files to our clients. The file size of XMLs is around 700MB
and is growing. I have been able to generate sample XML files and store the same(Still to test for full files).
However I have the following problems 1) It took 35 minutes to store 1 GB data in the TEXT column (create table test_text_nearly_1_GB as select repeat('1', 1024*1024*1023):: text as nearly_1_GB) 2) It took 30 Minutes to write the same to OS using COPY command Copy test_text_nearly_1_GB to '$HOME/test_text_nearly_1_GB.xml' However when I tested to write a table of size 3GB with 150000 rows it wrote to OS in 20 minutes. I am looking for a solution to 1) generate and write the 1GB file in OS in less than 10 minutes. 2) write the file to OS the results of a query 3) Find a solution when the file size become > 1GB Please help. Atul Goel SENIOR DEVELOPER Global DataPoint Middlesex House, 34-42 Cleveland Street London W1T 4LB, UK T: +44 (0)20
7079 4827 M: +44 (0)7846765098 |
