postgresql timescaledb tutorial COPY and UPDATE speed

Question

I'm following the tutorial: https://docs.timescale.com/v1.1/tutorials/tutorial-hello-nyc

I'm curious to know how long it takes you to get the requests:

"\COPY rides FROM nyc_data_rides.csv CSV"

and

UPDATE rides SET pickup_geom = ST_Transform(ST_SetSRID(ST_MakePoint(pickup_longitude,pickup_latitude),4326),2163);
UPDATE rides SET dropoff_geom = ST_Transform(ST_SetSRID(ST_MakePoint(dropoff_longitude,dropoff_latitude),4326),2163);

For me, the first one is 15 minutes long and the second one 23 minutes long.

That seems a lot to me, maybe I'm wrong. Otherwise how can I improve the speed of COPY and UPDATE requests?

I tried to create indexes only at the end as recommended elsewhere. But the gain is marginal.

Ps: this is a fresh installation of PostgreSQL 10 on windows 10, 16g RAM, i7.

Bye

Answer 1

That seems a bit weird. When you run \dx, do you see Timescale installed? I would also suggest running timescale-tune to make sure your instance is tuned correctly, but you really shouldn't have to for this tutorial.

Answer 2

Probably an io issue. The updates are taking a long time because they're essentially rewriting the whole table. Definitely wouldn't recommend doing that in production, but it seems like everything's taking longer than it should because you're having some io problems, you can test your io with something like the ioping tool and see how the latency/throughput is.