DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
What's in store for DevOps in 2023? Hear from the experts in our "DZone 2023 Preview: DevOps Edition" on Fri, Jan 27!
Save your seat
  1. DZone
  2. Data Engineering
  3. Databases
  4. Postgres Tips for the Average and Power User

Postgres Tips for the Average and Power User

Quick and easy tips to improve your Postgres queries.

Craig Kerstiens user avatar by
Craig Kerstiens
·
Jul. 23, 19 · Tutorial
Like (12)
Save
Tweet
Share
10.79K Views

Join the DZone community and get the full member experience.

Join For Free

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 the chain started last week internally among the Azure Postgres team.

The first email was titled "Random Citus Development and psql Tips". Some of these tips are relevant if you’re working directly on the Citus codebase, others relevant as anyone that works with Postgres, and some are useful for debugging Postgres internals. While the thread is still ongoing, here are just a few of the great tips:

In psql, Tag Your Queries and Use Ctrl+R

Psql supports Ctrl+R to search for 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, two to three letters is going to be enough to find the query.

Better psql Output

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


Get a Stack Trace for an Error

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
...


Generating and Inserting Fake Data

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)


A Better pg_stat_activity

We’ve talked about pg_stat_statementsbefore, but less about pg_stat_activity. Pg_stat_activity will show you information about currently running queries. Its 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
;


Managing Multiple Versions of Postgres

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.

What Are Your Tips

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.

PostgreSQL Power user

Published at DZone with permission of Craig Kerstiens, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • How to Create a Real-Time Scalable Streaming App Using Apache NiFi, Apache Pulsar, and Apache Flink SQL
  • Top 10 Secure Coding Practices Every Developer Should Know
  • Understanding gRPC Concepts, Use Cases, and Best Practices
  • A Brief Overview of the Spring Cloud Framework

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends: