Citus 13.0 is out! Now with PG17 Support. Read all about it in Naisila’s 13.0 blog post 💥
Citus 13.0 is out! Now with PG17 Support. Read all about it in Naisila’s 13.0 blog post 💥
Written by Craig Kerstiens
July 17, 2019
Personally I'm a big fan of email, just like blogging. To me a good email thread can be like a good novel where you're following along always curious for what comes next. And no, I don't mean the ones where there is an email to all-employees@company.com and someone replies all, to only receive reply-all's to not reply-all. I mean ones like started last week internally among the Azure Postgres team.
The first email was titled: Random Citus development and psql tips
, and from there it piled on to be more and more tips and power user suggestions for Postgres. Some of these tips are relevant if you're working directly on the Citus open source code, others relevant as anyone that works with Postgres, and some useful for debugging Postgres internals. While the thread is still ongoing here is just a few of the great tips:
Psql supports Ctrl+R to search previous queries you ran. For demos and when testing complex scenarios, I like adding a little comment to queries that then becomes the tag by which I can later find the query:
# SELECT count(*) FROM test; -- full count
┌───────┐
│ count │
├───────┤
│ 0 │
└───────┘
(1 row)
Time: 127.124 ms
(reverse-i-search)`f': SELECT count(*) FROM test; -- full count
In most cases, 2-3 letters is going to be enough to find the query.
I find \x lacking, but pspg is great. It is available from PGDG via sudo yum install -y pspg or the equivalent on your system. I have the following .psqlrc which sets up pspg with a very minimalistic configuration:
$ cat > ~/.psqlrc
\timing on
\pset linestyle unicode
\pset border 2
\setenv PAGER 'pspg --no-mouse -bX --no-commandbar --no-topbar'
\set HISTSIZE 100000
In psql:
# SELECT pg_backend_pid();
┌────────────────┐
│ pg_backend_pid │
├────────────────┤
│ 156796 │
└────────────────┘
(1 row)
In another shell:
$ gdb -p 156796
(gdb) b errfinish
Breakpoint 1 at 0x83475b: file elog.c, line 251.
(gdb) c
Continuing.
Back in psql:
# SELECT 1/0;
Back in gdb:
Breakpoint 1, errfinish (dummy=0) at elog.c:414
414 {
(gdb) bt
#0 errfinish (dummy=0) at elog.c:414
#1 0x00000000007890f3 in int4div (fcinfo=<optimized out>) at int.c:818
#2 0x00000000005f543c in ExecInterpExpr (state=0x1608000, econtext=0x1608900, isnull=0x7ffd27d1ad7f) at execExprInterp.c:678
...
I know there are a lot of ways to generate fake data, but if you want something simple and quick you can do it directly in SQL:
CREATE TABLE some_table (id bigserial PRIMARY KEY, a float);
SELECT create_distributed_table('some_table', 'id');
INSERT INTO some_table (a) SELECT random() * 100000 FROM generate_series(1, 1000000) i; -- 2 secs (40MB)
INSERT INTO some_table (a) SELECT random() * 100000 FROM generate_series(1, 10000000) i; -- 20 secs (400MB)
INSERT INTO some_table (a) SELECT random() * 100000 FROM generate_series(1, 100000000) i; -- 300 secs (4GB)
INSERT INTO some_table (a) SELECT random() * 100000 FROM generate_series(1, 1000000000) i; -- 40 mins (40GB)
We've talked about pg_stat_statements
before, but less about pg_stat_activity
. Pg_stat_activity
will show you information about currently running queries. Though it's default view gets improved with the following query, and is easy to tweak as well:
SELECT
pid,
-- procpid,
-- usename,
substring(query, 0, 100) as query,
query_start,
-- backend_start,
backend_type,
state
FROM
pg_stat_activity
--WHERE
-- state='active'
ORDER BY
query_start ASC
;
For many people one version of Postgres is enough, but if you're working with different versions in production it can be useful to have them locally as well for dev/prod parity. pgenv is a tool that helps you manage and easily swap between Postgres versions.
As our email thread goes on we may create another post on useful tips, but I'd also love to hear from you. Have some useful tips for working with Postgres, let us hear them and help share with others @citusdata.
Big thanks to Onder, Marco, Furkan on the team for contributing tips to the thread