I’ve written a bit before about understanding the output from EXPLAIN and EXPLAIN ANALYZE in PostgreSQL. Though understandably getting a grasp on execution plans could probably use some more guidance. Yet, this time around I’m taking a bit of a cop out and highlighting a few tools instead of documenting myself, which I’ve done in a talk I’ve frequently given Postgres Demystified.
Even from intial reviews of my previous post on expression based indexes I received a lot of questions and feedback around many different parts of indexing in Postgres. Here’s a mixed collection of valuable tips and guides around much of that.
In an earlier tweet I joked about some SQL that would generate the SQL to add an index to every column:
# SELECT 'CREATE INDEX idx_'
|| table_name || '_'
|| column_name || ' ON '
|| table_name || ' ("'
|| column_name || '");'
FROM information_schema.columns;
?column?
---------------------------------------------------------------------
CREATE INDEX idx_pg_proc_proname ON pg_proc ("proname");
CREATE INDEX idx_pg_proc_pronamespace ON pg_proc ("pronamespace");
CREATE INDEX idx_pg_proc_proowner ON pg_proc ("proowner");
Postgres is rich with options for indexing. First you’ve got a variety of types, and beyond that you can do a variety of things with each of these such as create unique indexes, use conditions to index only a portion of your data, or create indexes based on complex expressions or functions. In cases where you commonly use various PostgreSQL functions in your application or reporting you can get some great gains from this.
I’m reasonably proficient at SQL – a coworker when pseudocoding some logic for him pointed out that my pseudocode is what he thought was executable SQL. I’m fully capable of writing clear and readable SQL – which most SQL is not. Despite that I still have several bad habits when it comes to SQL. Without further adieu heres some of my dirty laundry so hopefully others can not make the same mistakes.
Following up on my earlier post about CX or Conference Experience – I’m going to dig in a bit on how you get good conversation to happen. In the past two years I’ve been to nearly 20 conferences, I’ve been to conferences with great talks, with great parties, with great swag, and hands down my favorite conferences have always been a result of great conversation. With the number of talks that are recorded and immediately available online after, what can I say I’m a hallway track guy.
I’ve seen a number of conferences intentionally design around this concept, in some ways the unconference is purely a hallway track conference. I’ve also seen conferences that weren’t clearly planned for this and have pulled off some of the best situations where people turn their phones off and engage in real conversation.
A couple of weekends ago I had the great opportunity to attend lessconf. It was an all around great conference, and as a result of the greatness I ended up having a conversation with a few people around conference experience. I must give much of the credit to Swift, as he mentioned he’d already been thinking alot about this since Waza. In general it feels like there’s a few key themes that any conference should focus on, then a lot of small things that can really push it over the top. Here’s a few:
In general the key areas for any great conference are:
Digging in deeper on the first area…
In almost any application it’s common to want to aggregate some set of values together, commonly in a comma separated form. Most developers do this by running a query to get much of the raw data, looping over the data and pushing it into a set, appending each new value to the appropriate key. Hopefully, it’s not a surprise that there’s a much better way to do this with PostgreSQL.
Postgres has a flexible and robust array datatype that comes with a variety of functions. Even without taking advantage of the array datatype in your application, you can still take advantage of some of the functions to get the functionality you need. Lets take a look at an example schema and use case.
The first area I tend to think about when it comes to developer marketing is around advocates. A simple definition of an advocate is someone that speaks/writes in favor of some thing.
Creating advocates is a means of creating more of myself to go out and talk loudly and ideally effectively. However, they do take time to cultivate and it is a hard item to track. But by cultivating advocates I’m able to scale what would otherwise be a bottleneck of my own personal time.
As developers we can tend to be a fickle bunch; especially in certain open source communities. We like to see intelligence and systems applied to things, hope for a better world through making things open, and appreciate when others relate to our world as we often attempt to relate to theirs. Marketing is often a loaded word when it comes to developers – leaving mixed feelings about webinars, email campaigns, and the like.
A quick clarification in terms of marketing I’m referring to product marketing, but of a technical product. This is based on the idea that developers are the new kingmakers and when marketing a product to them it needs to be done differently.
I blog because I’m lazy. There’s more too it though: In any given day I may explain something to someone, the first time I do this I make a bit of a mental note. The second time I do this, especially within a short time frame I make a physical note of this in the form of the title of a blog post. Once I’m already to a second time of doing this its almost inevitable I’ll continue repeating myself – and its valuable to others.