News for hackers. Stuff that matters.
Re:PG Casts – Postgres Screencasts by jbranchaud on Thursday June 16, 2016 @11:14AM (#11916667)
One of the creators here.

We'd love feedback on the screencasts and suggestions on topics that you'd like to see covered!

Re:PG Casts – Postgres Screencasts by Dangeranger on Friday June 17, 2016 @07:48AM (#11922040)
Please talk about database views, both regular and materialized. When they are best used, strengths and weaknesses. Compare composing a combined query out of CTE's and sub-queries.
Re:PG Casts – Postgres Screencasts by andruby on Friday June 17, 2016 @05:00AM (#11921438)
Hot standby. How to promote a slave to master. Continuous archiving. Effective logging and analysing with eg. PgBadger. Performance tuning and benchmarking.

Best practices around those tasks would really be helpful.

Re:PG Casts – Postgres Screencasts by kriro on Friday June 17, 2016 @05:31AM (#11921551)
A screencast on using PG to handle stuff that is often stored in noSQL databases would be interesting. I'd be interested in both using PG's new noSQL capabilities but more importantly also in walking through creating a Schema for the data (and the tradeoffs). I'd suggest mining some Tweets with metadata from the streaming API (JSON) and using that as a running example.
Re:PG Casts – Postgres Screencasts by graffitici on Friday June 17, 2016 @02:25PM (#11924509)
1 !
Re:PG Casts – Postgres Screencasts by kevindeasis on Friday June 17, 2016 @04:17AM (#11921248)
PostGIS would be amazing. Followed by JsonB. Then migrating from JsonB to relational. Hot standby might be interesting too.
Re:PG Casts – Postgres Screencasts by juliangoldsmith on Friday June 17, 2016 @09:46AM (#11922586)
+1 on PostGIS.
Re:PG Casts – Postgres Screencasts by kirang1989 on Friday June 17, 2016 @02:30PM (#11924547)
+1 for JsonB.
Re:PG Casts – Postgres Screencasts by sudhirj on Friday June 17, 2016 @02:24AM (#11920992)
CTEs, window functions, JSONB, PostGIS, and other stuff I wouldn't have heard of or learnt otherwise :)
Re:PG Casts – Postgres Screencasts by insertnickname on Friday June 17, 2016 @01:37AM (#11920866)
Is there a feed? I'd much rather have a feed than an e-mail newsletter.
Re:PG Casts – Postgres Screencasts by zeisss on Friday June 17, 2016 @03:44AM (#11921178)
Is there really no newsletter2feed service yet? :( Time to build one.
Re:PG Casts – Postgres Screencasts by teddyh on Friday June 17, 2016 @08:37AM (#11922244)
http://gmane.org/ converts a mailing list to NNTP, and also provides feeds.
Re:PG Casts – Postgres Screencasts by MasterYoda on Friday June 17, 2016 @06:00AM (#11921660)
Security is always important. So would be nice with a guide how to setup pg securly.
Re:PG Casts – Postgres Screencasts by madmulita on Saturday June 18, 2016 @10:48AM (#11928845)
+1
Re:PG Casts – Postgres Screencasts by kyriakos on Friday June 17, 2016 @05:24AM (#11921537)
This is a great idea. Thanks. PostgreSQL is a very complicated and feature rich system but has a steep learning curve once you deviate from the basic CRUD. Even though the documentation is decent its not very beginner friendly - hopefully the screencasts will grow in number and topics to help make Postgre more mainstream.
Re:PG Casts – Postgres Screencasts by Dangeranger on Friday June 17, 2016 @07:45AM (#11922031)
Please create an episode about creating custom functions, especially aggregation functions using PL/pgSQL
Re:PG Casts – Postgres Screencasts by ing33k on Friday June 17, 2016 @01:58AM (#11920928)
Nice !

request : can you do some screencasts on window functions ?

Re:PG Casts – Postgres Screencasts by saurabh on Friday June 17, 2016 @02:08AM (#11920955)
+1
Re:PG Casts – Postgres Screencasts by fleetfox on Friday June 17, 2016 @07:23AM (#11921970)
Reading and understanding EXPLAINS and planner, FTS.
Re:PG Casts – Postgres Screencasts by mhd on Friday June 17, 2016 @06:58AM (#11921887)
Lateral Joins

Recursive Queries (+ generating JSON from them)

Advanced Index Types

(Ab-)using arrays & custom types (contrast with JSON(B) for everything)

Re:PG Casts – Postgres Screencasts by dkarter on Friday June 17, 2016 @12:51PM (#11923716)
We just finished recording a new episode about Lateral Joins it will be the next episode (early next week). Thanks for your feedback!
Re:PG Casts – Postgres Screencasts by paulmd on Friday June 17, 2016 @12:19PM (#11923551)
Clustering/horizontally scaling Postgres. This is one of the toughest topics with Postgres IMO.
Re:PG Casts – Postgres Screencasts by deathtrader666 on Friday June 17, 2016 @05:46AM (#11921607)
Setting up PostgreSQL for a REST API ?
Re:PG Casts – Postgres Screencasts by boothead on Friday June 17, 2016 @07:57AM (#11922068)
Are you aware of http://postgrest.com/?
Re:PG Casts – Postgres Screencasts by abhimskywalker on Friday June 17, 2016 @08:37AM (#11922242)
Thanks! Was not aware if this is even possible. This looks quite awesome!
Re:PG Casts – Postgres Screencasts by snuxoll on Friday June 17, 2016 @11:55AM (#11923369)
It is quite awesome. It'd be even more awesome if it had support for kerberos (and delegation) built in so I didn't have to write an external JWT provider, if I could rely on PostgreSQL's built-in kerberos support for authentication it would be amazing.
Re:PG Casts – Postgres Screencasts by humanfromearth on Friday June 17, 2016 @04:55AM (#11921407)
Episode suggestion: VARCHAR vs TEXT
Re:PG Casts – Postgres Screencasts by paulmd on Friday June 17, 2016 @12:25PM (#11923584)
Pretty easy to get just from reading the first couple paragraphs of the documentation. Postgres has three string types, CHARACTER, CHARACTER VARYING, and TEXT.

CHARACTER VARYING is the basic type of string, and can be given with a maximum character length. If no length is given, the max value is assumed.

CHARACTER is a padded string - all strings are padded to the specified length with spaces. If no length is given, the length 1 is assumed.

TEXT is an alias for a CHARACTER VARYING without a length parameter (max length).

Actually all string types are handled the same internally, and there's no performance reason to use CHARACTER instead of CHARACTER VARYING. In fact it may hurt performance since the padding increases the data size. Using length constraints also carries a slight performance hit during writes as the constraint gets checked.

https://www.postgresql.org/docs/9.5/static/datatype-characte...

Re:PG Casts – Postgres Screencasts by NatW on Friday June 17, 2016 @06:05AM (#11921687)
A very detailed breakdown of the basics of 'explain analyze' with various examples.
Re:PG Casts – Postgres Screencasts by evv555 on Friday June 17, 2016 @10:49AM (#11922942)
Edge cases of jsonb queries and best practices.
Re:PG Casts – Postgres Screencasts by nek4life on Saturday June 18, 2016 @12:59PM (#11929414)
I'd love to see a tutorial for JSONB to be used as an alternative to an EAV model or a hybrid approach.
Re:PG Casts – Postgres Screencasts by madawan on Friday June 17, 2016 @04:46AM (#11921359)
+1 for administration (esp. hot stand-by).
Re:PG Casts – Postgres Screencasts by teddyh on Friday June 17, 2016 @05:12AM (#11921501)
Primary keys: INTEGER, BIGINT or UUID?
Re:PG Casts – Postgres Screencasts by philliphaydon on Friday June 17, 2016 @06:37AM (#11921805)
HiLo
Re:PG Casts – Postgres Screencasts by teddyh on Friday June 17, 2016 @07:12AM (#11921922)
For those who don’t know what it is:

https://vladmihalcea.com/2014/06/23/the-hilo-algorithm/

Basically, it optimizes for lots of insert operations by using a composite primary key, one of which is a SERIAL assigned by the server, and the other is a value arbitrarily assigned by the client. This allows a client to assign and use many new unique keys without asking the server for a new value for every individual row.

Re:PG Casts – Postgres Screencasts by jbranchaud on Friday June 17, 2016 @08:21AM (#11922159)
and natural keys!
Re:PG Casts – Postgres Screencasts by dagw on Friday June 17, 2016 @05:15AM (#11921508)
Working with raster data