In an earlier post I provided a high level overview of sharding. Sharding while a very solid approach to scaling capacity versus simply only relying on vertical scaling can also be a time intensive one. Additionally in some cases certain sites may only need extra capacity for a short lived period of time. Fortunately theres a nice middle ground alternative for scaling capacity that works well in quite a few cases. It even has a benefit that can potentially in place of sharding.
Category: Database
After my last post I had a variety of readers reach out about many different tweaks they’d made to their workflows using with psql. One people Grégoire Hubert had a wondeful extensive list of items. Grégoire has been a freelance in web development and he has worked with Postgresql for some time now in addition to being the author of Pomm. Without further ado heres what he has to say on how he uses psql:
Get the most of psql
Psql, the CLI postgreSQL client, is a powerful tool. Sadly, lot of developers are not aware of the features and instead look for a GUI to provide what they need. Let’s fly over what can psql do for you.
On at least a weekly basis and not uncommonly multiple times in a single week I get this question:
@neilmiddleton I’ve been hunting for a nice PG interface that works within other things. PGAdmin kinda works, except the SQL editor is a piece of shit
Sometimes it leans more to, what is the Sequel Pro equivilant for Postgres. My default answer is I just use psql, though I do have to then go on to explain how I use it. For those just interested you can read more below or just get the highlights here:
- Set your default
EDITORthen use \e - On postgres 9.2 and up
\x autois your friend - Set history to unlimited
\dall the things
Before going into detail on why psql works perfectly fine as an interface I want to rant for a minute about what the problems with current editors are and where I expect them to go in the future. First this is not a knock on the work thats been done on previous ones, for their time PgAdmin, phpPgAdmin, and others were valuable tools, but we’re coming to a point where theres a broader set of users of databases than ever before and empowering them is becoming ever more important.
Empowering developers, DBA’s, product people, marketers and others to be comfortable with their database will lead to more people taking advantage of whats in their data. pg_stat_statements was a great start to this laying a great foundation for valuable information being captured. Even with all of the powerful stats being captured in the statistics of PostgreSQL so many are still terrified when they see something like:
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Hash Join (cost=4.25..8.62 rows=100 width=107) (actual time=0.126..0.230 rows=100 loops=1)
Hash Cond: (purchases.user_id = users.id)
-> Seq Scan on purchases (cost=0.00..3.00 rows=100 width=84) (actual time=0.012..0.035 rows=100 loops=1)
-> Hash (cost=3.00..3.00 rows=100 width=27) (actual time=0.097..0.097 rows=100 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 6kB
-> Seq Scan on users (cost=0.00..3.00 rows=100 width=27) (actual time=0.007..0.042 rows=100 loops=1)
Total runtime: 0.799 ms
(7 rows)
Empowering more developers by surfacing this information in a digestable form, such as building on top of pg_stat_statements tools such as datascope by @leinweber and getting this to be part of the default admin we will truly begin empowering a new set of user.
But enough of a detour, those tools aren’t available today. If you’re interested in helping build those to make the community better please reach out. For now I live in a work where I’m quite content with simple ole psql here’s how:
For any successful web application there is likely to come a time when you need to conduct some large migration on the backend. I dont mean simple add a column here or add an index there, but rather truly sizeable migrations… Going from MySQL to Postgres or migrating from an older version of Postgres such as a 32 bit instance to a newer 64 bit instance. In these cases the default approach is to just schedule downtime often throwing up a splash screen saying so.
For many sites this approach is simply wrong and lazy, with little effort you can improve the experience and there by ease the burden in conducting these types of migrations. By having the ability to turn your site into a read only mode which Simon Wilson talked about in his post on Lanyrd you can still continue to operate just in a limited capacity. Andrew Godwin further talks about some of this as well in regards to the Lanyrd move and even includes the script they used to migrate data from MySQL to Postgres. Though just in talking with Simon about this a week ago it occurred to me they had not released the code for their read-only mode.
Finally onto the announcing, today I’m releasing django-db-tools. This is currently a very lightweight utility that allows you to flip your site into two modes.
Yesterday there was a post which hit Hacker News that talked about using SQL to access Mongo. While this is powerful I think much of the true value was entirely missed within the post.
SQL is an expressive language, though people are often okay with accessing Mongo data through its own ORM. The real value is that you could actually query the data from within Postgres then join across your data stores, without having to do some ETL process to move data around. Think… joining sales data from Postgres with user reviews stored in Mongo or searching for visits to a website (retained in redis) against purchases by user in Postgres.
The mechanism pointed out was a MongoDB Foreign Data Wrapper. A Foreign Data Wrapper or FDW essentially lets you connect to an external datastore from within a Postgres database. In addition to the Mongo FDW released the other day there’s many others. For example Postgres 9.0 and up ships with one called db_link, which lets you query and join across two different Postgres databases. Beyond that there’s support for a variety of other data stores including some you may have never expected:
Lets look at actually getting the Redis one running then see what some of the power of it really looks like. First we have to get the code then build it:
Earlier this week while I was at DjangoCon EU there seemed to be a surprising amount of talk about MongoDB. My problem with this isn’t with MongoDB, but in the assumption that only Mongo can solve what you’re looking for. By and far the most common feature is people want schemaless. It gives them flexibility in their data model and lets them iterate quickly. While I still opt for relational models that map cleanly to a relational database, there are cases where developers may want schemaless. I gave a quick lightning talk on this with slides here, but it is worth recapping.
This post is a list of many of the reasons to use Postgres, much this content as well as how to use these features will later be curated within PostgresGuide.com. If you need to get started check out Postgres.app for Mac, or get a Cloud instance at Heroku Postgres for free
Last week I did a post on the many reasons to use Postgres. My goal with the post was two fold:
- Call out some of the historical arguments against it that don’t hold any more
- Highlight some of the awesome but more unique features that are less commonly found in databases.
Once I published the post it was clear and was immediately pointed out in the comments and on hacker news that I missed quite a few features that I’d mostly come to take for granted. Perhaps this is due to so much awesomeness existing within Postgres. A large thanks to everyone for calling these out. To help consolidate many of these, here’s a second list of the many reasons to use PostgreSQL:
This post is a list of many of the reasons to use Postgres, much this content as well as how to use these features will later be curated within PostgresGuide.com. If you need to get started check out Postgres.app for Mac, or get a Cloud instance at Heroku Postgres for free
UPDATE: A part 2 has been posted on Why Use Postgres
Very often recently I find myself explaining why Postgres is so great. In an effort to save myself a bit of time in repeating this, I though it best to consolidate why Postgres is so great and dispel some of the historical arguments against it.