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

91–100 of 167 posts

Re: Lesser-known Postgres features

#91

Post by NightMKoder »

mnahkies wrote: post #74
NightMKoder wrote: post #63

One of the hardest types of queries in a lot of DBs is the simple `min-by` or `max-by` queries - e.g. "find the most recent post for each user." Seems like Postgres has a solution - `DISTINCT ON` - though personally I've always been a fan of how BigQuery does it: `ARRAY_AGG`. e.g. SELECT user_id, ARRAY_AGG(STRUCT(post_id, text, timestamp) ORDER BY timestamp DESC LIMIT 1)[SAFE_OFFSET(0)].* FROM posts GROUP BY user_id…

I've generally used `PARTITION BY` to achieve the same effect in big query (similar to what was suggested in the article for postgres). Does the `ARRAY_AGG` approach offer any advantages?

I assume you mean `ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) as row_number` with a `WHERE row_number = 1`. We ran into issues with memory usage (i.e. queries erroring because of too high memory usage).

The `ARRAY_AGG` approach uses a simple heap, so it's as efficient as MIN/MAX because it can be computed in parallel. ROW_NUMBER however needs all the rows on one machine to number them properly. ARRAY_AGG combination is associative whereas ROW_NUMBER combination isn't.

Re: Lesser-known Postgres features

#92

Post by micw »

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 get the exactly same result with that query as the "overlap" query:

SELECT * FROM meetings, new_meetings WHERE new_meetings.starts_atmeetings.starts_at;

Re: Lesser-known Postgres features

#93

Post by olddb »

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…

>You might think this is trivial -- but SERIAL creates an "owned" (by a certain user) sequence behind the scenes, and so you run into massive headaches if you try to move things around.

Maybe it is because I'm too old, but making id grow by sequence is the way how things 'ought' to be done in the old skool db admin ways. Sequences are great, it allows the db to to maintain two or more sets of incremental ids, comes in very handy when you keeping track of certain invoices that needs to have a certain incremental numbers. By exposing that in the CREATE statement of the table brings transparency, instead of some magical blackbox IDENTITY. However, it is totally understandable from a developer's perspective that getting to know the sequences is just unneeded headache. ;-)

Re: Lesser-known Postgres features

#94

Post by tomnipotent »

orf wrote: post #51
mjgp2 wrote: post #43

Earlier quoted context omitted.

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

Why would you use a btree for them? Wouldn’t a hash index be ideal?

Some databases don't give you a choice, and your table data is actually stored in a clustered index sorted by primary key. This means random inserts when using a UUID (without embedded time), and more page splitting.

Re: Lesser-known Postgres features

#95

Post by skunkworker »

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…

Also explain(analyze,buffers) is by far my favorite. It shows you the number of pages loaded from disk or cache.

Also to note: EXPLAIN just plans the query, EXPLAIN (ANALYZE) plans and runs the query. Which can take awhile in production.

Re: Lesser-known Postgres features

#96

Post by tomnipotent »

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 generally use both - a serial for internal RDBMS storage to coerce writes to be in order, and a secondary GUID that is app-specific and generated. The app will never care about the serial, and the RDBMS doesn't care about the GUID other than secondary index look-ups (since they're rarely used for range scans).

Re: Lesser-known Postgres features

#97

Post by mnahkies »

NightMKoder wrote: post #91
mnahkies wrote: post #74

Earlier quoted context omitted.

I've generally used `PARTITION BY` to achieve the same effect in big query (similar to what was suggested in the article for postgres). Does the `ARRAY_AGG` approach offer any advantages?

I assume you mean `ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) as row_number` with a `WHERE row_number = 1`. We ran into issues with memory usage (i.e. queries erroring because of too high memory usage). The `ARRAY_AGG` approach uses a simple heap, so it's as efficient as MIN/MAX because it can be computed in parallel. ROW_NUMBER however needs all the rows on one machine to number them properly. ARRAY_AGG combi…

That makes sense, I might have a play with the array agg approach. I'm especially curious if it has any impact on slot time consumed

Re: Lesser-known Postgres features

#99

Post by ccakes »

sharadov wrote: post #98

The comment one is pretty neat, am going to start using it db=# COMMENT ON TABLE sale IS 'Sales made in the system'; COMMENT

In-database comments combined with something like https://github.com/k1LoW/tbls make for very cheap database documentation.

No affiliation with tbls except that I'm a big fan

Re: Lesser-known Postgres features

#100

Post by christophilus »

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…

A friend of mine recently recommended CUIDs, which I'd never heard of before. Any thoughts on those? Neither usecuid.org nor https://www.npmjs.com/package/cuid give me much confidence, though (the former is not https, and the latter has not been updated in 2 years... which might mean it's done, or might mean it's abandoned).
Post reply on HN