Accessing an Existing PostgreSQL Connection from Lua

“Accessing a PostgreSQL database from Lua is easy, just ‘require ‘pgsql’, then do a pgsql.connectdb() and start executing queries. But this assumes that your program is written entirely in Lua, which might not always be the case.

This article describes techniques to access an existing PostgreSQL connection from Lua, i.e. a connection that was created in a C program that embeds a Lua interpreter. This is the typical use case when Lua is being used as an embedded language for extensions (and which is what Lua was designed for in the first place).

It is assumed that a software written in the C language embeds a Lua interpreter. A connection to a PostgreSQL server is made in the C code and a Lua script accesses this connection using the luapgsql module found at…”

Accessing PostgreSQL from Lua

“Lua is an efficient and easy to learn scripting language that has been designed to be be embedded into other software. It is being used as an extension language in many commercial and open source products. Lua programs are internally compiled to a bytecode which is then executed by a very fast “virtual machine”. Lua offers a very easy to use interface to software written in C, which allows the creation of extension modules, or bindings to other pieces of software, using the C language.

 The core of our products is written in C and PostgreSQL is the database of choice. Most extensions to the software, and more recently complete applications, are written in the Lua language. These extensions or applications access internal state of the C core using Lua bindings. While we used Lua to access PostgreSQL since many years already, the Lua interface to PostgreSQL was relatively simple and offered only a subset of libpq’s functionality. In preparation of a talk for PGConf.EU 2012, I decided some time ago to make the Lua PostgreSQL binding functionally equivalent to libpq. The old interface lacked a lot of support functions, asynchronous connection and command execution, support for asynchronous notifications etc…”

Understanding Postgres Performance

“The typical rule for most applications is that only a fraction of its data is regularly accessed. As with many other things data can tend to follow the 80/20 rule with 20% of your data accounting for 80% of the reads and often times its higher than this. Postgres itself actually tracks access patterns of your data and will on its own keep frequently accessed data in cache. Generally you want your database to have a cache hit rate of about 99%. You can find your cache hit rate with:…”

PostgreSQL Database Modeler

“Easily create and edit database models with simple and intuitive interface. The pgModeler have forms that highlight fields that must be filled to provide the correct generation of SQL code.”

“Built over Qt framework pgModeler can be compiled under Windows, Linux and MacOSX systems. The build scripts are easily configurable to resolve specifics dependencies on each system.”

“Model once and export to multiple versions. The pgModeler has a method of generating code that allows the created model to be exported to different versions of PostgreSQL.”

MySQL vs PostgreSQL

MySQL vs PostgreSQL is a decision many must make when approaching open-source relational databases management systems. Both are time-proven solutions that compete strongly with proprietary database software. MySQL has long been assumed to be the faster but less full-featured of the two database systems, while PostgreSQL was assumed to be a more densely featured database system often described as an open-source version of Oracle. MySQL has been popular among various software projects because of its speed and ease of use, while PostgreSQL has had a close following from developers who come from an Oracle or SQL Server background.

These assumptions, however, are mostly outdated and incorrect. MySQL has come a long way in adding advanced functionality while PostgreSQL dramatically improved its speed within the last few major releases. Many, however, are unaware of the convergence and still hold on to stereotypes based on MySQL 4.1 and PostgreSQL 7.4. The current versions are MySQL 5.6 and PostgreSQL 9.1.4…”

Database test: MySQL vs. PostgreSQL

“PostgreSQL might be called a textbook example of a good implementation of multithreading, making it the ideal application for the UltraSparc T1 to show what it’s made of. As long as this does not require too much computing power, but software is capable to split itself effortlessly into independent parts, the T2000 is in its element. The MySQL 5.0.20a graph shown below (the same as on the previous page except for the scale adaptation) illustrates the contrast once more, and demonstrates the importance of not just having multithreading in the software, but also a well-scaling implementation of it…”

The Design Of SQLite4

“SQLite4 is an alternative, not a replacement, for SQLite3. SQLite3 is not going away. SQLite3 and SQLite4 will be supported in parallel. The SQLite3 legacy will not be abandoned. SQLite3 will continue to be maintained and improved. But designers of new systems will now have the option to select SQLite4 instead of SQLite3 if desired.

SQLite4 strives to keep the best features of SQLite3 while addressing issues with SQLite3 that can not be fixed without breaking compatibility. Among the features that will remain in common between SQLite3 that SQLite4 are…”

If all these new DBMS technologies are so scalable, why are Oracle and DB2 still on top of TPC-C?

In the last decade, database technology has arguably progressed furthest along the scalability dimension. There have been hundreds of research papers, dozens of open-source projects, and numerous startups attempting to improve the scalability of database technology. Many of these new technologies have been extremely influential—some papers have earned thousands of citations, and some new systems have been deployed by thousands of enterprises.

So let’s ask a simple question: If all these new technologies are so scalable, why on earth are Oracle and DB2 still on top of the TPC-C standings? Go to the TPC-C Website with the top 10 results in raw transactions per second. As of today (May 16th, 2012), Oracle 11g is used for 3 of the results (including the top result), 10g is used for 2 of the results, and the rest of the top 10 is filled with various versions of DB2. How is technology designed decades ago still dominating TPC-C? What happened to all these new technologies with all these scalability claims?…”

Sharding Postgres with Instagram

“On Tuesday last week we had a terrific SFPUG meeting at which Mike Kreiger of Instagram explained how they grew and eventually sharded their 2TB of Postgres data to support 27 million users.  It’s a great presentation which explains the growth process of a successful web/mobile startup, as well as horizontally scaling PostgreSQL. Yes, you too can use PostgreSQL to make One Billion Dollars!
Video is on UStream.   Sorry you can’t see the slides on the video; we had technical issues with the camera.  Slides are here, you can click along with Mike talking on your own…”