13 ms·
Lesser-known Postgres features
- micw 5y agoI may be wrong, but isn't "overlapping" just "meeting_a.starts_at<meeting_b.ends_at && meeting_a.ends_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.
- jayd16 5y agoYou'd fail on the exactly the same time case.
- KarlKemp 5y agoI 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.
- zeroimpl 5y agoDon’t you just treat the range as half-open? Eg a '[)' range in Postgres.
- jpitz 5y agoWell, for this feature, it means if meeting a and meeting b share any point in time ( except their boundaries )
- micw 5y agoI have tried this with the condition I described and does not see any where it fails.
- micw 5y agoI get the exactly same result with that query as the "overlap" query: SELECT * FROM meetings, new_meetings WHERE new_meetings.starts_at<meetings.ends_at AND new_meetings.ends_at>meetings.starts_at;
- grangerg 5y agoBoth meeting A and B start at 2pm and end at 3pm. After you're done with that corner case, you may find it better to determine if they don't overlap and negate that.
- rvdginste 5y agoIf you use left-inclusive, right-exclusive intervals [), then then the overlap condition is: meeting_a.starts_at <= meeting_b.ends_at && meeting_b.starts_at <= meeting_a.ends_at So, indeed, the scenario as described in the article is more complex than needed.
- londons_explore 5y agoAvoid using lesser known features... They're the ones that will be hardest to migrate to a different database, most likely to be deprecated, and least likely to be understood by the next engineer to fill your shoes. While many of these are neat, good engineering practice is to make the simplest thing to get the job done.
- gmfawcett 5y agoI'm not sure why you are being downvoted. Your argument is reasonable and clearly presented, and "do the simplest thing that can work" is a tried and true design philosophy.
- handrous 5y agoCounterpoint: Databases often outlive applications. Database features usually do what they do very quickly and reliably. Used well, a full-featured database can make things like in-place refactors or re-writes of your application layer far easier, make everyday operation much safer, as well as making it much safe & useful to allow multiple applications access to the same database, which can be very handy for all kinds of reasons. > While many of these are neat, good engineering practice is to make the simplest thing to get the job done. That, or, use the right tool for the job.
- pyentropy 5y agoI'd rather have a non-portable SQL feature than an app-level "portable" implementation that almost certainly is slower and non-transactional. Migrating to another DB will always take work, no matter how much you try to ignore flavor-specific features. Query planning, encoding and charsets, locking abilities tend to be very different. A query can run fine in MySQL and cause a deadlock in Postgres even though it's syntactically valid in both.
- madmax108 5y agoThe upsert strategy mentioned (using ON CONFLICT to update instead of insert) is something we use internally quite a bit, esp in multi-insert transactions which were horrendous to handle without conflict resolution because any conflict would end up cancelling the entire transaction. Though I wish there was an easy way to figure out how many times "CONFLICT" actually occured (i.e how many times did we insert vs update).
- picardo 5y ago> Grant Permissions on Specific Columns This feature might be great for OLAP systems, but it's awful for OLTP systems. The last thing I want is debug permission errors bubbling from my database into the application layer. It may not be as efficient, but you should always manage your ACL rules in the application layer if you're building an OLTP app.
- blowski 5y agoThe word "always" should always be avoided in architecture discussions.
- picardo 5y agoI 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.
- blowski 5y agoEven then, it would be better phrased as a tradeoff. What's the downside of putting in the database? I'd guess it makes horizontally scaling harder, and it's less portable to other database vendors. Is there an upside, like you've got stronger data security guarantees?
- picardo 5y agoGood point. Phrasing it as a trade-off is the better approach. Agree with the downsides you listed, as well. Better security guarantees, for sure, but if an unauthorized user gains direct access to your database, you might have bigger problems from a security perspective.
- pfarrell 5y agoAgree with all points here. An upside to using db to handle data permissions is the data has the same protections if you are accessing with direct SQL or using an app. Also, those permissions would persist with the data in restored backups. I’m not advocating this and think for OLTP, building RBAC in the app layer is almost always a better idea, but these would be some benefits.
- cldellow 5y agoExpanding on the regex trick: I often use [.] instead of \. to match a period. They achieve the same thing, but [.] avoids the problem of your host language "helpfully" interpolating \. into . before sending the query to Postgres.
- sharadov 5y agoThe comment one is pretty neat, am going to start using it db=# COMMENT ON TABLE sale IS 'Sales made in the system'; COMMENT
- ccakes 5y agoIn-database comments combined with something like https://github.com/k1LoW/tbls https://github.com/k1LoW/tbls make for very cheap database documentation. No affiliation with tbls except that I'm a big fan
- gmac 5y agoOn this first point — 'Get the Number of Updated and Inserted Rows in an Upsert' — unfortunately this `xmax` method is not always reliable and there does not seem to be any reliable alternative. I posted to pgsql-hackers about this problem here: https://www.postgresql.org/message-id/DE57F14C-DB96-4F17-9254-AD0AABB3F81F%40mackerron.co.uk https://www.postgresql.org/message-id/DE57F14C-DB96-4F17-925...
- netcraft 5y agoGreat article, always learn a lot from this author. Here is another way I have used to do pivot tables / crosstab in postgres where you have a variable number of columns in the output: https://gist.github.com/ryanguill/101a19fb6ae6dfb26a01396c53fd3c66 https://gist.github.com/ryanguill/101a19fb6ae6dfb26a01396c53... You can try it out here: https://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=5dbbf7eadf0ed92f8d6a49fc5be8f3f2 https://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=5dbbf7eadf0ed...
- leephillips 5y agoYes, just the per-DB history was worth the price of admission. Too bad there is no RSS feed.
- gobblegobble2 5y agoThe feed is here: https://hakibenita.com/feeds/all.atom.xml https://hakibenita.com/feeds/all.atom.xml.
- leephillips 5y agoThanks!
- mikkelenzo 5y agoGreat article. Read through some of the other articles as well - some high quality content there!
- smackay 5y agoThe author's posts on Django are always worth reading. They're way more detailed than the run-of-the-mill rehash of the Django documentation that most blogs contain and there's always a nugget of "I never knew that" in them.
- jrumbut 5y agoI've seen similarly titled articles and they are usually full of the features that aren't used for a reason (MONEY column type, etc). It's nice to see one that's full of good advice.
- jakearmitage 5y agoI love those kinds of posts. Always something that you keep for life.
- crorella 5y agoGreat article, a lot of useful commands for those that work programming in the DB.
- gavinray 5y agoI 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-setting-the-value-of-an-auto-generated-key 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 run into massive headaches if you try to move things around. Identity columns don't, and avoid the issue altogether. https://www.2ndquadrant.com/en/blog/postgresql-10-identity-columns/ https://www.2ndquadrant.com/en/blog/postgresql-10-identity-c...
- cnity 5y agoI often move ID generation into the application layer (this also helps avoid things like enumeration attacks), and actually quite a lot of cool Postgres features blur that line a little bit. It's interesting to think sequences and other computational mechanisms in a DB, and whether they make architecting applications easier or harder. I don't have a strong opinion either way, but I'm interested in HN's opinion.
- superice 5y agoOne often hears the counterargument 'but using DB-specific features makes your application less portable!' to which I like to argue: When was the last time you moved an application from SQL-db to SQL-db engine? Follow up question: When was it ever 'easy' if you did? If you start from the basic premise that the database engine and the application are intertwined and are not loosely coupled, using Postgres-specific features feels much less icky from an architectural point of view. They are essentially part of your application given that you use something like Flyway for migrations and you don't manually run SQL against your production to install functions and triggers and such.
- toast0 5y agoAdding on, even when you are trying to make a portable application, you tend to want to make it work on either X or Y, and you can still exclude people from switching their existing data from one to the other. I've used software that at least tried to be portable, so you could install it with whatever database you had available.
- freeest-will 5y agoCareful 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.
- CabSauce 5y agoI don't belive this is correct for postgres. It's true for other RDBMS, I believe. <https://medium.com/@smitagudale712/distinct-vs-group-by-in-sql-server-b62527285f9c https://medium.com/@smitagudale712/distinct-vs-group-by-in-s...>
- freeest-will 5y agoI encountered this problem in Postgres 13. https://blog.timescale.com/blog/how-we-made-distinct-queries-up-to-8000x-faster-on-postgresql/ https://blog.timescale.com/blog/how-we-made-distinct-queries...
- 1500100900 5y agoA hand-written loose index scan sometimes helps in a case like this. I think it's not built-in yet.
- tbrock 5y agoIm 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!
- iaabtpbtpnn 5y agoWe use them in production, they work fine. You probably want to enable use_remote_estimate for postgres_fdw though.
- hyzyla 5y agoCould you describe for what use case are you using foreign data wrapper in production?
- shawn-butler 5y agoI used a foreign data wrapper to query elasticsearch indexes from within postgres.[0] It pushed alot of complexity down away from higher-level app developers not familiar with ES patterns. [0]: https://github.com/matthewfranglen/postgres-elasticsearch-fdw https://github.com/matthewfranglen/postgres-elasticsearch-fd...
- adgjlsfhk1 5y agoone place they are great in production is as part of a data migration. if you have an old database system with application code on top that you want to move to pure postgres, fdws provide a way to incrementally rewrite the application logic without ever having to do a hard cutover.
- laurent92 5y agoAtlassian 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!
- thearrow 5y agoFor the last point about "Find Overlapping Ranges", consider PostgreSQL range types [0] and their corresponding range operators [1]. [0]: https://www.postgresql.org/docs/current/rangetypes.html https://www.postgresql.org/docs/current/rangetypes.html [1]: https://www.postgresql.org/docs/current/functions-range.html https://www.postgresql.org/docs/current/functions-range.html
- grangerg 5y agoWhat I do is negate a check that determines if they don't overlap. Done.
- luhn 5y agoThat 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
- haki 5y agoI'm always looking for new copy tricks. Is "copy" a temporary table? Or a variable...?
- luhn 5y agoTemporary table. Although looking at the docs it seems CREATE TABLE AS is the recommended syntax. https://www.postgresql.org/docs/13/sql-createtableas.html https://www.postgresql.org/docs/13/sql-createtableas.html CREATE TEMP TABLE "copy" AS SELECT * FROM foobar;
- bajsejohannes 5y agoAnother 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.
- jeffbee 5y agoMy favorite relatively obscure pg feature is you can write stored procedures in perl, python, and tcl.
- mindcrime 5y agoNot to mention Java, R, Ruby, PHP, Scheme, and sh. https://www.postgresql.org/docs/9.5/external-pl.html https://www.postgresql.org/docs/9.5/external-pl.html Heck, there's even a pl/prolog out there, but it looks pretty old and I'm skeptical how useful it would be. https://github.com/salva/plswipl https://github.com/salva/plswipl
- brasetvik 5y agoThey're cool, but keep in mind that this often means you cannot run them on managed Postgres services.
- pageandrew 5y agoWow, 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)?
- jeffbee 5y agoI personally have never run into whatever the limitations might be of pl/python, but I suppose you could make a disaster by trying to dynamically load conflicting symbols or something like that. But I have used numpy in stored procedures, so at least that works. pl/sh also works wonderfully if you want to run a complex procedure in an isolated subprocess, but pl/sh is not part of the main postgresql distribution.
- mixmastamyk 5y agoThis example didn't bring anything new to the table, only adds redundant extra chars: SELECT * FROM users WHERE email ~ ANY('{@gmail\.com$|@yahoo\.com$}') Perhaps they were intending something similar to the following example instead. This one works but has a several potential lurking issues: with connection.cursor() as cursor: cursor.execute(''' SELECT * FROM users WHERE email ~ ANY(ARRAY%(patterns)s) ''' % { 'patterns': [ '@gmail\.com$', '@yahoo\.com$', ], }) The dictionary-style interpolation is unnecessary, the pattern strings should be raw strings (the escape is ignored only due to being a period), and this could be a SQL injection site if any of this is ever changed. I don't recommend this form as given, but it could be improved.
- jrmiii 5y ago> This example didn't bring anything new to the table, only adds redundant extra chars: OP indicated as much saying: > This approach is easier to work with from a host language such as Python I'm with you on the injection - have to be sure your host language driver properly escapes things.
- NightMKoder 5y agoOne 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 `DISTINCT ON` feels like a hack and doesn't cleanly fit into the SQL execution framework (e.g. you can't run a window function on the result without using subqueries). This feels cleaner, but I'm not actually aware of any other DBMS that supports `ARRAY_AGG` with `LIMIT`.
- mnahkies 5y agoI'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?
- NightMKoder 5y agoI 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.
- mnahkies 5y agoThat 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
- bbirk 5y agoI would use lateral join. Alternatively you can use window function instead of lateral join, buy from my experience lateral joins are usually faster for this kind of top-N queries in postgres. select user_table.user_id, tmp.post_id, tmp.text, tmp.timestamp from user_table left outer join lateral ( select post_id, text, timestamp from post where post.user_id = user_table.user_id order by timestamp limit 1 ) tmp on true order by user_id limit 30;
- brasetvik 5y agoTwo 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)
- dylanz 5y agoIt'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.
- brasetvik 5y agoYep! 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-materialize/ 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.
- skunkworker 5y agoAlso 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.
- brasetvik 5y agoRegarding "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)) < 'n'::text)) (Another approach could of course be to tokenize the email, but since it's about pattern matching in particular)
- ako 5y agoAlternatively you could use a trigram index which i've used in the past successfully to speed up queries using like '%gmail.com'. Blogpost discussing this approach: https://about.gitlab.com/blog/2016/03/18/fast-search-using-postgresql-trigram-indexes/ https://about.gitlab.com/blog/2016/03/18/fast-search-using-p...
- ForHackernews 5y agoSurprised to not see any mention of Postgres native pub/sub messaging: https://vijayg.com/postgresql-pubsub/ https://vijayg.com/postgresql-pubsub/ Is that feature considered well-known? Or is it so obscure this author didn't know about it?
- filomeno 5y agoI read that blog post but I don't quite understand it. I assumed it would work as a regular pub/sub pattern where you get notified when some event happens. However, in the attached example, they still poll the database every half second. I'm not sure I understand the idea.
- ForHackernews 5y agoIt's maybe not a great post, it was just one that came up when I searched. > JDBC driver cannot receive asynchronous notifications. Maybe their example is limited by the choice of client library. This might be a better example? https://tapoueh.org/blog/2018/07/postgresql-listen-notify/ https://tapoueh.org/blog/2018/07/postgresql-listen-notify/ Or the offical postgres docs are: https://www.postgresql.org/docs/14/sql-notify.html https://www.postgresql.org/docs/14/sql-notify.html https://www.postgresql.org/docs/14/sql-listen.html https://www.postgresql.org/docs/14/sql-listen.html https://www.postgresql.org/docs/14/libpq-notify.html https://www.postgresql.org/docs/14/libpq-notify.html
- filomeno 5y agoThanks, it now makes sense. Quite strange that this specific driver didn't support asynchronous notifications: if you have to poll the database anyway, there's no much difference between doing it without listen/notify support, I guess.
- boarnoah 5y agoThe upsert strategy mentioned (using ON CONFLICT to update instead of insert) has a catch, if your PK is a sequence it will increment even though the insert has failed. It's not a huge deal in most cases, is a bigger issue if you are running any sort of scheduled ETL to update (or insert new) things.
- orangepanda 5y agoDoes it matter? In case of rollbacks, the sequence would increment too. You have to handle gaps anyway.
- gerdesj 5y agoBeautifully presented, good advice. Enjoyed and bookmarked.
- Aeolun 5y ago> ERROR: permission denied for table users I would hesitate to call this a useful feature. This error message doesn’t tell you about the actual problem at all. How about something like: Permission denied, cannot select other than (id, name) from table users
- duncan-donuts 5y agoI just learned about OVERLAPS this week when reviewing a colleague’s code. We talked about the complexity of the conditions and found the overlaps operator and a ton of range operators. None of us knew they existed and they made our query a lot better.
- mrmister00 5y agoI enjoy using Postgres's Network Data Types. I'm sure it wasn't that hard to implement them, but the ease of use is undeniable.
- your_challenger 5y agoHaki Benita always has the best database blog posts <3
- est 5y agoGod damn I need to unlearn those because it will spoil my capability to develop non-postgres systems.
- quickthrower2 5y agoI’m crying in sql server land
- bifrost 5y agoOOooo! I love it! I've been a PG user for... a very long time. The fact it supports inet datatypes just makes me swoon TBH.
- sonium 5y agoI found this blog a gold-mine of information. Is there any book recommendation that goes in the same direction?