142
you are viewing a single comment's thread
view the rest of the comments
view the rest of the comments
this post was submitted on 18 Jul 2025
142 points (97.3% liked)
Programming
21776 readers
251 users here now
Welcome to the main community in programming.dev! Feel free to post anything relating to programming here!
Cross posting is strongly encouraged in the instance. If you feel your post or another person's post makes sense in another community cross post into it.
Hope you enjoy the instance!
Rules
Rules
- Follow the programming.dev instance rules
- Keep content related to programming in some way
- If you're posting long videos try to add in some form of tldr for those who don't want to watch videos
Wormhole
Follow the wormhole through a path of communities [email protected]
founded 2 years ago
MODERATORS
Postgres is cursed for only allowing 65535 parameters in a single query?
Someone correct me if I am wrong, but that is a fairly large number (I think Microsoft SQL is limited to 2000 or something like that) AND this seems like a terrible design pattern.
I learned this one the hard way when trying to query GeoJson data, and trying to get specific, constrained, data about specific features within an area. Excluding features the user doesn't have access to.
Sometimes this got up to 65k features.
I definitely could see geojson getting that large.
goes looking for the issue
Hmm. I would believe that there are efficiency gains from doing one large insert rather than many small
like, there are probably optimizations one can take advantage of in rebuilding indexes
and it'd be nice for database users to have a way to leverage that.
On the other hand, I can also believe that DBMSes might hold locks while running a query, and permitting unbounded (or very large) size and complexity queries might create problems for concurrent users, as a lock might be held for a long time.
EDIT: Hmm. Lock granularity probably isn't the issue:
https://stackoverflow.com/questions/758945/whats-the-fastest-way-to-do-a-bulk-insert-into-postgres
Any lock granularity issues would also apply to transactions.
Might be concerns about how the query-processing code scales.
I’d say running up against a 16bit number for a database import in 2025 is a little cursed. MS is special, still has a 260 path character limit (albiet soft now) in Windows.
Also with more phones taking an image and a video that is only 32767 snaps, which is probably a regular headache for initial imports.
I learned that not too long ago, too.
I mean it surprised me, but there are many ways around that. May be less efficient, but you can always use string-to-array, or json, or copy more for CTE then work with inputs as a table.
Create a user defined table type and use that as a parameter. I'm not sure what the postgres name of that is.
And how do you put data into the table?
Based on old memories since I've been working in mongo lately, after making the UDT on the db side, you make a data table that has the same name, namespace (ie dbo/public), and the same schema as the UDT (better if that could be generated) and populate it in code. Then you execute the db query with the UDT type as a parameter.
This is better for a few reasons, including not building up a string, but also having the same text means that each query didn't need to be re-parsed and can reuse execution plans. If the query text isn't an exact match, it gets that whole pipeline each time.