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

71–80 of 167 posts

Re: Lesser-known Postgres features

#71

Post by brasetvik »

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 you'll not spot the missing indexes (on e.g. reverse foreign keys, I see missing often enough)

- `EXPLAIN` and `EXPLAIN ANALYZE`. Don't save minutes of looking at your query plans during development by spending hours fixing performance problems in production. EXPLAIN all the things.

A significant percentage of production issues I've seen (and caused) are easily mitigated by those two.

By learning how to read and understand execution plans and how and why they change over time, you'll learn a lot more about databases too.

(CTEs/WITH-expressions are life changing too)

Re: Lesser-known Postgres features

#73

Post by iaabtpbtpnn »

cormacrelf wrote: post #60
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…

That syntax seems a bit verbose for a column that will most likely appear on every table you ever create. How about: id int SIMILAR TO SERIAL BUT WITHOUT PROBLEMS MOVING THINGS AROUND And the other variant for when you aren’t sure that worked: id int SIMILAR TO SERIAL BUT WITH EVEN FEWER PROBLEMS MOVING THINGS AROUND

Complain to the SQL standard authors, not the Postgres developers :)

Re: Lesser-known Postgres features

#74

Post by mnahkies »

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?

Re: Lesser-known Postgres features

#75

Post by dylanz »

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.

Re: Lesser-known Postgres features

#76

Post by brasetvik »

Regarding "Match Against Multiple Patterns", the examples are about finding the _suffixes_ of something, email domains in the example.

An attempt to find a suffix like that will not be able to use an index, whereas creating a functional index on the reverse and looking for the reversed suffix as a prefix will be:

  # create table users (id int primary key, email text);
  CREATE TABLE
  # create unique index on users(lower(email));
  CREATE INDEX
  # set enable_seqscan to false;
  SET
  
  # insert into users values (1, 'foo@gmail.com'), (2, 'bar@gmail.com'), (3, 'foo@yahoo.com');
  INSERT 0 3
  # explain select * from users where email ~* '@(gmail.com|yahoo.com)$';
                                  QUERY PLAN
  --------------------------------------------------------------------------
   Seq Scan on users  (cost=10000000000.00..10000000025.88 rows=1 width=36)
     Filter: (email ~* '@(gmail.com|yahoo.com)$'::text)

  # create index on users(reverse(lower(email)) collate "C"); -- collate C explicitly to enable prefix lookups
  CREATE INDEX

  # explain select * from users where reverse(lower(email)) ~ '^(moc.liamg|moc.oohay)';
                                               QUERY PLAN
  ----------------------------------------------------------------------------------------------------
   Bitmap Heap Scan on users  (cost=4.21..13.71 rows=1 width=36)
     Filter: (reverse(lower(email)) ~ '^(moc.liamg|moc.oohay)'::text)
     ->  Bitmap Index Scan on users_reverse_idx  (cost=0.00..4.21 rows=6 width=0)
           Index Cond: ((reverse(lower(email)) >= 'm'::text) AND (reverse(lower(email))
(Another approach could of course be to tokenize the email, but since it's about pattern matching in particular)

Re: Lesser-known Postgres features

#77

Post by luhn »

haki wrote: post #46
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...?

Temporary table. Although looking at the docs it seems CREATE TABLE AS is the recommended syntax. https://www.postgresql.org/docs/13/sql-createtableas.html

    CREATE TEMP TABLE "copy" AS
    SELECT * FROM foobar;

Re: Lesser-known Postgres features

#78

Post by williamdclt »

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.

That is true, but that has never been a bottleneck for me. We have a table with multiple hundreds of millions of rows, 20ish UUIDs indexes (yes it did get out of hand) and insert time isn't an issue.

Ofc, your app performance requirements might vary, and it is objectively true that UUIDs aren't ideal for btree indexes.

Re: Lesser-known Postgres features

#79

Post by williamdclt »

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?

part of the point of UUIDs is that you don't have to. Collision is unlikely enough that your time would be more wisely spent worrying about the sun exploding unless you generate an absolutely absurd amount of data.
Post reply on HN