4 ms·
This annoys me: > You can use parameterized queries to protect against SQL injection, No, that't not what parameterized queries are for. That's just a side be
by rjmunro 2y ago
This annoys me:
> You can use parameterized queries to protect against SQL injection,
No, that't not what parameterized queries are for. That's just a side benefit.
Parameterized queries are so that the database doesn't have to parse the data from SQL to do the query. It's stupid to turn all the data to text, send the text over the wire, escaped and quoted as appropriate, then parse it back to whatever form it was in originally when you can just send it binary straight to the database.
And if you are doing many similar queries with different results, e.g. inserts as here, or maybe querying the user table by user id every page load, the database doesn't have to parse any SQL each time and can even reuse the query plan.
This may be why psycopg3 performed better than pandas df.to_sql() function in the single row insert case.
- PolarizedPoutin 2y agoThank you for reading through it thoroughly and pointing this out! I'm still new and learning Postgres so this is good to know. I will update the post.
- munk-a 2y agoI would note that I think the above is a rather minority opinion - while parameterized queries are great for reuse simply using them to guard against SQL injection is still an excellent use of them. If your query is reusable then go for it, but most complex queries tend not to be reusable if they involve optional inputs.
- eddd-ddde 2y agoCan it actually reuse the query plan? Couldn't that lead to a worse performing plan eventually? Say after inserting lots of data such that a different plan becomes a better option.
- munk-a 2y agoUsually the TTLs you'll set on these plans[1] are pretty short. It's helpful if you need to do a burst of a thousand queries of the same form over a short interval. 1. At least effectively - this is usually manged via persisting handles or plans themselves. 2. Which is usually a bad code smell from ORM overuse. Every time you dispatch a query you're paying latency overhead - so in an ideal world your report delivery will involve a single query against the database per report request (whether this is a website and those requests are page loads or you're running some internal system for business reporting).
- rjmunro 2y agoI think it's going to be very rare that a database changes enough for a plan to be significantly worse than the optimal plan during the life of a prepared query. A database engine could easily mitigate against it by re-planning queries every 1000 runs of the query or have the query plan expire after 60 seconds or something. It might be worth trying to re-prepare the query from time to time as part of this kind of bulk insertion benchmarking. The other thing that would be good to try is closing the transaction every 1000 inserts or some other number. Doing several inserts in a single transaction is certainly better than 1 insert per transaction, but there may come a point where the transaction being too big starts to slow things down.
- dboreham 2y agoQuick note to observe that all of the above while true becomes less of a practical issue as CPUs become faster vs i/o, which they have done and probably will keep doing.
- forrestthewoods 2y agoWait what no stop that what are you talking about. Transforming data and then transforming it back will always be stupid and costly – in more ways than one.
- pphysch 2y agoFeels like you're a couple decades behind. Single core speeds haven't gone up much in the last decade, especially for "server" CPU models.
- simiones 2y agoIsn't the opposite mostly happening, with CPU's single-core performance mostly flat for the last decade, while I/O speeds have been improving every year (especially in networking, not as sure about disk I/O).
- davedx 2y agoI disagree that the security is a side benefit. It’s why most people choose to use parameterized queries.