Subscribe to this thread
Home - General / All posts - upload speed in postgres and port specification
tgazzard
133 post(s)
#27-Jun-18 00:05

Hi,

main question for this thread is uploading from m9 to postgres. Is it just me, or is it very slow? For example, I have a 1.2 million row table. Can copy it out of the postgres to m9 in under a minute.

But to load it back in takes close to an hour.

Whereas to export out m9 as a csv (less than a minute) and then upload using

\copy bu.sw_grid_cell from 'D:/analysis/sw_grid_cell.csv' delimiters ',' csv header;

Takes about 10 seconds.

Maybe it is just my set up? Do other people find it slow to upload data back into the postgres database?

Yes, I have a work around. But one of the nice things about m9 for me is that easy ability to pull data in and out of external databases. Would be great to be able to achieve similar speeds to the download out of the database.

tg

----------------------

On a different topic,

A minor improvement for me would be on the new data source, the inclusion of the port.

I have a number of databases that I connect to in postgres 9.6, postgres 10.4 and one I occasionally connect to on amazon. They each have a different port number. I realise I can put the port number in the string. But it wasn't obvious at first.

The first is what you get when you press edit query

{ "Source": "host=localhost user=postgres password=xxxxx dbname=xxxx" }

I manually added in the port number in this one

{ "Source": "host=localhost port=5433 user=postgres password=xxxx dbname=xxxx" }

A minor improvement for sure. But these things trip you up

Attachments:
ScreenHunter 10.png
ScreenHunter_1191 Jun. 27 08.59.jpg
ScreenHunter_1193 Jun. 27 09.08.jpg

adamw

8,090 post(s)
#27-Jun-18 15:02

We have a couple of things to try regarding making uploads to PostgreSQL in particular and databases in general faster. The upload speeds will never match download speeds for plenty of reasons, but we might be able to make them a little closer. We will look into this. Uploads being much slower than downloads is a relatively common issue, particularly with PostgreSQL (not saying PostgreSQL is doing something wrong, to the contrary, the database is clearly just trying to be as correct and as up to date as possible with respect to all connected clients and this clashes with some other scenarios).

Regarding the port, you can enter the port together with the name of the server, after a semicolon: localhost:5433. The dialog knows that you are trying to connect to PostgreSQL = a database that allows specifying a port to connect to explicitly, and will split the port from the name automatically.

tgazzard
133 post(s)
#27-Jun-18 22:54

Thanks for the tip about the localhost:5433 I wasn't aware of this protocol.

Also, appreciate your thoughts on the upload speed.

Manifold User Community Use Agreement Copyright (C) 2007-2017 Manifold Software Limited. All rights reserved.