When they need a relational database, software developers and system administrators often choose MySQL or PostgreSQL. For a lighter and simpler solution, however, developers should consider SQLite. It's used in so many open source and commercial products that SQLite is considered the most deployed SQL database engine in the world. This article will help you get started with SQLite and use it in your programs and scripts.
You can get optimum performance from your database by tuning your system in three areas: the hardware, the database, and the database server. Each increasingly more specialized than the last, with the tuning of the actual database server being unique to the software being used. If you're already familiar with tuning MySQL databases, you'll find tuning a PostgreSQL database server to be similar, but with some key differences to watch out for.
People putting together web applications often overlook PostgreSQL in favor of MySQL, yet PostgreSQL is enterprise-ready and has been used by familiar names such as Instagram and Disqus. One advantage is a license that grants the right to use, copy, modify, and distribute the software as long as the relevant copyright notices are maintained, and that doesn't force projects that use PostgreSQL to be released as open source code. You can use PostgreSQL and PHP to build a dynamic web site with data residing in a PostgreSQL database, and if you are familiar with using PHP to communicate with MySQL, it is relatively easy to make the transition to Postgres. If you don't know anything about PostgreSQL, start by reading PostgreSQL Administration for MySQL Admins to learn how to install PostgreSQL and create a simple database.
Storing arrays in database fields provides a number of challenges, but also a number of opportunities for RDBMS developers. We'll illustrate some of those issues using PostgreSQL as an example, but the principles can be applied to other database systems as well, including MySQL.
When people think about deploying an open source web ecosystem, they often choose Linux, along with Apache, PHP, and the MySQL database. However, MySQL isn't the only mature and feature-rich open source option; rival PostgreSQL can be an equally good choice. While the two are both relational databases, they differ when it comes to implementation details. But don't worry – if you know how to manage MySQL, you can learn how to handle PostgreSQL fairly quickly.
If you're a system administrator, chances are you use programs that interface with databases every day. One great advantage of open source software is that you can modify your applications' code to customize it for your needs. If your application uses PostgreSQL on the back end, it's not hard to access your database from a variety of languages. Here's how to get started, whether your code is in C, C++, PHP, Tcl, Python, or Perl.
The PostgreSQL relational database management system (RDBMS) extends the standard SQL3 it implements with several conveniences for application development. Among the most useful extensions are window functions, which perform a calculation across a set of table rows that are somehow related to the current row.
By using MySQL replication, you can distribute MySQL queries over multiple servers to improve application performance, provide high availability (HA), and distribute data across diverse physical locations. The process involves one or more master servers, which send databases or tables asynchronously to slave servers. For all of its potential benefits, MySQL replication can cause serious trouble, especially in complex environments. Follow the advice here to get off to a healthy start.
When you're choosing a database, you're making a long-term decision, because changing your mind later is difficult and expensive. You want to get it right the first time. Two popular open source databases, MySQL and PostgreSQL, are often the final two candidates when admins are preparing a new rollout. This high-level overview of these two open source powerhouses should help you choose which is more appropriate for your needs.
Database programmers and administrators often need to design SQL queries against database structures with which they're not intimately familiar. By using a visual query designer, programmers and admins can cut down on coding errors and save time. FOSS tool phpMyAdmin offers a simple builder that can help anyone design MySQL queries. Here's how to get started designing basic queries visually in phpMyAdmin.