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

191–200 of 329 posts

Re: Ways to shoot yourself in the foot with Postgres

#191

Post by h1fra »

alberth wrote: post #169
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 Dumb question: what's the use case for having a md5/hash field in your database?

I have answered here: https://news.ycombinator.com/item?id=35701126

In my case, I had to index big tables with URLs, with no need for partial match. I did it naively but did help a lot.

Re: Ways to shoot yourself in the foot with Postgres

#192

Post by smilliken »

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

The benefit is a proportionally smaller index.

Re: Ways to shoot yourself in the foot with Postgres

#193

Post by wongarsu »

azurelake wrote: post #155
nvilcins wrote: post #142

Earlier quoted context omitted.

> - 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…

Just FYI if you didn’t already know this: Any transaction which is run at a transaction isolation level other than SERIALIZABLE will not be affected by SSI. If you want to enforce business rules through SSI, all transactions should be run at the SERIALIZABLE transaction isolation level, and that should probably be set as the default. Given that running everything at SERIALIZABLE probably isn’t practical for you, I th…

Continuing with the FYIs:

Explicit locks can mean just calling LOCK TABLE account_balances IN SHARE ROW EXCLUSIVE MODE; early in the transaction and then doing SELECT ... FOR UPDATE; or similar configurations to enforce business rules where it matters.

https://www.postgresql.org/docs/current/sql-lock.html

Re: Ways to shoot yourself in the foot with Postgres

#194

Post by dist-epoch »

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

Have you measured the disk size of the index? That's where you should see a difference, not in speed.

Re: Ways to shoot yourself in the foot with Postgres

#195

Post by wongarsu »

masklinn wrote: post #99
jashmatthews wrote: post #88

Earlier quoted context omitted.

The defaults do suck but common storage options like SSDs or Elastic Block Storage still do sequential IO substantially faster than random.

Yes but nowhere near the extent rotating rust did. You may want to set random page costs higher than 1.0, in part because DB/FS-level pages and SSD blocks are completely different (and going through a block will be more efficient than having to hit multiple blocks), but probably 1.5 to 2.5. Interestingly enough according to some folks “seek” on EBS is highly concurrent, whereas “scan” is slow and more erratic, so you…

Looking up some random SSD benchmarks, 2.0 seems about right for high-quality SSDs. Though you might as well benchmark your specific setup.

Re: Ways to shoot yourself in the foot with Postgres

#196

Post by klysm »

white_dragon88 wrote: post #130
klysm wrote: post #106

Earlier quoted context omitted.

What do you use redis for?

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

So are you caching the query results keyed by query or something higher up?

Re: Ways to shoot yourself in the foot with Postgres

#197

Post by somehnguy »

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

Earlier quoted context omitted.

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

Have you measured the disk size of the index? That's where you should see a difference, not in speed.

It does appear smaller, but single digit megabytes on a table with millions of rows. Not a major difference for most use cases I think. But good to know for the few that it would make a difference.

Re: Ways to shoot yourself in the foot with Postgres

#198

Post by WirelessGigabit »

Waterluvian wrote: post #111
masklinn wrote: post #96

Earlier quoted context omitted.

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

We work in different places. Here the index in closed tickets would be smaller. But you know, some sales guy called and they want this little feature NOW.

Re: Ways to shoot yourself in the foot with Postgres

#199

Post by WirelessGigabit »

ellisv wrote: post #187
masklinn wrote: post #96

Earlier quoted context omitted.

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

Even when the column is made with NOT NULL?

Re: Ways to shoot yourself in the foot with Postgres

#200

Post by pramsey »

lopatin wrote: post #174
Calamitous wrote: post #173

Most common one I’ve seen in the last 5-10 years: using a JSON column instead of putting in a lookup table, or instead of properly analyzing and normalizing your data. That’s a mistake that you’ll be paying for for a while.

How much slower is it in your experience?

If you let the JSON blobs grow past the page size the cost of recovering TOAST tuples can be 10x reading main storage. Also JSON is fundamentally de-normalized so you can incur scan and read costs just from hauling out duplicate values where a nice normalized lookup would be snappy. And finally JSON recovery is going to pull the whole object every time, even though you are probably only interested in one element of the object, so again, higher recovery times compared to an equivalent normalized model.
Post reply on HN