Hacker News Reader

Comment by rjmunro | Hacker News Reader

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.

View on HN

I disagree that the security is a side benefit. It’s why most people choose to use parameterized queries.

Thank 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.

I 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.

Quick 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.

Isn'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).

Wait 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.

Not if you save transit time that can be more quickly made up in processing time.

True, but in this case we are converting to strings that are longer because they require quoting and escaping etc. Transit time will be longer.

Feels like you're a couple decades behind. Single core speeds haven't gone up much in the last decade, especially for "server" CPU models.

Can 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.

I 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.

Usually 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).

Reply on news.ycombinator.com