PgOpen 2016 recap

It’s been a few weeks now since we wrapped up the most recent PostgresOpen in Dallas.

Marianne Bellotti’s keynote was a fantastic and very interesting look into the current state of government IT.  I hope we get to see her at more conferences!

Other quick highlights:
– 2ndQ has a running beta of WordPress on Postgres! (Simon’s keynote)
– Early decisions by the Postgres team (e.g. to spend time working on subselects) directly resulted in my choosing Postgres over MySQL a few years later. (From Greg Stark’s “A Look at the Elephant’s Tail”)
– Keith Fiske (“Managing OS Provided PostgreSQL Packages”) gave a rundown of the vagaries of using Pg packages on different OSes, something that has occasionally driven me around the bend.

I got the most bang for my buck from the combination of Magnus’s Haganders “A Look at the Elephant’s Trunk” and Denish Patel’s “Advanced Postgres Monitoring” (slide deck here). Denish has a thorough list of metrics you should collect, and there are some changes coming in 9.6 to pg_stat_activity that may break current monitoring scripts that use it.

(Also: I am VERY EXCITED about pg_stat_progress_vacuum!)

(And I learned that NumPy is pronounced num-pie and not “rhymes with lumpy”.)

Bonus speaker tip: Many years ago I was in a talk in which the presenter asked “Does everybody understand [x]?” When noone responded, he continued “Just in case we have some people here who are too shy to raise their hands, [x] is…” and gave a brief explanation, enough for newer folks to follow along for the rest of the session. This is an excellent inclusion tactic. Scott Meade demonstrated another way to handle this situation: “So we’re at level set, [x] refers to …”

Thanks to our attendees, speakers, and sponsors for making this conference what it is. The committee is meeting this week to make plans for next year.  Watch the conference website and twitter feed for updates.

My Picks for PgOpen 2016

We’re back in Dallas for our 6th year of PostgresOpen!  This year, we’re out at the Westin Galleria, which I’m told has an ice rink. (Is it too late for someone bring the Slonik costume?)

In addition to choosing the talks for the program, part of my conference committee duties involve being a room host. I won’t get to see everything I want, but here’s five that are at the top of my “wish list”:

• Denish Patel’s Advanced Postgres monitoring – always looking for ways to do this better
• *Of course* I will be in Grant McAlister’s RDS talk
• Shaun Thomas clearly and simply explains even advanced topics, and I usually come out of the room thinking “BOY am I glad I don’t have to deal with THAT situation”. This year is Elephant Herd as a Service
• Sounds like there are some “interesting” stories behind Ilya Kosmodemiansky’s PostgreSQL Worst Practices
• And we are really excited to have Alexander Korotkov and Oleg Bartunov join our conference to talk about RUM indexes!

A couple of other sessions I recommend:

Check out Dimitri Fontaine’s You’d Better Have Tested Backups ; it’s pretty hair-raising.

Want to contribute back to Postgres, but don’t know where to start? Review some patches! David Steele will give you some pointers.

We will be having Lightning Talks, as usual. If you want to get the jump on everyone else, you can sign up here.

What’s new this year: instead of an auction, we’re having a golf tournament.  I’ve never golfed before and am looking forward to giving it a try. Come join us, it’ll be a hoot. (There is a dress code so pack accordingly if you think you might want to play.)

See you in Dallas in a few weeks! Stop by the reg desk and say hello.

Scale 14x recap

I went to SCALE mainly for PostgreSQL@Scale – Joe Conway’s grown this annual add-on into a two-day, two-track event.

The new venue is a huge improvement, even though the walk from the hotel was a bit longer than I expected. There are a lot more accessible-on-foot food options.

—

For my session, I gave an updated version of last year’s “RDS Postgres: A Journey down the Amazon”.

If you’re using RDS and aren’t keeping up with your monitoring, please be aware of the current required OS update.  You can view & schedule required updates via the web console; here are the corresponding cli commands:

aws rds describe-pending-maintenance-actions \
--filters Name=db-instance-id,Values=mydb,mydb1,mydb2

aws rds apply-pending-maintenance-action \
--resource-identifier arn:aws:rds:[region]:[account]:db:[db-name] \
--apply-action system-update \ 
--opt-in-type next-maintenance

I find JSON and text output more useful for these types of tasks; I’ll cover that in a later post.

Accessing RDS Pg logs is kind of a sticking point for some people, me included. (I _really_ want an easy way to get them into Splunk.)
Here’s Denish Patel’s work using the rds cli, and a quick bash script I threw together, which uses the aws cli.

Based on the quick headcount I took at the beginning of the session, attendees were about 2/3 devs, 1/3 DBAs. We also had a couple of people from the Amazon RDS team; it’s always nice to know I have backup in the audience! ;)

A couple of folks had questions after my talk and I didn’t catch up with them in the booth. If that is you, feel free to leave a comment here, or email me.

—

Other talks I attended:
Jim Mlodgenski’s Debugging PL/PgSQL contained some good tips on use of RAISE NOTICE and what not to do. I installed pl_profiler and started using it the next afternoon.

Xof’s JSON Home Improvement included advice on when to use JSON vs JSONB, or maybe even neither :) If you’ve been reading my blog for any length of time, you know that these kinds of discussions just end in me making plans for more benchmarking…

Peter Geoghegan’s UPSERT use cases  covered a good range of material from beginning to “We can do WHAT now?” and made me realize I can always improve my SQL skills.

I’ve been hearing about ToroDB  but hadn’t attended one of Alvaro’s talks yet. Very basically, it’s a Pg-backed Mongo interface. They take the json data and explode it out into sub-documents. Then there’s a table that stores the structure _in json_. It both blew my mind & had me smacking my forehead “of course!”

It is in alpha, so this applies.
—

Slides for talks are (or will be) on the Pg wiki.  We do rely on speakers to load their own slides, so if you are after something in particular, please ask & I will remind them. :)

—

I’m already looking forward to next year, and hope I get to go ice skating then!

PgOpen recap

We’ve wrapped up another PostgresOpen! The official numbers aren’t in yet, but we were up a significant percentage from last year. We had so many last-minute registrants and walk-ups that we ran out of extra badges.

Even better, I got to attend most of the talks on my list.

I can’t pick a favorite, but judging by the amount of notes I took, I learned the most in Joe Celko’s talk on measurement scales. The concepts and nomenclature were vaguely familiar from my long-ago education in the sciences, and it was interesting to tie them in with what I’m doing now.

Grant gave an excellent introduction to RDS Pg. I even learned some things. If you’re new to RDS Postgres, you should take this tutorial next time it’s offered. I continue to be impressed with the RDS team members; not only are they a whip-smart group, they’re also very personable and kind.

I learned about \ef (allows you to edit functions in place) in Keith Fiske’s Don’t Forget the Elephant, and was also reminded of how far Postgres has come, even just since 9.0. So many useful features!

Stella’s SQL Guru talk gave me some good pointers on window functions.

Slides for most of the talks are available on the wiki, and videos are on the way.

Between the social events and prepping for the Tour des Fleurs, I did not have time to see much of Dallas. I’ll likely run the TDF again next year, if the conf coincides with it. LMK if you’d like to join. The course is mostly flat; there’s one small hill.

Great to see everyone and hope to see you again next year!

My “Must-see” List for PgOpen

PostgresOpen is coming up in just a few weeks! (Disclosure: I am on the conference committee.) We are still working out the room assignments; your conference badge/printed program will have the right info.

The committee is very excited to have snagged Lacey Williams Henschel to give our keynote, on Open Source & Higher Ed. I’m looking forward to meeting her! (The real reason I’m at the reg desk is to try to meet everyone ;) )

As in previous years, four tutorials are scheduled for the day before the conference proper. As anyone who’s talked to me in the past year could guess, I’m most excited about Grant McAlister’s RDS tutorial. There’s always more to learn about this quickly maturing platform.

My picks for the rest of the conference are as follows:
Thursday sessions:
– Kevin Kempter’s Monitoring Postgres

– Joe Conway’s R talk

– Sarah Conway’s security talk was SRO last year; this year she’s back to focus on security in RDS. (In the same time slot, Jim Mlodgenski’s FDW talk is a good intro to that subject.)

– I’ve been using SQL for a while now :koff: but I’m still not that great at some things like window functions, so I’ll be in Stella Nisenbaum’s SQL Guru talk.

– Then I’ll finish out my day with David Steele’s Audit Logging.

Friday sessions:
– I’ve been enjoying the “History of Postgres”-type talks we’ve had at a few confs lately, and am looking forward to Keith Fiske’s version, Don’t Forget the Elephant.

– Shaun Thomas Highly Available Postgres.

– Of course I’ll be in Peter Don’t-even-try-to-pronounce-it Geoghegan’s UPSERT use cases.

– I’ve caught Magnus Hagander’s Tardis talk at NYC earlier this year. If you haven’t had the chance, you should check it out!

– And last, Phil Vacca’s Text Search.

We will be having a lightning talk session, as in previous years. Signups will open soon; watch the conference twitter stream for announcements.
—

It’s not too late to register, but our hotel room rate and reservation block expires on Sept 2, so act fast! If you’re a member of a PUG, ask your group leader for the conference discount code. (If you’re a PUG leader and haven’t received the code, please email the conference committee.

LFNW Conference Report

At the end of April, Mark Wong & I headed up to Bellingham for LinuxFest. This was my first time at the conference in several years, and though the expo hall was a bit smaller than I remembered, they had 1849 registered attendees! That is a truly excellent number for a community-run conference in a far corner of the US.

Somehow we had three Postgres booths: PgUS, 2nd Quadrant, and SEAPUG. This caused some confusion among the attendees. As in past years, booth visitors ran the gamut between experienced folks, random people who dropped in to see what was going on, and That Guy Who Just Wants to Argue^W Discuss Why MySQL Is Better [tm]. I hadn’t actually seen him in a while, it was good to catch up. :koff:

Talks I attended:
Emily Dunham’s Thinking in git, where I learned about git commit –dry-run and git pull –rebase (which makes it look like you were smart & pulled when you were supposed to.)

Robert Bernier’s Welcome To Total Security gave a fun historical overview of Postgres, and very good coverage of both beginner and more advanced information.

Frances Hocutt’s Why are these people following me. The big lesson for me from this talk was “Just because the abuse isn’t aimed at you, doesn’t mean you don’t suffer from it”, as I am currently dealing with a similar situation. Also: “Assume good faith” doesn’t mean “ignore evidence of bad faith”.

Eric Worden’s start_date, end_date: Calculate! included a pretty appalling example of a temporal table design, and how to fix it. This just convinced me even more that range types are The Right Way to handle these requirements.

On a personal note: I’ve been doing Pg-only confs for the past few years and made a rookie presenter mistake with my talk: I assumed a level of familiarity with Postgres that my audience didn’t have. I’ll need to adjust for that next time.

SCALE 13x and “You are not the database Superuser”

Another followup from my talk at SCALE 13x.

This isn’t to pick on RDS in particular; you’ll likely have these or similar issues with other “database as managed service” options.

Specific problems you should be aware of:

1a – As I discussed in my post about upgrading an RDS db to Pg 9.4, you can’t pg_dumpall; access to roles & credentials is restricted.
1b – … which means you can’t extract roles & credentials. (I’m told the RDS devs are working on a way to handle this.)

2 – Lots of in pg_stat_activity. You can’t see what the actual superuser is doing, of course; so far this is an annoyance more than a true problem, because I wouldn’t be able to smash a query the actual superuser is running, anyway.

3 – You can’t VACUUM a database, only individual tables.

4 – Oops: “FATAL: remaining connection slots are reserved for non-replication superuser connections”. This does not include you :), so you are effectively locked out of your instance. You can try calling Amazon support for this; my experience with this was that even a request for information about existing connections had to be escalated, and I was told they wouldn’t terminate connections for me. So you’re looking at killing them from the app side, or restarting the instance (with a higher max_connections, perhaps). This makes troubleshooting this issue particularly challenging.

5 – If you’re fond of using SET ROLE [rolename] to troubleshoot, you have to do the extra step of adding yourself to the role:
gabrielle=> SET ROLE db_rw;
ERROR: permission denied to set role "db_rw"
gabrielle=> GRANT db_rw TO gabrielle ;
GRANT ROLE
gabrielle=> SET ROLE db_rw;

SCALE 13x and “Yes, you still need a DBA”

Last weekend, I got to give a talk at SCALE‘s LA PgDay for the second year in a row. PGDay was two days this year, and unfortunately I had to miss Thursday – looks like I missed some great talks, too, dangit!

I reprised my RDS talk from PDXPUG’s January meeting. It certainly sparked some interesting discussions later.

(If you are too shy to ask questions in my talk, it is totally ok to come up afterwards and ask them, or stop by the Postgres booth & talk to me. That’s what I’m there for!)

I mentioned something in my talk that I want to throw out here because it’s been coming up in conversations recently:

If you have production data that you want to protect, you need a database adminstrator. Even if you are using a managed database service (RDS, Heroku, what have you), you need someone to:

– Choose an appropriate instance size for your workload
– Configure Postgres appropriately (as much as is possible on a managed service, anyway)
– Secure and audit databases
– Ensure data quality
– Tune queries (e.g. figure out what in [Sam Hill] the ORM is doing)
– Mentor devs
– …add your own here…

None of these tasks go away just because you’re using a managed database solution. You still need someone who can hop in there & get her hands dirty. You may disagree, but please keep this list handy http://www.postgresql.org/support/professional_support/.

PgConf.EU recap

I’m safely home from PgConf.EU. Madrid at this time of year was glorious, particularly to this Portlander. (I came home to a steady 12*C and rainy for the next week or … so ;))

We had over 300 attendees, making this the biggest Postgres conference to date, I hear. Of course, I couldn’t get to every talk I wanted (does that ever happen?), but here are some highlights:

Performance Archaeology was a thorough review of how Postgres performance has improved (or not) from version to version. I’m a sucker for benchmarks, and it makes me very happy that Tomas Vondra did this work :)

“Who’s the Fairest of Them All? Pg Interface Performance Comparison” was good from an informational standpoint (ODBC pretty much sucks) but also from a test design standpoint (hey, a valid use case for a Cartesian join!) Most relevant tip for me: complaints about db performance usually turn out to be caused by running queries returning one row at a time, one connection each – and usually from an ORM.

The demo of 3D rendering from Vincent Picavet’s “PostGIS Latest News” looked very promising. There’s a docker container available on his github; make sure you follow the setup instructions. I’m also excited about SP-GiST, spatial GiST indexes, which will provide faster reads and is 3X faster to build. It’s a WIP, and so far it only works on points.

XoF’s talk on “Finding and Repairing Data Corruption” covered some case histories from PgExperts. You all know I like the “war stories”; one thing I like especially about XoF’s talks is he includes “oh yeah, btw, don’t do [x] to try to fix this because you’ll make things worse”. Additional recommendation: disable autovacuum while you’re debugging corruption, because you don’t want it kicking off & changing things.

As usual, Simon and Alvaro packed a ton of info into “Locks Unpicked”. The most immediately useful tip for me was how to avoid the ACCESS EXCLUSIVE lock when adding an FK; do it in two steps: 1. ALTER TABLE [blahdeblah] NOT VALID 2. ALTER TABLE [blahdeblah] VALIDATE CONSTRAINT.

I didn’t get to attend Dimitri’s “You Better Have Tested Backups”, but I was in on a rehearsal. My reaction can be summed up with “You had to do what?!” If this talk didn’t scare you, I don’t know what will.

Craig Ringer’s talk about usability started with a round of “Error Message Jeopardy”, and included a reminder that we were all new once, and have forgotten how much we know. I personally accidentally tried to run psql on a -FC pg_dump just last week, and really appreciate the addition of the HINT message! I also hadn’t heard about the update problems on Yosemite.

Stephen Frost’s “Hacking Postgres” was one of my favorites. We got a tour of the source tree, backend components, and some background about the community coding conventions. (“Programming in Postgres may not always be standard C.”)
General advice:
– check the mailing list for people working on similar problems
– create your patch as a context diff or git –diff
– read your actual patch before you submit it, just in case you did something dorky.

Don’t forget to post your slides & leave conference and speaker feedback.

It was wonderful meeting people I’ve only known from the mailing lists & IRC. (For example.) Thanks very much to the PgConf.EU and SPI for helping me out! I hope to see you next year in Vienna.

SCALE12x Report

Last week I headed to sunny LA for the SoCal Linux Expo, aka SCALE. This was my first time at SCALE & I’ll definitely be going back (it reminded me of my first OSCON several years ago).

I’ve wanted to go to this conference for a few years now & finally had a reason to go: I gave a talk at LAPgDay (aka the Postgres track) on Friday. The Pg talks were mostly SRO throughout the day! That’s pretty exciting. I had a lot of fun giving my talk about Pg’s vacuum & autovacuum processes & got some good feedback about it. Which reminds me: if you haven’t already, please fill out the speaker survey; there’s a link on the back of your badge.

On Saturday, I spent most of my time in the Postgres booth (of course) with friends old and new. I’m just about finished following up with folks who had questions for me, so if you haven’t heard from me yet, hang tight. :) I managed to take one quick spin through the Expo Hall, and have to say I am very intrigued with the DIYBio movement, and hope the PDX group gets something going soon. I also picked up yet another copy of The Manga Guide to Databases at the NoStarch booth, and gave it away the day I got home. I can’t seem to keep a copy of that book for myself!

Two Pg tips I picked up this weekend (thanks Steven & Joe):
1. “TABLE my_table;” is a handy alias for “SELECT * FROM my_table;”

2. The ‘AS’ keyword is not required to specify a field name for an alias. Apparently it’s been this way for a while, and I just managed to avoid being bitten by it until last weekend when I misplaced a comma.