If you have production DB servers, chances are you're running variant of these queries:
SELECT sum(pg_relation_size(oid)) from pg_class where relkind = 'i'
SELECT sum(pg_relation_size(oid)) from pg_class where relkind = 'r'
To get summarized size of tables and/or indexes in your database (for example for graphing purposes).
This (getting pg_relation_size for rows in pg_class) has one problem – it can lock, or it can fail.
Continue reading Tips N' Tricks – getting sizes of relations without locks
I got asked on irc to show some examples how to use recursive CTE. Apparently my previous post wasn't good enough 🙂
I think that most of the users will use recursive cte to deal with trees I decided to show how to use it, even though it's not my favorite approach to dealing with trees in SQL.
Continue reading r/trees ( recursive trees, what did you think about? )
On 14h of December, Andrew Dunstan committed patch:
Add --exclude-table-data option to pg_dump.
Andrew Dunstan, reviewed by Josh Berkus, Robert Haas and Peter Geoghegan.
This allows dumping of a table definition but not its data, on a per table basis.
Table name patterns are supported just as for --exclude-table.
Continue reading Waiting for 9.2 – excluding data of table from dump
I'm huge fan of stand-up comedy, and one of the guys that I like is Louis CK. Learned today that he made new movie – “Live at the Beacon Theater", and it's available on his site. For $5. Without DRM, geographical limitations or other bullshit.
So, if you can handle his kind of jokes – it's a must have. And at this price (again: 5 USD!) it's not like your budget will collapse.
The site is https://buy.louisck.net/, and there is also interesting statement explaining some things, and telling how much did he made so far.
Apparently there are some DNS issues with their site – it the name doesn't resolve, either switch (temporarily) to 126.96.36.199 dns server, or use following information to fill your /etc/hosts:
$ host buy.louisck.net
buy.louisck.net is an alias for LiveSiteELB-1109051832.us-east-1.elb.amazonaws.com.
LiveSiteELB-1109051832.us-east-1.elb.amazonaws.com has address 188.8.131.52
LiveSiteELB-1109051832.us-east-1.elb.amazonaws.com has address 184.108.40.206
LiveSiteELB-1109051832.us-east-1.elb.amazonaws.com has address 220.127.116.11
LiveSiteELB-1109051832.us-east-1.elb.amazonaws.com has address 18.104.22.168
LiveSiteELB-1109051832.us-east-1.elb.amazonaws.com has address 22.214.171.124
LiveSiteELB-1109051832.us-east-1.elb.amazonaws.com has address 126.96.36.199
( title, in case you don't know, comes from excellent “movie" about databases )
The secret ingredient is of course sharding.
Can we do sharding in PostgreSQL? First, let's define what sharding is:
Continue reading The secret ingredient in the webscale sauce