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

121–130 of 329 posts

Re: Ways to shoot yourself in the foot with Postgres

#121

Post by vbilopav »

senttoschool wrote: post #115
vbilopav wrote: post #61

Earlier quoted context omitted.

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…

Probably because you can't do proper testing as easy as application code. And debugging is much harder.

I disagree on both points.

Edit: but I was referencing specific performances claims, that you will somehow take some load of database server. I just don't see it.

Re: Ways to shoot yourself in the foot with Postgres

#122

Post by orthoxerox »

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

1. It's much easier to debug concurrency issues when you use SPs, but much harder to debug anything else. 2. At some point you will want to move some of your data into another system, and will have to pull the logic out into the application layer. 3. PL/pgSQL (or any other imperative SQL extension) isn't something you can find lots of devs on the market for. 4. Upgrades and rollbacks are much more painful and require downtime.

My team wrote a few critically important pieces of software that are running on Oracle, and here's why we did this:

1. Concurrency issues were the biggest pain point that we tried to avoid. We still had to fix a lot of bugs in the logic itself, and debugging them without unit tests was painful 2. We were tightly integrated with another system written in PL/SQL. When we started on v2, an independent solution, I moved almost all logic out of the database except for the critical synchronization logic. 3. We had a veteran team of PL/SQL devs in house. We still needed to get a subcontractor that wrote the API layer in Java, something PL/SQL isn't suited for at all. 4. Upgrades and rollbacks were a pain, especially after we had to move to a 24x7 SLA that left us with no upgrade window. Oracle has edition-based redefinition, but Postgres doesn't.

Re: Ways to shoot yourself in the foot with Postgres

#123

Post by jci »

senttoschool wrote: post #115
vbilopav wrote: post #61

Earlier quoted context omitted.

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…

Probably because you can't do proper testing as easy as application code. And debugging is much harder.

I think I’m this is a commonly stated fact, but I don’t find it particularly true. Like any other technology, you just need to put in some initial effort to set up your test framework. In the case of PostgreSQL, pgTAP does a great job.

Re: Ways to shoot yourself in the foot with Postgres

#124

Post by Whitespace »

jaggederest wrote: post #19

Sometimes you must `EXPLAIN ANALYZE` expensive queries in production, sadly. The behavior of postgres (even on non-bitwise copies) can be different under load. The biggest way I have seen this be true is with fragmented tables/indexes - same data but organized more compactly can change planner behavior. Article actually touches on another way that can be true - if your `work_mem` is drastically different the planner…

The article and the comments here don't make it clear why running it in production shouldn't be done. If slow_query is already running in production, why would running EXPLAIN ANALYZE slow_query be bad?

Is the overhead of running EXPLAIN ANALYZE so much worse than running slow_query itself?

Re: Ways to shoot yourself in the foot with Postgres

#125

Post by code_biologist »

jaggederest wrote: post #95
fastball wrote: post #79

Earlier quoted context omitted.

I am of the firm opinion that Postgres + Redis are basically the only DBs you ever need.

Agreed, and I'm over in the corner sharpening an axe, looking at Redis with highly malicious intent, too. I think many of the things people use Redis for could be accomplished with a small postgres server and a decent schema e.g. `create table redislike (id bigserial primary key, contents jsonb);`

You can stuff some pretty insane stuff in Redis keys beside ints, giant multi MB strings with globby query patterns. Your general point is dead on.

Re: Ways to shoot yourself in the foot with Postgres

#126

Post by olavgg »

SigmundA wrote: post #102
albertopv wrote: post #24

Two years ago I moved to a new company using Postgres as THE relational db, coming from years of Sql Server I found poor query plan issues troubleshooting tools. Anyway, I don't know if it's the same in Postgres, but in Sql Server an OR condition like that could kill your performance quite easily in a relatively complex query, often I had to refactor my query to a UNION (usually with ALL to avoid a distinct step, but…

Compared to Sql server, PG's lack of true clustered indexes and no query plan re-use was very surprising, also no hints to wrangle a bad query plan!

And SQL server's lack of backup with SSH piping makes it basically pain to administer.

Re: Ways to shoot yourself in the foot with Postgres

#127

Post by petepete »

hans_castorp wrote: post #97
jaggederest wrote: post #92

Earlier quoted context omitted.

Another footgun is that, while `DELETE FROM table_name` is transactional, TRUNCATE is not transactional . Once you push the truncate button, that data is gone in every transaction everywhere all at once. You should be very hesitant about using TRUNCATE on a production database unless that table (and all related foreign keyed tables) are truly ephemeral. Even if the data is cleared every night at midnight, for example…

TRUNCATE is absolutely transactional. You can rollback a TRUNCATE statement if you run it in a transaction. https://dbfiddle.uk/xkgzxMUU The only difference to other DML statements is, that it will put an exclusive lock on the table. So until the TRUNCATE is committed, no other transaction can read from the table.

> The only difference to other DML statements is

Truncate is DDL. It's like dropping and recreating the table in a single operation.

Re: Ways to shoot yourself in the foot with Postgres

#128

Post by matwood »

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

Like most things, it depends.

Having application logic in the db is harder to debug and test (and possibly scale, but that also depends). But as you mention, it can be much faster if the logic is working on a lot of records.

Also, IME, the data store often far outlives the original application. Having the logic tightly coupled to the data model means future applications are less like to break the assumptions made in the original data model.

Re: Ways to shoot yourself in the foot with Postgres

#130

Post by white_dragon88 »

klysm wrote: post #106
fastball wrote: post #79

Earlier quoted context omitted.

I am of the firm opinion that Postgres + Redis are basically the only DBs you ever need.

What do you use redis for?

Caching frequently fetched complex objects to mitigate load on the Postgres DB.
Post reply on HN