Python script for importing .csv to_sql with Pandas fails unless I DROP TRIGGER for updating materialized viewb

I have a Postgres database with a table with a materialized view that automatically updates every time changes are made to the table via the following user-defined function and trigger:

I have a Postgres database with a table with a materialized view that automatically updates every time changes are made to the table via the following user-defined function and trigger:

create function refresh_matview_dohscrapemat()
returns trigger language plpgsql
as $$
begin
    refresh materialized view dohscrape;
    return null;
end $$;
create trigger refresh_matview_dohscrapemat
after insert or update or delete or truncate
on dohscrape for each statement
execute procedure refresh_matview_dohscrapemat();

I have a python script that uses pandas to automatically import .csv files into this table that only works if I DROP the TRIGGER for updating the materialized view before running it.

If I try to run the script without dropping the trigger I get the following error:

sqlalchemy.exc.NotSupportedError: (psycopg2.NotSupportedError) "dohscrape" is not a materialized view CONTEXT: SQL statement "refresh materialized view dohscrape" PL/pgSQL function refresh_matview_dohscrapemat() line 3 at SQL statement [SQL: 'INSERT INTO dohscrape (filename, content) VALUES (%(filename)s, %(content)s)'] [parameters: ({'filename':...(Background on this error at: http://sqlalche.me/e/tw8g)

It says "dohscrape" is not a materialized view, which is correct because that's the name of the table not the materialized view. The materialized view is named "dohscrapemat".

The link in the error message points to the following info:

NotSupportedError Exception raised in case a method or database API was used which is not supported by the database, e.g. requesting a .rollback() on a connection that does not support transaction or has transactions turned off.
This error is a DBAPI Error and originates from the database driver (DBAPI), not SQLAlchemy itself.

But I don't see how this would relate to an error caused by/related to a trigger for updating a materialized view, which I assume is the root issue because dropping it resolves the error.

Once I let the script upload to the table and recreate the trigger it all works fine, but I'd like to be able to run this script without having to drop and recreate the trigger.

Why is the trigger for refreshing the materialized view causing an import error? And why is pandas/sqlalchemy/psycopg2 confuse my table with its materialized view?

The snippet of code in the python script that uploads to the db is:

for files in source:
    if files.endswith(".csv"):
        df = pd.read_csv(os.path.join(sourcepath,files))
        df.to_sql(name='dohscrape',con=dbconn,if_exists='append',index=False)

I'm using Python 3.7 and Postgres 11.

Using PostgreSQL Database with Python

Using PostgreSQL Database with Python

In this article we will see how to connect to PostgreSQL from Python Script and perform queries.

In this article we will see how to connect to PostgreSQL from Python Script and perform queries.

PostgreSQL is an open source object-relational database management system. PostgreSQL is ACID-compliant and is transactional. It has triggers, foreign keys and supports functions and stored procedures.

PostgreSQL is used by giants like Uber, Apple, Netflix and Instagram.

Requirement:

Create a virtual environment using python 3 and activate it. Install below packages.

psycopg2==2.7.3.2

Installation:

Install the PostgreSQL database and utilities using below commands.

sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

By default, PostgreSQL sets up the user and database “postgres” upon a new installation. We need to switch to this user to use postgres database.

sudo -su postgres

Now go to the Postgres prompt by typing psql on terminal.

We are using version 10.3.

If you get any error in connecting to database, make sure PostgreSQL is running. Check the status using below command.

$ systemctl status postgresql

You can check for errors in logs using below command.

$ tail -f /var/log/postgresql

Creating database:

Before creating a new database, lets list all the databases. Use \l or \list for the same.

To create database, exit the psql terminal by typing \q and use command createdb testdb.

[email protected]:~$ createdb testdb
[email protected]:~$ psql
psql (10.3 (Ubuntu 10.3-1.pgdg16.04+1))
Type "help" for help.

postgres=# \l
                               List of databases
     Name      |  Owner   | Encoding | Collate | Ctype |   Access privileges   
---------------+----------+----------+---------+-------+-----------------------
 postgres      | postgres | UTF8     | en_IN   | en_IN | 
 rana_test     | postgres | UTF8     | en_IN   | en_IN | 
 template0     | postgres | UTF8     | en_IN   | en_IN | =c/postgres          +
               |          |          |         |       | postgres=CTc/postgres
 template1     | postgres | UTF8     | en_IN   | en_IN | =c/postgres          +
               |          |          |         |       | postgres=CTc/postgres
 testdb        | postgres | UTF8     | en_IN   | en_IN | 
(5 rows)

postgres=# \c testdb
You are now connected to database "testdb" as user "postgres".
testdb=# 

To connect to another database, use command \c or \connect and database name. \c testdb in this case.

Creating Table:

Most the query sytanx in PostgreSQL are same as MySQL.

create table users (
    id serial PRIMARY KEY,
    username varchar (20) NOT NULL,
    age smallint NOT NULL,
    location varchar (50) NOT NULL
);

Copy paste the above sytax in terminal and new table will be created. You can list the tables by typing \d.

testdb=# create table users (
testdb(#     username varchar (20) NOT NULL,
testdb(#     age smallint NOT NULL,
testdb(#     location varchar (50) NOT NULL
testdb(# );
CREATE TABLE
testdb=# \d
         List of relations
 Schema | Name  | Type  |  Owner   
--------+-------+-------+----------
 public | users | table | postgres
(1 row)

testdb=# 

You can learn more about querying from psql terminal by visitng official site. Lets go to Python code.

Connecting from Python Script:

We installed the psycopg package in virtual environment. Use below code in Python Script to connect to database.

import psycopg2


# this function will return the connection object
def connect():
    conn = None
    try:
        conn = psycopg2.connect(host="localhost", user="postgres", password="root", database="testdb")
    except Exception as e:
        print(repr(e))

    return conn

Inserting Data into Table:

First get the connection and cursor and then create query. Once query is executed, commit using connection and close the cursor and connection.

conn = connect()
cur = conn.cursor()

last_insert_id = None

# inserting data in users table
sql_query = "insert into users (username, age, location) values (%s, %s, %s) returning id;"

sql_data = (
    "Ajay",
    "25",
    "New York"
)


cur.execute(sql_query, sql_data)
last_insert_id = cur.fetchone()[0]
print("Last Insert ID " + str(last_insert_id))

conn.commit()
cur.close()
conn.close()

return last_insert_id

We are Inserting data in table and returning the primary key id which is the serial key.

Fetching Data from Table:

Select query for PostgreSQL is same as MySQL.

conn = connect()
cur = conn.cursor()

sql_query = "select username, age, location from users where location = %s;"
sql_data = ("Delhi")
cur.execute(sql_query, sql_data)

results = cur.fetchall()
return results

Updating a row:

conn = connect()
cursor = conn.cursor()

sql_query = "update users set location = %s where username = %s;"
sql_data = ("Mumbai", "Ajay")

cursor.execute(sql_query, sql_data)

cursor.close()
conn.close()

return True

To exit the terminal use \q command.

If you are facing any issue, feel free to comment.

What's Python IDLE? How to use Python IDLE to interact with Python?

What's Python IDLE? How to use Python IDLE to interact with Python?

In this tutorial, you’ll learn all the basics of using **IDLE** to write Python programs. You'll know what Python IDLE is and how you can use it to interact with Python directly. You’ve also learned how to work with Python files and customize Python IDLE to your liking.

In this tutorial, you'll learn how to use the development environment included with your Python installation. Python IDLE is a small program that packs a big punch! You'll learn how to use Python IDLE to interact with Python directly, work with Python files, and improve your development workflow.

If you’ve recently downloaded Python onto your computer, then you may have noticed a new program on your machine called IDLE. You might be wondering, “What is this program doing on my computer? I didn’t download that!” While you may not have downloaded this program on your own, IDLE comes bundled with every Python installation. It’s there to help you get started with the language right out of the box. In this tutorial, you’ll learn how to work in Python IDLE and a few cool tricks you can use on your Python journey!

In this tutorial, you’ll learn:

  • What Python IDLE is
  • How to interact with Python directly using IDLE
  • How to edit, execute, and debug Python files with IDLE
  • How to customize Python IDLE to your liking

Table of Contents

What Is Python IDLE?

Every Python installation comes with an Integrated Development and Learning Environment, which you’ll see shortened to IDLE or even IDE. These are a class of applications that help you write code more efficiently. While there are many IDEs for you to choose from, Python IDLE is very bare-bones, which makes it the perfect tool for a beginning programmer.

Python IDLE comes included in Python installations on Windows and Mac. If you’re a Linux user, then you should be able to find and download Python IDLE using your package manager. Once you’ve installed it, you can then use Python IDLE as an interactive interpreter or as a file editor.

An Interactive Interpreter

The best place to experiment with Python code is in the interactive interpreter, otherwise known as a shell. The shell is a basic Read-Eval-Print Loop (REPL). It reads a Python statement, evaluates the result of that statement, and then prints the result on the screen. Then, it loops back to read the next statement.

The Python shell is an excellent place to experiment with small code snippets. You can access it through the terminal or command line app on your machine. You can simplify your workflow with Python IDLE, which will immediately start a Python shell when you open it.

A File Editor

Every programmer needs to be able to edit and save text files. Python programs are files with the .py extension that contain lines of Python code. Python IDLE gives you the ability to create and edit these files with ease.

Python IDLE also provides several useful features that you’ll see in professional IDEs, like basic syntax highlighting, code completion, and auto-indentation. Professional IDEs are more robust pieces of software and they have a steep learning curve. If you’re just beginning your Python programming journey, then Python IDLE is a great alternative!

How to Use the Python IDLE Shell

The shell is the default mode of operation for Python IDLE. When you click on the icon to open the program, the shell is the first thing that you see:

This is a blank Python interpreter window. You can use it to start interacting with Python immediately. You can test it out with a short line of code:

Here, you used print() to output the string "Hello, from IDLE!" to your screen. This is the most basic way to interact with Python IDLE. You type in commands one at a time and Python responds with the result of each command.

Next, take a look at the menu bar. You’ll see a few options for using the shell:

You can restart the shell from this menu. If you select that option, then you’ll clear the state of the shell. It will act as though you’ve started a fresh instance of Python IDLE. The shell will forget about everything from its previous state:

In the image above, you first declare a variable, x = 5. When you call print(x), the shell shows the correct output, which is the number 5. However, when you restart the shell and try to call print(x) again, you can see that the shell prints a traceback. This is an error message that says the variable x is not defined. The shell has forgotten about everything that came before it was restarted.

You can also interrupt the execution of the shell from this menu. This will stop any program or statement that’s running in the shell at the time of interruption. Take a look at what happens when you send a keyboard interrupt to the shell:

A KeyboardInterrupt error message is displayed in red text at the bottom of your window. The program received the interrupt and has stopped executing.

How to Work With Python Files

Python IDLE offers a full-fledged file editor, which gives you the ability to write and execute Python programs from within this program. The built-in file editor also includes several features, like code completion and automatic indentation, that will speed up your coding workflow. First, let’s take a look at how to write and execute programs in Python IDLE.

Opening a File

To start a new Python file, select File → New File from the menu bar. This will open a blank file in the editor, like this:

From this window, you can write a brand new Python file. You can also open an existing Python file by selecting File → Open… in the menu bar. This will bring up your operating system’s file browser. Then, you can find the Python file you want to open.

If you’re interested in reading the source code for a Python module, then you can select File → Path Browser. This will let you view the modules that Python IDLE can see. When you double click on one, the file editor will open up and you’ll be able to read it.

The content of this window will be the same as the paths that are returned when you call sys.path. If you know the name of a specific module you want to view, then you can select File → Module Browser and type in the name of the module in the box that appears.

Editing a File

Once you’ve opened a file in Python IDLE, you can then make changes to it. When you’re ready to edit a file, you’ll see something like this:

The contents of your file are displayed in the open window. The bar along the top of the window contains three pieces of important information:

  1. The name of the file that you’re editing
  2. The full path to the folder where you can find this file on your computer
  3. The version of Python that IDLE is using

In the image above, you’re editing the file myFile.py, which is located in the Documents folder. The Python version is 3.7.1, which you can see in parentheses.

There are also two numbers in the bottom right corner of the window:

  1. Ln: shows the line number that your cursor is on.
  2. Col: shows the column number that your cursor is on.

It’s useful to see these numbers so that you can find errors more quickly. They also help you make sure that you’re staying within a certain line width.

There are a few visual cues in this window that will help you remember to save your work. If you look closely, then you’ll see that Python IDLE uses asterisks to let you know that your file has unsaved changes:

The file name shown in the top of the IDLE window is surrounded by asterisks. This means that there are unsaved changes in your editor. You can save these changes with your system’s standard keyboard shortcut, or you can select File → Save from the menu bar. Make sure that you save your file with the .py extension so that syntax highlighting will be enabled.

Executing a File

When you want to execute a file that you’ve created in IDLE, you should first make sure that it’s saved. Remember, you can see if your file is properly saved by looking for asterisks around the filename at the top of the file editor window. Don’t worry if you forget, though! Python IDLE will remind you to save whenever you attempt to execute an unsaved file.

To execute a file in IDLE, simply press the F5 key on your keyboard. You can also select Run → Run Module from the menu bar. Either option will restart the Python interpreter and then run the code that you’ve written with a fresh interpreter. The process is the same as when you run python3 -i [filename] in your terminal.

When your code is done executing, the interpreter will know everything about your code, including any global variables, functions, and classes. This makes Python IDLE a great place to inspect your data if something goes wrong. If you ever need to interrupt the execution of your program, then you can press Ctrl+C in the interpreter that’s running your code.

How to Improve Your Workflow

Now that you’ve seen how to write, edit, and execute files in Python IDLE, it’s time to speed up your workflow! The Python IDLE editor offers a few features that you’ll see in most professional IDEs to help you code faster. These features include automatic indentation, code completion and call tips, and code context.

Automatic Indentation

IDLE will automatically indent your code when it needs to start a new block. This usually happens after you type a colon (:). When you hit the enter key after the colon, your cursor will automatically move over a certain number of spaces and begin a new code block.

You can configure how many spaces the cursor will move in the settings, but the default is the standard four spaces. The developers of Python agreed on a standard style for well-written Python code, and this includes rules on indentation, whitespace, and more. This standard style was formalized and is now known as PEP 8. To learn more about it, check out How to Write Beautiful Python Code With PEP 8.

Code Completion and Call Tips

When you’re writing code for a large project or a complicated problem, you can spend a lot of time just typing out all of the code you need. Code completion helps you save typing time by trying to finish your code for you. Python IDLE has basic code completion functionality. It can only autocomplete the names of functions and classes. To use autocompletion in the editor, just press the tab key after a sequence of text.

Python IDLE will also provide call tips. A call tip is like a hint for a certain part of your code to help you remember what that element needs. After you type the left parenthesis to begin a function call, a call tip will appear if you don’t type anything for a few seconds. For example, if you can’t quite remember how to append to a list, then you can pause after the opening parenthesis to bring up the call tip:

The call tip will display as a popup note, reminding you how to append to a list. Call tips like these provide useful information as you’re writing code.

Code Context

The code context functionality is a neat feature of the Python IDLE file editor. It will show you the scope of a function, class, loop, or other construct. This is particularly useful when you’re scrolling through a lengthy file and need to keep track of where you are while reviewing code in the editor.

To turn it on, select Options → Code Context in the menu bar. You’ll see a gray bar appear at the top of the editor window:

As you scroll down through your code, the context that contains each line of code will stay inside of this gray bar. This means that the print() functions you see in the image above are a part of a main function. When you reach a line that’s outside the scope of this function, the bar will disappear.

How to Debug in IDLE

A bug is an unexpected problem in your program. They can appear in many forms, and some are more difficult to fix than others. Some bugs are tricky enough that you won’t be able to catch them by just reading through your program. Luckily, Python IDLE provides some basic tools that will help you debug your programs with ease!

Interpreter DEBUG Mode

If you want to run your code with the built-in debugger, then you’ll need to turn this feature on. To do so, select Debug → Debugger from the Python IDLE menu bar. In the interpreter, you should see [DEBUG ON] appear just before the prompt (>>>), which means the interpreter is ready and waiting.

When you execute your Python file, the debugger window will appear:

In this window, you can inspect the values of your local and global variables as your code executes. This gives you insight into how your data is being manipulated as your code runs.

You can also click the following buttons to move through your code:

  • Go: Press this to advance execution to the next breakpoint. You’ll learn about these in the next section.
  • Step: Press this to execute the current line and go to the next one.
  • Over: If the current line of code contains a function call, then press this to step over that function. In other words, execute that function and go to the next line, but don’t pause while executing the function (unless there is a breakpoint).
  • Out: If the current line of code is in a function, then press this to step out of this function. In other words, continue the execution of this function until you return from it.

Be careful, because there is no reverse button! You can only step forward in time through your program’s execution.

You’ll also see four checkboxes in the debug window:

  1. Globals: your program’s global information
  2. Locals: your program’s local information during execution
  3. Stack: the functions that run during execution
  4. Source: your file in the IDLE editor

When you select one of these, you’ll see the relevant information in your debug window.

Breakpoints

A breakpoint is a line of code that you’ve identified as a place where the interpreter should pause while running your code. They will only work when DEBUG mode is turned on, so make sure that you’ve done that first.

To set a breakpoint, right-click on the line of code that you wish to pause. This will highlight the line of code in yellow as a visual indication of a set breakpoint. You can set as many breakpoints in your code as you like. To undo a breakpoint, right-click the same line again and select Clear Breakpoint.

Once you’ve set your breakpoints and turned on DEBUG mode, you can run your code as you would normally. The debugger window will pop up, and you can start stepping through your code manually.

Errors and Exceptions

When you see an error reported to you in the interpreter, Python IDLE lets you jump right to the offending file or line from the menu bar. All you have to do is highlight the reported line number or file name with your cursor and select Debug → Go to file/line from the menu bar. This is will open up the offending file and take you to the line that contains the error. This feature works regardless of whether or not DEBUG mode is turned on.

Python IDLE also provides a tool called a stack viewer. You can access it under the Debug option in the menu bar. This tool will show you the traceback of an error as it appears on the stack of the last error or exception that Python IDLE encountered while running your code. When an unexpected or interesting error occurs, you might find it helpful to take a look at the stack. Otherwise, this feature can be difficult to parse and likely won’t be useful to you unless you’re writing very complicated code.

How to Customize Python IDLE

There are many ways that you can give Python IDLE a visual style that suits you. The default look and feel is based on the colors in the Python logo. If you don’t like how anything looks, then you can almost always change it.

To access the customization window, select Options → Configure IDLE from the menu bar. To preview the result of a change you want to make, press Apply. When you’re done customizing Python IDLE, press OK to save all of your changes. If you don’t want to save your changes, then simply press Cancel.

There are 5 areas of Python IDLE that you can customize:

  1. Fonts/Tabs
  2. Highlights
  3. Keys
  4. General
  5. Extensions

Let’s take a look at each of them now.

Fonts/Tabs

The first tab allows you to change things like font color, font size, and font style. You can change the font to almost any style you like, depending on what’s available for your operating system. The font settings window looks like this:

You can use the scrolling window to select which font you prefer. (I recommend you select a fixed-width font like Courier New.) Pick a font size that’s large enough for you to see well. You can also click the checkbox next to Bold to toggle whether or not all text appears in bold.

This window will also let you change how many spaces are used for each indentation level. By default, this will be set to the PEP 8 standard of four spaces. You can change this to make the width of your code more or less spread out to your liking.

Highlights

The second customization tab will let you change highlights. Syntax highlighting is an important feature of any IDE that highlights the syntax of the language that you’re working in. This helps you visually distinguish between the different Python constructs and the data used in your code.

Python IDLE allows you to fully customize the appearance of your Python code. It comes pre-installed with three different highlight themes:

  1. IDLE Day
  2. IDLE Night
  3. IDLE New

You can select from these pre-installed themes or create your own custom theme right in this window:

Unfortunately, IDLE does not allow you to install custom themes from a file. You have to create customs theme from this window. To do so, you can simply start changing the colors for different items. Select an item, and then press Choose color for. You’ll be brought to a color picker, where you can select the exact color that you want to use.

You’ll then be prompted to save this theme as a new custom theme, and you can enter a name of your choosing. You can then continue changing the colors of different items if you’d like. Remember to press Apply to see your changes in action!

Keys

The third customization tab lets you map different key presses to actions, also known as keyboard shortcuts. These are a vital component of your productivity whenever you use an IDE. You can either come up with your own keyboard shortcuts, or you can use the ones that come with IDLE. The pre-installed shortcuts are a good place to start:

The keyboard shortcuts are listed in alphabetical order by action. They’re listed in the format Action - Shortcut, where Action is what will happen when you press the key combination in Shortcut. If you want to use a built-in key set, then select a mapping that matches your operating system. Pay close attention to the different keys and make sure your keyboard has them!

Creating Your Own Shortcuts

The customization of the keyboard shortcuts is very similar to the customization of syntax highlighting colors. Unfortunately, IDLE does not allow you to install custom keyboard shortcuts from a file. You must create a custom set of shortcuts from the Keys tab.

Select one pair from the list and press Get New Keys for Selection. A new window will pop up:

Here, you can use the checkboxes and scrolling menu to select the combination of keys that you want to use for this shortcut. You can select Advanced Key Binding Entry >> to manually type in a command. Note that this cannot pick up the keys you press. You have to literally type in the command as you see it displayed to you in the list of shortcuts.

General

The fourth tab of the customization window is a place for small, general changes. The general settings tab looks like this:

Here, you can customize things like the window size and whether the shell or the file editor opens first when you start Python IDLE. Most of the things in this window are not that exciting to change, so you probably won’t need to fiddle with them much.

Extensions

The fifth tab of the customization window lets you add extensions to Python IDLE. Extensions allow you to add new, awesome features to the editor and the interpreter window. You can download them from the internet and install them to right into Python IDLE.

To view what extensions are installed, select Options → Configure IDLE -> Extensions. There are many extensions available on the internet for you to read more about. Find the ones you like and add them to Python IDLE!

Conclusion

In this tutorial, you’ve learned all the basics of using IDLE to write Python programs. You know what Python IDLE is and how you can use it to interact with Python directly. You’ve also learned how to work with Python files and customize Python IDLE to your liking.

You’ve learned how to:

  • Work with the Python IDLE shell
  • Use Python IDLE as a file editor
  • Improve your workflow with features to help you code faster
  • Debug your code and view errors and exceptions
  • Customize Python IDLE to your liking

Now you’re armed with a new tool that will let you productively write Pythonic code and save you countless hours down the road. Happy programming!

2020 PostgreSQL Trends Report

2020 PostgreSQL Trends Report

In this latest PostgreSQL trends report, we analyze the most popular cloud providers for PostgreSQL, VACUUM strategies, query management ...

PostgreSQL popularity is skyrocketing in the enterprise space. As this open source database continues to pull new users from expensive commercial database management systems like Oracle, DB2, and SQL Server, organizations are adopting new approaches and evolving their own to maintain the exceptional performance of their SQL deployments.

We recently attended the PostgresConf event in San Jose to hear from the most active PostgreSQL user base on their database management strategies. In this latest PostgreSQL trends report, we analyze the most popular cloud providers for PostgreSQL, VACUUM strategies, query management strategies, and on-premises vs public cloud use being leveraged by enterprise organizations.

You might also like:  Secure Node.js, Express.js and PostgreSQL API using Passport.js

Most Popular Cloud Providers for PostgreSQL Hosting

Let’s start with the most popular cloud providers for PostgreSQL hosting. It comes as no surprise that the top three cloud providers in the world made up 100% of the PostgreSQL deployments in the crowd across this enterprise report. AWS, however, has taken a significant leap from our last report, where they now average 77.4% of PostgreSQL cloud use compared to 55.0% in April. AWS does offer a managed hosting service for PostgreSQL called Amazon RDS, but there are many other DBaaS solutions that offer PostgreSQL hosting on AWS, such as ScaleGrid, that can provide multi-cloud support so you’re not locked in with a single cloud provider.

AWS was not the only cloud provider to grow – we found that 19.4% of PostgreSQL cloud deployments were hosted through Google Cloud Platform (GCP), growing 11% from April where they only averaged 17.5% of PostgreSQL hosting. This leaves our last cloud provider – Microsoft Azure, who represented 3.2% of PostgreSQL cloud deployments in this survey. This is one of the most shocking discoveries, as Azure was tied for second with GCP back in April, and is commonly a popular choice for enterprise organizations leveraging the Microsoft suite of services.

Most Used Languages With PostgreSQL

This is a new analysis we surveyed to see which languages are most popularly used with PostgreSQL. The supported programming languages for PostgreSQL include .Net, C, C++, Delphi, Java, JavaScript (Node.js), Perl, PHP, Python, and Tcl, but PostgreSQL can support many server-side procedural languages through its available extensions.

We found that Java is the most popular programming language for PostgreSQL, being leveraged by 31.1% of enterprise organizations on average. PostgreSQL can be easily connected with Java programs through the popular open source PostgreSQL Java Database Connectivity (JBDC) Driver, also known as PgJDBC.

Python was the second most popular programming language used with PostgreSQL, coming in close at an average of 28.9% use with PostgreSQL. Back in 2013, PostgreSQL surveyed their users to see which external programming languages was most often used with PostgreSQL, and found that Python only represented 10.5% of the results, showing a massive increase in popularity over the past six years.

The programming language C came in third place, averaging 20.0% use with PostgreSQL, followed by Go in fourth at 13.3%, PL/pgSQL in fifth at 11.1%, Ruby in sixth at 8.9% and both PHP and Perl in seventh at 4.4%. PHP was actually the most popular language used with PostgreSQL in 2013, representing almost half of the responses from their survey at 47.1% use. The last column, Other, was represented by C++, Node.js, Javascript, Spark, Swift, Kotlin, Typescript, C#, Scala, R, .NET, Rust and Haskell.

Most Popular PostgreSQL VACUUM Strategies

PostgreSQL VACUUM is a technique to remove tuples that have been deleted or are now obsolete from their table to reclaim storage occupied by those dead tuples, also known as Bloat. VACUUM is an important process to maintain, especially for frequently-updated tables before it starts affecting your PostgreSQL performance. In our survey, we asked enterprise PostgreSQL users how they are handling VACUUM to see what the most popular approaches are.

The most popular process for PostgreSQL VACUUM is the built-in autovacuum, being leveraged by 37.5% of enterprise organizations on average. The autovacuum daemon is optional, but highly recommended in the PostgreSQL community, at it automates both VACUUM and ANALYZE commands, continuously checking tables for deal tuples. While highly recommended, 33.3% of PostgreSQL users prefer to manually perform VACUUM in the enterprise space. Fibrevillage has a great article that outlines these common problems with autovacuum which may cause an organization to adopt a manual strategy:

  • autovacuum may run even when turned off to deal with transaction ID wraparound.
  • autovacuum is constantly running, which makes it start over every time it runs out of space, and start a new worker for each database in your cluster.
  • autovacuum can cause out of memory errors.
  • autovacuum may have trouble keeping up on a busy server.
  • autovacuum can easily consume too much I/O capacity.

Another surprising discovery was that 18.8% of organizations do not use VACUUM, as it is not yet needed. This may be because they are leveraging PostgreSQL in small applications or applications that are not frequently updated. 6.6% of organizations have developed a custom solution for PostgreSQL VACUUM, and 4.2% are in the process of planning their VACUUM strategy.

Most Popular PostgreSQL Slow Query Management Strategies

If you’re working with PostgreSQL, you likely know that managing queries is the #1 most time-consuming task. It’s a critical process with many aspects to consider, starting at developing a query plan to match your query structure with your data properties, to then analyzing slow-running queries, finally to optimizing those queries through performance tuning.

We found that 54.3% of PostgreSQL users are manually managing slow queries in enterprise organizations. This can be accomplished through their modules auto_explain and pg_stat_statements, checking pg_stat_activity for table and index activity on your server, analyzing the slow query log, or reviewing in your code.

On average, 21.7% of enterprise organizations are leveraging a monitoring tool to analyze and manage their PostgreSQL slow queries. This helps them significantly reduce the time it takes to identify which queries are running the slowest, most frequently, causing the most read or write load on your system, or queries missing an index by examining the rows.

17.4% of users, however, are not actively managing slow queries in the PostgreSQL deployments. We highly recommend adopting a query management strategy to ensure slow queries are not affecting the performance of your PostgreSQL deployments. 4.3% of users are currently in the process of planning their query management strategy, and 2.2% have developed a custom solution for managing their slow queries.

PostgreSQL Cloud vs On-Premises Deployments

Let’s end with one of the hottest topics in the PostgreSQL enterprise space – whether to deploy PostgreSQL in the cloud or on-premises. We’ve been actively monitoring this trend all year, and found that 59.6% of PostgreSQL deployments were strictly on-premises back in April from our 2019 PostgreSQL Trends Report and 55.8% on-premises in our 2019 Open Source Database Report just a few months ago in June.

Now, in this most recent report, we found that PostgreSQL on-premises deployments have decreased by 40% since April of 2019. On average, only 35.6% of PostgreSQL enterprise organizations are deploying exclusively on-premise. But organizations are not migrating their on-premises deployments altogether – 24.4% of PostgreSQL deployments were found to be leveraging a hybrid cloud environment. Hybrid clouds are a mix of on-premises, private cloud, and/or public cloud computing to support their applications and data. This is a significant increase from what we saw in April, jumping from 5.6% of PostgreSQL deployments up to 24.4% in September.

Hybrid cloud deployments are becoming more popular across the board — this recent report found that 57% of businesses opt for a hybrid cloud environment using both private and public clouds as places to store their data. While we see a large jump to the cloud, enterprise organizations are still leveraging on-premises environments in some capacity 60% of the time, compared to 65.2% in April. Lastly, we found that public cloud PostgreSQL deployments have grown 15% since April, now averaging 34.8% use by enterprise organizations.

It’s also important to note that this survey was conducted at the PostgresConf Silicon Valley event, while our April survey was conducted in New York City. The bay area is widely known for adopting new technologies, which allows us to hypothesize that this market has a higher cloud adoption rate than the east coast.

| PostgreSQL Deployment Types | Apr | Jun | Sep | Apr-Sep Growth |
| On-Premises | 59.6% | 55.8% | 35.6% | -40.0% |
| Hybrid Cloud | 5.6% | 16.3% | 24.4% | 336% |
| Public Cloud | 34.8% | 27.9% | 40.0% | 15.0% |

So, how do these results stack up to your PostgreSQL deployments and strategies?

Thank for reading! Please share if you liked it!

Further Reading

How to Install PostgreSQL on Ubuntu 18.04

An Introduction to Queries in PostgreSQL