Things I Hate About PostgreSQL (2013)

I somewhat agree, and somewhat have the opposite view:<p>I <i></i>love<i></i> psql and tab completion, it&#x27;s magic, in fact it spoiled me, I know have a hard time to work on any other DB.<p>Not mention in the article, but I love how the postgres dev keep up with modern SQL. Again it makes it really hard to go back on a SQL 92 compliant system.<p>Now, the vacuum business, that&#x27;s horrible. I&#x27;ve run into so many case where &quot;vacuum full&quot; just solve the weirdest problems. One very recently where I use postgres as a queue (because it was easy and works across platform&#x2F;clouds) nad it became slow and horrible even though I had dropped a bunch of schemas. &quot;vacuum full;&quot; oh wow!<p>The replications story is ugly. You can make it work, but it&#x27;s literally a craft and you end up having to babysit it.<p>One the author missed, lots of weird performance issue, got my answer from the uber post. The rule #1 of adding indices is to only use column that you really really need, because otherwise you&#x27;re going to slow down your writes. Well, it turns out not on postgres, all indices for the table you&#x27;re writing to are being updated! I&#x27;m pretty sure very few people were aware of that before uber told us.<p>The more I use the postgres front end (psql, SQL) the more I love it, but the backend (performance, replication) is starting to taste more and more sour.

There&#x27;s really only one thing I dislike about pgsql. It seems overly difficult to return two disjointed result sets from a stored procedure&#x2F;function. This is fairly common and easy in MSSQL. It also is easy in MySQL. Something like this:<p>SELECT int_col_1, int_col_2 FROM table1<p>SELECT varchar_1, varchar_2, int_col_2 FROM table2<p>In pgsql if the columns are the same type and number then we can use a union, otherwise the suggested alternative seems to be to just make 2 queries.

The slides are from mid-2013, how many points are still valid? Has there been progress in the last 3 years?

Sublime Text works well for me as a Postgres query editor. Rock solid super fast, unlike all alternatives, (I&#x27;ve tried them all) except psql, which is solid but ugly. ST3 has for me a much nicer interface and has amazing search and replace ability, attractive themes, syntax highlighting from pg specific plugin. Remarkably it even copes with queries that return millions of rows. You can control it&#x27;s behaviour with the same config options as psql, full feedback error messages, line numbers etc Easy to setup build system, see <a href="http:&#x2F;&#x2F;blog.code4hire.com&#x2F;2014&#x2F;04&#x2F;Sublime-Text-psql-build-system&#x2F;" rel="nofollow">http:&#x2F;&#x2F;blog.code4hire.com&#x2F;2014&#x2F;04&#x2F;Sublime-Text-psql-build-sy...</a>

I always enjoy good critique. There was a similarly excellent talk about the differences between Python and Ruby, which discussed the strengths and weaknesses of the design choices each made. It was <i>fascinating</i>. Unfortunately, I can&#x27;t remember the link...

I know a lot of people use Postgres (myself), and I was looking at the source recent, it seems pretty high quality. Makes it all the more surprising that there aren&#x27;t really any performance tests.<p>Reviewing and pulling things in is always hard, though. In other software I highly recommend <i>always</i> breaking up big things into smaller changes if possible, but I don&#x27;t know if that works well for the conservative release cycles for DBs.<p>Though maybe the conservative release cycle is what&#x27;s wrong? Having to do a refactor to implement a feature doesn&#x27;t mean that you need to do both at once.

Oddly, I don&#x27;t see anything about managing user permissions, access, and authorization. It&#x27;s wicked arcane compared with every other DB out there. I believe one main reason MySQL caught on early with hosting providers was because of this.

The main thing I&#x27;ve hated about postgres over the years is pgadmin3. Haven&#x27;t had to use it for a while but it was always easy to get it into a pickle in common situations, like losing the connection to the database.

One thing that I really wish existed was a comprehensive guide to vacuuming, transaction id wraparound and similar issues. There have been a few posts recently that discussed problems that arise, but what I haven&#x27;t seen is a sort of comprehensive &quot;here are the issues that exist, here is what you must monitor in order to be safe&quot; type of guide. The official docs are helpful, but fall short of that kind of guide, and none of the posts I&#x27;ve seen are really comprehensive.<p>Unfortunately, I&#x27;m pretty new to postgres and can&#x27;t write it myself.

I&#x27;d be all over pgsql if replication wasn&#x27;t such a mess. Yes it works (presumably), but I can&#x27;t find any useful (e.g. informative) information on a comparison between (the many??) methods to go about it and&#x2F;or most modern way to accomplish it.<p>If anyone here knows of a good source, I&#x27;d love to see it :D (If you use the word &quot;just&quot; or link me the docs, you&#x27;re dead to me).

I really like the slideshow UI. Anyone know which software was used to make this?

10 slides in, and I realize that my back button is fucked.<p>Why do people do this?

Just needs a [2013]

Yey, looks like people just watch presentations, not read it, that&#x27;s why here is 1 comment about the content, 15 about how the presentation looks like :)

What an awful format for slides.

Website is broken. UX fail.

<a href="http:&#x2F;&#x2F;www.wikivs.com&#x2F;wiki&#x2F;MySQL_vs_PostgreSQL" rel="nofollow">http:&#x2F;&#x2F;www.wikivs.com&#x2F;wiki&#x2F;MySQL_vs_PostgreSQL</a>

I still don&#x27;t understand database systems, how they work internally, and what problem they really solve.<p>It seems that new paradigms like R and RAM-only key values system are just simpler, faster and cheaper in programmer time. Loading everything in RAM and do a search seems like a huge saving of time and just works for most cases. Usually if you have more complex needs, you need to adapt your solution, and databases don&#x27;t seem like a silver bullet.<p>Database queries seem like a solution to the problem of storing data on disk when RAM was too expensive. So today it&#x27;s still used as some sort of standard, but when you can have 16GB of ram, I think you better teach yourself what sort of algorithm and data structures a database use to be faster, use the ones you like and need and solve your problem case by case.<p>The example of how reddit store its data is pretty demonstrative that ultimately, you should not let a database system do all the work. Databases are just a file format to me, but the way it tries to work for you at a lower level will respond to basic cases, but when you increase complexity it&#x27;s not relevant anymore.<p>Especially today when you have big data and machine learning, everyone should just learn to understand data manipulation. Not saying to teach yourself C all over again, but having a decent idea of the math of what indexing is really about. Forcing yourself to use a database because the company always used it isn&#x27;t appealing to me.<p>It&#x27;s like one of those things when a programmer had an idea which is based on a constraint, everyone starts using it, several products are made, but nobody really remember the original idea of the inventor of that paradigm.