9 ms·

PostgreSQL Features to Try

378 points by rodmena 7y ago

mistrial9 7y ago
re Inheritance -- used very well to solve a "Pretty Big Data" problem, making 60 child tables with two fields to differentiate.. some craft is required when building indexes, and in loading the data. Overall this inherited tables solution works very well and had many small positive side effects over time. As always, PG is rock-solid, everytime.
everdev 7y ago
A nice list but I'd also add row-level security (RLS): https://info.crunchydata.com/blog/a-postgresql-row-level-security-primer-creating-large-policies https://info.crunchydata.com/blog/a-postgresql-row-level-sec... It's amazing how much effort we put into restricting access on the server, but ignore user roles on the data layer.
- paulryanrogers 7y ago
  IME at scale you need a connection pool. Pools usually cache by connection credentials. And maintaining a large variety of roles with disparate access levels is cumbersome. (I've tried.) The end result is that column, much less row, level controls end up unused in most cases. Very security conscious places may just use stored procedures for everything, and limit access to those.
  - tellak 7y ago
    Note that you don’t have to use the connection credentials to make use of row level security in pg. the security policy can be based on session variables (or anything really) which can be changed during the connection. Edit: it’s not the same thing but fwiw pg row level security is completely decoupled from the dbms access control.
- usgroup 7y ago
  Couldn’t agree more ... row level security is brilliantly simple too. Access defined by query.
- natmaka 7y ago
  > It's amazing how much effort we put into restricting access on the server, but ignore user roles on the data layer. That's one of the reasons PostgREST seems promising to me. http://postgrest.org http://postgrest.org
  - everdev 7y ago
    I've looked into it as well and it looks really interesting. The biggest downside for me was that at some point I still needed a backend server to hold secrets or run business logic. At that point, Postgresql was just another server / dependency that I couldn't justify as all of the nifty RLS was done inside Postgres. It definitely introduced me to RLS though.
  - BerislavLopac 7y ago
    I've also been a happy user of Sandman: https://github.com/jeffknupp/sandman2 https://github.com/jeffknupp/sandman2
    - natmaka 7y ago
      There is a list at https://github.com/dbohdan/automatic-api/ https://github.com/dbohdan/automatic-api/
  - paulddraper 7y ago
    Check out https://www.graphile.org/postgraphile/ https://www.graphile.org/postgraphile/
ralusek 7y ago
I had a case where I needed to build some denormalized redundant models to handle certain queries. I also had a write-heavy, append-only event log portion of my data model. I figured it would scale very differently, and could be partitioned very differently than the rest of the data, so I wanted to try putting it in a separate DB and using a dblink/foreign data wrappers. I use DB triggers and dblink to produce the denormalized data from the event stream, and I consume the denormalized data from my application using pg notify. So, even if I do an insert or update with a SQL statement directly to the DB, it will produce the denormalized data without relying on application logic/hooks. It's definitely air tight, and nice knowing that no version of my data can currently be inconsistent, but there are definitely costs. For one, it's just slow. Bear in mind, I'm on commodity hardware, like t2, but it still seems slow, as some writes take up to 1-2s. It's also not great that it's basically magic, I can't really make alterations to change or improve what it's doing. Lastly, it's hard to version control. I have the triggers checked in with my application code, but it would be easy to not even be aware of their existence. On application startup,I override existing triggers with the ones from the code, just to make sure they're in sync, and I explicitly log out this process, but I could still see this whole process happening without it being at all clear to another developer. Would I use these features again? Probably not.
- jarym 7y ago
  You could use pglogical[1][2] and sync to an updatable view - it ought to be faster and it should be easier to maintain (the updatable view would act as an abstraction layer). [1] https://www.2ndquadrant.com/en/resources/pglogical/ https://www.2ndquadrant.com/en/resources/pglogical/ [2] https://github.com/2ndQuadrant/pglogical https://github.com/2ndQuadrant/pglogical
- mamcx 7y ago
  > some writes take up to 1-2s How big are the inserts? I put my event-sourcing log in a regular table that fully duplicate the rows of the inputs as json. Is not that slow ever to me.
jarym 7y ago
Very neat list. If I had the know-how I would do 2 things to Postgres to make some of these items more useful: - Foreign key support for inherited tables (i.e. a foreign key referencing a table with inheritance, the other way round works just fine). Table inheritance is super useful but lack of full foreign key support means we have to give up on some integrity (or replace them with hand-written constraints). - Bloom indexes on arrays and bigint (int8) types - would make it feasible to (almost) 'index all things' when working mostly with numeric data.
- i_have_to_speak 7y ago
  Foreign keys for partitions is coming in Postgres 12: https://www.depesz.com/2019/04/24/waiting-for-postgresql-12-support-foreign-keys-that-reference-partitioned-tables/ https://www.depesz.com/2019/04/24/waiting-for-postgresql-12-...
ed_blackburn 7y ago
NOTIFY and LISTEN appeals a lot but my understanding is LISTEN needs to be on the same connection, which isn’t realistic with connection pooling etc? Anyone got any experience with this feature?
- jarym 7y ago
  I use logical replication with a modified version of Debezium (https://debezium.io https://debezium.io) - I prefer it to NOTIFY/LISTEN as it means I can have my consumers completely decoupled.
  - hashhar 7y ago
    Debezium is an awesome project. I use it to maintain an audit log and use it as the event source for downstream microservices. It's also handy for building data analytics or warehousing pipelines. The best thing is that it can also track deletes and updates unlike simple polling of the database using select queries.
- sly010 7y ago
  On the same connection with the NOTIFY-er? I probably misunderstand somethings. Can you point to the part in the documentation that states this? That would defeat the purpose of it entirely. Edit: In fact the second sentence in the documentation is "[...] NOTIFY channel is invoked, either by this session or another one connected to the same database, all the sessions currently listening on that notification channel are notified, and each will in turn notify its connected client application." [0] [0] https://www.postgresql.org/docs/9.4/sql-listen.html https://www.postgresql.org/docs/9.4/sql-listen.html
- DrJokepu 7y ago
  They do not need to be on the same connection, but LISTEN will need its own dedicated connection that is kept alive, it shouldn’t be drawn from the connection pool. Very useful if you want to send messages only and exactly once a transaction is committed.
  - 7y ago
theandrewbailey 7y ago
There's full text search, but that only does exact matches. To make it more versatile, take a look at trigrams, which forgives you if you don't spell something exactly right. It breaks everything into three letter segments, and searches with those. https://www.postgresql.org/docs/current/pgtrgm.html https://www.postgresql.org/docs/current/pgtrgm.html
- jerrysievert 7y ago
  or, the best of both worlds: like or ilike using a trigram index CREATE INDEX trigram_index ON words_table USING GIN (sentence GIN_TRGM_OPS)
  - jordanthoms 7y ago
    Yeah that index is amazing if you are doing a lot of sliding LIKE queries (i.e. % placeholders at start and end). Speeds them up massively without having to switch to a full text index or change your queries at all.
- why-el 7y ago
  I have used this and combined it with a small autocomplete engine built on top of Redis a while back. It worked amazingly well. Users type three words and we fetch the entire list of completions from Redis, then we pass this through to Postgresql. We even built synonyms into the Redis cache, so that users who look for "car" and we have a "vehicle" in the DB, it still works. For whatever reason if the Redis instance is not up (less than 0.1% chance), then whatever the query is goes to PG's full text search backed by the trigrams. Beautiful. Granted for this to work the DB needs to be somewhat stable in size. For us, it was a list of products that rarely changes, so the Redis cache can be built on server bootstrap.
- ksec 7y ago
  I remember I read DuckDuckGo are still using Postgre for their Backend fairly recently. Couldn't find any source on it though Google didn't gave any useful information.
mhd 7y ago
Did anyone ever do something really interesting with table inheritance? This is one feature that looks like I might have use cases for all the time, but they never quite fit or offer a good benefit. Foreign data wrappers on the other hand are used by me all the time, as you can have a "common" stuff database and just inject that into all kinds of related ones (like per-tenant setups etc.). I've ran in some issues with joins and performance, but if need be you can just materialize them locally and work from that.
- VectorLock 7y ago
  I could see it being useful for a lot of situations where people might otherwise be looking for a 'schemaless' solution.
- nickpeterson 7y ago
  I looked into it for a bit, but it always felt like there were caveats that I didn't like. I think in the end, avoiding explicit hierarchies in modeling is better.
- btilly 7y ago
  That is the one feature that you shouldn't use. At $work we use table inheritance - each client gets their own schema with their own copies of the table. The idea is cute, but the problem is that the structure of indexing doesn't cooperate with it. Therefore even the simplest query against a single parent table turns into thousands of index lookups on thousands of different tables. As you can imagine, this is not exactly a scalable situation. If they fixed the index structure, if need be only for a few index types, it would make a huge difference. But it doesn't sound like that is easy internally or will happen any time soon.
- deleted 7y ago
  [deleted]
swasheck 7y ago
I’ve seen 3 or 4 of these posts over the last few months and they all seem to have half in common with each other. At this point do we need more posts calling attention to FDW and Table Inheritance as things nobody’s using/heard of? They’re certainly nifty, but I’d like more deep dives on PG internals and tuning to gain a bit more publicity on HN.
VectorLock 7y ago
The table inheritance one is something I wasn't aware of. I can think of so many great uses for it that it makes me kind of weary. There has to be some downside, its too good.
- jayd16 7y ago
  Isn't it just sugar for what you would normally do, which is add another table with the new columns and FK into the base table? I assume it adds some protection around type exclusivity, ie a cat row cannot be a dog row, where as you do not have that protection with cat and dog tables that both reference the animal table. Seems nice but usually you actually would prefer composition over inheritance.
  - VectorLock 7y ago
    It would simplify the reflection of the schema in code I think. Also not having to do a JOIN, or make a FK, or worry about the cardinality of the FK, or worry about ON DELETE CASCADE. It seems like it wraps it up pretty succinctly.
- xienze 7y ago
  Well, there’s this blurb in the documentation: “Although inheritance is frequently useful, it has not been integrated with unique constraints or foreign keys, which limits its usefulness.”
nightfly 7y ago
I'd add lateral joins to the list https://medium.com/kkempin/postgresqls-lateral-join-bfd6bd0199df https://medium.com/kkempin/postgresqls-lateral-join-bfd6bd01...
- Mortiffer 7y ago
  dude, so cool
- deleted 7y ago
  [deleted]
- mns06 7y ago
  Yep, in conjunction with TimescaleDB, lateral joins make joining time-series in the database very powerful: https://docs.timescale.com/v1.2/using-timescaledb/reading-data#last-point https://docs.timescale.com/v1.2/using-timescaledb/reading-da...
oftenwrong 7y ago
How about SKIP LOCKED, which is useful for building advanced queuing setups.
- shoo 7y ago
  https://www.2ndquadrant.com/en/blog/what-is-select-skip-locked-for-in-postgresql-9-5/ https://www.2ndquadrant.com/en/blog/what-is-select-skip-lock...
rezoner 7y ago
How about UPDATE LIMIT 1 instead of fancy edge case features?
- other_herbert 7y ago
  I'm curious why you need this? As a safety mechanism? You could just do a select with the limit then update that row from the primary key...
  - lucb1e 7y ago
    Sure, with a Turing compete system everything is a "you could just". It seems a lot easier to update limit 1 than to involve a subquery. If that is what GP meant it for, of course.
- ratiolat 7y ago
  UPDATE LIMIT 1 is an edge case
status_quo69 7y ago
The one thing I wish that NOTIFY had as a feature would be the ability to do NOTIFY [N], or at least NOTIFY 1. I was writing a very very basic job pool previously and had to try to overcome the thundering herd problem but at the end of it couldn't. I know it's a niche case and maybe there is a clever way of dealing with it, but the only way that I could find to avoid re-doing work and/or trying to select jobs that weren't taken was to allow each listener to be notified and lock the row, where each query for a job would be roughly SELECT * FROM jobs WHERE .... FOR UPDATE SKIP LOCKED and then bail out if I couldn't find any jobs. I could have been using NOWAIT instead, it's been a few years. We weren't looking at a huge number of async jobs to occur but needed reliability and auditing. Ultimately I believe that we scrapped this whole idea so it's all moot but it definitely would have made prototyping a bit easier
- mpartel 7y ago
  A possible workaround: have multiple channels so that each channel only has a small number of listeners, and NOTIFY a random channel.
paulddraper 7y ago
Missing from the list: --- 1. json functions: json_build_object, json_agg, etc. Ever want to product a hierarchical result set? { "groups": [ { "name": "Beatles", "users": [ { "name": "John" }, { "name": "Paul" } ] }, { "name": "Stooges", "users": [ { "name": "Moe" } ] } ] } Then SELECT json_build_object( 'groups', json_agg( json_build_object( 'name', g.name 'users', coleasce(u.users, '[]'::json) ) ) value FROM group g LATERAL LEFT JOIN ( SELECT json_agg( json_build_object('name', u.name) ) users FROM "user" u WHERE g.id = u.group_id ) u ON true Produce your entire JSON result in SQL with full power of aggregations, joins, etc., extensible to any nesting structure. At the extreme, you could use the PostgREST extension make an entire REST JSON API with just PostgreSQL. --- 2. With PostgreSQL's record-level security, end users can get their own connections to the database. You can even have an instant GraphQL server via Postgraphile. [1] [1] https://github.com/PostgREST/postgrest https://github.com/PostgREST/postgrest [2] https://www.graphile.org/postgraphile/ https://www.graphile.org/postgraphile/
- sixbrx 7y ago
  I love this way of getting data out of dbs. Before the json functions existed, I was doing a similar thing with the SQL/XML functions on both Postgres and Oracle (which had practically the same set of functions thanks to the SQL/XML ISO standard). Single round trips to grab all the data needed is awesome.
asah 7y ago
some of my favorites: - transactional DDL: https://www.google.com/search?q=transactional+ddl+postgresql https://www.google.com/search?q=transactional+ddl+postgresql - expression indexes: https://www.postgresql.org/docs/current/indexes-expressional.html https://www.postgresql.org/docs/current/indexes-expressional... - partial indexes: https://www.postgresql.org/docs/current/indexes-partial.html https://www.postgresql.org/docs/current/indexes-partial.html - Javascript UDFs (plv8): https://plv8.github.io https://plv8.github.io - hyperloglog: https://github.com/citusdata/postgresql-hll/releases/tag/v2.10.2 https://github.com/citusdata/postgresql-hll/releases/tag/v2....
- tonyarkles 7y ago
  It’s funny... I’ve been a Postgres user for a long time, and two years ago I had to work with MySQL and Oracle databases for a project with a client. I was absolutely horrified that they couldn’t do transactional DDL! I’d just been using that feature for so long that I assumed everything done in a transaction was atomic. That is such a huge feature!
  - developer2 7y ago
    FYI, Postgresql also has DDL statements that are not transaction safe. The list is quite small compared to software like MySQL, but they still exist. I lost the list I had gathered from a handful of their developers in IRC once, so unfortunately I can't share. Just know that Postgres isn't 100% safe with DDL statements.
mythrwy 7y ago
What I'd like is a postgres specific migration scheme that let me take advantage of a lot of cool (and unique) features postgres has without fighting with general purpose ORMs or having to manually edit migration files.
- kronin 7y ago
  This was discussed here recently: https://github.com/djrobstep/migra https://github.com/djrobstep/migra I haven't used it, but it fits your request of a postgresql specific migration tool where you don't have to futz with manually writing migration scripts.
  - deleted 7y ago
    [deleted]
  - mythrwy 7y ago
    Thanks! I did look at that, think I was participant in last discussion but somehow it got lost in the bookmarks. It doesn't have rollbacks (that I can see) but maybe it is good enough (aka better than Alembic migrations). It appears to deal with actual SQL rather than models which is nice. Will check it out.
- mythrwy 7y ago
  I like the idea of this approach (for anyone else interested in the topic). https://news.ycombinator.com/item?id=19105159 https://news.ycombinator.com/item?id=19105159 But it does seem like a lot more work when rapidly iterating schema.
- moltar 7y ago
  Have you looked at Sqitch? It’s the most flexible, but also low level.
  - mythrwy 7y ago
    Had not. Thank you!
- 7y ago
rodmena 7y ago
To me, one of the neatest features was the ability to send notifications from triggers: PERFORM pg_notify("send_sms", <RECORD>::TEXT); Amazing.
- koolba 7y ago
  The best part of this is that the notifications are transactional! If other work in the transaction causes a rollback then the notification is never sent.
- ht85 7y ago
  Putting it here because it isn't well known and bit me once. NOTIFY payloads are deduped over the course of a transaction, with a O(n^2) algorithm if all payloads are different. If you do a NOTIFY on delete or update and hit a few million rows, the query can go from seconds to hours. The solution would be to write your payloads to a temp table and send them in batch when the transaction ends. Notifying in chunks of 100 will make the process 10'000 faster.
gfiorav 7y ago
Beware of using FDW with extensions in Postgres (like PostGIS), since they might be written under the assumption that data is available locally and not over the network (I.e. full table pulls)
deleted 7y ago
[deleted]
lettergram 7y ago
I wrote a little post on doing fast text search in postgres: https://austingwalters.com/fast-full-text-search-in-postgresql/ https://austingwalters.com/fast-full-text-search-in-postgres... I use it regularly and it may help people. Makes use of triggers and some of the other items mentioned.
cryptokernel 7y ago
Just leaving this here: Do Tasks, Get Paid. Earn crypto by accomplishing tasks in the CoinCircle website. Join us here: https://coincircle.com/l/50VVxbObg3 https://coincircle.com/l/50VVxbObg3
faitswulff 7y ago
Now I'm curious: how far can you get in writing a simple REST API using just PostgreSQL?
- jasonmp85 7y ago
  I mean… http://postgrest.org/ http://postgrest.org/
xtracto 7y ago
Haha , subtle marketing bite. Did not see it coming. One thing is not like the others. Interesting the pub/sub. Anyone tried it? It s it ok for production loads?
ken 7y ago
Table inheritance sounds neat, but the limitations are so severe I've never found a case where it's better than just adding a JSON column. The manual says "These deficiencies [with table inheritance] will probably be fixed in some future release" -- and has since at least 2002, so I think it's safe to assume the situation will never improve.
- perlgeek 7y ago
  I have looked into table inheritance, and came to the same conclusion as you. Later I learned that table inheritance wasn't meant to be used for polymorphism, but rather to solve performance problems. I guess that sharded tables now fill that niche in a better way.
jpgvm 7y ago
Should also add advisory locks to this list. They are a little known but incredibly useful feature.
- lucb1e 7y ago
  What for? A lock doesn't sound that useful if it's only advisory.
  - jpgvm 7y ago
    I suggest you go look at https://www.postgresql.org/docs/9.4/explicit-locking.html https://www.postgresql.org/docs/9.4/explicit-locking.html. TLDR as you didn't even bother to Google: Allows you to create application defined locks. Useful for building job queues and other systems where row locks aren't ideal.
    - lucb1e 7y ago
      > application defined locks Ah, that makes more sense. Thanks!
victor106 7y ago
Previous discussion https://news.ycombinator.com/item?id=17356960 https://news.ycombinator.com/item?id=17356960

378 points by rodmena 7y ago

re Inheritance -- used very well to solve a "Pretty Big Data" problem, making 60 child tables with two fields to differentiate.. some craft is required when building indexes, and in loading the data. Overall this inherited tables solution works very well and had many small positive side effects over time. As always, PG is rock-solid, everytime.

everdev 7y ago

A nice list but I'd also add row-level security (RLS): https://info.crunchydata.com/blog/a-postgresql-row-level-security-primer-creating-large-policies https://info.crunchydata.com/blog/a-postgresql-row-level-sec... It's amazing how much effort we put into restricting access on the server, but ignore user roles on the data layer.

paulryanrogers 7y ago
IME at scale you need a connection pool. Pools usually cache by connection credentials. And maintaining a large variety of roles with disparate access levels is cumbersome. (I've tried.) The end result is that column, much less row, level controls end up unused in most cases. Very security conscious places may just use stored procedures for everything, and limit access to those.
- tellak 7y ago
  Note that you don’t have to use the connection credentials to make use of row level security in pg. the security policy can be based on session variables (or anything really) which can be changed during the connection. Edit: it’s not the same thing but fwiw pg row level security is completely decoupled from the dbms access control.
usgroup 7y ago
Couldn’t agree more ... row level security is brilliantly simple too. Access defined by query.
natmaka 7y ago
> It's amazing how much effort we put into restricting access on the server, but ignore user roles on the data layer. That's one of the reasons PostgREST seems promising to me. http://postgrest.org http://postgrest.org
- everdev 7y ago
  I've looked into it as well and it looks really interesting. The biggest downside for me was that at some point I still needed a backend server to hold secrets or run business logic. At that point, Postgresql was just another server / dependency that I couldn't justify as all of the nifty RLS was done inside Postgres. It definitely introduced me to RLS though.
- BerislavLopac 7y ago
  I've also been a happy user of Sandman: https://github.com/jeffknupp/sandman2 https://github.com/jeffknupp/sandman2
  - natmaka 7y ago
    There is a list at https://github.com/dbohdan/automatic-api/ https://github.com/dbohdan/automatic-api/
- paulddraper 7y ago
  Check out https://www.graphile.org/postgraphile/ https://www.graphile.org/postgraphile/

ralusek 7y ago

I had a case where I needed to build some denormalized redundant models to handle certain queries. I also had a write-heavy, append-only event log portion of my data model. I figured it would scale very differently, and could be partitioned very differently than the rest of the data, so I wanted to try putting it in a separate DB and using a dblink/foreign data wrappers. I use DB triggers and dblink to produce the denormalized data from the event stream, and I consume the denormalized data from my application using pg notify. So, even if I do an insert or update with a SQL statement directly to the DB, it will produce the denormalized data without relying on application logic/hooks. It's definitely air tight, and nice knowing that no version of my data can currently be inconsistent, but there are definitely costs. For one, it's just slow. Bear in mind, I'm on commodity hardware, like t2, but it still seems slow, as some writes take up to 1-2s. It's also not great that it's basically magic, I can't really make alterations to change or improve what it's doing. Lastly, it's hard to version control. I have the triggers checked in with my application code, but it would be easy to not even be aware of their existence. On application startup,I override existing triggers with the ones from the code, just to make sure they're in sync, and I explicitly log out this process, but I could still see this whole process happening without it being at all clear to another developer. Would I use these features again? Probably not.

jarym 7y ago
You could use pglogical[1][2] and sync to an updatable view - it ought to be faster and it should be easier to maintain (the updatable view would act as an abstraction layer). [1] https://www.2ndquadrant.com/en/resources/pglogical/ https://www.2ndquadrant.com/en/resources/pglogical/ [2] https://github.com/2ndQuadrant/pglogical https://github.com/2ndQuadrant/pglogical
mamcx 7y ago
> some writes take up to 1-2s How big are the inserts? I put my event-sourcing log in a regular table that fully duplicate the rows of the inputs as json. Is not that slow ever to me.

jarym 7y ago

Very neat list. If I had the know-how I would do 2 things to Postgres to make some of these items more useful: - Foreign key support for inherited tables (i.e. a foreign key referencing a table with inheritance, the other way round works just fine). Table inheritance is super useful but lack of full foreign key support means we have to give up on some integrity (or replace them with hand-written constraints). - Bloom indexes on arrays and bigint (int8) types - would make it feasible to (almost) 'index all things' when working mostly with numeric data.

i_have_to_speak 7y ago
Foreign keys for partitions is coming in Postgres 12: https://www.depesz.com/2019/04/24/waiting-for-postgresql-12-support-foreign-keys-that-reference-partitioned-tables/ https://www.depesz.com/2019/04/24/waiting-for-postgresql-12-...

ed_blackburn 7y ago

NOTIFY and LISTEN appeals a lot but my understanding is LISTEN needs to be on the same connection, which isn’t realistic with connection pooling etc? Anyone got any experience with this feature?

jarym 7y ago
I use logical replication with a modified version of Debezium (https://debezium.io https://debezium.io) - I prefer it to NOTIFY/LISTEN as it means I can have my consumers completely decoupled.
- hashhar 7y ago
  Debezium is an awesome project. I use it to maintain an audit log and use it as the event source for downstream microservices. It's also handy for building data analytics or warehousing pipelines. The best thing is that it can also track deletes and updates unlike simple polling of the database using select queries.
sly010 7y ago
On the same connection with the NOTIFY-er? I probably misunderstand somethings. Can you point to the part in the documentation that states this? That would defeat the purpose of it entirely. Edit: In fact the second sentence in the documentation is "[...] NOTIFY channel is invoked, either by this session or another one connected to the same database, all the sessions currently listening on that notification channel are notified, and each will in turn notify its connected client application." [0] [0] https://www.postgresql.org/docs/9.4/sql-listen.html https://www.postgresql.org/docs/9.4/sql-listen.html
DrJokepu 7y ago
They do not need to be on the same connection, but LISTEN will need its own dedicated connection that is kept alive, it shouldn’t be drawn from the connection pool. Very useful if you want to send messages only and exactly once a transaction is committed.
- 7y ago

theandrewbailey 7y ago

There's full text search, but that only does exact matches. To make it more versatile, take a look at trigrams, which forgives you if you don't spell something exactly right. It breaks everything into three letter segments, and searches with those. https://www.postgresql.org/docs/current/pgtrgm.html https://www.postgresql.org/docs/current/pgtrgm.html

jerrysievert 7y ago
or, the best of both worlds: like or ilike using a trigram index CREATE INDEX trigram_index ON words_table USING GIN (sentence GIN_TRGM_OPS)
- jordanthoms 7y ago
  Yeah that index is amazing if you are doing a lot of sliding LIKE queries (i.e. % placeholders at start and end). Speeds them up massively without having to switch to a full text index or change your queries at all.
why-el 7y ago
I have used this and combined it with a small autocomplete engine built on top of Redis a while back. It worked amazingly well. Users type three words and we fetch the entire list of completions from Redis, then we pass this through to Postgresql. We even built synonyms into the Redis cache, so that users who look for "car" and we have a "vehicle" in the DB, it still works. For whatever reason if the Redis instance is not up (less than 0.1% chance), then whatever the query is goes to PG's full text search backed by the trigrams. Beautiful. Granted for this to work the DB needs to be somewhat stable in size. For us, it was a list of products that rarely changes, so the Redis cache can be built on server bootstrap.
ksec 7y ago
I remember I read DuckDuckGo are still using Postgre for their Backend fairly recently. Couldn't find any source on it though Google didn't gave any useful information.

mhd 7y ago

Did anyone ever do something really interesting with table inheritance? This is one feature that looks like I might have use cases for all the time, but they never quite fit or offer a good benefit. Foreign data wrappers on the other hand are used by me all the time, as you can have a "common" stuff database and just inject that into all kinds of related ones (like per-tenant setups etc.). I've ran in some issues with joins and performance, but if need be you can just materialize them locally and work from that.

VectorLock 7y ago
I could see it being useful for a lot of situations where people might otherwise be looking for a 'schemaless' solution.
nickpeterson 7y ago
I looked into it for a bit, but it always felt like there were caveats that I didn't like. I think in the end, avoiding explicit hierarchies in modeling is better.
btilly 7y ago
That is the one feature that you shouldn't use. At $work we use table inheritance - each client gets their own schema with their own copies of the table. The idea is cute, but the problem is that the structure of indexing doesn't cooperate with it. Therefore even the simplest query against a single parent table turns into thousands of index lookups on thousands of different tables. As you can imagine, this is not exactly a scalable situation. If they fixed the index structure, if need be only for a few index types, it would make a huge difference. But it doesn't sound like that is easy internally or will happen any time soon.
deleted 7y ago
[deleted]

swasheck 7y ago

I’ve seen 3 or 4 of these posts over the last few months and they all seem to have half in common with each other. At this point do we need more posts calling attention to FDW and Table Inheritance as things nobody’s using/heard of? They’re certainly nifty, but I’d like more deep dives on PG internals and tuning to gain a bit more publicity on HN.

VectorLock 7y ago

The table inheritance one is something I wasn't aware of. I can think of so many great uses for it that it makes me kind of weary. There has to be some downside, its too good.

jayd16 7y ago
Isn't it just sugar for what you would normally do, which is add another table with the new columns and FK into the base table? I assume it adds some protection around type exclusivity, ie a cat row cannot be a dog row, where as you do not have that protection with cat and dog tables that both reference the animal table. Seems nice but usually you actually would prefer composition over inheritance.
- VectorLock 7y ago
  It would simplify the reflection of the schema in code I think. Also not having to do a JOIN, or make a FK, or worry about the cardinality of the FK, or worry about ON DELETE CASCADE. It seems like it wraps it up pretty succinctly.
xienze 7y ago
Well, there’s this blurb in the documentation: “Although inheritance is frequently useful, it has not been integrated with unique constraints or foreign keys, which limits its usefulness.”

nightfly 7y ago

I'd add lateral joins to the list https://medium.com/kkempin/postgresqls-lateral-join-bfd6bd0199df https://medium.com/kkempin/postgresqls-lateral-join-bfd6bd01...

Mortiffer 7y ago
dude, so cool
deleted 7y ago
[deleted]
mns06 7y ago
Yep, in conjunction with TimescaleDB, lateral joins make joining time-series in the database very powerful: https://docs.timescale.com/v1.2/using-timescaledb/reading-data#last-point https://docs.timescale.com/v1.2/using-timescaledb/reading-da...

oftenwrong 7y ago

How about SKIP LOCKED, which is useful for building advanced queuing setups.

shoo 7y ago
https://www.2ndquadrant.com/en/blog/what-is-select-skip-locked-for-in-postgresql-9-5/ https://www.2ndquadrant.com/en/blog/what-is-select-skip-lock...

rezoner 7y ago

How about UPDATE LIMIT 1 instead of fancy edge case features?

other_herbert 7y ago
I'm curious why you need this? As a safety mechanism? You could just do a select with the limit then update that row from the primary key...
- lucb1e 7y ago
  Sure, with a Turing compete system everything is a "you could just". It seems a lot easier to update limit 1 than to involve a subquery. If that is what GP meant it for, of course.
ratiolat 7y ago
UPDATE LIMIT 1 is an edge case

status_quo69 7y ago

The one thing I wish that NOTIFY had as a feature would be the ability to do NOTIFY [N], or at least NOTIFY 1. I was writing a very very basic job pool previously and had to try to overcome the thundering herd problem but at the end of it couldn't. I know it's a niche case and maybe there is a clever way of dealing with it, but the only way that I could find to avoid re-doing work and/or trying to select jobs that weren't taken was to allow each listener to be notified and lock the row, where each query for a job would be roughly SELECT * FROM jobs WHERE .... FOR UPDATE SKIP LOCKED and then bail out if I couldn't find any jobs. I could have been using NOWAIT instead, it's been a few years. We weren't looking at a huge number of async jobs to occur but needed reliability and auditing. Ultimately I believe that we scrapped this whole idea so it's all moot but it definitely would have made prototyping a bit easier

mpartel 7y ago
A possible workaround: have multiple channels so that each channel only has a small number of listeners, and NOTIFY a random channel.

paulddraper 7y ago

Missing from the list: --- 1. json functions: json_build_object, json_agg, etc. Ever want to product a hierarchical result set? { "groups": [ { "name": "Beatles", "users": [ { "name": "John" }, { "name": "Paul" } ] }, { "name": "Stooges", "users": [ { "name": "Moe" } ] } ] } Then SELECT json_build_object( 'groups', json_agg( json_build_object( 'name', g.name 'users', coleasce(u.users, '[]'::json) ) ) value FROM group g LATERAL LEFT JOIN ( SELECT json_agg( json_build_object('name', u.name) ) users FROM "user" u WHERE g.id = u.group_id ) u ON true Produce your entire JSON result in SQL with full power of aggregations, joins, etc., extensible to any nesting structure. At the extreme, you could use the PostgREST extension make an entire REST JSON API with just PostgreSQL. --- 2. With PostgreSQL's record-level security, end users can get their own connections to the database. You can even have an instant GraphQL server via Postgraphile. [1] [1] https://github.com/PostgREST/postgrest https://github.com/PostgREST/postgrest [2] https://www.graphile.org/postgraphile/ https://www.graphile.org/postgraphile/

sixbrx 7y ago
I love this way of getting data out of dbs. Before the json functions existed, I was doing a similar thing with the SQL/XML functions on both Postgres and Oracle (which had practically the same set of functions thanks to the SQL/XML ISO standard). Single round trips to grab all the data needed is awesome.

asah 7y ago

some of my favorites: - transactional DDL: https://www.google.com/search?q=transactional+ddl+postgresql https://www.google.com/search?q=transactional+ddl+postgresql - expression indexes: https://www.postgresql.org/docs/current/indexes-expressional.html https://www.postgresql.org/docs/current/indexes-expressional... - partial indexes: https://www.postgresql.org/docs/current/indexes-partial.html https://www.postgresql.org/docs/current/indexes-partial.html - Javascript UDFs (plv8): https://plv8.github.io https://plv8.github.io - hyperloglog: https://github.com/citusdata/postgresql-hll/releases/tag/v2.10.2 https://github.com/citusdata/postgresql-hll/releases/tag/v2....

tonyarkles 7y ago
It’s funny... I’ve been a Postgres user for a long time, and two years ago I had to work with MySQL and Oracle databases for a project with a client. I was absolutely horrified that they couldn’t do transactional DDL! I’d just been using that feature for so long that I assumed everything done in a transaction was atomic. That is such a huge feature!
- developer2 7y ago
  FYI, Postgresql also has DDL statements that are not transaction safe. The list is quite small compared to software like MySQL, but they still exist. I lost the list I had gathered from a handful of their developers in IRC once, so unfortunately I can't share. Just know that Postgres isn't 100% safe with DDL statements.

mythrwy 7y ago

What I'd like is a postgres specific migration scheme that let me take advantage of a lot of cool (and unique) features postgres has without fighting with general purpose ORMs or having to manually edit migration files.

kronin 7y ago
This was discussed here recently: https://github.com/djrobstep/migra https://github.com/djrobstep/migra I haven't used it, but it fits your request of a postgresql specific migration tool where you don't have to futz with manually writing migration scripts.
- deleted 7y ago
  [deleted]
- mythrwy 7y ago
  Thanks! I did look at that, think I was participant in last discussion but somehow it got lost in the bookmarks. It doesn't have rollbacks (that I can see) but maybe it is good enough (aka better than Alembic migrations). It appears to deal with actual SQL rather than models which is nice. Will check it out.
mythrwy 7y ago
I like the idea of this approach (for anyone else interested in the topic). https://news.ycombinator.com/item?id=19105159 https://news.ycombinator.com/item?id=19105159 But it does seem like a lot more work when rapidly iterating schema.
moltar 7y ago
Have you looked at Sqitch? It’s the most flexible, but also low level.
- mythrwy 7y ago
  Had not. Thank you!
7y ago

rodmena 7y ago

To me, one of the neatest features was the ability to send notifications from triggers: PERFORM pg_notify("send_sms", <RECORD>::TEXT); Amazing.

koolba 7y ago
The best part of this is that the notifications are transactional! If other work in the transaction causes a rollback then the notification is never sent.
ht85 7y ago
Putting it here because it isn't well known and bit me once. NOTIFY payloads are deduped over the course of a transaction, with a O(n^2) algorithm if all payloads are different. If you do a NOTIFY on delete or update and hit a few million rows, the query can go from seconds to hours. The solution would be to write your payloads to a temp table and send them in batch when the transaction ends. Notifying in chunks of 100 will make the process 10'000 faster.

gfiorav 7y ago

Beware of using FDW with extensions in Postgres (like PostGIS), since they might be written under the assumption that data is available locally and not over the network (I.e. full table pulls)

deleted 7y ago

[deleted]

lettergram 7y ago

I wrote a little post on doing fast text search in postgres: https://austingwalters.com/fast-full-text-search-in-postgresql/ https://austingwalters.com/fast-full-text-search-in-postgres... I use it regularly and it may help people. Makes use of triggers and some of the other items mentioned.

cryptokernel 7y ago

Just leaving this here: Do Tasks, Get Paid. Earn crypto by accomplishing tasks in the CoinCircle website. Join us here: https://coincircle.com/l/50VVxbObg3 https://coincircle.com/l/50VVxbObg3

faitswulff 7y ago

Now I'm curious: how far can you get in writing a simple REST API using just PostgreSQL?

jasonmp85 7y ago
I mean… http://postgrest.org/ http://postgrest.org/

xtracto 7y ago

Haha , subtle marketing bite. Did not see it coming. One thing is not like the others. Interesting the pub/sub. Anyone tried it? It s it ok for production loads?

ken 7y ago

Table inheritance sounds neat, but the limitations are so severe I've never found a case where it's better than just adding a JSON column. The manual says "These deficiencies [with table inheritance] will probably be fixed in some future release" -- and has since at least 2002, so I think it's safe to assume the situation will never improve.

perlgeek 7y ago
I have looked into table inheritance, and came to the same conclusion as you. Later I learned that table inheritance wasn't meant to be used for polymorphism, but rather to solve performance problems. I guess that sharded tables now fill that niche in a better way.

jpgvm 7y ago

Should also add advisory locks to this list. They are a little known but incredibly useful feature.

lucb1e 7y ago
What for? A lock doesn't sound that useful if it's only advisory.
- jpgvm 7y ago
  I suggest you go look at https://www.postgresql.org/docs/9.4/explicit-locking.html https://www.postgresql.org/docs/9.4/explicit-locking.html. TLDR as you didn't even bother to Google: Allows you to create application defined locks. Useful for building job queues and other systems where row locks aren't ideal.
  - lucb1e 7y ago
    > application defined locks Ah, that makes more sense. Thanks!

victor106 7y ago

Previous discussion https://news.ycombinator.com/item?id=17356960 https://news.ycombinator.com/item?id=17356960