5 ms·

I administer a medium-sized postgres db, around (now) 110GB of data, and recently had the opportunity to revisit the old .conf file when I redeployed that datab

by bshipp 8y ago

I administer a medium-sized postgres db, around (now) 110GB of data, and recently had the opportunity to revisit the old .conf file when I redeployed that database to a different server. What I quickly realized was that years of tweaks I had implemented as I learned postgres from various (now outdated) online sources were making it difficult to optimize my new instance. I decided to start again from scratch. I discovered a handy little utility called pghero and set up it's internal monitoring: https://github.com/ankane/pghero https://github.com/ankane/pghero I backed up my old .conf replaced it with a default and blew away all my old indices, and fired up the database. Of course, nothing worked well. But step by step, working with postgres' internal tools and pghero, I took a brand new look at every previous assumption and was much more strategic about where and when I created an index and which tuning parameter I tweaked. Long story short, I was able to knock 30 GB off of the install disk size (based, in large part, on unnecessary duplicate indices), accelerate the most common queries, and everything worked a lot smoother. Plus my postgres installation was no longer fighting old postgres .conf tuning tweaks which were designed for much older versions of postgres. Just adding my vote for revising your old tuning parameters after your database (and your programmer/administrator) has matured a bit.

joeyrobert 8y ago
This is a common operation for my team as well. In my old project, so many indexes that were added were just assumptions about the usage pattern of the data. Not only did they blow up the size of the table and INSERT time, they became the opposite of a red-herring where developers would stop and look elsewhere because the table "already has indexes". Only adding indexes with good use of EXPLAIN/EXPLAIN ANALYZE on expected usage patterns yielded good results. Often we'd just blow the indexes away and fiddle until we added the correct one to enable an index scan / index only scan. Other big boons for us have been using ENUM types where necessary (small known dataset for a column) -- now your column takes 4 bytes instead of N bytes for a string. I find them a bit easier to work in than foreign keys for this optimization because of their direct string mapping. We've had really good mileage with Datadog's APM tools, RDS performance insights and pghero as you mentioned.
- bshipp 8y ago
  The ENUM type sounds like a great little tweak; I've got quite a few small tables that describe a fixed number of rows so I'll definitely investigate that. I'm my own worst enemy for indexes, because I'm not only the database administrator but also the analyst. So when I'm trying to solve an ad-hoc problem and encounter a long-running query there's a 99% probability that I may have "accidentally" generated a new index to accelerate that query and then forgotten to remove it when I was done. Lesson learned, and also a good argument for disaggregating the administrative and analytical user permissions.
  - aidos 8y ago
    More recently I’ve got into the habit of creating a new schema when I’m analysing / patching data so I can pull out the data I need into new tables and index accordingly. Because I do it from vim and have a plugin to execute the queries, I just create a record as I go (and I set the path to hit my new schema first so any new tables go there).
    - bshipp 8y ago
      Ooooh, I like that idea a lot, especially since I can assign it a tablespace on a different drive.
- paulddraper 8y ago
  They were useful. Unfortunately enums can't be changed (e.g. add a value) in a transaction without some extra work
- Jedd 8y ago
  > Other big boons for us have been using ENUM types where necessary (small known dataset for a column) -- now your column takes 4 bytes instead of N bytes for a string. I find them a bit easier to work in than foreign keys for this optimization because of their direct string mapping. Can you give an example? It sounds like normalising the schema would have similar (perhaps more) benefits.
fake-name 8y ago
PGHero is great, and as someone who's reported (minor) issues and made feature requests on the issue tracker, the author is quite open to improvements too.
11235813213455 8y ago
just wondering, did you have to plan a maintenance downtime for doing all that or could it be done without interrupting the production DB?
- beatgammit 8y ago
  Not OP, but I imagine you could just duplicate the DB into a test environment to mess around with it.
  - bshipp 8y ago
    That's exactly what it was. I had a bit of time after buying the server before I wanted to deploy it in production so I copied the last backup over and just started messing around. Because this database primarily ingests data from outside sources and doesn't drive user experiences, I was able to easily duplicate the data flow to replicate the production environment. I recognize this would be a much different beast when it comes to a database that supports a hundred thousand users or something. However, Postgresql does have the ability to do hot standby and streaming replication so it's likely not as difficult as one might imagine. https://www.postgresql.org/docs/current/warm-standby.html https://www.postgresql.org/docs/current/warm-standby.html

by bshipp 8y ago

joeyrobert 8y ago

This is a common operation for my team as well. In my old project, so many indexes that were added were just assumptions about the usage pattern of the data. Not only did they blow up the size of the table and INSERT time, they became the opposite of a red-herring where developers would stop and look elsewhere because the table "already has indexes". Only adding indexes with good use of EXPLAIN/EXPLAIN ANALYZE on expected usage patterns yielded good results. Often we'd just blow the indexes away and fiddle until we added the correct one to enable an index scan / index only scan. Other big boons for us have been using ENUM types where necessary (small known dataset for a column) -- now your column takes 4 bytes instead of N bytes for a string. I find them a bit easier to work in than foreign keys for this optimization because of their direct string mapping. We've had really good mileage with Datadog's APM tools, RDS performance insights and pghero as you mentioned.

bshipp 8y ago
The ENUM type sounds like a great little tweak; I've got quite a few small tables that describe a fixed number of rows so I'll definitely investigate that. I'm my own worst enemy for indexes, because I'm not only the database administrator but also the analyst. So when I'm trying to solve an ad-hoc problem and encounter a long-running query there's a 99% probability that I may have "accidentally" generated a new index to accelerate that query and then forgotten to remove it when I was done. Lesson learned, and also a good argument for disaggregating the administrative and analytical user permissions.
- aidos 8y ago
  More recently I’ve got into the habit of creating a new schema when I’m analysing / patching data so I can pull out the data I need into new tables and index accordingly. Because I do it from vim and have a plugin to execute the queries, I just create a record as I go (and I set the path to hit my new schema first so any new tables go there).
  - bshipp 8y ago
    Ooooh, I like that idea a lot, especially since I can assign it a tablespace on a different drive.
paulddraper 8y ago
They were useful. Unfortunately enums can't be changed (e.g. add a value) in a transaction without some extra work
Jedd 8y ago
> Other big boons for us have been using ENUM types where necessary (small known dataset for a column) -- now your column takes 4 bytes instead of N bytes for a string. I find them a bit easier to work in than foreign keys for this optimization because of their direct string mapping. Can you give an example? It sounds like normalising the schema would have similar (perhaps more) benefits.

fake-name 8y ago

PGHero is great, and as someone who's reported (minor) issues and made feature requests on the issue tracker, the author is quite open to improvements too.

11235813213455 8y ago

just wondering, did you have to plan a maintenance downtime for doing all that or could it be done without interrupting the production DB?

beatgammit 8y ago
Not OP, but I imagine you could just duplicate the DB into a test environment to mess around with it.
- bshipp 8y ago
  That's exactly what it was. I had a bit of time after buying the server before I wanted to deploy it in production so I copied the last backup over and just started messing around. Because this database primarily ingests data from outside sources and doesn't drive user experiences, I was able to easily duplicate the data flow to replicate the production environment. I recognize this would be a much different beast when it comes to a database that supports a hundred thousand users or something. However, Postgresql does have the ability to do hot standby and streaming replication so it's likely not as difficult as one might imagine. https://www.postgresql.org/docs/current/warm-standby.html https://www.postgresql.org/docs/current/warm-standby.html