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.
Why would you use a btree for them? Wouldn’t a hash index be ideal?
Great 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/101a19fb6ae6dfb26a01396c53... You can try it out here: https://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=5dbbf7eadf0ed...
Yes, just the per-DB history was worth the price of admission. Too bad there is no RSS feed.
My favorite relatively obscure pg feature is you can write stored procedures in perl, python, and tcl.
Wow, 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)?
I 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.
Im 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!
We use them in production, they work fine. You probably want to enable use_remote_estimate for postgres_fdw though.
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…
Be wary when using guids for PK in clustered indices, in databases that support them, see for example https://stackoverflow.com/questions/11938044/what-are-the-be...
. Sadly, Postgresql doesn't have them maintained on-the-fly like Ms SQL does so it is less used feature.
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…
I've given up on believing that there is such a thing as portable SQL. If you use the lowest common denominator, you're likely to have queries that perform suboptimally. Hell, even temp tables aren't portable because of the different syntax between databases. I've worked with tons of queries that would would take minutes to run without temp tables while running in milliseconds with them. People may as well take advantage of all the nice things their database offers instead of fearing a situation that is unlikely to happen and which will be a tough change anyway.
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…
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 benefits of UUIDs pretty moot.
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?
"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."
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