Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Ways to shoot yourself in the foot with Postgres

philbooth.me

Post reply on HN
Print view View original

251–260 of 329 posts

Re: Ways to shoot yourself in the foot with Postgres

#251

Post by jaggederest »

johnthescott wrote: post #250
jaggederest wrote: post #213

Earlier quoted context omitted.

The first Sybase grew out of the Ingres project at UCB, so transitively, SQL Server is also a descendant.

bob epstein was vp at brittion-lee when he left to form sybase. BLI built a relational database machine (IDM), which was influenced by ingres but not much was inherited, code wise. sybase used a VM/pcode architecture, very much not like ingres. https://www.google.com/search?q=britton+lee+inc+wikipedia

Neat, thanks for elaborating, I knew BLI grew out of the ingres project but not how closely they were related.

Re: Ways to shoot yourself in the foot with Postgres

#252

Post by steve-chavez »

rwmj wrote: post #135
eitland wrote: post #131

Earlier quoted context omitted.

Might be a good idea if you arr the only one on the team or if everyone is like you. If not, be prepared to check for sql injection vulnerabilities the first n PRs from any new team member. Also to explain how to do it and your reasoning for it.

Kind of goes without saying that any framework/library you use must not allow you to write SQL injection vulnerabilities, and if it does you should stop using it right now.

SQL injection is always possible with an ORM, since they always allow executing raw SQL as an escape hatch.

Re: Ways to shoot yourself in the foot with Postgres

#253

Post by zeroimpl »

vbilopav wrote: post #61
ak39 wrote: post #26

"2. Push all your application logic into Postgres functions and procedures" Why are functions and procedures (an abstraction layer at db layer) considered harmful to performance when the same abstraction layer will be required at the application layer (introducing out of process overhead and possibly network traffic)? I don't agree with this advice. (Or I don't understand it.)

I don't understand it either. Author seems to be arguing against long functions/procedures. But if you move that to the client, presumably with ORM support - you're going to be executing more or less the same sequence of SQL queries and commands. Only difference is that when doing it on client you will have a lot of latency. Yes, you can cache some data in between those commands to avoid same multiple queries, but if…

I’m fully against using triggers to implement business logic, but find stored procedures can be great for encapsulating some elements of application/business logic. I’ve got several applications that access the same databases, and putting logic in the database avoids needing to duplicate it in clients.

Most comments about debugability are nonsense. It’s just different, with some pros and cons. One simple example - if you have a bug in production, you’re not going to attach a debugger to your production application. But you can absolutely open a readonly connection to the database and start running queries, invoking functions, etc. It helps if you can architect your functions to distinguish pure readonly functions from those with side effects, but you can still debug even if that’s not the case.

Re: Ways to shoot yourself in the foot with Postgres

#254

Post by eastern »

Zanfa wrote: post #42
maxloh wrote: post #28

Earlier quoted context omitted.

It is always more easier to scale horizontally at application layer (just adding more servers) than at database layer (which involves syncing data between multiple database instances).

In my experience more often than not, Postgres performance problems aren't really caused by the database, but either badly designed schemas or queries. For a lot of developers, the thinking goes that 10s of millions of rows sounds like a lot like big data, so they must start building microservices, distributed systems, use K/V stores and horizontally scale a la Google, whereas their entire dataset could actually fit…

100%. Exaggerating the bigness of their own data is a common phenomena. Sometimes one is talking to a group of developers who are all so impressed with this bigness but every one of them has a phone in their pocket which could fit their entire dataset.

Re: Ways to shoot yourself in the foot with Postgres

#255

Post by branko_d »

bakuninsbart wrote: post #75
smcleod wrote: post #56

Earlier quoted context omitted.

Came here to say exactly this. Over the last 12~ years working with PostgreSQL I've dealt with quite a few performance related issues - almost all were poorly written queries.

Can you point to some good resources on how to write better postgres queries? Or give examples of common pitfalls?

https://use-the-index-luke.com/

Re: Ways to shoot yourself in the foot with Postgres

#256

Post by bagol »

rwmj wrote: post #129

The main tip I learned from using PostgreSQL (or relational databases in general) is never use an ORM . They cause far more trouble than they are worth and it's far easier to see what is going on when you're writing SQL queries directly.

Anyone not using ORM, will eventually build his own ORM (or at least query builder). I think the argument is not about "not using ORM", but more about "not using ORM made by someone else".

Re: Ways to shoot yourself in the foot with Postgres

#257

Post by dror »

bhawks wrote: post #232
hannofcart wrote: post #80

Earlier quoted context omitted.

Googling? That's so passe. I just enter my vague question into this AI chat thingy and I try the first thing that it tells me on my production server. Has worked fine for me so far. What could possibly go wrong?

Wow that's old school. Here we have an agent integrated into langchain that executes the command directly on the server. If there are any errors it uses ai to debug and fix them too. !/s see https://python.langchain.com/en/latest/modules/agents/toolki...

Agent, shmagent.

CREATE EXTENSION pggpt;

and you're done. The AI watches your db and adjusts the params on the fly as needed. We're joking, but I give it less than 12 months before there'll be something like this.

Re: Ways to shoot yourself in the foot with Postgres

#258

Post by j45 »

orthoxerox wrote: post #176
j45 wrote: post #170

Earlier quoted context omitted.

A middle ground that has had some success is managing a queue in Postgres that falls out business or application logic in the app, whether it’s micro service or monolith.

Yes, that's basically what we ended up doing: a queue of tasks in Postgres that a variable number of workers could access via a clever SP that encapsulated all inter-task conflicts and spat out the next task you were allowed to process.

There are some good business process management tools that could manage that mapping instead of the clever SP, but you have me intrigued.

Are there any links that could be helpful to work through the programs and caveats of such a SP?

Part of it for me is creating a table that can store the process in the database to be able to traverse it reasonably.

Re: Ways to shoot yourself in the foot with Postgres

#259

Post by orthoxerox »

j45 wrote: post #258
orthoxerox wrote: post #176

Earlier quoted context omitted.

Yes, that's basically what we ended up doing: a queue of tasks in Postgres that a variable number of workers could access via a clever SP that encapsulated all inter-task conflicts and spat out the next task you were allowed to process.

There are some good business process management tools that could manage that mapping instead of the clever SP, but you have me intrigued. Are there any links that could be helpful to work through the programs and caveats of such a SP? Part of it for me is creating a table that can store the process in the database to be able to traverse it reasonably.

It was basically a more complicated version of this: https://stackoverflow.com/questions/68809885/how-to-select-f...

Re: Ways to shoot yourself in the foot with Postgres

#260

Post by reitanuki »

mschuster91 wrote: post #100
reitanuki wrote: post #81

Earlier quoted context omitted.

You do not need to copy the DB, see pg_upgrade

That depends on the circumstances. To quote the manpage: > If you use link mode, the upgrade will be much faster (no file copying) and use less disk space, but you will not be able to access your old cluster once you start the new cluster after the upgrade. Link mode also requires that the old and new cluster data directories be in the same file system. (Tablespaces and pg_wal can be on different file systems.) Clone…

I'm not familiar this much with MySQL then: if you upgrade your MySQL database, can you still roll back to the old version? If you can't then this is the same restriction when you use the `pg_upgrade` tool in link mode.

(Note that one approach if concerned about the potential for downtime if it goes wrong might be to `pg_upgrade` your secondary in a hot-standby replication setup, then failover and `pg_upgrade` the former primary. In practice, `pg_upgrade` runs very quickly — seconds even on multi-terabyte databases — afaik it doesn't touch the actual data but just the metadata, but read the man pages for more intricate details on that :)).

Post reply on HN