I go to a lot of conferences. If you ask my co-workers, I probably go to too many conferences. Going to a lot of conferences, I get a chance to see a lot of keynotes, closing keynotes, and plenary discussions. Different conferences have different keynotes, but the one thing that sticks out in my head is that the keynotes and opening talks at a conference set the mood for the entire event.

Conference organizers, take note: we form our impressions based on the first things we see. For first time visitors, the first thing they see at a conference is frequently the keynote. It sets the stage for the learning they’re about to experience. Three conferences stand out this year for their high quality keynotes: OSCON, CodeMash, and Surge

What Makes For a Good Keynote?

I don’t know what makes for a good keynote, but I do know that it should reflect your conference. The keynote sets the mood; attendees get a feel for what their day is going to be like at a conference based on how they feel after the keynote. Bombard them with two hours of marketing material and they’re not going to feel good about the rest of their day. Reward them with two hours of intellectually stimulating content and they’re going to look forward to another six hours of learning.

Attending OSCON is a heady experience – there are concurrent sessions on different topics. I could go from a talk about Big Data to Go, Google’s new programming language, to a talk about HTML and CSS in a two hour time period. The keynotes were no different. They showcased both the breadth of interests present at the conference. Talks ranged from DIY biotech startups to open source community to launching robots into space.

Surge featured a keynote from Bryan Cantrill talking about various failures throughout his career. As a conference of operations staff and professional generalists, Bryan’s remarks rang true with all of the attendees. We’ve all been in an “oh shit” situation where millions of dollars hang in the balance based on the code we’ve written and the actions we’re about to take. Bryan summed up the feel of the conference and set the stage for the next few days of learning.

CodeMash is a great event that’s run outside of Cleveland in the winter. If you’ve ever been to Cleveland in January, you’ll know that the primary reason to visit Cleveland in January is to remind yourself why you live somewhere else. There was several feet of snow on the ground and my car was indistinguishable from a snow bank. CodeMash is renowned as a fun and educational conference. Chad Fowler delivered a hilarious and topical keynote about moving beyond hearsay and misunderstanding and opening your eyes to the world around you.

What Do All Three Conferences Share?

The common theme is that all three conferences are community conferences. Sure, OSCON is put on by a huge book publisher but it wouldn’t be a success without the people who volunteer to make it a success. OmniTI support Surge, but they aren’t the only sponsor; Surge was a vendor agnostic event. And CodeMash is put on by a non-profit group with the goal of making the event as cheap as possible for attendees. These three events are built for the community.

All three keynotes opened my eyes to the conferences. I knew what to expect: the stage was set. A good keynote sets the expectations of attendees. A keynote doesn’t have to be timeless, there is a place for a product demonstration, but a keynote should say something. When a keynote says “We have you trapped, watch this demonstration,” attendees notice: they shuffle papers, they play games on their phone, they do anything possible to mentally escape from the situation. Unfortunately, it does a disservice to the conference because most attendees won’t remember who gave a bad keynote, they’ll just remember that a conference had a bad keynote.

When a keynote is good, people notice. They sit up and take part. They cheer, they clap, they tweet, and they blog. The most important part, though, is that a good keynote draws the audience in account and makes them more than a passive set of ears; the audience is brought into the fold and becomes a member of an exclusive community open only to the people in the room. Nobody else can take part. A good keynote – like a good conference – embraces, entertains, and educates.

A bad keynote is a commercial that I can’t turn off.

Why would anyone want to use PostgreSQL instead of SQL Server? There are a lot of factors to consider when choosing how to store your data. Sometimes we need to look deeper than the standard choice and consider something new. If you’re starting a brand new project, where should you store your data? Here are ten reasons why you might want to consider PostgreSQL over SQL Server.

Releases Every Year

Let’s face it, waiting three to five years for new functionality to roll out in any product is painful. I don’t want to constantly be learning new functionality, but on the flip side I don’t want to be writing hack solutions to critical business problems because I know something is coming down the pipe, but I can’t wait for a few more years before I implement it myself. Rapid release cycles guarantee that the PostgreSQL development team is able to quickly ship the features that users need and make frequent improvements.

Starting with version 9.0, the PostgreSQL release cycle has switched to a yearly cycle. Before that, PostgreSQL released whenever the features were done. Looking at the major releases on Wikipedia, it’s obvious that major releases still rolled out about once every 18 months. An 18 month release cycle isn’t bad for any software product, much less a mission critical one like a database.

True Serialization

Snapshot isolation guarantees that all reads in a transaction see a consistent snapshot of data. In addition, a transaction should only commit if the ways that it changes data don’t conflict with other changes made since the snapshot was taken. Unfortunately, snapshots allow anomalies to exist. It’s possible to create a situation where two valid transactions occur that leave the database in an inconsistent state – the database doesn’t pass its own rules for data integrity.

Serializable snapshot isolation was added to PostgreSQL in version 9.1. SSI emulates strict serial execution – transactions behave as if they are executing one after another. If there is a conflict, or even a potential conflict, the database engine throws an error back to the caller (who is left to figure out the appropriate next step).

Serializable snapshot isolation sounds painful. The kicker is that it makes it possible for databases to behave in ways that work to guarantee an even stronger level of consistency. Applications can be developed to assume that data modification will fail and subsequently retry failed transactions. The true benefit is that well written software can avoid data inconsistencies and maintain the illusion that all is operating as it should be.

Sane Defaults, Ridiculous Tuning

Okay, to be fair PostgreSQL ships with some ridiculously conservative shared memory settings. Most other PostgreSQL settings are conservative, but general enough for most generic workloads. Many people deploying PostgreSQL will not have to make many changes to PostgreSQL (probably just increasing shared_buffers to 25% of total RAM to start).

Once a PostgreSQL installation is up and running, there are a number of settings that can be changed. The best part, though, is that most of these settings can be changed at the server, database, user, or even individual query level. It’s very common to have mixed workload servers – most activity on the server is basic CRUD, but a small percentage of activity are reports that need to be aggressively tuned. Instead of moving the individual reports out to running on separate space (either separate servers, databases, or even in separate resource pools in the same database), we can simply tune a few queries to use the appropriate parameters including the memory to allocate for sorting and joins.

Unlogged Tables

Are you sick of trying to get minimally logged bulk inserts to work? Me too. Instead of trying various mechanisms to minimally log some tables, PostgreSQL give us option of creating an unlogged table – simply add the UNLOGGED directive to a create table statement and everything is ready to go.

Unlogged tables bypass the write ahead log; they aren’t crash safe, but they’re stupid fast. Data in an unlogged table will be truncated after the server crashes or there is an unclean shutdown, otherwise it’ll still be there. They’re also excluded from replication to a standby server. This makes unlogged tables ideal for ETL or other data manipulation processes that can easily be repeated using source data.

KNN for Geospatial… and More

Yeah, I hear ya, SQL Server will have this soon, but PostgreSQL already has it. If K Nearest Neighbor searches are critical for your business, you’ve already gone through some pain trying to get this working in your RDBMS. Or you’ve given up and implemented the solution elsewhere. I can’t blame you for that – geospatial querying is nice, but not having KNN features is a killer.

PostgreSQL’s KNN querying works on specific types of indexes (there are a lot of index types in PostgreSQL). Not only can you use KNN querying to find the 5 nearest Dairy Queens, but you can also use a KNN search on other data types. It’s completely possible to perform a KNN search and find the 10 phrases that are closest to “ice cream”.

KNN search capability makes PostgreSQL a serious contender for anyone looking at implementing geospatial querying. The additional flexibility puts PostgreSQL in a leadership position for many other kinds of search driven applications.

Transaction-Controlled Synchronous Replication

One of the easiest ways to keep another copy of your database is to use some kind of database replication. SQL Server DBAs will largely be used to transactional replication – a dedicated agent reads the SQL Server log, collects outstanding commands, and then ships them over to the subscriber where they are applied.

PostgreSQL’s built-in replication is closer to SQL Server’s mirroring than SQL Server’s replication (PostgreSQL’s replication has a readable standby). Log activity is hardened on the primary and then streamed to the secondary. This can either happen synchronously or asynchronously. Up until PostgreSQL 9.1, replication was an all or nothing affair – every transaction was either synchronous or asynchronous. Developers can set a specific transaction by setting the synchronous_replication configuration value for that single transaction. This is important because it makes it possible to write copious amounts of data to logging tables for debugging purposes but not have performance be impacted by synchronously committing writes to the log tables.

Any time we have more choice in how we develop our applications, I’m happy.

Writeable CTEs

CTEs are great for reads, but if I need to do something more complex with them, there are other issues involved. An example is going to make this much easier. Let’s say I want to delete stale data, but I want to store it in an archive table. To do this with SQL Server, the easiest route (from a development standpoint) is going to be to elevate my isolation level to at least snapshot, if not serializable, and use isolation levels to guarantee that no data will be changed. I could also load the PK value of the comments to be deleted into a temp table and reference that multiple times.

Both methods work, but both methods have problems. The first requires that the code be run in a specific isolation level. This relies on specific settings to be in place that may not be available. The code could also be copied out of the procedure and run in SSMS, leading to potential anomalies where a few rows are deleted but not archived. That’s no big deal for spam comments, but it could be critical in other situations. The second method isn’t necessarily bad, there’s nothing wrong with it, but it involves extra code noise. That temporary table isn’t necessary to solve our problem and is a byproduct of dealing with different isolation levels.

PostgreSQL has a different way to solve this problem: writeable CTEs. The CTE is constructed the same way it would be constructed in T-SQL. The difference is that when we’re using PostgreSQL, the data can be modified inside the CTE. The output is then used just like like the output of any other CTE:

CREATE TABLE old_text_data (text_data text); 

WITH deleted_comments AS (
  DELETE FROM comments
  WHERE comment_text LIKE '%spam%'
  RETURNING comment_id, email_address, created_at, comment_text
)
INSERT INTO spam_comments
SELECT *
FROM deleted_comments

This can be combined with default values, triggers, or any other data modification to build very rich ETL chains. Under the covers it may be doing the same things that we’ve outlined from SQL Server, but the conciseness is beneficial.

Extensions

Ever want to add some functionality to SQL Server? What about keep up to date on that functionality? This can be a huge problem for DBAs. It’s very easy to skip a server when you roll out new administrative scripts across your production environment. Furthermore, how do you even know which version you have installed?

The PostgreSQL Extension Network is a centralized repository for extra functionality. It’s a trusted source for open source PostgreSQL libraries – no sneaky binaries are allowed. Plus, everything in PGXN is versioned. When updating PGXN provided functionality, the extension takes care of the update path for you – it knows how to make sure it’s up to date.

There are extensions for things ranging from K-means clustering, Oracle compatibility functions, to remote queries to Amazon S3.

Pushing this functionality out into extensions makes it easy for developers and DBAs to build custom packages that look and act like core functionality of PostgreSQL without trying to get the package through the PostgreSQL release process. These packages can then be developed independently, advance at their own rate, and provide complex functionality that may not fit within the release plan of the PostgreSQL core team. In short, there’s a healthy ecosystem of software being built around PostgreSQL.

Rich Temporal Data Types ###

One of my favorite features of PostgreSQL is the rich support for temporal data types. Sure, SQL Server 2008 finally brought some sophistication to SQL Server’s support for temporal data, but it’s still a pretty barren landscape. Strong support for temporal data is critical in many industries and, unfortunately, there’s a lot of work that goes on in SQL Server to work around the limitations of SQL Server’s support for temporal data.

PostgreSQL brings intelligent handling of time zones. In addition to supporting the ISO 8601 standard (1999-01-08 04:05:06 -8:00), PostgreSQL supports identifying the time zone by an abbreviation (PST) or by specifying a location identifier (America/Tijuana). Abbreviations are treated like a fixed offset from UTC, location identifiers change with daylight savings rules.

On top of time zone flexibility, PostgreSQL has an interval data type. The interval data type is capable of storing an interval of up to 178,000,000 years with precision out to 14 digits. Intervals can measure time at a number of precisions from as broad as a year to as narrow as the microsecond.

Exclusion Constraints

Have you ever tried to write any kind of scheduling functionality using SQL Server? If you have, you’ll know that when you have business requirements like “two people cannot occupy the same conference room at the same time”, you’ll know that this difficult to enforce with code and usually requires additional trips to the database. There are many ways to implement this purely through application level code and none of them lead to happy users or developers.

PostgreSQL 9.0 introduced exclusion constraints for columns. In short, we define a table and then add an additional constraint that includes a number of checks where at least one of the checks is false. Exclusion constraints are supported under the hood by indexes, so these operations are as quick as our disks and the index that we’ve designed. It’s possible to use exclusion constraints in conjunction with temporal or geospatial data and make sure that different people aren’t reserving the same room at the same time or that plots of land don’t overlap.

There was a presentation at the 2010 PGCon that going into the details of exclusion constraints. While there is no video, the slides are available and they contain enough examples and explanations to get you started.

Bonus Feature – Cost

It’s free. All the features are always there. There are no editions of PostgreSQL – the features always exist in the database. Commercial support is available from a number of companies, some of them even provide additional closed source features, but the core PostgreSQL database is always available, always free, and always contains the same features.

Getting Started

Want to get started with PostgreSQL? Head on over to the download page and pull down a copy for your platform of choice. If you want more details, the documentation is thorough and well written, or you can check out the tutorials in the wiki.

If you’re only indexing to make reads faster, you need to think again. Indexes can make every operation in the database faster, even deletes.

The Problem: Deletes are Very Slow

I was lazily researching new development techniques one day when I received an email from a client asking why deletes could be slow. I rattled off a few quick possibilities and promised that I’d look into it as soon as I was able to. Due to the vagaries of travel, it took me more time than I’d expected to dig into the problem, but I found something interesting that I should have come to mind right from the start.

This database contains some hierarchical data. My initial thought was that there was a cascading delete taking place in the hierarchy. After some initial checks into cascading deletes, fragmentation, statistics, and triggers, I ran an actual delete on a test system and found something very interesting – almost all of the time spent deleting the row was spent performing a clustered index scan on a different table.

What Happens During a Delete?

When you try to delete a row, a few things happen. SQL Server says “OK, let’s make sure that we can actually delete this row, what else depends on it?” SQL Server will check for dependent rows by examining all foreign keys. It will then check any related tables for data. If there is an index, SQL Server will use that index to check for related data. If there isn’t an index, though, SQL Server will have to scan the table for data.

Deletes and Table Scans

Don’t believe me? Try this out yourself.

Make a new database. Copy data in from the Sales.SalesOrderHeader and Sales.SalesOrderDetail tables in AdventureWorks. I use the Import Data wizard to quickly copy data from one database to another.

ALTER TABLE Sales.SalesOrderHeader
ADD CONSTRAINT PK_SalesOrderHeader PRIMARY KEY (SalesOrderID);

ALTER TABLE Sales.SalesOrderDetail
ADD CONSTRAINT PK_SalesOrderDetail PRIMARY KEY (SalesOrderDetailID);

ALTER TABLE Sales.SalesOrderDetail
ADD CONSTRAINT FK_SalesOrderDetail_SalesOrderHeader
FOREIGN KEY (SalesOrderID) REFERENCES Sales.SalesOrderHeader(SalesOrderID)
ON DELETE CASCADE;

With these three statements in place, we’re able to create a situation where SQL Server has to perform a full table scan just to delete a single row. Make sure you’ve told SQL Server to include the actual execution plan and run this:

DELETE FROM Sales.SalesOrderHeader
WHERE SalesOrderID = 51721;

Once that query runs, the execution plan is going to look a bit like the execution plan below. If you add it up, 99% of the work comes from finding the rows to delete in the SalesOrderDetail table and then actually deleting them.

Making Deletes Faster

How would we go about making deletes like this faster? By adding an index, of course. Astute readers will have noticed the missing index information in that execution plan I took a screenshot of. In this case, the missing index looks something like this:

CREATE NONCLUSTERED INDEX IX_SalesOrderDetail_SalesOrderID
ON Sales.SalesOrderDetail(SalesOrderID);

Before adding the index, the query had a cost of 2.35678. After adding the index, the delete has a cost of 0.0373635. To put it another way: adding one index made the delete operation 63 times faster. When you have a busy environment, even tiny changes like this one can make it faster to find and delete records in the database.

What’s All of This Mean?

When you’re looking into database performance problems, remember that you aren’t always reading just to return data to the user, sometimes you need to find data in order to delete it. Even when we’re trying to get rid of data, it can be helpful to have an index to make deletes go faster.

...
If you like our posts and free webcasts, you'll love working with us.

Everyone poops and everyone grows. We all reach that growth stage in our own time, but sooner or later we all encounter it. Once you reach a growth stage you will experience some of these common emotions.

Denial

It’s 8:37AM and your developers just barged into your cubicle yelling, ”The database can’t handle the load!” What’s your first reaction? If you’re like me, you probably threw a stapler at them. If you’re a bit more level-headed, you most likely said something like, “That’s unpossible!”

Congratulations, you’re in the first stage of database growth: denial. It’s a natural response to hearing that your application has grown beyond the physical confines of your database. The usual reaction is to quickly scramble for historical performance data, your performance tuning scripts, and server level performance enhancements.

You might be successful; you may be able accomplish enough at this stage to fight off performance problems for a few more months. If trends continue you’ll have to get over your denial and move on to the next stage of your growth.

Anger

You’ve accepted that you can’t keep denying the problem. You’ve even accepted that there is a problem. In a meeting with the architecture review team, you’ve probably even said, “Let’s face it, there’s a problem.” Everyone nodded their heads in agreement and you got a smile on your face until the senior architect said, “After analyzing the network traffic and feature usage, we realize that we need to make changes in our architecture. We’re going to split the application into feature silos; each silo will have a separate database.”

And then your anger sets in. “Do you have any idea how much work that’s going to be? This will destroy normalization,” you shout. “We can’t lose integrity throughout the entire application!”

The architect smiles at you and says, “We won’t be losing critical integrity, core writes will go to a master service where they’ll be replicated to the feature database servers. You’ll have all the integrity that’s necessary for each feature to function. The developers will change how a few writes are performed in the middle tier, but that should be the biggest change.”

Reluctantly, you agree. You return to your desk, grumbling, and begin plans to rip apart the beautifully normalized schema that you worked hard to design with your database design team. You’re angry about the work you need to put into denormalizing the database and the scope of your changes compared to the development team.

Bargaining

You’ve successfully redesigned your database by splitting out features into separate databases. This let you identify the main performance problems and move them off to their own servers. It wasn’t the solution you liked, but you came to terms with it. Heck, the migration even went smoothly. After your initial misgivings and anger you were able to implement a good solution.

Unfortunately, your design couldn’t withstand the forces of a free market economy. After being mentioned on Oprah, the slow and steady growth curve has become a spike: it’s the dreaded hockey stick of scale!

You’re prepared – you bust out your scripts and monitoring tools and get to work. Within a few hours you’ve identified the slowest queries, the crappiest indexes, and come to the conclusion that the biggest bottleneck is the three year old server and five year old SAN.

Armed with your facts, delightful graphs, and hardware requirements you head up to your manager’s office to request new hardware. And then the bargaining begins. You know you’ll get enough hardware in the end, but at what cost?

Depression

You’re running on brand new servers and big fast SAN, what could possibly go wrong? Unprecedented success. Your company continues to grow at a ridiculous rate. It’s good for the stockholders, it’s good for the executives, and it’s good for your bonuses. Unfortunately, it’s bad for your schedule.

With newfound success comes a host of new problems: locking, blocking, and now deadlocks. You could configure read slaves using replication (despite the nasty reputation). If it’s sometime after the first quarter of 2012 you could use Denali’s availability groups to scale out reads and provide some additional safety features. Either way, you know that there’s no way one server per feature is going to keep up with the load and you know that you can’t split up your features any more than you already have.

You’ve resigned yourself to long nights of baby sitting replication, custom solutions, and waiting for new versions of SQL Server to bring you much needed features.

Acceptance

Over time you’ve turned what you thought was a Rube Goldberg machine of database scalability into a high performance, high availability solution. After configuration replication and, eventually, availability groups you worked with the architecture team to design more ways to scale out – writes are being routed at the application level using a process known as sharding. Using sharding you’re now able to grow different portions of different features at different rates of growth. You no longer have to scale up your entire infrastructure in response to the needs of a small percentage of the users; they can be broken out and scale at their own rate.

Moving On

Of course, not all stages of growth will happen in this order. You might even skip some of them altogether. No matter how you grow, these mechanisms for dealing with growth are all valid and will serve you well.

...
If you like our posts and free webcasts, you'll love working with us.

Did you know that SQL Server’s locking has a name? It’s called two-phase locking. If we’re really getting specific about it, SQL Server uses what’s called strong strict two-phase locking or SS2PL. We’ll get there in a few minutes, right now we’re going to take a look at what makes up two-phase locking.

But First, Some History

The earliest references to two-phase locking (2PL) that I can find is in Bernstein and Goodman’s 1981 paper Concurrency Control in Distributed Database Systems. The authors examine multiple 2PL techniques for synchronizing transactions, alternatives using timestamp ordering, integrated concurrency control methods combining 2PL and timestamp ordering, before mentioning some other also-rans in an appendix. (This may also be the first use of the now tired example involving bank balances and transactions.)

Even though his paper focuses on distributed databases, it’s still valuable because it sets up a common vocabulary for things to come. There’s a lot of theory in here. It’s interesting, but it’s still mathematical theory and most people glaze over when they see that sort of thing.

The Basics of Two-Phase Locking

2PL works by being explicit about who is doing what to whom. Or, in clearer terms:

(1) Different transactions cannot simultaneously own conflicting locks; and (2) once a transaction surrenders ownership of a lock, it may never obtain additional locks

Locks conflict if they’re not compatible with each other. That is to say that if both locks are on the same thing and at least one lock is a write lock, that’s a conflict. (Astute readers will notice that readers won’t block readers.) Anyone who has used SQL Server for a while will be familiar with what happens when we try to acquire a conflicting lock: we wait. Sometimes, we’ll wait for a good long while and a lock will eventually be released. Sometimes, we would end up waiting forever (a deadlock) if the lock manager didn’t step in and kill off one of the processes.

Why Is It Called Two-Phase Locking?

This process is called two-phase locking because there are two distinct phases. The two rules above hint at them, but in effect a transaction can either be issuing locks or releasing locks, it cannot be in stasis.

In reality, this works more like the following:

1. A statement is issued by an application.
2. SQL Server compiles the statement and determines the types of locks that are needed to most efficiently satisfy the query.
3. Once all locks are acquired, the transaction is in a ready state.
4. SQL Server will begin operations and release locks as appropriate.

If you’re really playing along at home, at some point you’ll figure out that lock acquisition and release varies by isolation level and results in the various phenomena that you see in each of the isolation levels. If you noticed that on your own, give yourself a gold star. If you didn’t, you’re normal.

Deadlocks

Deadlocks are, sadly, a byproduct of a 2PL mechanism. Most of the literature talks about things like transaction graphs (or waits-for graphs) and edges. The Great Triumvirate illustrates this perfectly.

Daniel Webster is grooming his eyebrows in a fashion that almost became known as ‘the Webster’. He’s impatiently waiting for Henry Clay to finish with the funny pages, but he won’t release his comb until he has the funny pages. Henry Clay is reading what passes for the funny pages in 1836 (hint: it’s the New Yorker). Henry is well pleased with himself, but he’s waiting on John C Calhoun to relinquish the volumizer before he will give up the funny pages. John C Calhoun is volumizing his hair to lofty heights but he really wants Daniel Webster’s eyebrow comb. Everyone is waiting on something from everyone else, but nobody will give up first. This is a deadlock. Okay, maybe that’s not really a deadlock, but it’s better than a waits-for graph. A combination of techniques can be used to determine which transactions will be killed off. SQL Server will typically use the least expensive transaction (in terms of optimizer cost). SQL Server avoids some problems, too, by not attempting to retry transactions.

Strict Two-Phase Locking

So far, we’ve only talked about 2PL, but I said SQL Server is SS2PL. Between the two is Strict Two-Phase Locking or S2PL. S2PL is like 2PL but there are some more rules.

To be considered S2PL, a transaction has to follow the rules of 2PL (sound like normalization rules?). In addition, a transaction also has to release write locks after the transaction has ended and either been rolled back or committed. Interestingly, nothing is directly said about read locks in S2PL. However, read locks can be released as they are no longer needed during the transaction.

Strong Strict Two-Phase Locking

SS2PL (called S2PL in Concurrency Control and Recovery in Database Systems) requires that the locks are only released after the transaction is finished and has been committed or rolled back. SS2PL provides serializability – database transactions appear as if they are atomic and occurring in complete isolation from one another. Serializable transactions are interesting because for a database to truly be serializable, it should be possible to process transactions in any order, s long as the effective is the same as that of some serial order (not any, just some).

Why the Devil Should I Care?

Locks are the primary way that SQL Server manages concurrency. This is a limitation of the database. New CPUs, a SAN, more disks, solid state hardware, and more RAM will not remove locking, blocking, and deadlocks from the database. New CPUs, a SAN, more disks, solid state hardware, and more RAM will make locking, blocking, and deadlocks happen faster. They may happen so fast, that you don’t really notice the problem until it’s growing out of control.

Combine a healthy knowledge of how locking operates with a working knowledge of isolation levels and some allegedly insurmountable application problems can be resolved through simple changes in the data layer.

PASS 2011 Session Abstracts

Every November, a bunch of database geeks gather for the Professional Association for SQL Server’s (PASS) international Summit. This year it’s going to be held October 11-24 in Seattle, Washington. I didn’t submit last year since I was involved with the abstract selection process. This year I’m not involved, so I decided to submit a few abstracts.

Rewrite Your T-SQL for Great Good!

Refactoring SQL is not like refactoring application code. This talk will cover proven SQL refactoring techniques that will help you identify where performance gains can be made, apply quick fixes, improve readability, and help you quickly locate places to make sweeping performance improvements. Jeremiah Peschka has years of hands on experience tuning SQL applications for performance, throughput, and concurrency.

Why I submitted this session: I submitted this session because it’s a fun session to give, it crosses boundaries between DBA and developer, and I’ve given it a few times before.

The Database is Dead, Long Live the Database

If relational databases are so great, why are people talking about NoSQL? Shouldn’t we explore other ways to store and manipulate data? We’ll look at four scenarios – caching, session state, flexible data models, and batch processing – and discuss how traditional databases perform in each situation and what other options exist on the market. At the end of this session, attendees will have a better understanding of how different workloads perform in RDBMSes, best practices, and alternative storage solutions to make your life easier.

Why I submitted this session: I wrote this session when I was asked to speak at Stir Trek: Thor Edition. Writing it has been a lot of fun and has started the process of crystallizing a lot of the ideas in my head around data storage. This talk focuses on a few areas where relational databases don’t do a good job and proposes solutions to pick up the slack.

Rules, Rules, and Rules

Computers are governed by the rules of physics: electrons, drive heads, and disk platters can only move so fast. Database systems are built according to those rules: memory is faster than disk which is faster than the network. Database schemas and queries are built within the rules of database systems. You will hit the limitations of these rules. If you know what the rules are and why they are in place, you’ll know when it’s time to break them… and how to succeed.

Why I submitted this session: This is also a session I’ve given before. Andy Leonard asked me to speak at the inaugural SQLPeople event about my passion. One of my passions is learning about computer science and how it can be applied to databases in a practical way. (There’s a lot of purely theoretical information that only matters when you’re implementing an RDBMS.) This session is an extended version of the talk I gave at SQLPeople. I’m incredibly excited about it and I’ll be bummed if it doesn’t get accepted.

The Other Side of the Fence: Lessons Learned from Leaving Home

Traveling the world changes your outlook on things, home just doesn’t look quite the same once you’ve traveled. The same can be said for SQL Server; working with databases like PostgreSQL, Cassandra, and Hadoop forced Jeremiah Peschka to re-learn concepts that he took as a given. Learn from his experiences about the importance of understanding isolation levels, data storage and retention, querying patterns, and even database functionality in this talk drawn from his experiences as a DBA, consultant, and developer.

Why I submitted this session: There’s a theme going on here – I’ve learned a lot about database and application design and how it’s sometimes necessary to move outside of my comfort zone to build an effective system. This is a 3.5 hour session that will cover a lot of features in SQL Server. I learned a lot working with other databases, and I hope that this information helps some other people.

The fastest query is one you never execute.

The premise is that one of the slowest parts of starting up an application isn’t starting the application itself, it’s loading the initial application state. This can become a problem when you’re loading many copies of your application on many servers, especially you’re in the cloud and paying for CPU cycles. In that article, a commenter proposes reading application start up state from a serialized blob; basically a chunk of memory written to disk. The trick is that the serialized blob is stored in cache rather than on disk or in a database. Sometimes you need to hit disk in order to refresh the cache, but the general idea is that all configuration info is stored in a single binary object that can be quickly read and used to start up an application to a known good state.

Caching for More Than Start Up Times

Once you start caching application start state, it’s natural to look for more places to introduce additional caching. Remember, the fastest query is the one that you never execute.

Most people already know that they can add caching to their application to improve performance and get around slower parts of the system. There are a number of well understood design patterns that focus around caching and its place in software architecture. A lot of people don’t take this one step further and use caching as a trick to avoid down time when they roll out updates.

You might be thinking “Wait a minute, doesn’t my database/SAN/operating system have some kind of cache?” You’re right, it does. Storage cache is your last line of defense before reading from disk. Why not cache things in your application and skip the network hit?

So what happens when you need to update the application? In the past you probably scheduled an outage in the middle of the night. Or maybe you performed rolling outages from server to server and then slowly brought features online across groups of servers. However you did it, it’s complicated, requires down time, and you need to have a rollback plan; rollbacks on large databases can take a lot of time.

What if instead of just caching configuration to avoid slow start up, you start caching all data (or as much as can fit into memory)? You’re doing that already, right? Why mention it again?

If you’re caching data already, it seems logical that your application is written with multiple tiers. Those tiers are probably divided out by application or by service. If so, there’s a lot of logical separation between different features and functionality. You might even be calling a read/write API as if it were a service provided by a third party. This is a perfect example of how you can cache your reads and avoid hitting lower layers of the application; the front end never needs to know that anything exists apart from the services that provide data.

If you can cache data at the service level, you can theoretically take your back end systems offline for maintenance and bring them back online with minimal disruption to your users. Ideally, there would be no disruption. You could queue up modifications during your maintenance window and then commit them to the database once the updated database, services, or features are back online.

The Beauty of Isolation

By isolating features and layers from each other, you can make your applications more responsive. Rather than relying on servers to respond quickly during application start times, you can make it possible to load binary configuration data from cache. Frequently run queries can be served even faster by caching results in memory. Down times can even be avoided by caching reads and writes during the maintenance window. Of course, caching writes can be difficult. You can start by caching reads and keep your application up most of your users; it’s better than shutting everyone out completely.

---

To learn more about caching on Windows, read up on AppFabric Cache. On the *nix side of things, there’s the tried and true memcache. More novel and exotic solutions exist, but AppFabric Cache and memcache are great places to get started.

...
If you like our posts, you'll love our free Technology Triage Tuesday webcasts.

I recently covered the internals of a row in PostgreSQL, but that was just the storage piece. I got more curious and decided that I would look into what happens when a row gets updated. There are a lot of complexities to data, after all, and it’s nice to know how our database is going to be affected by updates.

Getting Set Up

I started by using the customer table from the pagila sample database. Rather than come up with a set of sample data, I figured it would be easy to work within an existing set of data.

The first trick was to find a customer to update. Since the goal is to look at an existing row, update it, and then see what happens to the row, we’ll need to be able to locate the row again. This is actually pretty easy to do. The first thing I did was retrieve the ctid along with the rest of the data in the row. I did this by running:

SELECT  ctid, *
FROM    customer
ORDER BY ctid
LIMIT 10 ;

This gives us the primary key of a customer to mess with as well as the location of the row on disk. We’re going to be looking at the customer with a customer_id of 1: Mary Smith. Using that select statement, we can see that Mary Smith’s data lives on page 0 and in row 1

Updating a Row

Now that we know who we’re going to update, we can go ahead and mess around with the data. We can take a look at the row on the disk using the get_raw_page function to examine page 0 of the customer table. Mary Smith’s data is at the end of the page.

Why is Mary’s data the first row in the table but the last entry on the page? PostgreSQL starts writing data from the end of the page but writes item identifiers from the beginning of the page.

We already know that Mary’s row is in page 0, position 1 because of the ctid we retrieved in our first query. Let’s see what happens when we update some of Mary’s data. Open up a connection to PostgreSQL using your favorite interactive querying tool. I use psql on the command prompt, but there are plenty of great tools out there.

BEGIN TRANSACTION

UPDATE  customer
SET     email = 'mary.smith@gmail.com'
WHERE   customer_id = 1 ;

Don’t commit the transaction yet!

When we go to look for Mary’s data using the first select ordered by ctid, we won’t see her data anywhere.

Where did her data go? Interestingly enough, it’s in two places right now because we haven’t committed the transaction. In the current query window, run the following command:

 SELECT ctid, xmin, xmax, * FROM customer WHERE customer_id = 1;

After running this, we can see that the customer’s row has moved off of page 0 and is now on page 8 in slot 2. The other interesting thing to note is that the xmin value has changed. Transactions with a transaction id lower than xmin won’t be able to see the row.

In another query window, run the previous select again. You’ll see that the row is still there with all of the original data present; the email address hasn’t changed. We can also see that both the xmin and xmax columns now have values. This shows us the range of transactions where this row is valid.

Astute readers will have noticed that the row is on disk in two places at the same time. We’re going to dig into this in a minute, but for now go ahead and commit that first transaction. This is important because we want to look at what’s going on with the row after the update is complete. Looking at rows during the update process is interesting, but the after effects are much more interesting.

Looking at page 0 of the customer table, we can see that the original row is still present. It hasn’t been deleted yet. However, PostgreSQL has marked the row as being “old” by setting the xmax value as well as setting the t_ctid value to 00 00 00 08 00 02. This tells us that if we look on page 8 in position 2 we’ll find the newest version of the data that corresponds to Mary Smith. Eventually this old row (commonly called a dead tuple) will be cleaned up by the vacuum process.

If we update the row again, we’ll see that it moves to a new position on the page, from (8,2) to (8,3). If we did back in and look at the row, we’ll see that the t_ctid value in Mary Smith’s record at page 8, slot 2 is updated from 00 00 00 00 00 00 to 00 00 00 08 00 03. We can even see the original row in the hex dump from page 8. We can see the same information much more legibly by using the heap_page_items function:

select * from heap_page_items(get_raw_page('customer', 8));

There are three rows listed on the page. The row with lp 1 is the row that was originally on this page before we started messing around with Mary Smith’s email address. lp 2 is the first update to Mary’s email address.

Looking at t_infomask2 on row 2 we can immediately see two things… I lied, I can’t immediately see anything apart from some large number. But, once I applied the bitmap deciphering technology that I call “swear repeatedly”, I was able to determine that this row was HEAP_HOT_UPDATED and contains 10 attributes. Refer to htup.h for more info about the bitmap meanings.

The HOTness

PostgreSQL has a unique feature called heap only tuples (HOT for short). The HOT mechanism is designed to minimize the load on the database server in certain update conditions:

1. A tuple is repeatedly updated
2. The updates do not change indexed columns

For definition purposes, an “indexed column” includes any columns in the index definition, whether they are directly indexes or are used in a partial-index predicate. If your index definition mentions it, it counts.

In our case, there are no indexes on the email column of the customer table. The updates we’ve done are going to be HOT updates since they don’t touch any indexed columns. Whenever we update a new row, PostgreSQL is going to write a new version of the row and update the t_ctid column in the most current row.

When we read from an index, PostgreSQL is going to read from the index and then follow the t_ctid chains to find the current version of the row. This lets us avoid additional hits to disk when we’re updating rows. PostgreSQL just updates the row pointers. The indexes will still point to the original row, which points to the most current version of the row. We potentially take an extra hit on read, but we save on write.

To verify this, we can look at the index page contents using the bt_page_items function:

SELECT  *
FROM    bt_page_items('idx_last_name', 2)
ORDER BY ctid DESC;

We can find our record by moving through the different pages of the index. I found the row on page 2. We can locate our index row by matching up the ctid from earlier runs. Looking at that row, we can see that it points to the ctid of a row with a forwarding ctid. PostgreSQL hasn’t changed the index at all. Instead, when we do a look up based on idx_last_name, we’ll read from the index, locate any tuples with a last name of ‘SMITH’, and then look for those rows in the heap page. When we get to the heap page, we’ll find that the tuple has been updated. We’ll follow the update chain until we get to the most recent tuple and return that data.

If you want to find out more about the workings of the Heap Only Tuples feature of PostgreSQL, check out the README.

This past weekend I had the honor and pleasure of speaking at the first SQLPeople event in Richmond, Virginia.

The Back Story

Back in February, Andy Leonard asked me to speak at a new event. The idea behind his new event was inspiration. Instead of focusing on educating others, Andy asked us to share our own inspiration. Instead of presenting a seminar or training course, the idea was to talk about my work, my vision, and my passion for database technology (to steal Andy’s own words).

It didn’t take me long to say “Yes!” It’s not every day that I get asked to talk about myself for more than 30 seconds, much less be openly invited to talk about myself with a group of people, slides, a projector, and a microphone. Can you imagine that? 40 minutes of nothing but me? Already, I could see that Andy was a man of vision.

My Original Idea

My original idea was to talk about federated databases, SQL/MED, and hybrid data. I’m really glad that Andy asked me for my abstract a second time; once he did, I couldn’t work those ideas together into anything vaguely resembling a coherent narrative.

The funny thing about inspiration is that it’s different from interest. I think federated databases are interesting; I’ve written about them enough. I think that SQL/MED is really cool; that’s how I dug into federated databases. I certainly think that hybrid data/polyglot persistence/buzzword du jour is an interesting idea; I’m talking about it at Stir Trek.

Ultimately none of these things get me excited at a base level: they’re interesting but not intriguing.

The Evolution

I sat down in front of the computer and brain stormed. I don’t think the idea really gelled until I was furiously re-typing my abstract over and over again. As I wrote, the core idea changed a number of times. Free-writing is a wonderful tool, but it’s incredibly painful when you’re trying to write something that you promised to a dear friend. Especially when you realize how late you are.

As I kept iterating over my original ideas I realized that I wasn’t excited and moved by them. I just kept typing, though. I know from experience that I can iterate through ideas over and over again and eventually get to something great. Turns out that’s exactly what happened. After slapping at the keyboard for a while, I cranked out this beauty:

If you asked Jeremiah Peschka to pick three things he’s interested in about computers, he’d say “data” and then look at you funny. If you asked him again, he’d saying “data design, database design, and designing around the limits of the first two.” This is a rapid tour of the building blocks of databases, how those choices affect what we do with data, and why we have to break the rules from time to time to get things done.

The Talk

I had a lot of fun talking about how different aspects of software design, database design, and hardware design influence the design of databases. The talk covered some of the theoretical underpinnings of databases at a very high level, it only scratched the surface of some of the things that I wanted to discuss. Computer science is such a broad field and even a small piece of it like database design can contain a huge amount of information that it’s difficult to pick and choose the relevant parts.

The twenty minute format was a welcome limitation; it forced me to focus on what was most important in my topic. I had to focus on what inspires me to keep learning and what inspires me to share part of my journey with the other people.

Thank you to everyone who put on the event and everyone who attended. It was a great opportunity to share the things I enjoy about this field.

The Future

You’d think things would end here, after all I gave a talk about my inspiration once, right?

I had so much fun putting the talk together and got so much great feedback from the attendees that I thought I would refine the short talk into a longer version. There’s a lot of stuff that I left out of the presentation. It’s not that it wasn’t relevant, but I had to trim material so I could finish the talk in 20 minutes. I want to revisit the talk an add more material; there’s so much interesting information out there that it seems a shame to not share it.

For best performance, use RAID 10. Always.

Tagged: Erin, Jes.

---

Tom LaRock challenged me to write an 11 word or less blog post. I decided to offer some succinct advice as well as tag two people, all in under 11 words.