
Things I Hate About PostgreSQL (2013) - subleq
http://kupershmidt.org/pg/10_Things_I_Hate_About_PostgreSQL/#/
======
dorfsmay
I somewhat agree, and somewhat have the opposite view:

I __love __psql and tab completion, it 's magic, in fact it spoiled me, I know
have a hard time to work on any other DB.

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.

Now, the vacuum business, that's horrible. I've run into so many case where
"vacuum full" just solve the weirdest problems. One very recently where I use
postgres as a queue (because it was easy and works across platform/clouds) nad
it became slow and horrible even though I had dropped a bunch of schemas.
"vacuum full;" oh wow!

The replications story is ugly. You can make it work, but it's literally a
craft and you end up having to babysit it.

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're going to slow down your writes. Well, it
turns out not on postgres, all indices for the table you're writing to are
being updated! I'm pretty sure very few people were aware of that before uber
told us.

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.

~~~
chucky_z
Really? Coming from PGSQL to a MySQL-only shop made me really miss PGSQL's
performance. Sure, MySQL is faster on `select pk from tbl where pk=1`, but the
second you get into complicated joins, including subqueries, or doing _any_
analytics the performance is very random.

With pgsql, I can get a clear concise explain and if I disagree with what's
happening (because I know exactly what my hardware is capable of) I can tune
any query to be decently performant.

With mysql, you get... I mean explain is ok, but I wouldn't put it above that.
"EXPLAIN ANALYZE" is my gold standard forever and ever. I even liked it more
than SQL Server's equivalent; which is also fantastic.

~~~
epoch1970
MySQL's lack of CTEs is particularly annoying. Even SQLite has supported them
for some time now!

I've seen this cause MySQL query authors to use various workarounds that end
up impacting the query performance negatively.

~~~
kbenson
I suspect MySQL is used with more bare bones schemas, without as many SQL
function and complex triggers, as it has worse support for those (IMO, as
someone that used MySQL). This does have the side effect of making it very ORM
friendly, and the more you rely on an ORM the less you care or even know what
queries are being run under the covers.

------
aninteger
There's really only one thing I dislike about pgsql. It seems overly difficult
to return two disjointed result sets from a stored procedure/function. This is
fairly common and easy in MSSQL. It also is easy in MySQL. Something like
this:

SELECT int_col_1, int_col_2 FROM table1

SELECT varchar_1, varchar_2, int_col_2 FROM table2

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.

~~~
brightball
What is a good example of when you'd want to do that? I've been doing database
work on the 4 majors for about 15 years and this is the first I've ever heard
of somebody wanting to return two totally different result sets from a single
procedure.

I have no issue with putting logic in your database. In many cases it's the
ideal solution...but your example is something I've never even heard mentioned
in 15 years much less advocated for or used as criticism for not being
available.

~~~
barrkel
One place I worked used a disconnected data set model. The idea was a single
stored procedure returned multiple result sets all relating to a single
business entity; specifically, an insurance quote, along with all the drivers,
vehicles, accidents, convictions, etc. The entity was locked by inserting a
row in a table.

Thereafter, the application didn't communicate with the database until it was
time to save the record. The disconnected data set was small enough to store
in shared session store, or even in a cookie (encrypted, naturally).

This architecture had a number of interesting knock-on effects. The entire
state of a client's conversation with the server was tiny and entirely
encapsulated by this disconnected data set, so you could record and play back
each request to recreate a bug.

It didn't have to use multiple result sets from the stored procedure, but it
did save a bunch of round trips.

~~~
Drdrdrq
>... or even in a cookie (encrypted, naturally).

I won't comment on anything else because I don't know the case, but you should
never put user data in cookies. Even with encryption you are exposing it
unnecessarily, not to mention you have to double check data integrity... Why?
Cookies are not meant for this.

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

~~~
scrollaway
The google thing gets worse every release.

The Django project fixed it by adding canonical links to all the docs, see for
example view-
source:[https://docs.djangoproject.com/en/1.9/ref/models/querysets/](https://docs.djangoproject.com/en/1.9/ref/models/querysets/)
(Has a <link rel="canonical"
href="[https://docs.djangoproject.com/en/1.10/ref/models/querysets/...](https://docs.djangoproject.com/en/1.10/ref/models/querysets/">))

The postgres project _really_ needs to do that. It's a mess.

------
garyclarke27
Sublime Text works well for me as a Postgres query editor. Rock solid super
fast, unlike all alternatives, (I'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's behaviour with the same config options as psql, full
feedback error messages, line numbers etc Easy to setup build system, see
[http://blog.code4hire.com/2014/04/Sublime-Text-psql-build-
sy...](http://blog.code4hire.com/2014/04/Sublime-Text-psql-build-system/)

~~~
bdcravens
Have you look at DataGrip?
([https://www.jetbrains.com/datagrip/](https://www.jetbrains.com/datagrip/))
Been using since it was in beta, and it handles SQL Server and Postgresql
well. (haven't used it for other platforms yet)

------
qwertyuiop924
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 _fascinating_.
Unfortunately, I can't remember the link...

~~~
pmontra
Maybe
[http://www.wikivs.com/wiki/Python_vs_Ruby](http://www.wikivs.com/wiki/Python_vs_Ruby)
or one of the links at the end of that page?

~~~
openfuture
Extra relevant link cause when you try to go to 'main page' from that article
you get a postgresql error!

------
rtpg
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't really any performance tests.

Reviewing and pulling things in is always hard, though. In other software I
highly recommend _always_ breaking up big things into smaller changes if
possible, but I don't know if that works well for the conservative release
cycles for DBs.

Though maybe the conservative release cycle is what's wrong? Having to do a
refactor to implement a feature doesn't mean that you need to do both at once.

------
jkmcf
Oddly, I don't see anything about managing user permissions, access, and
authorization. It'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.

~~~
lobster_johnson
That's odd, I feel the opposite. For years the way to create a user in MySQL
was this:

    
    
        grant usage on *.* to 'bob'@'localhost'
          identified by 'itsasecret';
    

Meanwhile, PostgreSQL had the extremely straightforward "createuser" command
line tool, and the "create user" SQL statement. (MySQL got "create user" and
"alter user" around 2006 or so.)

Having to edit pg_hba.conf to allow network connections was always an
annoyance for me, but at least the error message you get if it's set up wrong
_tells_ you to edit that file, which is also very straightforward and contains
documentation out of the box.

Not allowing external access by default is arguably a security practice,
though I always thought distro maintainers ought to have erred on the side of
usability by enabling localhost access by default.

------
stuaxo
The main thing I've hated about postgres over the years is pgadmin3. Haven'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.

~~~
ahachete
Have you tried pgadmin 4? It's a complete rewrite.

~~~
elmigranto
It looks pretty awkward to me. I don't get why they felt it's a good idea to
do it in a browser with spaghetti-bowl of jQuery full of second-long
animations.

~~~
theprotocol
It is indeed awful, albeit functionally superior to pgAdmin III.

------
hyperpape
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't seen is a sort
of comprehensive "here are the issues that exist, here is what you must
monitor in order to be safe" type of guide. The official docs are helpful, but
fall short of that kind of guide, and none of the posts I've seen are really
comprehensive.

Unfortunately, I'm pretty new to postgres and can't write it myself.

~~~
elmigranto
I found this to be great introductory that touches on all the major points to
keep in mind with Postgres. It's pretty up-to-date too!

[https://www.youtube.com/watch?v=knUitQQnpJo](https://www.youtube.com/watch?v=knUitQQnpJo)

It isn't quite what you ask for, but should be enough to get you high-level
overview of major systems and contains solid practical advices (on vacuuming,
mvcc, replication, backups, etc…), so you know what to google for deeper
understanding.

~~~
hyperpape
Thanks! It's too long for me to watch right now, but I've saved it for the
next time I can dedicate that much time to a video.

~~~
elmigranto
Feel free to skip to any topic, it is quite well structured to do that.

------
fideloper
I'd be all over pgsql if replication wasn't such a mess. Yes it works
(presumably), but I can't find any useful (e.g. informative) information on a
comparison between (the many??) methods to go about it and/or most modern way
to accomplish it.

If anyone here knows of a good source, I'd love to see it :D (If you use the
word "just" or link me the docs, you're dead to me).

~~~
epoch1970
Why do you reject the documentation? It has exactly what you're asking for:

[https://www.postgresql.org/docs/current/static/different-
rep...](https://www.postgresql.org/docs/current/static/different-replication-
solutions.html)

It describes the various methods that are available, it lists implementations
of each, it explains the tradeoffs involved, and so on. Table 25-1 summarizes
the information.

Regardless of the database system being used, replication is just inherently
complex. There isn't really a one-size-fits-all solution. The method to use
depends on the requirements and context of a given implementation.

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

~~~
kawsper
This is made with Reveal.js, but there is another one as well called
Impress.js, they seem quite equal.

[0]
[https://github.com/hakimel/reveal.js/](https://github.com/hakimel/reveal.js/)

[1]
[https://github.com/impress/impress.js/](https://github.com/impress/impress.js/)

------
kchoudhu
10 slides in, and I realize that my back button is fucked.

Why do people do this?

~~~
CJefferson
If I was doing slides like this, it would be because I was going to use them
in a talk. Then I would want the back button to go back one slide.

If I then put them on the internet, I may not bother editing them to make the
more "internet friendly".

~~~
rix0r
But how would you go forward in the first place? Not with the "Forward"
button, because you haven't visited the page yet.

Instead, you press "Right" or "PgDn" or what have you. So I would expect the
converse button ("Left" or "PgUp") to go back a slide as well (which it
presumably does).

That leaves the browser's Back button to go back to the previous web page.

Everybody's happy!

~~~
coldtea
> _That leaves the browser 's Back button to go back to the previous web page_

Why approach 10+ slides as "a single page"?

If you consider them as 10 web pages, the back button makes sense to go back
one page. Same for the left button, given the "presentation" use case.

And if they were actually designed as webpages, with html links taking you to
slide2.html, slide3.html etc., that's exactly what you would get.

So if anything, whether this is a SPA or not, this is more in tune with how
the internet works, and how it was designed to work.

~~~
jimhefferon
This way a person can link to a particular page.

~~~
coldtea
You CAN link to a particular page. Here, page 14.

[http://kupershmidt.org/pg/10_Things_I_Hate_About_PostgreSQL/...](http://kupershmidt.org/pg/10_Things_I_Hate_About_PostgreSQL/#/14)

------
altstar
Just needs a [2013]

------
alex-yo
Yey, looks like people just watch presentations, not read it, that's why here
is 1 comment about the content, 15 about how the presentation looks like :)

~~~
tsomctl
I'm not even able to read it. I can't figure out how to go forward from the
title page.

~~~
coldtea
Since time immemorial, the way to go forward in such pages is to press the
left and right arrow buttons.

This has worked in 99.9999% of presentations uploaded on the internet and
posted on HN.

It's also very intuitive to try, since this is how it is in any presentation
desktop app too.

~~~
chrismonsanto
> Since time immemorial, the way to go forward in such pages is to press the
> left and right arrow buttons.

"Time immemorial"? Damn, I remember pretty clearly what life was like before
Web 2.0, and I'm still in my 20s. The first time I encountered one of these
presentations I was confused as hell, at least this one has the decency to put
arrows to click in the bottom right corner, a lot of presentations don't.

~~~
coldtea
> _I remember pretty clearly what life was like before Web 2.0, and I 'm still
> in my 20s_

Well, I'm in my late thirties, and "before web 2.0" is "time immemorial" in
tech years.

> _The first time I encountered one of these presentations I was confused as
> hell_

Yeah, but that should have been like 5-10 years ago. How come people still
don't get them?

Heck I was confused as hell when I first encountered DOS, mice, GUIs, UNIX,
browsers, etc back in the day. But we learn and move on. What puzzles me is
that HN is not full of "average users" but devs and technies, and also the
fact that such presentations are posted tons of times a month, and yet someone
still asks...

(Sure, I can understand that this could imply a "fundamental non
intuitiveness" of such UI, but whether it's intuitive or not when we first
meet it, it should be second nature by now.

I believe in "idiom based design" over intuitiveness (which constraints us to
UIs that we can understand at first glance, preventing designs that could need
a little getting accustomed to, but be far more powerful in the long run).

And I'd argue it's not even that non-intutive. From games to Powerpoint, all
kinds of apps use the arrow keys to navigate -- why wouldn't one at least try
them?

~~~
chrismonsanto
> How come people still don't get them?

The reason these comments keep appearing is because tech community is large &
growing and each person encounters & is confused by their first presentation
in this style at a different time. As much as I hate the relevant xkcd meme,
it is pretty apt here: [https://xkcd.com/1053/](https://xkcd.com/1053/)

> Mice

This isn't a good analogy because you are in control of whether you use a
mouse or not. If it confuses you, you'll either stop using it or figure it out
before participating on HN. OTOH you can surf the web for years without
encountering one of these presentations, so it's jarring when someone else
creates a web page that violates your expectation of how the web works

> And I'd argue it's not even that non-intutive. From games to Powerpoint, all
> kinds of apps use the arrow keys to navigate -- why wouldn't one at least
> try them?

Because people are used to navigating the web with their mouse, not their
keyboard

------
creshal
What an awful format for slides.

~~~
divbit
Besides the fact that the font is really tiny, it's actually much nicer on
phone than most of the "n things..." websites which seem to try and maximize
clicks.

~~~
hibbelig
The font is _too_ tiny. I couldn't read the mono space parts.

~~~
vacri
Really? It all looks perfectly legible for me on FF and Chrome, on both win +
lin. It'd be even more so if fullscreened like you'd expect a slideshow to be
presented.

~~~
hibbelig
Probably, but the context here was mobile. The monospaced parts are too tiny
on my iPhone 5c.

------
nbevans
Website is broken. UX fail.

~~~
coldtea
It's amazing that in 2016 people still don't know to press left and right
arrows (or spacebar) in an online presentation.

It's the way ANY slideshow works, on desktop and online form. And it's a
trivial thing to try, even when not told to. Not to mention by now there have
been around 1000 such slideshows posted on HN.

~~~
wtbob
> It's amazing that in 2016 people still don't know to press left and right
> arrows (or spacebar) in an online presentation.

It's amazing that in 2016 people still don't fall back gracefully in the
absence of JavaScript and/or CSS.

~~~
curried_haskell
Sorry, but no it's not. You're in the extreme minority if you don't have JS or
CSS. It's simply not even worth the time to cater to you.

~~~
wtbob
> It's simply not even worth the time to cater to you.

That attitude is evil. That attitude underlies much (albeit not _all_ ) that
is wrong with the Web in 2016. That attitude is wrong.

Espousing that attitude evidences a profound failure to understand the value
of the Web.

------
known
[http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL](http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL)

------
jokoon
I still don't understand database systems, how they work internally, and what
problem they really solve.

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't seem like a silver bullet.

Database queries seem like a solution to the problem of storing data on disk
when RAM was too expensive. So today it'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.

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's not
relevant anymore.

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't appealing to me.

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

~~~
coldtea
> _I still don 't understand database systems, how they work internally, and
> what problem they really solve._

The use a formal, mathematical abstraction (relational algebra), even if
somewhat crapily implemented by particular RDBMS, to facilitate saving data,
relationships between data, and queries, while at the same time assuring
certain properties (e.g. ACID) and doing so in a platform/programming language
neutral format and with a consistent and industry standard query interface.

NoSQLs, key-stores and the like are not newer developments -- they predate
databases. They were found to be a bad fit for what we wanted in enterprise
use, and RDBS caught on.

For uses with huge data (Google and such scale) a denormalised approach might
be more practical for performance reasons, in which case developers and ad-hoc
programs get to re-implement all the functionality and assurances of a RDBMS
in an ad-hoc way on top of rawer stores. (And all this could be alleviated
with a properly optimised RDBMS for such purposes the still respects
relational algebra).

In a conventional enterprise setting on the other hand, a DB trumps NoSQL etc
solutions any day of the week.

> _Database queries seem like a solution to the problem of storing data on
> disk when RAM was too expensive. So today it '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._

The whole idea is to free the data from being tied to a particular language,
data structures and algorithms.

Back in 2000-2007 it was all about XML, and how we should store data in XML
format and get them back with XQUERY, XPATH and the like. A lot of people
bought into the hype and the resulting products. Then it was about JSON -- and
we now have JSON stores. In 10 years, it will be something else, again ad-hoc.

Meanwhile SQL has worked for the past 3+ decades, and is based on a solid
mathematical abstraction (relational algebra).

> _It '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._

You'd be surprised. Or rather you're exactly right -- few "really remember the
original idea of the inventor of that paradigm", and that's why we're moving
in circles with ad-hoc technologies re-implemented 30+ years after they were
discarded. Or why people jumped enthusiastically to Mongo to return crying
back to PostgreSQL.

Read a little around here for example:

[http://www.dbdebunk.com/2015/07/the-sql-and-nosql-effects-
wi...](http://www.dbdebunk.com/2015/07/the-sql-and-nosql-effects-will-
they.html)

[http://www.allanalytics.com/author.asp?section_id=2386&doc_i...](http://www.allanalytics.com/author.asp?section_id=2386&doc_id=280912)

[http://www.dbdebunk.com/2015/11/moving-in-circles-sql-for-
no...](http://www.dbdebunk.com/2015/11/moving-in-circles-sql-for-nosql.html)

~~~
arielb1
JSON is just as much rooted in math as SQL tables - it is `fix T = FiniteMap
String T` plus a few primitive types, while SQL is `Collection (Set [String])`
plus a few primitive types.

Of course, SQL has a nice efficiently-implementable algebra of set
comprehensions, which may or may not be interesting for your particular use-
case.

~~~
coldtea
> _JSON is just as much rooted in math as SQL tables - it is `fix T =
> FiniteMap String T` plus a few primitive types, while SQL is `Collection
> (Set [String])` plus a few primitive types._

It's not the tables that make SQL what it is, it's the relations.

Heck, it could even not have any types at all (a la dyamic languages or
Sqlite), and relational algebra as an abstraction would still hold.

