postila

Better, automated tools for optimization are needed. Postgres ecosystem right now lacks a lot of things that enterprise systems have for decades.

things like superuser should be disabled by default (only DBA needs superuser access, not anybody else).

Also: 3. Ensure that you don't use or provide anybody superuser in Postgres. (Otherwise, there is much simpler way to download&run anything that explain in the article – just `copy tablename from program 'wget ... && chmod a+x ... && ...'`

This is one of the interesting optimizations that DBMS should do automatically but it doesn't.

https://github.com/NikolayS/postgres_dba -- this toolset includes a version of report (see report a1) which analyses all tables in your database and shows opportunities to optimize.

I mean, in more recent Postgres versions, more things related to window functions are available – like CUBE, GROUPING SETS, etc. 9.3 was released back in 2013.

||'' is another way to do ::text ? :-)

select version();

PostgreSQL 9.3.20 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.3) 4.8.4, 64-bi

I wonder why so old version is being used. I will be not supported by the community pretty soon.

postgreS

Here is an example of ~$65 item merged with others https://www.amazon.com/gp/product/B076RS1WRZ/ref=oh_aui_deta... – a lot of five-star reviews (marked as verified), but very bad quality.

MS Azure also provides Postgres now [1]. And also does Alibaba Cloud [2]. They even have a special version of it – something for analytics/warehousing [3]. So at least 4 big cloud providers have Postgres!

1) https://azure.microsoft.com/en-us/services/postgresql/

2) http://www.pgconf.asia/JP/wp-content/uploads/2016/12/Postgre...

3) https://www.alibabacloud.com/product/hybriddb-postgresql

+1. I also use *@mydomain.com feature in G Suite, and it's very convenient to understand which companies sell/pass email databases to others w/o my permission. In some cases, you need to reply from that "aliased" address -- in this case, I do go to the Settings, add an alias, got a confirmation code, and confirm it. Then this new "address" is available in GMail in drop-down "From:" menu when you write a new email.

username+anything@mydomain.com is also a useful feature (as well as u.s.er.nam.e@mydomain.com – dots are all ignored in GMail; some services don't allow "+" in email address field, so you can use finite number of variants with ".").

These little tricks make GMail convenient for geeks :)

and redash https://redash.io/ and many more

Ditto. ~1.5 years in one project, ~1y in another. Recent update is excellent -- now you can see HTTP headers & cookies inside Postgres functions, so it's possible to save user IP address, user agent, etc. So easy, quick, scalable and with great performance control.

About it: http://info.crunchydata.com/blog/postgres-stig-disa-security...

Why?

Many practices mentioned in the talk are as bad in Postgres as in any other (R)DBMS

Was curious when were LISTEN/NOTIFY implemented in Postgres.

Seems like 6.4 version already had it: https://www.postgresql.org/docs/6.4/static/sql-notify.html

6.4 was released in 1998...

https://github.com/postgres/postgres/blob/REL6_4/doc/src/sgm...

Funny: the error message says "integer divide by 0", so it's basically stack overflow error.

I now use okmeter.io and really happy (especially for nginx and Postgres monitoring). They improve it constantly, and installation took just a couple of minutes. SMS/email/slack notifications work great (however, for slack, I needed to put a webhook).

It's so funny to hear sometimes that noSQL is better that SQL databases and see how much more powerful tools can be built using old good relational engines.

Also, this story a year ago story https://www.linkedin.com/pulse/mongodb-32-now-powered-postgr... was simply epic.

The very short video posted in CMAS news doesn't fully reflect the awesomeness of what's been done. The full recordings of one of the competition's days can be found here: https://www.youtube.com/watch?v=nyWBhcQJNY4

Truly fantastic video of freediving competition. It reminds me awesome NASA TV with their live streaming of spacewalks.

I've never seen so well-organized coverage of a freediving event: good video quality, deep camera going back and forth to 100+ meters, amazing live comments. Various styles, athletes' errors underwater and at the surface, and breathtaking mystery of free fall – the video carries all the details.

Good to see how the combination of modern technologies can be used to achieve such results with relatively low costs.

Decision to store plain text user passwords must cost someone's job.

This looks like really good and well-timed reaction to Uber's case. And shame on Uber that it was absolutely not transparent, hiding its problems.

If you use FOSS, you must be open to communications with the Open Source community. There is a lot of ways to do it. If you're a giant, you can find resources to change the software and contribute with patches, but if you cannot do it, please at least speak openly (or at least privately to some core developers), explaining your needs and issues.

Using FOSS without that is the same as using pirate versions of Oracle or Microsoft products, without buying support subscription.

Have you tried this? https://github.com/ivalkeen/vim-simpledb

For some cases (like single HDD and low amount of RAM) it should be so, but for some cases(from "the whole table is already loaded to RAM" to "SSD" and to "a table is partitioned among multiple drives"), it must give very good improvements for analytical queries.

Many people smarter than me do think that caching [SQL] queries proved to be really bad idea (regardless of DBMS). Why caching API queries would be better?

Even if I would use query caching, I cannot imagine how it would help me to deal with really long queries (say, lacking proper indexes). Caching doesn't help when your query runs for 30 seconds – nobody will wait (and produce cache for others) so long.

Ok, but nested loop in so many situations is totally losing to other algorithms to join data (like merge join, hash join) when you deal with large datasets, right? So again, it will be inefficient by default, for so many cases.

There is a lot of cases when user needs just 20 rows from billions but getting them is very hard problem. One of such examples is well-known: it's twitter-like data model, where a user can "follow" thousands of others and you need to fetch top N recent posts from all that thousands streams.

In Postgres, straightforward approach to query such data is based on JOINs and it's absolutely inefficient. This can be dramatically optimized with recursive CTEs, arrays and loose index scan approach, but GraphQL by default it will do straightforward JOINs, right?

I hope GraphQL has (or will have soon) ways to overwrite/redefine queries, but again, it leads us to the same problems "patch driven development" that everyone hated in ORMs during decades. That's why I'm saying that GraphQL is "a new ORM", but it's more dangerous due to it's openness and proximity to web users, that's why it can bring even more dev and devops pain to the world that ORMs did during decades.

Yes but if in the past you could blame your internal developer for making slow SQL query, now these rich possibilities to perform really stressful queries to your DBMS are open to the public, right?

The main riddle to me is: in case of RDBMS in backend, how can we guess in advance which indexes are needed and how can we forbid/limit all "heavy" queries?

Does anybody considered this problem at all? (Giving too much flexibility to client and allowing non-optimal queries like joining several big tables or data collections w/o proper index support.) It's so weird that all materials I saw about GraphQL hushed up this question which is essential for the future of this technology.

And it's so similar to ORM's issues all the industry experienced past 20 years. But perhaps more dangerous due to the public nature of many APIs.