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

Lesser-known Postgres features

hakibenita.com

Post reply on HN
Print view View original

81–90 of 167 posts

Re: Lesser-known Postgres features

#81

Post by brasetvik »

dylanz wrote: post #75
brasetvik wrote: post #71

Two things that aren't exactly lesser-known, but that I wish more used continuously as part of development: - generate_series(): While not the best to make _realistic_ test data for proper load testing, at least it's easy to make a lot of data. If you don't have a few million rows in your tables when you're developing, you probably don't know how things behave, because a full table/seq scan will be fast anyway - and…

It's worth to note that earlier versions of PostgreSQL didn't include the "AS NOT MATERIALIZED" option when specifying CTE's. In our setup, this had huge hits to performance. If we were on a more recent version of PostgreSQL (I think 11 in this case), or if the query writer just used a sub-query instead of a CTE, we would have been fine.

Yep! A lot of older posts about CTEs largely advice against them for this reason.

Postgres 12 introduced controllable materialization behaviour: https://paquier.xyz/postgresql-2/postgres-12-with-materializ...

By default, it'll _not_ materialise unless it's recursive, or if there are >1 other CTEs consuming it.

When not materializing, filters may push through the CTEs.

Re: Lesser-known Postgres features

#82

Post by stjohnswarts »

spiffytech wrote: post #44
gavinray wrote: post #25

Earlier quoted context omitted.

Yeah I recognize the arguments for UUID keys: - Avoids people being able to just iterate through records, or to discover roughly how many records of a thing you have - Allows you to generate the key before the row is saved I think I default to auto-increment ID's due to: - Familiarity bias - They have a temporal aspect to them (IE, I know row with ID 225 was created before row with ID 392, and approximately when they…

> They have a temporal aspect to them (IE, I know row with ID 225 was created before row with ID 392, and approximately when they might be created) UUIDv7 (currently a draft spec[0]) are IDs that can be sorted in the chronological order they were created In the meantime, ulid[1] and ksuid[2] are popular time-sortable ID schemes, both previously discussed on HN[3] [0] https://datatracker.ietf.org/doc/html/draft-peabod…

Hmmm that's really useful :) . thanks!

Re: Lesser-known Postgres features

#83

Post by williamdclt »

handrous wrote: post #67
sodapopcan wrote: post #38

Earlier quoted context omitted.

How often does migrating databases ever actually happen? I'm not saying it doesn't happen, but I've never experienced it or know anyone who has (I've asked!). I certainly would shy away from using features that make my app code cleaner and improves the all-around performance of the app on the slim-to-none chance that one day I might one day have to migrate off of postgres.

20-year career so far. Never seen a database swapped out. I've seen "apps" replaced on top of databases, or more programs added to access the same database. I've seen the app and database both get thrown out and replaced, because they were tightly coupled[0], as the parent advocates (Rails + ActiveRecord seems to be prime for this kind of "gotta throw it all out" situation). I've never seen the program stay the same…

I've seen it firsthand, but we're talking about an early-stage startup moving from MySQL to Postgres. It's definitely not enough to convince me not to use database-specific features.

Re: Lesser-known Postgres features

#84

Post by uhoh-itsmaciek »

gavinray wrote: post #14

I want to stress the importance of not using id int SERIAL If you are on a somewhat recent version of postgres, please do yourself a favor and use: id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY An "identity column", the part here: https://hakibenita.com/postgresql-unknown-features#prevent-s... You might think this is trivial -- but SERIAL creates an "owned" (by a certain user) sequence behind the scenes, and so…

Another favor you should do yourself is use bigint keys (unless using uuid as suggested elsewhere, or you're very, very sure you'll never chew through 2 billion ids).

Re: Lesser-known Postgres features

#85

Post by codetrotter »

spiffytech wrote: post #44
gavinray wrote: post #25

Earlier quoted context omitted.

Yeah I recognize the arguments for UUID keys: - Avoids people being able to just iterate through records, or to discover roughly how many records of a thing you have - Allows you to generate the key before the row is saved I think I default to auto-increment ID's due to: - Familiarity bias - They have a temporal aspect to them (IE, I know row with ID 225 was created before row with ID 392, and approximately when they…

> They have a temporal aspect to them (IE, I know row with ID 225 was created before row with ID 392, and approximately when they might be created) UUIDv7 (currently a draft spec[0]) are IDs that can be sorted in the chronological order they were created In the meantime, ulid[1] and ksuid[2] are popular time-sortable ID schemes, both previously discussed on HN[3] [0] https://datatracker.ietf.org/doc/html/draft-peabod…

Yup this. Ulid is great, and implemented in many languages. For example there’s a crate named Ulid that you can use that gives you ulid’s in Rust.

Re: Lesser-known Postgres features

#86

Post by micw »

I may be wrong, but isn't "overlapping" just "meeting_a.starts_atmeeting_b.starts_at"?

Or in words: There is an overlap if meeting A starts before meeting B ends and meeting A ends after meeting B starts.

So the scenario described looks way more complex as it actual seems to be.

Re: Lesser-known Postgres features

#87

Post by jpitz »

micw wrote: post #86

I may be wrong, but isn't "overlapping" just "meeting_a.starts_at meeting_b.starts_at"? Or in words: There is an overlap if meeting A starts before meeting B ends and meeting A ends after meeting B starts. So the scenario described looks way more complex as it actual seems to be.

Well, for this feature, it means if meeting a and meeting b share any point in time ( except their boundaries )

Re: Lesser-known Postgres features

#88

Post by jayd16 »

micw wrote: post #86

I may be wrong, but isn't "overlapping" just "meeting_a.starts_at meeting_b.starts_at"? Or in words: There is an overlap if meeting A starts before meeting B ends and meeting A ends after meeting B starts. So the scenario described looks way more complex as it actual seems to be.

You'd fail on the exactly the same time case.

Re: Lesser-known Postgres features

#89

Post by micw »

jpitz wrote: post #87
micw wrote: post #86

I may be wrong, but isn't "overlapping" just "meeting_a.starts_at meeting_b.starts_at"? Or in words: There is an overlap if meeting A starts before meeting B ends and meeting A ends after meeting B starts. So the scenario described looks way more complex as it actual seems to be.

Well, for this feature, it means if meeting a and meeting b share any point in time ( except their boundaries )

I have tried this with the condition I described and does not see any where it fails.

Re: Lesser-known Postgres features

#90

Post by Ginden »

evandwight wrote: post #41
netcraft wrote: post #19

Earlier quoted context omitted.

I'll stop short of giving a recommendation or using the word "should", but ill give encouragement to consider using uuid's for keys. I have used them in several systems and have never had any issues with them, and they solve so many issues. The ability to generate a key on the client or on the server or in the database is great for one. And the fact that keys are unique not only in a table but in the system (or many…

Do you write logic to handle collisions?

Unless you are writing nuclear warhead management system, writing UUID collision handling is waste of time. Client can retry on top level if request failed.
Post reply on HN