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

61–70 of 167 posts

Re: Lesser-known Postgres features

#61

Post by mixmastamyk »

This 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.

Re: Lesser-known Postgres features

#62

Post by Robin_Message »

lr4444lr wrote: post #58
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…

Technically there are many advantages, but operationally, I find it extremely useful in systems where an int PK is auto-generated to use it as a pretty good proxy for a relative created time with relation to other records, with built-in indexing that avoids having to index on an actual created datetime column for a lot of less precise lookup purposes. The backend frameworks I use around data access make the security…

As someone below suggests, ULID is a good alternative which has a temporal aspect in the high bits so it is still a proxy for relative creation time.

Re: Lesser-known Postgres features

#63

Post by NightMKoder »

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
`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`.

Re: Lesser-known Postgres features

#64

Post by CRConrad »

superice wrote: post #22
cnity wrote: post #18

Earlier quoted context omitted.

I 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.

One 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 fea…

>>> [DB Sequences]

> One often hears the counterargument 'but using DB-specific features makes your application less portable!'

OK, sorry for the probably stupid question: Isn't it just a matter of, for each sequence, selecting its current value and then creating the new one in the target database to start from there? Should be, if perhaps not easily, still reasonably scriptable... Or what am I missing?

Re: Lesser-known Postgres features

#65

Post by masklinn »

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?

> Why would you use a btree for them?

1. because PRIMARY KEY is its own constraint, and the underlying index is not under you control

2. because PRIMARY KEY further restricts UNIQUE, and as of postgres 14 "only B-tree indexes can be declared unique"

Re: Lesser-known Postgres features

#66

Post by jrmiii »

mixmastamyk wrote: post #61

This 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) ''' % { 'patte…

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

Re: Lesser-known Postgres features

#67

Post by handrous »

sodapopcan wrote: post #38
londons_explore wrote: post #7

Avoid 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.

How often does migrating databases ever actually happen? I'm not saying it doesn't happen, but I've never experienced it or know anyone who has (I've asked!). I certainly would shy away from using features that make my app code cleaner and improves the all-around performance of the app on the slim-to-none chance that one day I might one day have to migrate off of postgres.

20-year career so far. Never seen a database swapped out. I've seen "apps" replaced on top of databases, or more programs added to access the same database. I've seen the app and database both get thrown out and replaced, because they were tightly coupled[0], as the parent advocates (Rails + ActiveRecord seems to be prime for this kind of "gotta throw it all out" situation). I've never seen the program stay the same while the DB is swapped out from under it.

[0] yes, that's actually tightly coupling them, because now your DB is too unsafe to use without the "app" you built on top, and doesn't provide enough functionality to make it worth trying to retrofit that safety onto it.

Re: Lesser-known Postgres features

#68

Post by mindcrime »

jeffbee wrote: post #47

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

Not to mention Java, R, Ruby, PHP, Scheme, and sh.

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

Re: Lesser-known Postgres features

#69

Post by skrtskrt »

superice wrote: post #22
cnity wrote: post #18

Earlier quoted context omitted.

I 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.

One 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 fea…

Done it multiple times, most recently we got an MVP off the ground using only ElasticSearch as a backend, since the primary use case we wanted to validate and demonstrate was discoverability.

As we added more features, the lack of transactions and relational structures started to slow us down, so we dropped in Postgres as a backend, and having application-generated UUID4s as primary keys was a big part in making that move fairly painless

Re: Lesser-known Postgres features

#70

Post by fwip »

spullara wrote: post #59
evandwight wrote: post #41

Earlier quoted context omitted.

Do you write logic to handle collisions?

"A collision is possible but the total number of unique keys generated is so large that the possibility of a collision is almost zero. As per Wikipedia, the number of UUIDs generated to have atleast 1 collision is 2.71 quintillion. This is equivalent to generating around 1 billion UUIDs per second for about 85 years."

For clarification, that's the number of UUIDs at which you have a 50% chance of at least one collision.

Wikipedia also adds: "the probability to find a duplicate within 103 trillion version-4 UUIDs is one in a billion."

Post reply on HN