PG Casts – Postgres Screencasts

https://www.pgcasts.com/

by craigkerstiens • 10 years ago

260 55 10 years ago

pgcasts.com

jbranchaud5• 10 years ago

One of the creators here.

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

insertnickname2• 10 years ago

Is there a feed? I'd much rather have a feed than an e-mail newsletter.

zeisss• 10 years ago

Is there really no newsletter2feed service yet? :( Time to build one.

ing33k• 10 years ago

Nice !

request : can you do some screencasts on window functions ?

saurabh• 10 years ago

sudhirj• 10 years ago

CTEs, window functions, JSONB, PostGIS, and other stuff I wouldn't have heard of or learnt otherwise :)

kevindeasis• 10 years ago

PostGIS would be amazing. Followed by JsonB. Then migrating from JsonB to relational. Hot standby might be interesting too.

juliangoldsmith• 10 years ago

+1 on PostGIS.

kirang1989• 10 years ago

+1 for JsonB.

madawan• 10 years ago

+1 for administration (esp. hot stand-by).

humanfromearth• 10 years ago

Episode suggestion: VARCHAR vs TEXT

paulmd2• 10 years ago

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

andruby• 10 years ago

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.

teddyh3• 10 years ago

Primary keys: INTEGER, BIGINT or UUID?

philliphaydon• 10 years ago

HiLo

jbranchaud5• 10 years ago

and natural keys!

dagw• 10 years ago

Working with raster data

kyriakos• 10 years ago

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.

kriro• 10 years ago

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.

graffitici• 10 years ago

1 !

deathtrader666• 10 years ago

Setting up PostgreSQL for a REST API ?

boothead• 10 years ago

Are you aware of http://postgrest.com/?

MasterYoda• 10 years ago

Security is always important. So would be nice with a guide how to setup pg securly.

madmulita• 10 years ago

NatW• 10 years ago

A very detailed breakdown of the basics of 'explain analyze' with various examples.

mhd• 10 years ago

Lateral Joins

Recursive Queries (+ generating JSON from them)

Advanced Index Types

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

dkarter• 10 years ago

We just finished recording a new episode about Lateral Joins it will be the next episode (early next week). Thanks for your feedback!

fleetfox• 10 years ago

Reading and understanding EXPLAINS and planner, FTS.

Dangeranger3• 10 years ago

Please create an episode about creating custom functions, especially aggregation functions using PL/pgSQL

Dangeranger3• 10 years ago

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.

evv555• 10 years ago

Edge cases of jsonb queries and best practices.

paulmd2• 10 years ago

Clustering/horizontally scaling Postgres. This is one of the toughest topics with Postgres IMO.

nek4life• 10 years ago

I'd love to see a tutorial for JSONB to be used as an alternative to an EAV model or a hybrid approach.

hd4• 10 years ago

A guide on setting up on a popular Linux distro/optimization/maintenance would be nice. Also maybe a guide on setting up Postgresql to use with a RESTful API? And one on window functions as others have said. If you know any good reporting tools that connect to Postgresql then that would be nice. I know, I don't ask for much.

insertnickname2• 10 years ago

For this, I highly recommend this tutorial by Christophe Pettus from PyCon: https://www.youtube.com/watch?v=knUitQQnpJo

It covers the basics of setting up Postgres as well as other things.

spacemanmatt• 10 years ago

I think a tutorial on OpenRESTy would be very appropriate.

oceankid• 10 years ago

So good. I'm on the other end of the spectrum. I'd love more beginner videos about how to build a simple CRUD app that lets PG do the heavy lifting.

Most web frameworks focus on routing/validations/sessions and new programmers like me tend to write the data storage logic around the library, even though SQL is so much nicer and intuitive for this sort of thing.

jbranchaud5• 10 years ago

This sentiment is part of what inspired PG Casts. The database can do lots of powerful things that framework ORMs tend to keep in the shadows. We want to make the DB more accessible!

Dowwie• 10 years ago

I started an awesome postgres SQL repo on github but haven't started to commit to It. I think we share a common goal, but mine isn't as ambitious in that video requires even more effort!

shabble• 10 years ago

An important part of this (that I've still not really figured out) is, if you're pushing much of your validation in the DB, how & where to interpret the different validation errors you might receive, and pass back through your application for turning into friendly messages, highlighting formfields, etc, etc.

unwind• 10 years ago

Oh, so this is about Postgres and not about Paul Graham doing screencasts? I totally failed to understand that from the title and skipped it. :) I see the clarifying "Postgres" there now, but didn't before. D'oh.

fiatjaf• 10 years ago

The screencast in which a guy just copies and pastes statements doesn’t help at all. If it is just copy and paste I can copy myself.

jbranchaud5• 10 years ago

Would it be an improvement if we jumped back and forth between Vim so that you can see the syntax highlighted SQL before each statement is executed?

cdubzzz• 10 years ago

Or perhaps use pgcli?

http://pgcli.com/

Dangeranger3• 10 years ago

Yes that would be helpful. I've edited SQL statements in Vim before and then sent the written buffer to PSQL. The added syntax highlighting made a difference to others watching my terminal.

DasIch• 10 years ago

I'd much rather see this than waiting on someone type out long statements and enduring the unnecessary pauses typing creates.

guitarbill• 10 years ago

this, plus the transcript is stellar and has syntax highlighting!

unixhero• 10 years ago

Would be sweet to learn how to get the PG indexed search engine to work. And how to interface with it in Php or Ruby

dylanz• 10 years ago

This is great. Keep it up! I don't mind the copy/pasting at all (I'd rather have the video progress than watch someone type something out). Looking forward to future shows (and 30 Rock references).

jbranchaud5• 10 years ago

Thanks for the feedback. Seems like a happy medium would be to copy and paste from an editor that is on screen.

showkhill• 10 years ago

The Generating JSON from SQL episode is cool but i'd really like to see an advanced follow up. For instance I've written a query to serialise a collection of records that have many relationships embedded, that conforms to the jsonapi 1.0 standard. Works great but very messy with deeply nested subqueries - hard to read, hard to reuse or alter. I'd really like to see an episode demonstrating a more composable way to craft a complex query like the aforementioned.

PG Casts – Postgres Screencasts

Related Stories