Uploading large files takes too long

I'm trying to upload a 500kb file to the neon db, but it takes around 27s. My colleague's upload speed is almost instantaneously. Could this be a latency issue? Any clues on how to solve the problem would be much appreciated!

file_content=$(<file.js)

encoded_content=$(echo "$file_content" | base64)

echo "INSERT INTO source_maps (id, project_app_id, name, file, created_at) VALUES ('test-1', 'your_project_app_id', 'your_name', '$encoded_content', EXTRACT(epoch FROM now()) * 1000);" > command.sql

psql "connection string" -f command.sql

file_content=$(<file.js)

encoded_content=$(echo "$file_content" | base64)

echo "INSERT INTO source_maps (id, project_app_id, name, file, created_at) VALUES ('test-1', 'your_project_app_id', 'your_name', '$encoded_content', EXTRACT(epoch FROM now()) * 1000);" > command.sql

psql "connection string" -f command.sql

12 Replies

compatible-crimson•2y ago

Where is your colleague located, where are you located, and where is the database?

compatible-crimson•2y ago

I would also suggest using a psql script like https://stackoverflow.com/a/14123513/7572728

Stack Overflow

Postgresql: inserting value of a column from a file

For example, there is a table named 'testtable' that has following columns: testint (integer) and testtext (varchar(30)). What i want to do is pretty much something like that: INSERT INTO testta...

rival-black•2y ago

is that a good idea to store files in the database?

like-gold•2y ago

Generally it's not considered best practice, but for some use cases it's probably OK.

fascinating-indigoOP•2y ago

colleague: switzerland me: romania db: ohio (us) could the distance be the issue? do you reckon it's a good practice to store files under 500kb in the db?

compatible-crimson•2y ago

I think it makes more sense to store files in blob storage I don't think the distance between switzerland and romania could cause such an issue might look at internet upload speeds

fascinating-indigoOP•2y ago

my upload speed is close to 900 Mbps

compatible-crimson•2y ago

Hmmm, then I am not sure what to tell you. Is that the only query that runs slow for you?

fascinating-indigoOP•2y ago

yeah, so far this is the only one. I'll look into storing the files in a blob storage like you suggested, thanks for your help!

compatible-crimson•2y ago

@criscript look at cloudflare r2, amazon s3, minio, or ceph. Then store the path to the blob in your postgres database alternatively just a host a file server somewhere postgres just isn't a great database for storing large files in general. The data is stored out of line (not with the rest of the row). Which means another data fetch

fascinating-indigoOP•2y ago

thanks! will keep this in mind

compatible-crimson•2y ago

here are the docs for this by the way: https://www.postgresql.org/docs/current/storage-toast.html

PostgreSQL Documentation

73.2. TOAST

73.2. TOAST # 73.2.1. Out-of-Line, On-Disk TOAST Storage 73.2.2. Out-of-Line, In-Memory TOAST Storage This section provides an overview of TOAST (The …

Gaming

Programming

Uploading large files takes too long

Did you find this page helpful?