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

41–50 of 167 posts

Re: Lesser-known Postgres features

#41

Post by evandwight »

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…

Do you write logic to handle collisions?

Re: Lesser-known Postgres features

#42

Post by picardo »

mcherm wrote: post #29
picardo wrote: post #12

Earlier quoted context omitted.

I qualified it by saying "if you're building an OLTP app" though. I speak from personal experience. If you know a good usecase for ACL in database for OLTP workflows, I'm all ears.

> If you know a good usecase for ACL in database for OLTP workflows, I'm all ears. Here's one that's meaningful to me. We have a single database with five different applications that access it, each of them managed by a separate team. By enforcing access constraints in the database we guarantee the access constraints will be applied in all cases. It is difficult to ensure that in application code managed by separate…

I see, thanks for your input. Most of the workflows I worked on involved provisioning one database per app, so I hadn't entertained this angle.

Are there any risks to changing ACL rules on a production database server from a stability perspective?

Re: Lesser-known Postgres features

#43

Post by mjgp2 »

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…

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

Re: Lesser-known Postgres features

#44

Post by spiffytech »

gavinray wrote: post #25
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…

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

[1] https://github.com/ulid/spec

[2] https://github.com/segmentio/ksuid

[3] ulid discussion: https://news.ycombinator.com/item?id=18768909

UUIDv7 discusison: https://news.ycombinator.com/item?id=28088213

Re: Lesser-known Postgres features

#45

Post by bajsejohannes »

Another great hidden feature is the LISTEN/NOTIFY system. You can use it to watch for changes on a table, and then use that to e.g. update a view in the UI.

For example create a table with a trigger on insert "NOTIFY new_data". Then on query do

    LISTEN new_data;
    SELECT ...;
Now you'll get the results and any future updates.

Re: Lesser-known Postgres features

#46

Post by haki »

luhn wrote: post #40

That COPY trick is really neat. I've always used SELECT INTO. SELECT * INTO TEMP copy FROM foobar; \copy "copy" to 'foobar.csv' with csv headers

I'm always looking for new copy tricks. Is "copy" a temporary table? Or a variable...?

Re: Lesser-known Postgres features

#48

Post by pageandrew »

jeffbee wrote: post #47

My favorite relatively obscure pg feature is you can write stored procedures in perl, python, and tcl.

Wow, I've been writing a lot of PL/pgSQL recently and did not know this.

Would these be drop in replacements for PL/pgSQL? Are there any performance tradeoffs to using one over another? Any changes in functionality (aka functions you can call)?

Re: Lesser-known Postgres features

#49

Post by laurent92 »

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!

Atlassian gives us sales figures as a large CSV. Nothing better than loading the CSV as a Postgres view with a foreign data wrapper, and performing pivots/joins/anything on it. Replace the file with a more recent one, and you even have versioning!

Re: Lesser-known Postgres features

#50

Post by gavinray »

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…

Wow, neat. TIL
Post reply on HN