DEV Community: Jonathan Powell

How OpenAI Scales Postgres to +1M Queries Per Second

Jonathan Powell — Mon, 30 Jun 2025 21:04:32 +0000

Introduction

OpenAI is one of the companies at the forefront of the AI movement. ChatGPT is one of the most popular LLM applications, and grows everyday. OpenAI has to handle millions of queries per second, and uses Postgres for critical workloads. Their use of Postgres isn’t a surprise, Postgres is one of the most popular relational databases. What is surprising, however, is how they optimize Postgres to support their needs. Most of their optimizations are based on battle tested best practices.

In a recent talk titled “Scaling Postgres for OpenAI”, OpenAI Technical Staff shared how they scaled Postgres for their use-cases. My takeaway from the talk is that they aren’t using exotic optimizations to get the best performance. They’re monitoring their database, finding bottlenecks, and using best practices to fix issues.

Context

Here’s the context. OpenAI has a single primary database and several read replicas. At the start of their performance journey, the primary served reads and writes, while the read replicas served a subset of read queries. Over time, they had several incidents related to handling high traffic, and spent time hardening the database.

The talk has more in-depth analysis, but I want to highlight how they approach performance. Every optimization they made is in service of reducing load on their primary database as much as possible.

Techniques

1. Review Your Application for Unneeded Queries

The team profiled their application and found code paths with read queries, that didn't need to be made. Removing queries from these code paths reduced the number of queries that hit the primary database.

2. Audit ORM Queries and use raw SQL where needed

Object Relation Mapping (ORM) libraries let us interact with the database without writing a single line of SQL. We get to focus on the relationships between our data rather than how to write the correct joins. The tradeoff is that the ORM may generate complex SQL, when simpler queries suffice. The OpenAI team reviewed the generated SQL from their ORM, and replaced over-complicated queries with raw SQL where it made sense.

3. Move Read Queries to the Read Replicas

To reduce load on their primary database, the OpenAI team moved as many read queries to read replicas as possible. There is a consistency tradeoff when moving read queries to read replicas. That is, replicas may take some time to be updated with the latest changes from the primary, especially if replica lag is high. However, reducing load on the primary by an order of magnitude was an acceptable tradeoff for potential impacts to consistency.

4. Use Timeouts For Queries

Long running queries may hold onto connections and prevent other transactions from being processed. Configuring statement and transaction timeouts ensures the database won’t fail to process other queries due to a few long-running transactions. These can be applied at the application level, or in Postgres itself.

5. Use Connection Pools to optimize connection usage

The OpenAI team introduced PgBouncer, a connection pooler for Postgres, to their system to optimize connection pool usage. Connection Pooling is a common technique to efficiently manage the number of connections an application opens to a database. Most database libraries provide connection pooling at the application level. Your database library will manage the connections and re-use them as efficiently as possible. PgBouncer takes it one level lower to the database server. App Servers connect to the PgBouncer proxy just like the would a database, and PgBouncer creates or re-uses connections as efficiently as possible. Whether you choose to go with PgBouncer, or use built-in functionality from your DB library, connection pooling is always a good idea.

Conclusion

Database performance doesn't have to be complicated. Even hyper-scale AI companies lean on best practices to get the performance they're looking for.

To learn more about how OpenAI scaled their postgres instances give the talks a view here:

Full Talk (50 minutes): https://www.youtube.com/watch?v=Ni1SGhNu-Q4
Lightning Talk Version (25 minutes): https://www.youtube.com/watch?v=NvY2kvi1Fa0

Leave a comment about how you've improved database performance in your systems!

And here are some related resources about PgBouncer and Postgres Config:

PgBouncer: https://www.pgbouncer.org/usage.html
Postgres Client Configuration: https://www.postgresql.org/docs/current/runtime-config-client.html

We Have Code Quality At Home: Open Source Java Code Quality Tools

Jonathan Powell — Tue, 07 May 2024 03:13:03 +0000

Code Quality
The Tools
Spotless
SpotBugs
PMD
Putting It Together
Links

The best tool for the job isn't always the best tool you have available.

Engineering teams are often have the build vs. buy conversation. Do we want to "buy" an off-the-shelf tool to solve our problem or do we want to build it in-house? The conversation is usually for complex engineering solutions, but this time we're talking about code quality tools.

Code Quality

I think code quality is more about standardizing practices across a team than writing "clean code". Everyone has their own conceptions of best practices, so using code quality tools like linters, and static analysis tools standardize practices across teams. Even better, they can be integrated to the pull request, and CI/CD process to automate the practice of "code quality" - letting developers focus on reviewing the business logic of code changes.

You get a few benefits of integrating automated code quality tools in your projects:

Standardized (and automated) best practices
Easier onboarding of new team members coming from different tech stacks
Evangelize good practices across the codebase

The Tools

SonarQube is one of the most common off-the-shelf code quality solutions, but I'm going to highlight 3 open source java plugins for "good enough" code quality in your Java projects: Spotless, SpotBugs, and PMD

Spotless

Spotless is an open-source, multi-language, customizable code formatter for projects.
It comes with a Maven Plugin that can be customized as needed.

Here's an example of what it would look like. Spotless comes with some built-in formats. In this example, we specify we want to use Palantir's Java formatting rules:

      <plugin>
        <!-- https://github.com/diffplug/spotless -->
        <groupId>com.diffplug.spotless</groupId>
        <artifactId>spotless-maven-plugin</artifactId>
        <version>${spotless.version}</version>
        <configuration>
          <java>
            <!-- Tell Spotless to Use Palantir's Custom Java Format-->
            <palantirJavaFormat>
              <version>2.39.0</version>
              <!-- optional -->
              <style>PALANTIR</style>
              <!-- or AOSP/GOOGLE (optional) -->
              <formatJavadoc>false</formatJavadoc>
              <!-- defaults to false (optional, requires at least Palantir 2.39.0) -->
            </palantirJavaFormat>
          </java>
        </configuration>
    </plugin>

Once the plugin is added to the pom, you can check for formatting issues with:

mvn spotless:check

Or format your code with:

mvn spotless:apply

SpotBugs

SpotBugs is an open source static anlysis tool. "SpotBugs uses static analysis to inspect Java bytecode for occurrences of bug patterns." This means that SpotBugs runs against the compiled source source code, rather than raw Java files. Because it analyses bytecode, it can catch some types of bugs that source code analysis would not catch.

SpotBugs provides a maven plugin for easy usage. Here's what it would look like in a pom.xml:

      <plugin>
        <groupId>com.github.spotbugs</groupId>
        <artifactId>spotbugs-maven-plugin</artifactId>
        <version>4.8.4.0</version>
        <configuration>
          <plugins>
            <plugin>
              <groupId>com.h3xstream.findsecbugs</groupId>
              <artifactId>findsecbugs-plugin</artifactId>
              <version>1.12.0</version>
            </plugin>
          </plugins>
        </configuration>
      </plugin>

Once added, you can run SpotBugs from the command line. I've include mvn clean compile because you want to ensure you've compiled the latest version of your code.
It also comes with a nice GUI if you want an easy way to know which classes have issues.

# Generate a report for SpotBugs errors
mvn clean compile spotbugs:spotbugs

# Check for SpotBugs errors
mvn clean compile spotbugs:check

mvn clean compile spotbugs:gui

If you want to suppress a SpotBugs Warning, you can use the @SuppressFBWarnings annotation with the SpotBugs errors as a parameter(s)

@SuppressFBWarnings(value={"NM_METHOD_NAMING_CONVENTION", "NP_TOSTRING_COULD_RETURN_NULL"},justification = "This is why we ignore these FindBugs warnings.")
public class MyClassWithSpotBugsWarnings {
  // ...
}

SpotBugs will not automatically fix any errors it finds. It's up to the developer to decide if it's findings warrant code changes.

PMD

PMD is a source code static analysis tool. It inspects your Java files for any issues, and has a configurable set of rules
to look at.

PMD provides a maven plugin to easily setup rules you care about. Here's what it
would look like in a pom.xml

      <plugin>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-pmd-plugin</artifactId>
        <version>3.22.0</version>
        <!-- Link to a custom ruleset file for rules we care about -->
        <configuration>
          <rulesets>
            <ruleset>src/main/resources/pmd/custom-ruleset.xml</ruleset>
          </rulesets>
          <excludes>
            <exclude>**/test/**</exclude>
          </excludes>
        </configuration>
      </plugin>

You can find a list of availble rules on the docs site and include/exclude them as needed.

Once added to the pom.xml, you can run PMD:

# Generate a report of PMD violations
mvn pmd:pmd

# Check the code for any PMD violations
mvn pmd:check

If you want to suppress a PMD violation you can do it like so:

@SuppressWarnings({"PMD.UnusedPrivateField", "PMD.SingularField"})
public class MyClassWithPMDViolations {
    // Your code here
}

Like SpotBugs, PMD will not automatically fix issues. It's up to the developer to decide if violations warrant changes, or ignore them.

Putting it together

Together these tools give you linting and static analysis that you can customize to your needs.
If you want to see a sample of them in action, you can find a sample maven project I made, complete with fully configured pom.xml file here:

https://github.com/jpowell96/j-codequality-starter

Passing The DataCamp SQL Associate Certificate

Jonathan Powell — Wed, 10 Apr 2024 20:11:05 +0000

I just passed the DataCamp SQL Associate Exam. Here's a short list of concepts you should be comfortable with for the exam, and some notes I have about the exam itself.

Timed Quiz Portion

The timed quiz portion of the exam tests your knowledge on SQL syntax, queries, data manipulation, and general knowledge database management concepts like normalization, data modeling.

Here's a shortlist the SQL concept(s) covered. If you're familiar with the terms, or have used them at least once, you'll be able to answer most questions.

Joins: LEFT JOIN, FULL JOIN, INNER JOIN, UNION, UNION ALL
String Manipulation: UPPER, LOWER, LIKE, ILIKE, ~, REGEX, TRIM, LEFT, RIGHT
Date Manipulation: DATE_PART, DATE_TRUNC, TO_CHAR
Number Manipulation: ROUND, TRUNC
AGGREGATE FUNCTIONS: MAX, MIN, AVG, percentiles

The more general database questions may require taking the DataCamp courses because they seem to expect you to use the same language that instructors use in those courses.

Timed Practical Exam Portion

The timed practical exam is a mutli-part question where you write queries for some provided tables.

Most of the queries require simple joins, where clauses, and occasionally manipulating data such as missing values.

DataCamp provides a practical exam that's a good reference point for if you're ready. If you can complete the practice exam, you are more than prepared to complete the practical exam.

When you take the practical you're given 4 hours to complete it. If you've done the practice exam, you can complete much faster than in 4 hours.

You'll need to be able to:

Join tables together
Use subqueries and where clauses to filter out data
Manipulate data to handle missing values, or set default values for columns

Preparing for the Certification Exam

Based on the exam(s) and the courses I've taken, these DataCamp courses reflect the content that I saw on the exam:

SQL for Business Analysts
Functions for Manipulating Data in PostgresSQL
Manipulating Data in SQL
Database Design
Joining Data in SQL

If you want more SQL practice outside of DataCamp courses, I've used DataLemur, and StrataScratch to practice SQL data analysis questions.

Taking the Exam

Overall, I thought the exam felt like a survey of beginner to intermediate SQL knowledge. The timed exam has a handful of tricky questions, but overall a general understanding of SQL should allow you to a pass. The practical exam is also beginner to intermediate level SQL. If you know how to join tables, use filter clauses like WHERE and IN, manipulate text and numbers, and set default values, you can pass the exam.

Time Series Analysis on FDIC Bank Failures

Jonathan Powell — Wed, 10 Apr 2024 13:21:44 +0000

Intro
Readout
Importing The Data
Querying The Data
Analyzing the Data
Wrapping It Up

FDIC Bank Failures - Time Series Analysis

My latest endeavor with SQL is working with timeseries data.
For this mini-analysis, I decided to chart out US bank failures overtime.

I found an official dataset of FDIC Bank Failures on data.gov, which has public
datasets from the US governemnt. It lists all the banks that have failed since 2000.

Readout

Over the time period from 2000 to 2023, there were 568 banks that closed. Georiga, Florida, and Illinois had the most closers over the time period with 93, 76, and 69 closures respectively. On a year basis, Florida had the most closures in a single year with 29 banks closing in 2010.

As expected, bank closures trend upwards between 2008 - 2012, peaking with 157 closures in 2010. The year over year changes highlight this explosion of failures.

From 2007 to 2008, the number of bank closures grew over 8x from 3 closures to 25 closures, and grew even more the following year - from 25 closures in 2008 to 140 in 2009 - a 460% increase. Closures in a single year peaked in 2010 with 157 closures with Florida having the most closures with 29 bank clousres. July 2009 was the worst month for closures, with 29 closings in a single month.

Importing the Data

To import the data into my Postgres Database I did the following steps

Create a table to represent the rows in the CSV file

CREATE TABLE bank_failures (
id bigserial,
bank_name TEXT,
city TEXT,
state TEXT,
cert TEXT,
acquiring_institution TEXT,
closing_date DATE,
fund BIGINT
);

Copy the data into the table from the csv file, using the COPY command

COPY bank_failures (
bank_name,
city,
state,
cert,
acquiring_institution,
closing_date,
fund
) FROM '/path/to/banklist.csv' WITH (FORMAT csv, DELIMITER ',', QUOTE '"', ESCAPE '"', ENCODING 'UTF-8');

I originally set the ENCODING to ASCII, but after running into some issues realized I needed to set the encoding to 'UTF-8'.
Now, with the correct character encoding,
I imported my data for real but got this error message:

ERROR: invalid byte sequence for encoding "UTF8": 0x96

After some digging online, I realized this meant there was a non UTF-8 character in the csv file. There was a hyphen character '-' for one of the banks was a special character. I replaced it with a "regular" hyphen - character and that allowed me to import all the data.

Once imported, the data looked like this:

id	bank_name	city	state	cert	acquiring_institution	closing_date	fund
40	Citizens Bank	Sac City	IA	8758	Iowa Trust & Savings Bank	2023-11-03	10545
41	Heartland Tri-State Bank	Elkhart	KS	25851	Dream First Bank, N.A.	2023-07-28	10544
42	First Republic Bank	San Francisco	CA	59017	JPMorgan Chase Bank, N.A.	2023-05-01	10543
43	Signature Bank	New York	NY	57053	Flagstar Bank, N.A.	2023-03-12	10540
44	Silicon Valley Bank	Santa Clara	CA	24735	First-Citizens Bank & Trust Company	2023-03-10	10539

Querying the data

My goal was to run a few simple queries to get the number of bank failures by year,
and then another query to get all the bank failures by month.

I started with a query to find all the months/years that banks had failures

-- Return bank failures by month, year based on the dataset
 SELECT
  EXTRACT('YEAR' FROM closing_date :: DATE) as closing_year, 
  EXTRACT('MONTH' FROM closing_date :: DATE) as closing_month, 
  COUNT(*) as total_failures  
FROM bank_failures 
GROUP BY closing_year, closing_month
ORDER BY closing_year, closing_month;

To my surprise, there were some months missing from this query. There wasn't a row for months from January - October, 2000.

It may seem odd, but this is the expected output. If there weren't any failures for a given month in a year, there wouldn't be rows. If I want there to be rows for every month from 2000 - 2023, I need to have date for those months too.

To accomplish this I made a table called timescale, that contains all the months, years from January, 2000 - December 2023. My initial attempt was overcomplicated, but I realized PostgreSQL provides a helpful function, generate_series() That lets me generate a series of rows between to dates:

-- Generate a row for every month between Jan, 2000 and Dec, 2023
SELECT generate_series('2000-01-01', '2023-12-01','1 month'::interval) as timescale;

-- Generate a row for every year between Jan, 2000 and Dec, 2023
SELECT generate_series('2000-01-01', '2023-12-01','1 year'::interval) as timescale;

This code will return a result set containing a row for every month from January 2000 until December 2023.

With this table in mind, we can redo our original "Failures per Month" query and JOIN the timescale onto our bank_failures table:

-- Return bank failures by month, year based on the dataset
with series as (SELECT
generate_series('2000-01-01', '2023-12-01','1 month'::interval) as timescale)
SELECT
  EXTRACT('YEAR' FROM timescale) as closing_year, 
  EXTRACT('MONTH' FROM timescale) as closing_month, 
  COUNT(bank_failures.closing_date) as total_failures  
-- the series table has complete dates, so join bank_failures onto the series for complete data for the complete time period
FROM series LEFT JOIN bank_failures 
on EXTRACT('YEAR' from timescale) = EXTRACT('YEAR' from closing_date :: DATE)
and EXTRACT('MONTH' from timescale) = EXTRACT('MONTH' from closing_date :: DATE)
GROUP BY closing_year, closing_month
ORDER BY closing_year, closing_month;

Important Notes:

The bank_failures table is left joined onto the timescale. This is done because the timescale includes every month, year while the bank_failures table only includes records for month/years where a bank failed.
We do a COUNT(bank_failures.closing_date) rather than a COUNT(*)
- A left join will return a record for each row on the timescale table. COUNT(*) would return 1 failure, because it counts rows including NULLs. COUNT(bank_failures.closing_date) ensures that we only count cases where there were bank failure in a given month.

Likewise, if we wanted to upsample the data to yearly buckets, the query would look like this:

with series as (SELECT
generate_series('2000-01-01', '2023-12-01','1 year'::interval) as timescale)
SELECT
  EXTRACT('YEAR' FROM timescale) as closing_year, 
  COUNT(bank_failures.closing_date) as total_failures  
FROM series LEFT JOIN bank_failures 
on EXTRACT('YEAR' from timescale) = EXTRACT('YEAR' from closing_date :: DATE)
GROUP BY closing_year
ORDER BY closing_year;

We've updated the generate_series() function to use an interval of 1 year rather than 1 month, and
updated our JOIN condition to be based on matching years.

Analyzing the Data

I saved the queries as materialized views and ran a few queries on the data. I used a materialized view because I know that the data is for a fixed time period and will not need to be refreshed. A temp table, or even writing to an actual table would work as well.

create materialized view bank_failures_by_month as (
with series as (SELECT
generate_series('2000-01-01', '2023-12-01','1 month'::interval) as timescale)
SELECT
  EXTRACT('YEAR' FROM timescale) as closing_year, 
  EXTRACT('MONTH' FROM timescale) as closing_month, 
  COUNT(bank_failures.closing_date) as total_failures  
FROM series LEFT JOIN bank_failures 
on EXTRACT('YEAR' from timescale) = EXTRACT('YEAR' from closing_date :: DATE)
and EXTRACT('MONTH' from timescale) = EXTRACT('MONTH' from closing_date :: DATE)
GROUP BY closing_year, closing_month
ORDER BY closing_year, closing_month);

Here are some of the queries I ran on the dataset, and materialized view:

How many bank closed over the entire time period:

select count(*) from bank_failures bf ;

Which states had the most bank failures over the complete time period?

select 
    state, 
    COUNT(*) as bank_failures
from bank_failures bf 
group by state
order by COUNT(*) desc
limit 10;

state	bank_failures
GA	93
FL	76
IL	69
CA	43
MN	23
WA	19
MO	16
AZ	16
MI	14
TX	13

Which states had the most closures over the time period?

select 
    state, 
    extract('YEAR' from closing_date) :: TEXT as closing_year, 
    COUNT(*) as state_failures_by_year
from bank_failures bf 
group by state, extract('YEAR' from closing_date) 
order by COUNT(*) desc
limit 10;

state	closing_year	state_failures_by_year
FL	2010	29
GA	2009	25
GA	2011	23
IL	2009	21
GA	2010	21
CA	2009	17
IL	2010	16
FL	2009	14
FL	2011	13
CA	2010	12

For each year, which state(s) had the most bank failures? Include ties.

with ranked_state_failures_by_year as (
    select 
     extract('YEAR' from closing_date) as closing_year,
     state,
     COUNT(*) as state_failures_by_year,
     -- Window functions are evaluated AFTER the group by clause, so this is comparing the bank failures per state
     dense_rank() over (partition by extract('YEAR' from closing_date) order by  COUNT(*) desc) 
    from bank_failures bf 
    group by extract('YEAR' from closing_date), state
    order by extract('YEAR' from closing_date), COUNT(*) desc),
all_years as (
    select generate_series('01-01-2000', '01-01-2023', '1 year'::interval) as timescale
)
select 
    extract('YEAR' from all_years.timescale) as closing_year, 
    coalesce(state, 'N/A') as state, 
    coalesce(state_failures_by_year, 0) as state_failures_by_year
from 
all_years left JOIN
ranked_state_failures_by_year
on  extract('YEAR' from all_years.timescale) = ranked_state_failures_by_year.closing_year 
-- Some years do not have bank failures, so we include a null check to ensure they appear in the result set
where dense_rank = 1 or dense_rank is null
order by all_years.timescale;

closing_year	state	state_failures_by_year
2000	HI	1
2000	IL	1
2001	AR	1
2001	OH	1
2001	IL	1
2001	NH	1
2002	FL	2
2003	WI	1

Which years had the most bank failures?

select 
    RANK() over (order by sum(total_failures) desc),
    closing_year::int, 
    SUM(total_failures) as total_failures
from bank_failures_by_month 
group by closing_year
order by SUM(total_failures) desc
limit 5;

rank	closing_year	total_failures
1	2010	157
2	2009	140
3	2011	92
4	2012	51
5	2008	25

Which month/year had the most bank failures?

select 
    RANK() over (order by total_failures desc),
    closing_year :: INT, 
    closing_month,
    total_failures
from bank_failures_by_month 
order by total_failures desc
limit 5;

rank	closing_year	closing_month	total_failures
1	2009	7	24
2	2010	4	23
3	2010	7	22
4	2009	10	20
5	2010	3	19

What was percent change each year?*

with bank_failures_by_year as (
-- Return a table with the year, total_failures, and toal failures in the previous year, defaulting to 0 if not data is found
SELECT
  EXTRACT('YEAR' FROM timescale) as closing_year, 
  COUNT(bank_failures.closing_date) as total_failures,
  coalesce(LAG(COUNT(bank_failures.closing_date)) over (order by EXTRACT('YEAR' FROM timescale)),0) as prev_year_failures
FROM (SELECT
generate_series('2000-01-01', '2023-12-01','1 year'::interval) as timescale) as series LEFT JOIN bank_failures 
-- Join the series of years with the bank failures table based on matching years
on EXTRACT('YEAR' from timescale) = EXTRACT('YEAR' from closing_date)
GROUP BY closing_year
ORDER BY closing_year)
-- Calculate the year over year change between each year of bank failures
select closing_year, total_failures, 
    case 
        when closing_year = 2000 then 'N/A'
        when prev_year_failures = total_failures  then '0%'
        when prev_year_failures  = 0 then 'N/A'
        else ROUND(((total_failures - prev_year_failures) :: NUMERIC / prev_year_failures) * 100.0, 2) || '%' 
    end as yoy_pct_change
from bank_failures_by_year;

closing_year	total_failures	yoy_pct_change
2006.0	0	0%
2007.0	3	N/A
2008.0	25	733.33%
2009.0	140	460.00%
2010.0	157	12.14%
2011.0	92	-41.40%
2012.0	51	-44.57%
2013.0	24	-52.94%

Wrapping it up

For time series data that has gaps, the generate_series() function can enable
analyzing the data with a complete timeline. And, if needed, can support different
levels of granularity (day, week, month, etc.) to match the granularity of your date.

Improvements

I made use of the EXTRACT function often in my queries. It might make sense to just have columns for month, year since I was using extract so often.
Each row has an acquiring institution. It would be interesting to revisit
the dataset and use some recursive queries to uncover chains of acquisitions.
Normalizing the some columns such as state, acquiring institution, institution could make it easier to run queries related to who acquired which banks. FDIC has additional datasets that assign a unique id to institutions. An enhancement to this project would be to import all FDIC institutions and use that as a reference to normalize data for institutions in the bank failures dataset.

Helpful Links

Connecting with your Database with the Information Schema

Jonathan Powell — Mon, 19 Feb 2024 15:17:30 +0000

Intro
The Information Schema
System Views
System Catalog
Putting It All Together

Intro

If you've worked with SQL before, you're familiar with writing queries to ask questions about the data in your database. Give me all the people that user A follows. Tell me how many sales we made in the last week.

Databases are good for asking questions about your data, but they also store metadata about the data in your database. PostgreSQL in particular stores data about Table names, schemas, indexes, views and much more. And it's just a few sql queries away from you.

Querying Metadata in PostgreSQL

PostgreSQL stores metadata for the database in 3 areas: The Information Schema, System Views, and System Catalogs.

The Information Schema

Postgres' Information Schema "consists of a set of views that contain information about the objects defined in the current database." A database schema is essentially a way to put tables into their own group. By default, any tables you create in Postgres are part of a database schema called "public".

Though you'll typically query your tables like:

SELECT * FROM my_table LIMIT 10;

You can also include the schema as part of your query like so:

SELECT * FROM public.my_table LIMIT 10;

The information schema, as its name suggests, is a schema in your Postgres Database with tables that store data about the data in your database. You can write queries against the tables in this schema to learn more about your database(s).

You can find the full list of tables in the information schema in the Postgres Docs, but I'm sharing a few that are handy.

-- Find all the schemas in your database
select 
distinct table_schema 
from information_schema.tables;

-- Find all the tables in your schema
select table_name 
from information_schema.tables 
where table_schema = 'public';

-- Information about the columns in a table
select 
  column_name,
  data_type, 
  column_default,
  is_nullable 
from information_schema.columns 
where table_name = 'sales';

-- Name of all the constraints for a given table
select * 
from information_schema.constraint_column_usage 
where table_schema ='public' 
and table_name ='sales';

System Views

Postgres System Views are a collection of views that also have metadata. These tables are listed as system views. All of them are part of the pg_catalog schema.

-- Find indexes in a schema
select 
tablename,
indexname,
indexdef FROM pg_catalog.pg_indexes where schemaname = 'public';

-- Find indexes for a table
select 
tablename,
indexname,
indexdef 
FROM pg_catalog.pg_indexes 
where schemaname = 'public' AND tablename='mytable';

You can also list materialized views in your database with:

select 
schemaname,
matviewname,
ispopulated,
definition
FROM pg_matviews 
where schemaname='public';

System Catalogs

"The system catalogs are the place where a relational database management system stores schema metadata, such as information about tables and columns, and internal bookkeeping information."

Like Systems Views, the system catalog tables are also part of the pg_catalog schema. This schema, just like the information_schema exists by default in your Postgres database.

If I want some estimates about the size of a table, the System Catalog has a table, pg_class that lets me inspect the table size and other attributes:

select 
relname, 
relpages, 
reltuples 
FROM pg_catalog.pg_class 
WHERE relname='sales';

Output:

    relname    | relpages | reltuples 
---------------+----------+-----------
     sales     |        8 |       568

Or, with some extra functions, you can calculate the size of your tables and indexes*:

SELECT relname, pg_relation_size(oid) as bytes, 
          pg_size_pretty(pg_relation_size(oid)) 
     FROM pg_class 
    WHERE relnamespace = 'public'::regnamespace 
    ORDER BY relname;

*Query from Designing high-performance time series data tables on Amazon RDS for PostgreSQL

   relname    |  bytes  | pg_size_pretty 
--------------+---------+----------------
 sales        | 1638400 | 1600 kB
 sales_id_seq |    8192 | 8192 bytes

Putting It All Together

Queries can be really powerful when you join tables across these schemas.
For example, you can list all of the constraints of a table. Here's a query
that will give you all of the constraints for a given table in your database.

select table_name, column_name, constraint_name, 
case 
    when pgcc.contype = 'p' then 'Primary Key Constraint'
    when pgcc.contype = 'f' then 'Foreign Key Constraint'
    when pgcc.contype = 'c' then 'Check Constraint'
end as constraint_type
, pg_get_constraintdef(pgcc."oid" , true) as constraint_definition 
from information_schema.constraint_column_usage iccu
inner join  pg_catalog.pg_constraint pgcc on iccu.constraint_name = pgcc.conname
where table_name = 'parent';

Output:

 table_name | column_name |       constraint_name        |    constraint_type     |             constraint_definition             
------------+-------------+------------------------------+------------------------+-----------------------------------------------
 parent     | id          | parent_pkey                  | Primary Key Constraint | PRIMARY KEY (id)
 parent     | id          | child_parent_id_fkey         | Foreign Key Constraint | FOREIGN KEY (parent_id) REFERENCES parent(id)
 parent     | parent_name | name_less_than_20_characters | Check Constraint       | CHECK (length(parent_name::text) <= 20)

If you want to find more information about the Information Schema or other Postgres System Tables you can find them here:
Information Schema
System Catalogs
System Views

Analyzing Retail Sales Data in SQL

Jonathan Powell — Wed, 24 Jan 2024 01:37:32 +0000

Intro

This year one of my goals was to improve my data analysis skills. As a software engineer, most of time is spent in application level code. Though I work with relational databases, I really don't work with complex queries beyond a few joins. But, data analysis is a common part of our job. If you've ever been on-call, you'v'e probably had to query your database to understand the state of your sysem, who is impacted, when did an issue start?

To improve my data analysis skills I got a subscription to DataCamp, a popular online course platform focused on data analytics, data engineering, and AI. I've completed a number of courses, and now I'm testing out my skills by doing what's called an Exploratory Data Analysis (EDA).

Exploratory Data Analysis

Overview
Findings
Exploratory Queries
Preparing the Data

Overview

I grabbed this Superstore Sales Dataset from Kaggle as a CSV file.

It has 13 columns that include sales data for a generic superstore:

Ship Mode: Mode of shipping used for shipment delivery
Segment: (Categorical) Customer segment product was shipped to
Country: Country in which the shipment was delivered
City: City in which shipment was delivered
State: State in which the shipment was delivered
Postal Code: Postal code the shipment was delivered to
Region: Country region
Category: The category product belongs to
Sub-Category: Sub-category of the product
Sales: Sale made in USD
Quantity: Product quantity
Discount: Discount given on the product
Profit: Profit/loss made on the sale

Given this sales data, I asked a few questions to learn more.
I used SQL to parse the CSV and load it into a table I created and I asked a few questions about the data.

Findings

Here were some of my findings from querying the dataset.

Overall profit of the dataset was $286,397.02 dollars. California, New York, and Washington State contributed most to the overall profits.

When organized by region the West had the most profit, followed by the East, South, and finally Central regions.

Though capital cities tended to be the highest profit cities for states, some states had non-capital cities as their highest profit cities. For example, Lebanon, Tennessee was the highest profit city in the state.

Technology was the most profitable product category among the three categories (Tech, Furniture, Office Supplies) but the Office Supplies categoryhad the most sales (~6200 sales).

Though total technology sales were lower, Tech products had the highest profit per unit. Corporate Copiers had the highest profit per unit with ~ $271 dollars of profit per copier. By comparison, the highest profit per unit for Office Supplies was ~$14, for Appliances.

Across most states (48), products in the Home Office segment had the most sales. Binders and Paper sold the most units of Home Office items with 1,111 binders sold and 1,021 units of Paper sold.

Exploratory Queries

How many sales were made in the dataset?

SELECT COUNT(*) FROM sales; -- 9994

How many total items were sold?

SELECT SUM(quantity) FROM sales; -- 37873

What was the total profit overall? And, what were the top 10 highest profit states?

WITH aggregate_profits AS (
SELECT 
    CASE 
        WHEN country IS NOT NULL THEN country
        ELSE 'Overall'
    END as country,
    state,
    SUM(profit) as total_profit 
FROM sales 
GROUP BY GROUPING SETS((country, state), ()) 
ORDER BY country, state)

SELECT country, state, total_profit 
FROM aggregate_profits
ORDER BY total_profit DESC LIMIT 10;

    country    |   state    | total_profit 
---------------+------------+--------------
 Overall       |            |  286397.0217
 United States | California |   76381.3871
 United States | New York   |   74038.5486
 United States | Washington |   33402.6517
 United States | Michigan   |   24463.1876
 United States | Virginia   |   18597.9504
 United States | Indiana    |   18382.9363
 United States | Georgia    |   16250.0433
 United States | Kentucky   |   11199.6966
 United States | Minnesota  |   10823.1874

Which city had the most profit in each state?

WITH ranked_profits_by_state_city AS (
    select state, city, sum(profit) as profit, 
    RANK() OVER (PARTITION BY state ORDER BY sum(profit) DESC) as rank  
    FROM sales GROUP BY state, city ORDER BY state, rank)

SELECT state, city, profit FROM 
ranked_profits_by_state_city
WHERE rank = 1;

        state         |      city       |   profit   
----------------------+-----------------+------------
 Alabama              | Mobile          |  2175.8292
 Arizona              | Glendale        |   182.8598
 Arkansas             | Fayetteville    |  1691.9419
 California           | Los Angeles     | 30440.7579
 Colorado             | Thornton        |   140.8398
 Connecticut          | Fairfield       |  1221.6226
 Delaware             | Newark          |  8086.1715
 District of Columbia | Washington      |  1059.5893

What were total sales and profit by catagory, and subcategory?

SELECT category, 
sub_category, 
COUNT(*) as total_sales,
SUM(profit) as profit
FROM sales 
GROUP BY ROLLUP(category, sub_category)
ORDER BY category ASC NULLS FIRST, sub_category ASC NULLS FIRST;

    category     | sub_category | total_sales |   profit    
-----------------+--------------+-------------+-------------
                 |              |        9994 | 286397.0217
 Furniture       |              |        2121 |  18451.2728
 Furniture       | Bookcases    |         228 |  -3472.5560
 Furniture       | Chairs       |         617 |  26590.1663
 Furniture       | Furnishings  |         957 |  13059.1436
 Furniture       | Tables       |         319 | -17725.4811
 Office Supplies |              |        6026 | 122490.8008
 Office Supplies | Appliances   |         466 |  18138.0054
 Office Supplies | Art          |         796 |   6527.7870
 Office Supplies | Binders      |        1523 |  30221.7633
 Office Supplies | Envelopes    |         254 |   6964.1767
 Office Supplies | Fasteners    |         217 |    949.5182
 Office Supplies | Labels       |         364 |   5546.2540
 Office Supplies | Paper        |        1370 |  34053.5693
 Office Supplies | Storage      |         846 |  21278.8264
 Office Supplies | Supplies     |         190 |  -1189.0995
 Technology      |              |        1847 | 145454.9481
 Technology      | Accessories  |         775 |  41936.6357
 Technology      | Copiers      |          68 |  55617.8249
 Technology      | Machines     |         115 |   3384.7569
 Technology      | Phones       |         889 |  44515.7306

What were sales and profit by region and category?

SELECT 
region,
category, 
COUNT(*) as total_sales,
SUM(profit) as total_profit,
FROM sales GROUP BY ROLLUP(region, category)
ORDER BY region NULLS FIRST, category NULLS FIRST;

 region  |    category     | total_sales | total_profit 
---------+-----------------+-------------+--------------
         |                 |        9994 |  286397.0217
 Central |                 |        2323 |   39706.3625
 Central | Furniture       |         481 |   -2871.0494
 Central | Office Supplies |        1422 |    8879.9799
 Central | Technology      |         420 |   33697.4320
 East    |                 |        2848 |   91522.7800
 East    | Furniture       |         601 |    3046.1658
 East    | Office Supplies |        1712 |   41014.5791
 East    | Technology      |         535 |   47462.0351
 South   |                 |        1620 |   46749.4303
 South   | Furniture       |         332 |    6771.2061
 South   | Office Supplies |         995 |   19986.3928
 South   | Technology      |         293 |   19991.8314
 West    |                 |        3203 |  108418.4489
 West    | Furniture       |         707 |   11504.9503
 West    | Office Supplies |        1897 |   52609.8490
 West    | Technology      |         599 |   44303.6496

What were sales, and profits by segment, cateory, and subcategory

 SELECT 
        segment, 
        category, 
        sub_category, 
        ROUND(SUM(profit), 2) AS total_profit,
        SUM(quantity) AS units_sold, 
        ROUND(SUM(profit) / SUM(quantity) :: NUMERIC, 2)  AS profit_per_unit 
FROM sales 
GROUP BY segment, category, sub_category 
ORDER BY profit_per_unit  DESC;

   segment   |    category     | sub_category | total_profit | units_sold | profit_per_unit 
-------------+-----------------+--------------+--------------+------------+-----------------
 Consumer    | Technology      | Copiers      |     24083.71 |        117 |          205.84
 Consumer    | Technology      | Phones       |     23837.11 |       1685 |           14.15
 Consumer    | Technology      | Accessories  |     20735.92 |       1578 |           13.14
 Consumer    | Furniture       | Chairs       |     13235.33 |       1234 |           10.73
 Consumer    | Technology      | Machines     |      2141.06 |        217 |            9.87
 Consumer    | Office Supplies | Appliances   |      6981.93 |        908 |            7.69
 Consumer    | Office Supplies | Envelopes    |      3264.41 |        442 |            7.39
 Consumer    | Office Supplies | Binders      |     17995.60 |       3015 |            5.97
 Consumer    | Office Supplies | Paper        |     15534.64 |       2602 |            5.97
 Consumer    | Office Supplies | Storage      |      7104.20 |       1619 |            4.39
 Consumer    | Furniture       | Furnishings  |      7919.42 |       1834 |            4.32
 Consumer    | Office Supplies | Labels       |      3075.99 |        715 |            4.30

What are the most popular product segments by quantity sold?

SELECT 
    state, 
    segment, 
    RANK() OVER (PARTITION BY state ORDER BY COUNT(quantity)) as popularity 
FROM sales GROUP BY state, segment;

        state         |   segment   | popularity 
----------------------+-------------+------------
 Alabama              | Home Office |          1
 Alabama              | Consumer    |          2
 Alabama              | Corporate   |          3
 Arizona              | Home Office |          1
 Arizona              | Corporate   |          2
 Arizona              | Consumer    |          3

Preparing the Data

Using the psql cli, I create the database

psql -- Launch Postgres CLI
CREATE DATABASE sales_db;
\c sales_db -- Connect to the newly created database

Create the database table for the data in the csv

CREATE TABLE sales (
id bigserial,
ship_mode TEXT,
segment TEXT,
country TEXT,
city TEXT,
state TEXT,
postal_code TEXT, 
region TEXT,
category TEXT,
sub_category TEXT,
sales BIGINT,
quantity BIGINT,
discount FLOAT,
profit NUMERIC);

Load the csv file intot the table

Note: I removed the header row in the csv before copying it.

COPY sales(ship_mode,
segment,
country,
city,
state,
postal_code, 
region,
category,
sub_category,
sales,
quantity,
discount,
profit)
FROM '/path/to/SampleSuperstore.csv' (DELIMITER ',', FORMAT csv, HEADER false);

This command failed because the sales column in the csv file actually a numeric type, not a bigint. I changed the column to a numeric type to allow the data to be imported.

ALTER TABLE sales ALTER COLUMN sales TYPE NUMERIC;

With the correct types I could now run the COPY command and populate the
database table.

Retrieving Files with the 'fetch' API

Jonathan Powell — Sun, 24 May 2020 16:34:05 +0000

Javascript's Fetch API is usually used to get data from an API. But it can also be used to retrieve files!

Fetch a .txt file

The Response object that is returned from 'fetch', has a few methods that let you retrieve the data returned from the request

.json(): returns json
.text(): returns a string of all the text in the response

We use the .text() method to get a string of the text from a file.

fetch('example.txt')
.then(response => response.text()) 
.then(textString => {
    console.log(textString);
});

The process is identical if we wanted to retrieve a .csv file and do something with the data that's in the file. But we have some extra code to break up the file into rows.

fetch('example.csv')
.then(response => response.text()) 
.then(csvString => {
    //Split the csv into rows
    const rows = csvString.split('\n');
    for (row of rows) {
    //Split the row into each of the comma separated values
        console.log(row.split(","));
    }
});

Look at this GitHub repo for example code:
https://github.com/jpowell96/readFilesWithFetch

Depth First Search Javascript.

Jonathan Powell — Tue, 23 Jul 2019 20:59:43 +0000

Depth-First Search is another one of the algos that can come up on interview questions. Here it is in Javascript.

The awesome thing about DFS and BFS is that there's just a one line change to go from a DFS to a BFS - and vice versa!

Writing a simple version of Breadth First Search in Javascript

Jonathan Powell — Tue, 23 Jul 2019 20:57:39 +0000

Breadth First Search is one of those top 10 algorithms to know for an interview. Here it is in javascript. This code works if you're using an adjacency list to represent your graph.

DEV Community: Jonathan Powell

How OpenAI Scales Postgres to +1M Queries Per Second

Introduction

Context

Techniques

1. Review Your Application for Unneeded Queries

2. Audit ORM Queries and use raw SQL where needed

3. Move Read Queries to the Read Replicas

4. Use Timeouts For Queries

5. Use Connection Pools to optimize connection usage

Conclusion

We Have Code Quality At Home: Open Source Java Code Quality Tools

Table Of Contents

Code Quality

The Tools

Spotless

SpotBugs

PMD

Putting it together

Links

Passing The DataCamp SQL Associate Certificate

Timed Quiz Portion

Timed Practical Exam Portion

Preparing for the Certification Exam

Taking the Exam

Time Series Analysis on FDIC Bank Failures

Table Of Contents

FDIC Bank Failures - Time Series Analysis

Readout

Importing the Data

Querying the data

Analyzing the Data

Wrapping it up

Improvements

Helpful Links

Connecting with your Database with the Information Schema

Table Of Contents

Intro

Querying Metadata in PostgreSQL

The Information Schema

System Views

System Catalogs

Putting It All Together

Analyzing Retail Sales Data in SQL

Intro

Exploratory Data Analysis

Table Of Contents

Overview

Findings

Exploratory Queries

Preparing the Data

Retrieving Files with the 'fetch' API

Fetch a .txt file

Depth First Search Javascript.

Writing a simple version of Breadth First Search in Javascript