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

181–190 of 329 posts

Re: Ways to shoot yourself in the foot with Postgres

#181

Post by h1fra »

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

If it's highly biased indeed, in combinaison of a condition it's useful.

I was referring of indexing the column without distinction, the last time I checked (years ago) Postgres didn't do any statistical distribution so the query planner was always discarding the index anyway.

Re: Ways to shoot yourself in the foot with Postgres

#182

Post by fastball »

electroly wrote: post #180
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.

I definitely think they're great picks but I don't think the statement is making a particularly strong or interesting claim. I think it's equally true with basically any RDBMS in place of PostgreSQL. MySQL + Redis? Absolutely you'd be fine, tons of high-performance sites do this, probably more than use PostgreSQL. SQL Server + Redis? Still fine; you're Stack Overflow. Oracle + Redis? Weird choice but you'll still be…

Well what I'm saying here is that SQL is great and sometimes it's genuinely helpful to have a much faster key-value store in a variety of scenarios.

That's basically all you need.

From there, it's PostgreSQL and Redis specifically because Postgres is the best SQL database and Redis is the best high-performance KV store.

Re: Ways to shoot yourself in the foot with Postgres

#183

Post by h1fra »

nextaccountic wrote: post #158
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…

> - You can speed up, by a huge margin, big string indices with md5/hash index (only relevant for exact match) Do you mean a https://www.postgresql.org/docs/current/indexes-types.html#I... index? It's a 32-bit hash (but which hash is it, is it CRC32?). How to do a MD5 index? Anyway, MD5 is slow, does Postgres offer fast hashes like SipHash (DoS resistant) or FNV (not DoS resistant)?

You can store the md5 (or any hash) in a new column and use it in the index instead of the string column. It will still be a string index but much shorter. You have to be aware of hash collision but in my case it was a multi column index so the risk was close to zero. MD5 was maybe not the best choice but it's builtin so available everywhere.

What I did to not maintain a second column is to use the function directly in the index:

``` CREATE UNIQUE INDEX CONCURRENTLY "groupid_md5_uniq" ON "crawls" ("group_id", md5("url")); ```

``` SELECT * FROM crawls WHERE group_id= $0 AND md5(url) = md5($1) ```

This simple trick, that did not required an extensive refactor, speed up the query time by a factor of thousand.

Re: Ways to shoot yourself in the foot with Postgres

#184

Post by somehnguy »

dist-epoch wrote: post #138
somehnguy wrote: post #133

Earlier quoted context omitted.

> - Index on Boolean is useless, it's an easy mistake that will take memory and space disk for nothing. I’ve seen this advice elsewhere as well, but recently tried it and found it wasn’t the case on my data set. I have about 5m rows, with an extremely heavy bias on one column being ‘false’. Adding a plain index on this column cut query time in about half. We’re in the millisecond ranges here, but still.

Just index the less common value: CREATE INDEX ON session(is_active) WHERE is_active;

I tested that and it seemed to make 0 difference between a basic 'create index on table(column)'.

Re: Ways to shoot yourself in the foot with Postgres

#185

Post by mamcx »

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 work with all major RDBMS on the market (I integrate with ERPs/Accounting packages so even RDBMS that are niche and things that are a insult to call DBMS).

ANYONE that have a problem with RDBMS "functions and procedures & views (!)" are invariably mishandling the RDBMS: Bad schemas, null refactoring in the DB after years/decades(!) of cruft, re-implementation, poorly, of things the RDBMS already have (like for example, date types), procedural doing stuff that SQL already do easier and in short time, too big SQL that never, ever, use VIEWS to abstract away, the RDBMS was never upgraded or is assumed never will so nothing of the new things inventing like 10 years ago is used.

And that is a short list.

---

If you consider the RDBMS like the BEST programming language environment (sans SQL but still better than most languages for data!) and use the most BASIC ideas around it: like think a little about how do your schemas considering the queries you will do, some refactoring at least once every 5 years, pls!, use the CORRECT data types, pls pls!, use VIEWS pls pls pls!, etc your logic in triggers/functions MUST BE short and EASY.

Re: Ways to shoot yourself in the foot with Postgres

#186

Post by klysm »

somehnguy wrote: post #184
dist-epoch wrote: post #138

Earlier quoted context omitted.

Just index the less common value: CREATE INDEX ON session(is_active) WHERE is_active;

I tested that and it seemed to make 0 difference between a basic 'create index on table(column)'.

I know nothing about partial indices in Postgres, but it seems like for indexing a Boolean, you either index the true or false values right? I feel like Postgres could intelligently choose to pick the less frequent value

Re: Ways to shoot yourself in the foot with Postgres

#187

Post by ellisv »

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

Also good to remember that booleans can have 3 values: true, false, or null. Creating a partial index on `WHERE NOT NULL` can be helpful too.

Re: Ways to shoot yourself in the foot with Postgres

#188

Post by Mavvie »

> Setting acquired_at on read guarantees that each event is handled only once. After they've been handled, you can then delete the acquired events in batches too (there are better options than Postgres for permanently storing historical event data of unbounded size).

This bothers me. It's technically true, but ignores a lot of nuance/complexity around real-world event processing needs. This approach means you will never be able to retry event processing in case it fails (or your server is shut down/crashes). So you either have to update the logic to also process events where "acquired_at is older than some timeout", which breaks your "handled only once" guarantee, or you can change to a SELECT FOR UPDATE SKIP LOCKED approach which has its own problems like higher database resource usage (but at least it won't process a slow job twice at the same time).

Re: Ways to shoot yourself in the foot with Postgres

#189

Post by h1fra »

nvilcins wrote: post #142
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…

> - Related: be sure to understand the difference between transaction vs explicit locking, a lot of people assume too much from transaction and it will eventually breaks in prod. I recently went from: * somewhat understanding the concept of transactions and combining that with a bunch of manual locking to ensure data integrity in our web-app; to: * realizing how powerful modern Postgres actually is and delegating int…

I think, in the using Postgres as a queue scenario, it's not fixing the problem that two processes can read the same row at the same time thus both executing the process.

If you manually SELECT FOR UPDATE SKIP LOCKED LIMIT 1, then the second process will be forced to select the next task without waiting for the lock.

Post reply on HN