PostgreSQL Interview Questions and Answers

PostgreSQL is an open source, object-relational database management system (ORDBMS). It is used to store data securely; supporting best practices and allow retrieving them when request is processed.  It supports both SQL (relational) and JSON (non-relational) querying. PostgreSQL (Post-gress-Q-L) is developed by the PostgreSQL Global Development Group (a worldwide team of volunteers). It is not controlled by any corporation or other private entity. It is open source and its source code is available free of charge. It runs on numerous platforms including Linux, most flavors of UNIX, Mac OS X, Solaris, Tru64, and Windows. It supports text, images, sounds, and video, and includes programming interfaces for C / C++, Java, Perl, Python, Ruby, Tcl and Open Database Connectivity (ODBC).

• PostgreSQL runs on all major operating systems i.e. Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), and Windows etc.
• PostgreSQL supports text, images, sounds, and video, and includes programming interfaces for C / C++, Java, Perl, Python, Ruby, Tcl and Open Database Connectivity (ODBC).
• PostgreSQL supports a lot of features of SQL like Complex SQL queries, SQL Sub-selects, Foreign keys, Trigger, Views, Transactions, Multisession concurrency control (MVCC), Streaming Replication (as of 9.0), Hot Standby (as of 9.0).
• In PostgreSQL, table can be set to inherit their characteristics from a “parent” table.
• Nested transactions (save points)
• Asynchronous replication
• Native Microsoft Windows Server version
• Tablespaces
• Point-in-time recovery
• You can install several extensions to add additional functionality to PostgreSQL.

• Data integrity: When reliability and data integrity are an absolute necessity without excuses, PostgreSQL is the best choice.
• Complex, custom procedures: If you require your database to perform custom procedures, PostgreSQL, being extensible, is the best choice.
• Integration: In the future, if there is a chance of necessity arising for migrating the entire database system to a propriety (e.g. Oracle) solution, PostgreSQL will be the most compliant and easy to handle base for the switch.
• Complex designs: Compared to other open-source and free RDBMS implementations, for complex database designs, PostgreSQL offers the most in terms of functionality and possibilities without giving up on other valuable assets.

Multi-Version Concurrency Control (MVCC) is an advanced technique for improving database performance in a multi-user environment. This feature or time lag occurs when someone else is on the content. All the transactions are kept as a record.

In computer science, write-ahead logging (WAL) is a family of techniques for providing atomicity and durability (two of the ACID properties) in database systems. AL (Write-Ahead Logging) is a standard method for ensuring data integrity. A detailed description can be found in most (if not all) books about transaction processing. Briefly, WAL’s central concept is that changes to data files (where tables and indexes reside) must be written only after those changes have been logged, that is, after log records describing the changes have been flushed to permanent storage.

CTIDs identify specific physical rows by their block and offset positions within a table. They are used by index entries to point to physical rows. A logical row’s CTID changes when it is updated, so the CTID cannot be used as a long-term row identifier. But it is sometimes useful to identify a row within a transaction when no competing update is expected

There are built in functions such as B-tree, hash table, and GIST indices can be used or users can define their own indices. PostgreSQL can scan the index backwards. An expression index can be created with the addition of a WHERE clause. Partial index created with addition of WHERE clause

There are various data administration tools they are

• PSQL
• PGADMIN
• SQLyog
• Navicat for MySQL
• PHPPGADMIN
• phpMyAdmin

Most of these tools are front end administration tools and web-based interfaces. Out of these phppgadmin is the most popular one.

This command is used for enabling compilation of all libraries and applications. This process generally slows down the system and it also increases the binary file size. Debugging symbols are present which can assist developers in noticing bugs and problems associated with their script.

PostgreSQL offers many advantages for your company or business over other database systems.

• More profitable business models with wide-scale deployment.
• No possibility of being audited for license compliance at any stage.
• Flexibility to do concept research and trial deployments without needing to include additional licensing costs.
• Better support than the proprietary vendors
• Significant saving on staffing costs
• Legendary reliability and stability
• Extensible
• Cross platform
• Designed for high volume environments
• Immunity to over-deployment
• GUI database design and administration tools

/usr/local/etc/rc.d/010.pgsql.sh start

/usr/local/etc/rc.d/postgresql start

/usr/local/etc/rc.d/010.pgsql.sh stop

/usr/local/etc/rc.d/postgresql stop

/usr/local/bin/createdb mydatabase

/usr/local/bin/pg_dump mydatabase > mydatabase.pgdump

Create user user with password ‘password’;

You probably have run out of virtual memory on your system, or your kernel has a low limit for certain resources. Try this before starting the server:

Ulimit -D 262144

Limit Datasize 256m

Some of the languages which PostgreSQL supports are as follows: – It supports a language of its own known as PL/pgSQL and it supports internal procedural languages. Pl/pgSQL can be compared to oracle, PL/SQL, etc. Languages such as Perl, Python, TCL can be used as embedded languages.

PostgreSQL – CBRT

Syntax: CBRT ()

Description: CBRT function returns cube root

MySQL and PostgreSQL are both free and open source powerful and full-featured databases. You should be able to compare these two databases. Here is the complete article on this.

There are various enhancements provided to the straight relational data model by PostgreSQL they are support for arrays which includes multiple values, inheritance, functions and extensibility. Jargon differs because of its object-oriented nature where tables are called as classes.

Yes. Like other open source databases, PostgreSQL is easy to run in virtual containers and is highly portable. Several companies have support for PostgreSQL in cloud hosting environments, including Heroku, GoGrid and Joyent.

Performance: For simple read-heavy operations, PostgreSQL can be an overkill and might appear less performant than the counterparts, such as MySQL.

Popularity: Given the nature of this tool, it lacks behind in terms of popularity, despite the very large amount of deployments – which might affect how easy it might be possible to get support.

Hosting: Due to above mentioned factors, it is harder to come by hosts or service providers that offer managed PostgreSQL instances.