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

111–120 of 329 posts

Re: Ways to shoot yourself in the foot with Postgres

#111

Post by Waterluvian »

masklinn wrote: post #96
h1fra wrote: post #89

Few tips I gathered along the years: - Configure Vacuum and maintenance_work_mem regularly if your DB size increases, if you allocate too much or too often it can clog up your memory. - If you plan on deleting more than a 10000 rows regularly, maybe you should look at partition, it's surprisingly very slow to delete that "much" data. And even more with foreign key. - Index on Boolean is useless, it's an easy mistake…

> Index on Boolean is useless, it's an easy mistake that will take memory and space disk for nothing. However if the field is highly biased (e.g. 90 or 99% one value) it can be useful to create a partial index on the rarer value. Though even better is to create a partial index on the other stuff filtered by that value, especially if the smaller set is the commonly queried one (e.g. soft-deletes).

Yeah. Finding ”open” tickets, for example. There’s actually some really good cases to index on a Boolean.

Re: Ways to shoot yourself in the foot with Postgres

#112

Post by ahachete »

The recommendation for work_mem doesn't account for all the possible cases. It is already noted elsewhere on this thread [1] that the use of memory per connection could be higher than work_mem, and this is true even if you don't use stored procedures, as the memory incurred can be on a per-query node. So it can be a multiple of work_mem per connection.

But there's a factor that even worsens this: parallel query, which is typically enabled by default, and will add another multiple to work_mem.

Tuning work_mem is a hard art, and requires a delicate balance between trying to optimize some query's performance (that could avoid touching disk or using some indexes) vs the risk of causing db-wide errors like OOMs (very dangerous) or running out of SHM (errors only on queries being run, but still not desirable). So I normally lean on being quite conservative (db stability first!) so I divide the available memory (server memory - OS memory - shared_buffers and other PG buffers memory) by the number of connections, also divided by the parallelism and by another multiple factor --and then leave some additional room.

In any case I'd recommend reviewing the detailed information, suggestions and links on the topic on postgresqlCONF [2] (disclaimer: a free project built by my company)

[1]: https://news.ycombinator.com/item?id=35697986

[2]: https://postgresqlco.nf/doc/en/param/work_mem/

(edit: formatting)

Re: Ways to shoot yourself in the foot with Postgres

#113

Post by re-thc »

postdb wrote: post #38
remus wrote: post #34

Earlier quoted context omitted.

I think the article is kinda mixing two points here. One the one hand, it is sensible to try and keep all your business logic in one place (could be the database, could be the application) as spreading it across multiple places can make it hard to maintain. The current trend is to do your business logic in the application and treat the db as a data storage layer. The point in the article is that if you're using this…

For sure, it doesn't matter where you put it if it is in one place. If one look at company like superbase, and their product like PostgresREST. It is just way faster way to develop API, and it will scale too. Often it is about how one 'horizontally' scale.

PostgresREST was invented way before Superbase was around. Just a FYI.

Re: Ways to shoot yourself in the foot with Postgres

#114

Post by jeltz »

moring wrote: post #105
h1fra wrote: post #89

Few tips I gathered along the years: - Configure Vacuum and maintenance_work_mem regularly if your DB size increases, if you allocate too much or too often it can clog up your memory. - If you plan on deleting more than a 10000 rows regularly, maybe you should look at partition, it's surprisingly very slow to delete that "much" data. And even more with foreign key. - Index on Boolean is useless, it's an easy mistake…

> Index on Boolean is useless, it's an easy mistake that will take memory and space disk for nothing. Why? Is it because an index on the bool alone, with symmetric distribution, will still leave you with half the table to scan? In other words, does that statement apply to biased distribution (as mentioned by another response) or indices on multiple fields of which one is a boolean?

Yes, it is because it leaves you with half the table the scan while adding the overhead of doing an index scan. And of you have a biased distribution you probably want a partial index since those are smaller.

Re: Ways to shoot yourself in the foot with Postgres

#115

Post by senttoschool »

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…

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

Re: Ways to shoot yourself in the foot with Postgres

#117

Post by veddan »

ainar-g wrote: post #54
call_me_g0d wrote: post #20

I could never warm up to PostgresSQL. Guess I'll always stick with MySQL

Can I ask why? I generally only see the “I switched from MySQL to PostgreSQL and loving it” comments in my info-bubble, so it'd be interesting to know what people who prefer to use MySQL feel is still lacking in PostgreSQL.

I'm a bit behind on modern PostgreSQL so I might be wrong, but I believe MySQL has better support for what they call "online DDL" (modifying tables without blocking simultaneous queries).

Last time I checked, MySQL supported it in more cases. MySQL can also be explicit about it via "lock assertions":

  ALTER TABLE ..., LOCK=NONE;
will give an error if the requested operation can't be performed while still allowing concurrent reads and writes to the table (if you're fine with preventing writes you can use LOCK=SHARED).

The LOCK clause isn't just an assertion and can actually affect how MySQL performs the operation, but I tend to think of it as asserting "this won't cause downtime by locking this multi billion-row table while it spends an hour rewriting it".

Re: Ways to shoot yourself in the foot with Postgres

#119

Post by peter_retief »

Handed down to me by the grey beards! I like that. Postgres default settings are not the best, I remember moving from mysql to postgres some time back and discovered this. I always wondered why the default would not work best for most deployments? Anyone know why this is so?

Re: Ways to shoot yourself in the foot with Postgres

#120

Post by code_biologist »

moring wrote: post #105
h1fra wrote: post #89

Few tips I gathered along the years: - Configure Vacuum and maintenance_work_mem regularly if your DB size increases, if you allocate too much or too often it can clog up your memory. - If you plan on deleting more than a 10000 rows regularly, maybe you should look at partition, it's surprisingly very slow to delete that "much" data. And even more with foreign key. - Index on Boolean is useless, it's an easy mistake…

> Index on Boolean is useless, it's an easy mistake that will take memory and space disk for nothing. Why? Is it because an index on the bool alone, with symmetric distribution, will still leave you with half the table to scan? In other words, does that statement apply to biased distribution (as mentioned by another response) or indices on multiple fields of which one is a boolean?

Half the rows to scan in 99% of cases means you’ll still hit every page and incur exactly the same amount of IO (the expensive part) as a full table scan.
Post reply on HN