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

111–120 of 167 posts

Re: Lesser-known Postgres features

#111

Post by KarlKemp »

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.

I believe it's about the common case of two events sharing a single (infinitely small) point of time: meeting A from 3 to 4, followed by meeting B from 4 to 5.

Those two time periods do overlap mathematically, but not physically: they share the common point of "4", even if that point has duration 0.

I know the iCal standard goes into this for a bit.

Re: Lesser-known Postgres features

#112

Post by hyzyla »

iaabtpbtpnn wrote: post #54
tbrock wrote: post #35

Im surprised there is no mention of foreign data wrappers, easily one of the best but lesser known features. Would i use them in production? no. Are they fun to play around with? Yes!

We use them in production, they work fine. You probably want to enable use_remote_estimate for postgres_fdw though.

Could you describe for what use case are you using foreign data wrapper in production?

Re: Lesser-known Postgres features

#113

Post by hackbinary »

netcraft wrote: post #19
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…

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…

>> I'll stop short of giving a recommendation or using the word "should" ...

I beleive the word you are looking for is "ought". :)

Re: Lesser-known Postgres features

#114

Post by afiori »

Ginden wrote: post #90
evandwight wrote: post #41

Earlier quoted context omitted.

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.

expecting collision to happen is probably a waste of time, but it still sounds like a good idea to bound how much damage a collision might cause.

Re: Lesser-known Postgres features

#116

Post by 1500100900 »

freeest-will wrote: post #20

Careful with DISTINCT ON, it can be extremely slow, like 100x slower than a verbose and less readable alternative. But it might not matter in your use case.

A hand-written loose index scan sometimes helps in a case like this. I think it's not built-in yet.

Re: Lesser-known Postgres features

#117

Post by djbusby »

codetrotter wrote: post #85
spiffytech wrote: post #44

Earlier quoted context omitted.

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

Came here to also hype ULID. It's just like INT, but 128bit, timestamps, every lang, and moving data around is trivial because no ID ever collide with anything else ever. Makes finding crap in my logs easier. (But UUID has that feature too) - it's the sorting that does it for me.

Re: Lesser-known Postgres features

#118

Post by ChristianGeek »

codetrotter wrote: post #85
spiffytech wrote: post #44

Earlier quoted context omitted.

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

Is there a PostgreSQL extension for ulid?

Re: Lesser-known Postgres features

#119

Post by tehlike »

yardstick wrote: post #27
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…

I prefer UUIDs as well but one other benefit of an integer type key is in the index. UUIDs, especially v4, make for bloated indexes. If there’s a very specific performance reason I’ll use int, otherwise uuid.

You can use sequential guids. That's what I do. Works well for not bloating indices.

Re: Lesser-known Postgres features

#120

Post by tehlike »

mjgp2 wrote: post #43
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…

Uuidv4 have worse performance when inserting into the btree for the primary key.

Use sequential guids.
Post reply on HN