11 ms·
Postgres: The Bits You Haven't Found
- codgercoder 14y agoyet another example of "responsive interface" meaning "designed for a phone or a tablet"
- MBCook 14y agoI'm on my iPhone and its totally unviewable. It looks like I can see maybe 20% of a slide, with parts if that covered up by black bars. I can't resize or re-zoom. Total disaster.
- res0nat0r 14y agoCalm down and view it via your browser on your PC.
- jhawthorn 14y agoThese are clearly slides, meant to be fullscreen on a projector. Up/Down, Left/Right, PgUp/PgDown all work, much like they would in powerpoint. I'm a little baffled by this threads hostility towards the lack of a friendlier design.
- mcintyre1994 14y agoHow do I move through the slides? Zooming out suggests there are more slides, but there's nothing to advance through them. Chrome console: Failed to load resource: the server responded with a status of 500 (Internal Server Error) http://postgres-bits.herokuapp.com/js/sh_lang/sh_bash.min.js http://postgres-bits.herokuapp.com/js/sh_lang/sh_bash.min.js Uncaught HTTP error: status 500 sh_main.min.js:4
- craigkerstiens 14y agoYou should be able to progress through the slides by using the arrow keys.
- mcintyre1994 14y agoI admit that was a stupid thing to miss, it works fine, sorry. The Javascript error persists though, not sure if that's affecting anything, but there's nothing noticeable. Works fine for me on Android (default browser JB) too.
- znowi 14y agoYes, the bash highlight is missing and the showoff handler kicks in instead resulting in 500. Thanks to the dev mode you get complete stack trace with code preview :)
- lysium 14y agoHm, how is that site supposed to work? Looks like there should be slide, but I can only see the title page.
- pvh 14y agoNavigate with arrow keys - these are slides from a talk I gave.
- untog 14y agoI can't find the arrow keys on my phone :/
- njharman 14y agoThey are renamed "swipe" on mobile devices.
- yareally 14y agoSome keyboards on mobile have arrow keys. I know Swype does and so does swiftkey. edit: couldn't get the keyboard to pop up on Android, but putting it into landscape mode worked swiping slides (on the stock browser with Android 4.2 [not Chrome]).
- yangyang 14y agoSo does Hacker's Keyboard, along with ctrl, alt and a load of other useful stuff: https://play.google.com/store/apps/details?id=org.pocketworkstation.pckeyboard&hl=en https://play.google.com/store/apps/details?id=org.pocketwork...
- weaksauce 14y agoTap works well on iOS.
- TommyDANGerous 14y agoawesome read, learned a lot.
- jnazario 14y agoreally really great stuff, thank you for posting. i had no idea about a lot of these. and you note that the source is still pretty clean after all these years.
- michaelmior 14y agoFantastic! I'm mostly a MySQL user, but the more I read about Postgres, the more I like.
- throwawayG9 14y agoI moved away from MySQL to PostgreSQL recently, you can do it too =). Just be sure to read about its differences and how to tune it for performance.
- michaelmior 14y agoThat's my biggest concern about making the switch. I've gotten used to the performance characteristics of MySQL and some of it's quirks. Some small side projects on Heroku have been pushing me to learn more Postgres and perhaps I'll use it for something more important soon.
- jaytaylor 14y agoI used to be really, really into MySQL, but since getting into Postgres I've come around to preferring it. They're both good.. but anecdotally Postgres is more developer friendly and generally easier to deal with in production. Postgres also comes with features that blow MySQL away in terms of flexibility and letting you do truly cool stuff. One example: Advanced query optimization with partial or reverse indexes.
- crypto5 14y agoOne of the things I am missing in postgresql is compressed tables. It's actually big game changer in some cases.
- ozataman 14y agoHow were the slides generated? Looks pretty slick.
- pvh 14y agoUsing Showoff, like this: https://github.com/pvh/postgres-bits https://github.com/pvh/postgres-bits
- phillmv 14y agoNote from someone who organizes conferences: Do NOT use any slide deck framework that doesn't have a solid export function - jpgs, pdfs, whatever. The videographer who will be editing your video will not be able to deal with your esoteric format - and screengrabbing every slide is horrible busy work.
- craigkerstiens 14y agoshowoff can generate PDF's just fine
- pvh 14y agoawesome, would you generate a PDF of my slides then?
- parfe 14y agoThe slides recommend UUIDs as a primary key ("just use UUIDs. seriously"). I took a look at the manual and there isn't too much of a Why (as well as 4 different versions of UUID to choose from). Anyone have a write up that explores UUIDs vs integer primary keys? I was only able to find people asking permission ("I have X with a UUID, can I use it as a pk?) rather than people talking about Why you would prefer UUID as the default. Sidenote: Literally any button press you could reasonably expect to move to another slide works. PgDown, Right, Down, Space. Stop shitting up a decent submission to whine about it.
- ExpiredLink 14y agoUUIDs are used as surrogate keys: http://en.wikipedia.org/wiki/Surrogate_key http://en.wikipedia.org/wiki/Surrogate_key
- parfe 14y agoIn a way that an integer is not?
- ExpiredLink 14y agoI may have misunderstood the question. A UUID is a (very long) integer.
- chris_wot 14y agoInteger values are also used as surrogate values. Use of a natural key, IMO, is bad practice.
- e12e 14y agoWhy do you think natural key(s) (w/index(es) as needed) are bad practice? Data model refactoring?
- chris_wot 14y agoA natural key as a primary key is terrible practice because I've never even once seen a natural key that wasn't tied to business logic. Logic that potentially can change. Don't do it, you only make it worse for yourself if you pick a natural key for a PK!
- pdog 14y agoThe slides recommend using UUIDs (and not "numbers") as the primary key: CREATE TABLE t ( uuid uuid PRIMARY KEY DEFAULT uuid_generate_v4(), name text); I understand that rows are stored physically in primary key order. Any idea if the "uuid-ossp" module, used to generate UUID primary keys, ensures that new rows are written sequentially on the disk after existing rows (which helps for both read- and write-locality)?
- pvh 14y agoPostgres does not store keys in primary key order, actually, but in write order. This is an artifact of the MVCC model. There is a CLUSTER command that would do what you describe above, but it is (effectively) useless since you can't really run it without taking the database offline while the table gets rewritten.
- fdr 14y agoThe indexes, though, both benefit (locality) and suffer from (lock contention) such random identifiers. That having been said, many data sets work with UUID just fine and the fact that such sets are easier to catenate and compare is very convenient. Unless you actually need some sequential semantics to your records or are redlining a system where the locality of the index really helps, I prefer the non-coordinated assignment of IDs overall, as pvh points out in his slides. Also, as other posters mention, uuid has sequential forms that can obviate some of this and reduce it to a 128-bit number...but the common form of a good-quality random one is really quite good enough until the indexes really start to get quite big, and this path is most useful if one remains disinterested in partitioning, which may be ill advised. Having to renumber (aka 'renaming', a form of one of the "two hard problems" in computer science...) everything is a big barrier to partitioning, so if one can afford to avoid renumbering, I think avoiding it is a good idea.
- atombender 14y agoYou can do online clustering with pg_repack (https://github.com/reorg/pg_repack https://github.com/reorg/pg_repack). Given a table T, it will: 1. Create a new empty table T'. 2. Add a trigger on T that will clone any operations to T'. 3. Copy all rows from T to T' in clustering order. 4. Atomically switch the tables so that T' becomes T. Unlike CLUSTER, the only lock required is in step 4, when it needs an exclusive lock on the table; it cannot do the switch until all current transactions are done with T (you can specify a timeout). The only other drawback is that that it does not support TRUNCATE, CREATE INDEX, or some ALTER TABLE operations being run while it's busy in step 3. Also, unlike PostgreSQL's own CLUSTER command, it does not need an index to order by. We run pg_repack regularly at night to keep our databases clustered. It works really well, though I really wish Postgres could get native online clustering. SQL Server has had automatic clustering for many years, as has DB2, and Oracle has index-ordered tables.
- moron4hire 14y agohow about we just stop using keys that have no semantic meaning to their data, period?
- LaGrange 14y agoNot sure if you're trolling, but just in case you're not: because any key that has semantic meaning to the data sooner or later is proven to be neither unique, always present nor constant.
- moron4hire 14y agothen what is the point of ever having a unique constraint
- LaGrange 14y agoIn case of something that's not a primary key, you can just get rid of it in the future. While technically it is possible to change the PK, it's way too much pain compared to having a synthetic one.
- jfb 14y agoIf a selected key isn't unique, it's a failure of the data model.
- jacques_chester 14y agoIt's a failure of clairvoyance, you mean. Reality is more complex than any model. That's why we build models. They are, by their very nature, simpler than reality.
- jfb 14y agoYeah, true. A model can be valid for some subset of data for some fixed period of time. The real failure is when the model isn't valid even for the defined period. Skill at articulating the limitations and implicit assumptions of any model is far too rare.
- thepumpkin1979 14y agoIn case someone is interested, I've created a ruby gem that uses hstore as backing store of multi-language text fields for Rails models. Contributions are welcome -> https://github.com/firebaseco/multilang-hstore https://github.com/firebaseco/multilang-hstore
- np422 14y agoThis was a really good presentation, I've worked with postgres and enterprisedb on and off for many years but I still managed to learn a few new things.
- lysium 14y agoInteresting read! I assume, this is all not portable? Or is there similar functionality for other DB, say MySQL (not that I am aware of) or Oracle?
- pvh 14y agoSome of it is in the standard, some of it is PG specific. MySQL is pretty terrible and doesn't support any of it that I'm aware of. I expect Oracle has their own versions of much of this stuff, but I've made it a hobby in my life to not learn Oracle.
- ibejoeb 14y agoOracle has subquery factoring (the "with" clause) and recursion; native arrays, hash maps, collections, nested tables, and other complex data types; remote database links; queues (similar to listen/notify); regex querying; attribute-level timezone support; extremely powerful window functions; text search; tons of built-in crypto (if you really insist); and most of the related things on this list. The biggest killers (vs. postgres): no simple helpers (generate_series, for example, is achievable with simple, efficient recursion, but you have to write the whole statement); a truly antiquated client program (sqlplus); built-in json, but you do have associative arrays/hash maps and nested tables, so you can achieve exactly the same thing, and it's indexable*; and range types (meh imo).
- jacques_chester 14y agoI think I've had Oracle blow up on multiple AS statements, so that's one place where Postgres would come out well in front. Basically WITH-AS lets you declared temporary named subqueries. That is, a pretty stock standard programming technique -- give a name to a fiddly expression to simplify subsequent code. Very useful. Oracle has lots of annoyances though. No SERIAL or IDENTITY type. Having to write the same boilerplate sequence/insert trigger/update trigger over and over and over is very tedious and a recipe for subtle errors when you forget one of them or mess it up. No boolean type in SQL. I don't even know what to say about that.
- stox 14y agoOne note, Postgres is well over 18 years old. Postgres using SQL is 18 years old. Postgres had its own query language which was replaced with SQL in Postgres95, which in turn became PostgreSQL.
- rgbrenner 14y agoPostgreSQL evolved from the Ingres project at the University of California, Berkeley. In 1982, the project leader, Michael Stonebraker, left Berkeley to make a proprietary version of Ingres. He returned to Berkeley in 1985 and started a post-Ingres project. Starting in 1986, the team published a number of papers describing the basis of the system, and by 1988 had a prototype version. The team released version 1 to a small number of users in June 1989 http://en.wikipedia.org/wiki/PostgreSQL#History http://en.wikipedia.org/wiki/PostgreSQL#History
- thomseddon 14y agoShame you can't view it on mobile (iPhone), saved for later.
- deleted 14y ago[deleted]
- Ensorceled 14y agoIt looks like dozens, maybe hundreds, of people all had problem figuring out how to see the next slide. Do the math, that's, cumulatively, probably an hour or two of life wasted perhaps more. I expect webpages to have a mouse based interface, not a keyboard based interface and I'm not alone. It's not churlish of us to complain about our wasted time. Interesting! And, as a postgresql lover, thanks for posting.
- pvh 14y agoGlad you enjoyed the slides. I put a comment about navigation on the first slide. Hopefully that will help.
- Ensorceled 14y agoMe too! I absolutely learned a ton from it ... I hope other Postgres fans didn't just walk away.
- deleted 14y ago[deleted]
- bjourne 14y ago> caveat: WITH expressions are optimization boundaries This is a pretty big caveat and one of the rare areas in which postgres does worse than other database systems. In SQL Server a non-recursive common table expression is treated by the optimizer similar to a macro - You can break up a complicated query with unions and group bys into easier to read cte:s and be confident that the optimizer will piece them all together into a query whose execution plan is equal to the original one. But with postgres you can't because of the optimization boundaries. Its optimizer will attempt to make each cte as individually efficient as possible which can lead to a much worse execution plan overall. You can use views instead which, in contrast to cte:s, postgres optimizer can see through.
- gleb 14y agoIt's more subtle than "worse." Apparently CTE optimization fence is part of SQL standard, and it can be useful to hand-optimize queries. But you are right, when using CTEs for readability it tends to hurt more than help. Interesting thread about being able to control this: http://www.postgresql.org/message-id/201209191305.44674.db@kavod.com http://www.postgresql.org/message-id/201209191305.44674.db@k...
- bjourne 14y agoYeah, I've also heard that claim being made on the mailing list but it is probably wrong: http://dba.stackexchange.com/questions/27425/is-the-optimisation-fence-behaviour-of-a-cte-with-query-specified-in-the-sql2 http://dba.stackexchange.com/questions/27425/is-the-optimisa.... For writable cte:s it matters, for read-only ones the number of execution times doesn't.
- pvh 14y agoSorta. It can be handy for guiding the optimizer away from a bad optimization choice but that's really a bad habit to get into.
- bsg75 14y agoAre there more details about this? As a regular CTE use is both Postgres and MSSQL, but was unaware of this difference, I would like to know where I may be shooting performance to hell.
- alexanderh 14y agoCan you re-order columns yet? http://wiki.postgresql.org/wiki/Alter_column_position http://wiki.postgresql.org/wiki/Alter_column_position 18 years, and still, nobodys implemented this yet? Its not a total deal breaker, but jeebus christopher columbus christ. It certainly would be nice.
- ww520 14y agoCan you just specify the order in your query? Why do you care how the physical order is stored? Or do you even care about the physical order? Would an "apparent" logical order be sufficient?
- alexanderh 14y agoIf you're using postgresql strictly as a programming data store, its fine. Your app will certainly abstract away the ordering of the columns Its just nice though, if you often find yourself working on raw tables using one of the many Admin tools. Sometimes you just need it to work like Excel to be productive. MySQL makes all of that trivial. PostgreSQL obviously keeps track of the order you created the columns, so being able to edit this ordering seems trivial in my mind. It just seems odd that there certainly is some demand there for it, yet nobodys picked up the slack in 18 years. Obviously there are workarounds. Why dont they include the work arounds as scripts with the distrobution, etc? Maybe I will try to implement something and submit a patch. No experience in database dev though :( Like I said though, its not a deal breaker. I'm just used to Open Source projects over-implementing features. Not under-implementing them. Especially after 18 years.
- e12e 14y ago> Its just nice though, if you often find yourself working on raw tables using one of the many Admin tools. Sometimes you just need it to work like Excel to be productive. Clearly this is a bug in the admin tool? I seem to recall MS SQL allows you to "drag columns around" -- obviously doing absolutely nothing to the database -- it's just a view. After all rows are just relational tuples -- they have no ordering.