PostgreSQL, a free, feature-rich and extensible open-source database system with a broad developer community, has become one of the most widely used databases in the world, offering enterprise-grade features appropriate for high-load, business-critical systems.
As with all database systems, anyone just starting to learn about PostgreSQL can benefit from a clear, incremental approach to developing a strong skillset. This article outlines such an approach, which is also developed in far more detail – including step- by-step instructions and code samples – in “Postgres. The First Experience,” a free, downloadable book by Pavel Luzanov, Egor Rogov, and Igor Levshin.
1. Getting Started – Installation on Linux and Windows
Depending on your operating system, PostgreSQL installations differ, so it’s important to access specific instructions for your environment. For ease of deployment, you can use the Postgres Pro Standard 12 or 13 distribution, which is fully compatible with vanilla PostgreSQL and available from the Microsoft Azure Marketplace.
Once you’ve downloaded PostgreSQL, you can run the installation wizard, following the advice in the downloaded instructions as you go.
2. Connecting to a server
In Postgres, connecting to a server requires using the psql client from the command line in the interactive mode. While many developers no longer like the idea of a command line interface, using it for Postgres makes sense because it is available in every version of Postgres, and it is very convenient for everyday DBA tasks, such as writing small queries and automating processes.
If you prefer a graphical user interface (GUI), you can try pgAdmin or another downloadable solution. pgAdmin can make it easier to run some DBA tasks, including reviewing database objects and running SQL queries, and the latest version includes the standard PostgreSQL utilities, system catalog information, administration functions, and SQL commands. It also has a built-in PL/pgSQL debugger that is useful to learn about.
3. Practicing on a demo SQL database
A demo SQL database is the best way to learn the fundamentals of using Postgres. To go to the next level and learn about how to create complex queries, create a demo database with at least eight tables and fill it with usable data. You can also download a free, pre-filled demo database.
4. Using PostgreSQL with your application
Once you’ve got the basics of Postgres down, you can create the database for your application. However, don’t use the “database user” created during the installation process for the application because this user has superuser privileges. Instead, create a new user and make it the owner of a separate database, so that its rights are limited to this database instance only.
You can then follow the general instructions for connecting to a new database on behalf of the new user. Once this connection is made, you’re ready to develop an application, keeping in mind the basics of the client server relationship. Whether to locate the business logic of the application on the server side, the client side, or a bit of both will depend on your knowledge and future needs.
5. Minimal server setup
While the default settings of PostgreSQL will work on nearly any hardware configuration, you’ll get better performance if the database configuration is based on the physical characteristics of the server and the typical application workload. To ensure optimal performance for production databases, most database administrators take a deeper dive into PostgreSQL database administration, including taking online or in-person courses.
6. Postgres includes a number of advanced features that can improve the capabilities of your applications:
- Full-text search – This feature enables you to search text-based data in a big data database using a relevance algorithm – similar to how Google Search works.
- JSON and JSONB – The simplicity of NoSQL databases – the absence of a fixed row- and column-oriented structure – delivers tremendous speed, but these databases are insufficient for sophisticated database work. JSON and JSONB bring some of the speed benefits of NoSQL to SQL without breaking the traditional SQL database structure.
- Foreign data wrappers – To enable Postgres-based applications to share data with other applications, PostgreSQL supports the ISO/IEC 9075-9 standard (SQL/MED, Management of External Data), which defines how to work with external data sources via a mechanism called foreign data wrappers. Postgres includes two foreign data wrappers, one for external PostgreSQL databases and one for files on a server. Foreign data wrappers for Oracle, MySQL and SQL Server are also available as extensions.
- The PostgreSQL 13 release also includes key new features including b-tree deduplication, incremental sorting, parallelized vacuum, and enhanced partitioning.
- The most current release, PostgreSQL 14, includes enhancements for greater scalability, extensibility and usability and features several performance improvements for parallel queries, heavily-concurrent workloads, partitioned tables, logical replication, and vacuuming.
For a complete list of PostgreSQL features, review the PostgreSQL feature matrix.
Conclusion
The more you learn about PostgreSQL from the start, the more you can optimize your applications from the ground up – and the more valuable you will become to your organization. You can also track expert blogs and the availability of free webinars. Keep in mind that PostgreSQL version 14 is on the horizon and includes numerous enhancements, including some that may impact a possible migration.
About the Author
Igor Levshin is Director of Content of Postgres Professional, the company that makes PostgreSQL enterprise-ready.
Sign up for the free insideBIGDATA newsletter.
Join us on Twitter: @InsideBigData1 – https://twitter.com/InsideBigData1
Leave a Reply